Re: AW: how to merge two postgresql server instances into one

On 10/7/21 1:19 AM, Marco Lechner wrote:
> Hi Adrian,
> 
> does this clearify the mission:
> 
> Recent:
> PostgreSQL 11:
>    - Db1_foo
>    - Db2_bar
>    - postgres
>    - template1
> PostgreSQL 12:
>    - Db3_zii
>    - Db4_gee
>    - postgres
>    - template1
> 
> Result after Upgrade:
> PostgreSQL 14:
>    - Db1_foo
>    - Db2_bar
>    - Db3_zii
>    - Db4_gee
>    - postgres (not from PG11/PG12)
>    - template1 (not from PG11/PG12)
> 
> Used extensions: postgis, hstore.

Are you going to be using the same extension versions.
In particular PostGIS?

> But I see, that another task might be to have user/roles from both PG11 and PG12 copied to PG14.

Something like:

pg_dumpall -g -p <version_port> -U postgres -f <version_number>_globals.sql

run against the 11 & 12 clusters will get you the roles. NOTE: this will 
also get you tablespaces so if those are in use that is a consideration.

Then :

psql -d postgres -U postgres -p <14_port> -f <version_number>_globals.sql

will restore them to new cluster. If they are repeated the above will 
throw something like:

ERROR:  role "adrian" already exists

for repeated roles but continue on to fill in the roles that don't exist.

As to rest see Heikki Pernu's post.


> 
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com