pglogical

pglogical 2

The pglogical 2 extension provides logical streaming replication for PostgreSQL, using a publish/subscribe model. It is based on technology developed as part of the BDR project (http://2ndquadrant.com/BDR).

While pglogical is actively maintained, EnterpriseDB (which acquired 2ndQuadrant in 2020) focuses new feature development on a descendant of pglogical: Postgres Distributed. Postgres Distributed introduced new features such as DDL replication, write leaders, parallel apply, and more.

We use the following terms to describe data streams between nodes, deliberately reused from the earlier Slony technology:

• Nodes - PostgreSQL database instances
• Providers and Subscribers - roles taken by Nodes
• Replication Set - a collection of tables

pglogical is utilising the latest in-core features, so we have these version restrictions:

• Provider & subscriber nodes must run PostgreSQL 9.4+
• PostgreSQL 9.5+ is required for replication origin filtering and conflict detection
• Additionally, subscriber can be Postgres-XL 9.5+

Use cases supported are:

• Upgrades between major versions (given the above restrictions)
• Full database replication
• Selective replication of sets of tables using replication sets
• Selective replication of table rows at either publisher or subscriber side (row_filter)
• Selective replication of table columns at publisher side
• Data gather/merge from multiple upstream servers

Architectural details:

• pglogical works on a per-database level, not whole server level like physical streaming replication
• One Provider may feed multiple Subscribers without incurring additional disk write overhead
• One Subscriber can merge changes from several origins and detect conflict between changes with automatic and configurable conflict resolution (some, but not all aspects required for multi-master).
• Cascading replication is implemented in the form of changeset forwarding.

Requirements

To use pglogical the provider and subscriber must be running PostgreSQL 9.4 or newer.

The pglogical extension must be installed on both provider and subscriber. You must CREATE EXTENSION pglogical on both.

Tables on the provider and subscriber must have the same names and be in the same schema. Future revisions may add mapping features.

Tables on the provider and subscriber 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.

Tables must have the same PRIMARY KEYs. It is not recommended to add additional UNIQUE constraints other than the PRIMARY KEY (see below).

Some additional requirements are covered in Limitations and Restrictions.

Installation

Packages

pglogical is available as RPMs via yum for Fedora, CentOS, & RHEL, and as DEBs via apt for Debian and Ubuntu, or as source code here. Please see below for instructions on installing from source.

Installing pglogical with YUM

The instructions below are valid for Red Hat family of operating systems (RHEL, CentOS, Fedora). Pre-Requisites

Pre-requisites

These RPMs all require the PGDG PostgreSQL releases from http://yum.postgresql.org/. You cannot use them with stock PostgreSQL releases included in Fedora and RHEL. If you don’t have PostgreSQL already:

• Install the appropriate PGDG repo rpm from http://yum.postgresql.org/repopackages.php
• Install PostgreSQL
  • PostgreSQL 9.5: yum install postgresql95-server postgresql95-contrib
  • PostgreSQL 9.6: yum install postgresql96-server postgresql96-contrib
  • PostgreSQL 10: yum install postgresql10-server postgresql10-contrib
  • PostgreSQL 11: yum install postgresql11-server postgresql11-contrib
  • PostgreSQL 12: yum install postgresql12-server postgresql12-contrib
  • PostgreSQL 13: yum install postgresql13-server postgresql13-contrib
  • PostgreSQL 14: yum install postgresql14-server postgresql14-contrib
  • PostgreSQL 15: yum install postgresql15-server postgresql15-contrib
  • PostgreSQL 16: yum install postgresql16-server postgresql16-contrib
  • PostgreSQL 17: yum install postgresql17-server postgresql17-contrib
  • PostgreSQL 18: yum install postgresql18-server postgresql18-contrib
  • PostgreSQL 19: yum install postgresql19-server postgresql19-contrib

Installation

You can proceed to install pglogical for your PostgreSQL version:

• PostgreSQL 9.5: yum install pglogical_95
• PostgreSQL 9.6: yum install pglogical_96
• PostgreSQL 10: yum install pglogical_10
• PostgreSQL 11: yum install pglogical_11
• PostgreSQL 12: yum install pglogical_12
• PostgreSQL 13: yum install pglogical_13
• PostgreSQL 14: yum install pglogical_14
• PostgreSQL 15: yum install pglogical_15
• PostgreSQL 16: yum install pglogical_16
• PostgreSQL 17: yum install pglogical_17
• PostgreSQL 18: yum install pglogical_18
• PostgreSQL 19: yum install pglogical_19

Installing pglogical with APT

The instructions below are valid for Debian and all Linux flavors based on Debian (e.g. Ubuntu).

Pre-requisites

• Add the https://apt.postgresql.org/ repository. See the site for instructions.
• Install PostgreSQL
  • PostgreSQL 9.5: sudo apt-get install postgresql-9.5
  • PostgreSQL 9.6: sudo apt-get install postgresql-9.6
  • PostgreSQL 10: sudo apt-get install postgresql-10
  • PostgreSQL 11: sudo apt-get install postgresql-11
  • PostgreSQL 12: sudo apt-get install postgresql-12
  • PostgreSQL 13: sudo apt-get install postgresql-13
  • PostgreSQL 14: sudo apt-get install postgresql-14
  • PostgreSQL 15: sudo apt-get install postgresql-15
  • PostgreSQL 16: sudo apt-get install postgresql-16
  • PostgreSQL 17: sudo apt-get install postgresql-17
  • PostgreSQL 18: sudo apt-get install postgresql-18
  • PostgreSQL 19: sudo apt-get install postgresql-19

Installation

Once pre-requisites are complete, installing pglogical is simply a matter of executing the following for your version of PostgreSQL:

• PostgreSQL 9.5: sudo apt-get install postgresql-9.5-pglogical
• PostgreSQL 9.6: sudo apt-get install postgresql-9.6-pglogical
• PostgreSQL 10: sudo apt-get install postgresql-10-pglogical
• PostgreSQL 11: sudo apt-get install postgresql-11-pglogical
• PostgreSQL 12: sudo apt-get install postgresql-12-pglogical
• PostgreSQL 13: sudo apt-get install postgresql-13-pglogical
• PostgreSQL 14: sudo apt-get install postgresql-14-pglogical
• PostgreSQL 15: sudo apt-get install postgresql-15-pglogical
• PostgreSQL 16: sudo apt-get install postgresql-16-pglogical
• PostgreSQL 17: sudo apt-get install postgresql-17-pglogical
• PostgreSQL 18: sudo apt-get install postgresql-18-pglogical
• PostgreSQL 19: sudo apt-get install postgresql-19-pglogical

From source code

Source code installs are the same as for any other PostgreSQL extension built using PGXS.

Make sure the directory containing pg_config from the PostgreSQL release is listed in your PATH environment variable. You might have to install a -dev or -devel package for your PostgreSQL release from your package manager if you don't have pg_config.

Then run make to compile, and make install to install. You might need to use sudo for the install step.

e.g. for a typical Fedora or RHEL 9 install, assuming you're using the yum.postgresql.org packages for PostgreSQL:

sudo dnf install postgresql19-devel
PATH=/usr/pgsql-19/bin:$PATH make clean all
sudo PATH=/usr/pgsql-19/bin:$PATH make install

Usage

This section describes basic usage of the pglogical replication extension.

Quick setup

First the PostgreSQL server has to be properly configured to support logical decoding:

wal_level = 'logical'
max_worker_processes = 10   # one per database needed on provider node
                            # one per node needed on subscriber node
max_replication_slots = 10  # one per node needed on provider node
max_wal_senders = 10        # one per node needed on provider node
shared_preload_libraries = 'pglogical'

If you are using PostgreSQL 9.5+ (this won't work on 9.4) and want to handle conflict resolution with last/first update wins (see Conflicts), you can add this additional option to postgresql.conf:

track_commit_timestamp = on # needed for last/first update wins conflict resolution
                            # property available in PostgreSQL 9.5+

pg_hba.conf has to allow logical replication connections from localhost. Up until PostgreSQL 9.6, logical replication connections are managed using the replication keyword in pg_hba.conf. In PostgreSQL 10 and later, logical replication connections are treated by pg_hba.conf as regular connections to the provider database.

Next the pglogical extension has to be installed on all nodes:

CREATE EXTENSION pglogical;

If using PostgreSQL 9.4, then the pglogical_origin extension also has to be installed on that node:

CREATE EXTENSION pglogical_origin;

Now create the provider node:

SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=providerhost port=5432 dbname=db'
);

Add all tables in public schema to the default replication set.

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

Optionally you can also create additional replication sets and add tables to them (see Replication sets).

It's usually better to create replication sets before subscribing so that all tables are synchronized during initial replication setup in a single initial transaction. However, users of bigger databases may instead wish to create them incrementally for better control.

Once the provider node is setup, subscribers can be subscribed to it. First the subscriber node must be created:

SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=thishost port=5432 dbname=db'
);

And finally on the subscriber node you can create the subscription which will start synchronization and replication process in the background:

SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=providerhost port=5432 dbname=db'
);

SELECT pglogical.wait_for_subscription_sync_complete('subscription1');

Creating subscriber nodes with base backups

In addition to the SQL-level node and subscription creation, pglogical also supports creating a subscriber by cloning the provider with pg_basebackup and starting it up as a pglogical subscriber. This is done with the pglogical_create_subscriber tool; see the --help output.

Unlike pglogical.create_subscription's data sync options, this clone ignores replication sets and copies all tables on all databases. However, it's often much faster, especially over high-bandwidth links.

Node management

Nodes can be added and removed dynamically using the SQL interfaces.

• pglogical.create_node(node_name name, dsn text) Creates a node.

  Parameters:

  • node_name - name of the new node, only one node is allowed per database
  • dsn - connection string to the node, for nodes that are supposed to be providers, this should be reachable from outside

• pglogical.drop_node(node_name name, ifexists bool) Drops the pglogical node.

  Parameters:

  • node_name - name of an existing node
  • ifexists - if true, error is not thrown when subscription does not exist, default is false

• pglogical.alter_node_add_interface(node_name name, interface_name name, dsn text) Adds additional interface to a node.

  When node is created, the interface for it is also created with the dsn specified in