Thread: how to merge two postgresql server instances into one
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 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
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 seidenn, 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 7.10.2021 11.19, 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. > 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, essei 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
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