how to merge two postgresql server instances into one

On 10/6/21 2:58 AM, Marco Lechner wrote:

Hi,

how to merge two postgresql server instances into one? I have a
postgresql-11 and -12 instance on my server and want to upgrade both
(pg_upgrade?) into one postgresql-14 instance.

The bigger issue will be the merge. Define what that means in this context?

The postgresql-14 instance does not exist yet (fresh install of
postgresql-14 server packages). Using pg_upgrade to upgrade the larger
one and pg_dumpall/pg_restore for the smaller one? Or are there any
other nice recommended workflows?

i.A. Dr. Marco Lechner

Leiter Fachgebiet RN 1 │ Head RN 1

--

Bundesamt für Strahlenschutz │ Federal Office for Radiation Protection

Koordination Notfallschutzsysteme │ Coordination Emergency Systems │ RN 1

--
Adrian Klaver
adrian.klaver@aklaver.com

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.
But I see, that another task might be to have user/roles from both PG11 and PG12 copied to PG14.

-----Ursprüngliche Nachricht-----
Von: Adrian Klaver <adrian.klaver@aklaver.com>
Gesendet: Mittwoch, 6. Oktober 2021 17:33
An: Marco Lechner <mlechner@bfs.de>; pgsql-general@lists.postgresql.org
Betreff: Re: how to merge two postgresql server instances into one

WARNUNG: Diese E-Mail kam von außerhalb der Organisation. Klicken Sie nicht auf Links oder öffnen Sie keine Anhänge, es sei denn, Sie kennen den/die Absender*in und wissen, dass der Inhalt sicher ist.

On 10/6/21 2:58 AM, Marco Lechner wrote:

Hi,

how to merge two postgresql server instances into one? I have a
postgresql-11 and -12 instance on my server and want to upgrade both
(pg_upgrade?) into one postgresql-14 instance.

The bigger issue will be the merge. Define what that means in this context?

The postgresql-14 instance does not exist yet (fresh install of
postgresql-14 server packages). Using pg_upgrade to upgrade the larger
one and pg_dumpall/pg_restore for the smaller one? Or are there any
other nice recommended workflows?

i.A. Dr. Marco Lechner

Leiter Fachgebiet RN 1 │ Head RN 1

--

Bundesamt für Strahlenschutz │ Federal Office for Radiation Protection

Koordination Notfallschutzsysteme │ Coordination Emergency Systems │
RN 1

--
Adrian Klaver
adrian.klaver@aklaver.com

I would suggest you setup a new virtual server, LXC container or docker
instance with the new
version of pg14, pg_dump the old databases(one by one) and then import
them to the new ones.
That way, you do not have to mess with multiple installations of
Postgres and can use minimal
extra resources and can have a later(hopefully more secure) underlying
OS and libraries for the new version.

You may have to do multiple attempts until you get the dumps cleaned of
non-relevant information
and/or have correct extensions/whatever in place on the target instance.
You can keep the old databases online the whole time and as long as
possible until you have
the whole process ready.

The feasibility of the actual final migration depends of course on
whether you need to have the databases
online all the time or is there downtime allowed. Also, the amount of
data is of importance as well.
(Database restore may take a very long time. )

If you need to keep the databases online all the time but can keep them
read-only, this process would
still work. I see no practical way of doing this if you need to keep
them online and allow modifications while
doing the migration. Possibly it could be done by setting up a
replication via Slony or something similar but
this is very complicated given the differences in versions.

If more downtime is allowed, it might also be possible to directly
upgrade one of the instances without
dump/restore cycle, but please have a full backup of all the postgresql
data files and utilities before that.
After upgrade, you could do dump+restore for the other database.

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