Eric Radman : a Journal

PostgreSQL's built-in Logical Replication

SQL databases provide a critical service by giving the application a consistent view of data, but logical replication makes it possible to trade in the guarantee that a query always reads the most recent write for other features. Instead of global behavior, logical replication allows a relational database to target particular features for distributed processing.

The second feature is that, to my knowledge is unique to SQL is the ability to replicate a subset of data between databases that are otherwise unrelated.

Server Configuration

There are three pieces required to get logical replication working:

  1. A user with the replication attribute
  2. Set wal_level to logical
  3. A pg_hba.conf for the connecting host

PostgreSQL will not allow a subscription to be created until the upstream server meets these requirements.

Selecting Tables for Export

CREATE PUBLICATION localharvest_pub FOR TABLE farmer, product;

The psql(1) commands for some operations are not obvious, but you can get help with \?.

localharvest=> \dRp+
                            List of publications
       Name       |    Owner     | All tables | Inserts | Updates | Deletes
 localharvest_sub | localharvest | f          | t       | t       | t
(1 row)

List subscriptions using \dRs.

Establishing a Connection

CREATE SUBSCRIPTION localharvest_sub
  CONNECTION 'host=db1 dbname=localharvest user=localharvest password=XXXXXX'
  PUBLICATION localharvest_pub;

Tables must have a primary key, otherwise, you'll see this unhelpful error

ERROR:  relation "public.farmer" does not exist

Also, you can only connect to a master

ERROR:  could not create replication slot "localharvest_sub": ERROR:  logical decoding cannot be used while in recovery

Once a subscription is created a corresponding replication slot is created on the master

localharvest=# \x
localharvest=# select * from pg_replication_slots;
-[ RECORD 1 ]-------+-----------------
slot_name           | localharvest_sub
plugin              | pgoutput
slot_type           | logical
datoid              | 16478
database            | localharvest
temporary           | f
active              | t
active_pid          | 41867
xmin                |
catalog_xmin        | 606
restart_lsn         | 0/16C0280
confirmed_flush_lsn | 0/16C02B8


Postgres will run triggers on a replicated table, if you set it to respond to remote updates


Mirroring Table Definitions

The synchronization of table contents is handled automatically, providing you have created the tables in both places.

# publisher
pg_dump -s -t farmer -t product > replicated_tables.sql
# subscriber
psql -f replicated_tables.sql

If a column is added in one database, replication will be blocked but PostgreSQL will retry until the schema change has been applied to all of the subscribers as well.

Promoting a New Master

If you promote a standby to a master and change the DNS name pointing to it the logical replication launcher will resolve the new name. There is one manual step however; physical replication does not copy replication slots, so in the case of failover we need to create a slot manually

SELECT pg_create_logical_replication_slot('localharvest_sub', 'pgoutput');

Watching Replication Slots

Since logical replication requires a replication slot, a wedged subscriber can prevent a master from vaccuming old WAL. To keep an eye on this I run a cron job that alerts if any of the replication slots are behind.keep an eye on this I run a cron job that alerts if any of the replication slots are behind.

# Run as the postgres user

SELECT slot_name \
FROM pg_replication_slots \
WHERE pg_current_wal_flush_lsn() - confirmed_flush_lsn > 500 \
OR confirmed_flush_lsn IS NULL \

for slot in $(psql -At -c "$CHECK_SQL")
        echo "Log Sequence Numbers have diverged for a replication slot"
        psql <<-SQL
          SELECT slot_name, slot_type, database, temporary, active, restart_lsn,
            pg_current_wal_flush_lsn() - confirmed_flush_lsn AS lsn_delta
          FROM pg_replication_slots
          WHERE slot_name='${slot}'

Disconnecting Subscribers

If communication is functioning, all you have to do to unhook a subscriber is run DROP SUBSCRIPTION, and the corresponding replication slot will be removed from the master. If the subscriber cannot communicate with the master the slot must be removed manually

SELECT * FROM pg_replication_slots;
SELECT pg_drop_replication_slot('localharvest_sub');


Can we configure multi-master using PostgreSQL built-in replication? The answer is no, because the built-in logical replication does not yet provide a mechanism for implementing conflict resolution.

Last updated on April 11, 2019