PostgreSQL 11 Administration Cookbook

How to do it…

Let's look at a few different examples of how to use logical replication:

1. To publish changes from all tables on a postgres database on nodeA, use the following:

SELECT pglogical.replication_set_add_all_tables(                 set_name := 'default',                 schema_names := ARRAY['public'],                 true);

Then, issue the following command on nodeB:

SELECT pglogical.create_subscription(                 subscription_name := 'my_subscription_name',                 provide_dsn := 'host=nodeA dbname=postgres'                 );

2. Publish changes for TableX on the MyApp database on nodeA by using the following:

SELECT pglogical.create_replication_set(                 set_name := 'SmallSet');SELECT pglogical.replication_set_add_table(                 set_name := 'SmallSet',                 relation := 'TableX');

Then, immediately copy all table data and subscribe ...