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