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(dot)klaver(at)aklaver(dot)com