PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it...

For this recipe, pgha1 is our provider and pgha2 will remain our subscriber. Follow these instructions on the provider node (unless told otherwise) to copy the pgbench tables and all future changes from pgha1 to pgha2:

1. Extract the table creation statements from the primary database with the following command as the postgres user:

pg_dump -s -t 'pgbench*' postgres > /tmp/tables.sql

2. Create the empty tables on the subscriber node by executing this command as the postgres user on the primary node:

psql -U rep_user -h pgha2 -f /tmp/tables.sql postgres

3. Confirm that the tables exist on the subscriber node by executing the following SQL statement on the subscriber node:

SELECT schemaname, tablename FROM pg_tables WHERE tablename ...