Demystifying pglogical - Tutorial

pglogical is a logical replication system implemented entirely as a PostgreSQL extension. It is fully integrated, and requires no triggers or external programs.

pglogical asynchronously replicates only changes in the data using logical decoding. This makes it very efficient as only the differences are replicated and also tolerant to network faults since it can resume after the fault.

After a frustrating session trying to setup pglogical, I've decided to document some of the quirks and missing information from the documentation.

All in all, the documentation is great, but there are some things I wish I knew before I started which would have made my experience less painful.
Note that I won't be covering the installation process as it is well documented. However, I do have a postgresql ansible role for Ubuntu that handles it as well if you want to go that route.

Note: this post does not attempt to be a replacement for the official documentation and only serves to provide supplementary information.

Nodes and Roles

A node simply refers to a PostgreSQL server. It can play the role of:

• Provider (upstream):  changes to data on this node are tracked and replicated to subscribing nodes.
• Subscriber (downstream): subscribes to changes on a provider node and replicates changes.

These roles are not mutually exclusive. A node can be both a provider and a subscriber.

It can subscribe to multiple providers to aggregate data, it can also serve as a provider to other downstream nodes and cascade changes to them (see section on origins).

In fact, a pair of nodes can subscribe to each other and perform bi-directional replication. Support for this is limited however, and you will have to consider your use case carefully.

We use it in this manner and it works well for us as our writes are heavy on our primary and only occasional on our secondary.

If what you need is true multi-master, then consider Postgres-BDR. Code from pglogical is actually used in BDR.

Configuration

We will look at how to configure your nodes to prepare for pglogical. Configuration changes should be made in postgresql.conf.

Remember to restart postgres after configuration.

Provider

1. Set wal_level = 'logical' to enable logical decoding.
2. Set max_worker_processes = $x where $x is the number of databases to be replicated. You can safely set it to a high number like 10.
3. Set max_replication_slots = $y where $y is the number of subscribers. Replication slots ensure that changes are published to all subscribers before segments are removed. The extension automatically handles slot creation on your behalf. You can set it to a high number like 10.
4. Set max_wal_senders = $z where $z is the number of subscribers. You can safely set it to a high number like 10.
5. Set shared_preload_libraries = 'pglogical'.
6. If you are running PostgreSQL 9.5+ and want to be able to fine tune your conflict resolution, then set track_commit_timestamp = on

wal_level = 'logical'
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
shared_preload_libraries = 'pglogical'
track_commit_timestamp = on # 9.5+ only

Subscriber

1. Set max_worker_processes = $x where $x is the number of provider nodes we are subscribing to. You can safely set it to a high number like 10.
2. Set shared_preload_libraries = 'pglogical'.

max_worker_processes = 10
shared_preload_libraries = 'pglogical'

Remember that the above is just the minimum you will need.

Host-based Authentication (pg_hba.conf)

You will need to allow replication connections from localhost on each node.e.g

host replication all 127.0.0.1/32 md5

While I am yet to test if it is required, I also enable connections to the provider database from the subscriber node.

host $provider_database_name $provider_replication_user $subscriber_host_or_ip md5

And to the subscriber database from the provider node.

host $subscriber_database_name $subscriber_replication_user $provider_host_or_ip md5

Setup

Extension

On each node, create the extension:

CREATE EXTENSION IF NOT EXISTS pglogical; 

Replication User

On each node, create the appropriate role for pglogical to use:

CREATE ROLE $replication_user WITH SUPERUSER REPLICATION LOGIN ENCRYPTED PASSWORD '$replication_user_password';

Node

We now need to create the node in pglogical. You should do this only once per database.

SELECT pglogical.create_node(
    node_name := '$name',
    dsn := 'host=$host port=$port dbname=$database_name user=$replication_user password=$replication_user_password'
);

The dsn is a postgres connection string. I am not sure how many of the actual options are supported, but the following are mainly what you will use:

• $name: this is the name of your node. It should allow you to easily identify the node. Make it unique across databases and nodes.
• $host: the domain name or ip address of the node.
• $port: the port for postgres.

Conflict Resolution

On your subscriber nodes, select the most appropriate conflict resolution strategy for your situation (check the docs) and execute:

set pglogical.conflict_resolution = '$conflict_resolution_strategy'

Replication Sets

You will need to create replication sets on your provider nodes. This is already pretty well unambiguously covered in the docs and so I won't get into it.

I suggest you not use the default set as with a custom set, you can delete and recreate it easily if you make a mistake.

Also, if you want to support updates and deletes, your table must have a primary key.

Subscriptions

SELECT pglogical.create_subscription(
    subscription_name := '$subscription_name',
    provider_dsn := 'host=$provider_host port=$provider_port dbname=$provider_database_name user=$provider_replication_user password=$provider_replication_user_password', 
    replication_sets := $replication_sets,
    synchronize_structure := $synchronize_structure,
    synchronize_data := $synchronize_data ,
    forward_origins := $forward_origins
);

• $subscription_name: this is the name of your subscription.
• provider_dsn: connection string for the provider.
• $replication_sets: an array of replication sets to subscribe to. default: '{ddl_sql}'
• $synchronize_structure: should we synchronize the structure before starting the replication?  default: 'false'
• $synchronize_data: should we synchronize the data before starting the replication?  default: 'true'
• $forward_origins: array of origin names to forward. more info on origins below.  default: '{all}'

Synchronize Structure

This option gave me the most trouble and is the main reason I am writing this.

I assumed that only the structure of the tables in the replication set would be synchronized, but I found out that it does more than that.

It was attempting to create an extension on my subscriber node which was only needed on the provider and it was causing my setup to fail due to missing dependencies.

If you aren't doing anything crazy in your provider, this is an easy way to get things set up. Otherwise, you will need to create the table structure yourself.

Tables must have the same columns, with the same data types in each column. CHECK constraints, NOT NULL constraints, etc must be the same or weaker (more permissive) on the subscriber than the provider.

Origins

Origin refers to the source of the changes.

By default, forward_origins is set to '{all}' which means that all changes to the data will be replicated.
This is the ideal setting to use a cascaded setup.

You can also set it to '{}' which will only replicate changes that originate on the provider itself. This excludes changes that occur in a replica session (see session_replication_role) which is how pglogical applies changes.
This is the ideal setting to use in a bi-directional setup.

Initial Replication Strategies

There are some strategies you might want to consider when setting up your replication for the first time.

For smaller databases, you can create your replication set, add all tables, and create your subscription. This doesn't work well for large databases.

In such a situation, you can:

Create a replication set, add all tables to it, create your subscription but set synchronize_data to false.
You can then synchronize the data using pglogical.alter_subscription_synchronize  or pglogical.alter_subscription_resynchronize_table for each table in the set.

Or, you can create your replication set, create the subscription and then add each table to the set using pglogical.replication_set_add_table with synchronize_data set to true.

Management

DDL

There will come a time when you will need to; add a column, a constraint or change a data type. Since pglogical expects the table definition to be consistent, if at anypoint, they get out of sync, the replication will fail.

You can attempt to stop all transactions and wait for all pending changes to replicate before applying the changes to both the provider and subscriber.

Alternatively, pglogical provides pglogical.replicate_ddl_command to synchronize ddl changes from the provider to the subscriber.

Sequences

If you want any sort of multi-master setup such as with bi-directional, you will need to handle sequences if you use them.
You can avoid this by using Guids if you rely on sequences only for primary keys.

pglogical can synchronize sequences. It does this by creating a buffer on the subscriber based on how quickly the sequence is being consumed. e.g. on the provider, the sequence can be at 1 and on the subscriber 1000 to create that buffer.

The developers have said this might change in PostgreSQL 10 if decoding of sequences makes it.

I find that the current implementation is not another thing I want to mentally manage. There are 2 alternatives approaches you can use.

Segmented Sequences

Segmentation involves creating a sufficiently large buffer between the sequences on different nodes.

 SELECT setval('foo', 0); -- node 1

 SELECT setval('foo', 5000000000); -- node 2

 SELECT setval('foo', 10000000000); -- node 3

You should also set a maximum value and disable cycling so that in case you do reach it, the failure will draw your attention so you can fix it. MAXVALUE = $start + $segment_size.

 ALTER SEQUENCE foo MAXVALUE 5000000000 NO CYCLE; -- node 1

 ALTER SEQUENCE foo MAXVALUE 10000000000 NO CYCLE; -- node 2
 
 ALTER SEQUENCE foo MAXVALUE 15000000000 NO CYCLE; -- node 3

This has the benefit of being applied to more than 2 nodes, but requires you to be able to estimate how large your table can get. You can attempt to bypass this by using a bigserial with sufficiently large segments.
You might be wondering how this differs from the pglogical implementation. It's the same concept, but this time, I don't have to worry about pglogical handling it properly or what would happen in the event of a long network fault and an insufficient buffer.

Alternating Sequences

This is ideal when there are only 2 nodes involved.

You set the first node to an odd number and the other to an even number with an increment of 2.

 CREATE SEQUENCE foo INCREMENT BY 2 START WITH 1 -- node 1;

 CREATE SEQUENCE foo INCREMENT BY 2 START WITH 2 -- node 2;

This will make node 1 use odd sequence numbers and and node 2, even.


Well, that's it for now.
I hope this helps you get started on your journey and hopefully, you don't face the same challenges I did.