pglogical

Introduction

The pglogical extension provides logical streaming replication for PostgreSQL using a publish/subscribe model.

pglogical is a logical replication system implemented entirely as a PostgreSQL extension. It is fully integrated and does not require triggers or external programs. It provides an efficient way to selectively replicate data.

For more information, see the official pglogical documentation.

Supported Versions

This extension is available to the latest minor versions of RDS for PostgreSQL 12 and later versions. You can run the following SQL statement to check whether your DB instance supports this extension:

SELECT * FROM pg_available_extension_versions WHERE name = 'pglogical';

If this extension is not supported, upgrade the minor version of your DB instance or upgrade the major version using dump and restore.

For details about the extensions supported by RDS for PostgreSQL, see Supported Extensions.

Features

Use cases supported are:

• Upgrades between major versions (given the above restrictions)
• Full database replication
• Selective replication of sets of tables using replication sets
• Data gather/merge from multiple upstream servers

Requirements:

• The pglogical extension must be installed on both the provider and subscriber.
• Tables on the provider and subscriber must have the same names and be in the same schema.
• Tables on the provider and subscriber must have the same columns, with the same data types in each column.
• Tables must have the same primary key. It is not recommended to add additional unique constraints other than the primary key.
• To replicate multiple databases, you must set up individual provider/subscriber relationships for each. There is no way to configure replication for all databases in a PostgreSQL install at once.

Extension Installation and Uninstallation

• Installing the extension

  select control_extension('create', 'pglogical');

• Deleting the extension

  select control_extension('drop', 'pglogical');

For more information, see Installing and Uninstalling an Extension on the RDS Console and Installing and Uninstalling an Extension Using SQL Commands.

Basic Usage

To use the pglogical extension, you need to modify the configuration parameters.

wal_level = 'logical'

shared_preload_libraries = 'pglogical'

For details about how to modify the shared_preload_libraries parameter, see Modifying the shared_preload_libraries Parameter.

1. To configure a logical streaming replication, create a provider node.

   SELECT pglogical.create_node(
       node_name := 'provider',
       dsn := 'host=127.0.0.1 port=5432 dbname=test user=provider_user'
   );

2. To configure a replication set, add all tables in public to the default replication set.

   SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

   

   Replication sets provide a mechanism to control which tables in the database will be replicated and which actions on those tables will be replicated.

   default indicates that all tables and all operations on these tables will be replicated.

   For more information about replication sets, see official pglogical documentation.

3. Create a subscriber node. Once the provider node is configured, the subscriber can subscribe to it.

   SELECT pglogical.create_node(
       node_name := 'subscriber',
       dsn := 'host=127.0.0.1 port=5432 dbname=test user=subscriber_user'
   );

4. Create a subscription on the subscriber. After the subscription is created, the synchronization and replication processes are started.

   SELECT pglogical.create_subscription(
       subscription_name := 'subscription',
       provider_dsn := 'host=providerhost port=5432 dbname=test user=provider_user'
   );
   SELECT pglogical.wait_for_subscription_sync_complete('subscription');

Advanced Usage

1. Create a table to be replicated on the provider and subscriber, respectively.

   create table test(id int primary key, name text, reg_time timestamp);

   

   The names and structures of the tables on the provider and subscriber must be the same.

2. Insert data to the table on the provider.

   insert into test select generate_series(1,10000),'test',now();

3. Add the table to the replication set on the provider.

   -- Adding all tables to the replication set
   SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
   -- Adding specified tables to the replication set
   SELECT pglogical.replication_set_add_table( set_name := 'default', relation := 'test',synchronize_data := true);

   

   If you add all tables to the replication set, run the following statement on the subscriber to synchronize data. Otherwise, data cannot reach the subscriber and the subscription status is unknown.

   select pglogical.alter_subscription_synchronize('subscription1');

   If you add specified tables to the replication set, the tables are automatically synchronized by default.

4. Verify that the table has been added to the replication set.

   select * from pglogical.replication_set_table ;

5. Query the subscription status on the subscriber.

   select * from pglogical.show_subscription_table('subscription1','test');

6. Check whether the table data is synchronized on the subscriber.

   select count(*) from test;