AW: how to merge two postgresql server instances into one

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(dot)klaver(at)aklaver(dot)com>
Gesendet: Mittwoch, 6. Oktober 2021 17:33
An: Marco Lechner <mlechner(at)bfs(dot)de>; pgsql-general(at)lists(dot)postgresql(dot)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(dot)klaver(at)aklaver(dot)com