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);

And then, by issuing 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');

And then immediately copy all table data and then subscribe to changes for TableX:

SELECT pglogical.create_subscription(
                 subscription_name := 
'SmallSet_subscription',
                 replication_sets := ARRAY['SmallSet'],
                 provide_dsn := 'host=nodeA dbname=postgres'
                 );

3. Publish changes for rows on TableY where status=7 on the MyApp database on nodeA, adding this into the existing replication set and immediately synchronizing data to all subscribing nodes:

SELECT pglogical.replication_set_add_table(
                 set_name := 'SmallSet',
                 relation := 'TableY',
                 row_filter := 'status = 7',
                 synchronize_data = true);