PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it…

Assuming we have our old server (pg-old) and a new server (pg-new) to act as the subscriber, and our database to be copied is named myapp and stored in /db/pgdata, follow these instructions:

1. Bootstrap an empty PostgreSQL instance on pg-new using initdb as the postgres user:

initdb -D /db/pgdata

2. Copy postgresql.conf and pg_hba.conf from pg-old to pg-new.
3. Copy the .pgpass file from pg-old to pg-new.
4. Dump all global objects on pg-old from pg-new as the postgres user:

pg_dumpall -g -h pg-old -U rep_user \ -f global_objects.sql --database postgres

5. Import all global objects in pg-new as the postgres user:

psql -f global_objects.sql

6. Create the empty myapp database as the postgres user on pg-new:

createdb myapp

7. Dump all Data ...