PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it...

As with all of the previous recipes, pgha1 is our origin server and pgha2 will remain our replication subscriber. Execute all commands in this recipe as the postgres system user on the provider node unless stated otherwise. Follow these steps to copy the sample pgbench tables:

1. Extract the table creation statements from the primary node with the following command:

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

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

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

3. Execute this SQL on the primary server to create a replication set:

SELECT pglogical.create_replication_set( set_name := 'pgbench', replicate_insert := TRUE, replicate_update ...