Re: AW: how to merge two postgresql server instances into one - Mailing list pgsql-general

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