Logical replication in PostgreSQL is moving forward

Peter Eisentraut

About me

• PostgreSQL since 1999
• 2ndQuadrant since 2016
• EDB since 2020

What is logical replication?

Decoding-based systems

• BDR 1 (PG 9.4)
• pglogical 2 (PG 9.4–)
• BDR 2 (PG 9.6)
• nativ (PG 10–)
• pglogical 3 (PG 10–)
• BDR 3 (PG 11–13)
• BDR 4 (PG 14–)

Decoding-based systems

• BDR 1 (PG 9.4)
• pglogical 2 (PG 9.4-)
• BDR 2 (PG 9.6)
• nativ (PG 10-)
• pglogical 3 (PG 10-)
• BDR 3 (PG 11-13)
• BDR 4 (PG 14-)

New in PostgreSQL 15

• row filter
• column selection
• replicate all tables in a schema
• skip erroring transactions
• deactivate failing subscriptions (*)
• more pg_stat_*
• less superuser
• replicate sequences

Row filter

CREATE PUBLICATION p1
  FOR TABLE tab1 WHERE (x > 0);

Column selection

CREATE PUBLICATION p1
  FOR TABLE tab1 (a, b, c);

All tables in a schema

CREATE PUBLICATION p1
  FOR ALL TABLES IN SCHEMA sch1;

Skip erroring transactions

ALTER SUBSCRIPTION s1
  SKIP (lsn = '0/12345');

Deactivate failing subscriptions

CREATE SUBSCRIPTION s1
  ...
  WITH (disable_on_error = true);

More pg_stat_*

=# SELECT * FROM pg_stat_subscription_stats;
 subid | subname | apply_error_count | sync_error_count | stats_reset
-------+---------+-------------------+------------------+-------------
 ...

Less superuser

• owner of subscription does not have to be superuser
• subscription processes don't need to run as superuser anymore
• subscriptions observe table privileges

Plan for PostgreSQL 16

• replicate sequences
• pg_create_subscriber
• delayed replication (*)
• initial schema synchronization

Possible future features

• conflict resolution
• schema synchronization/DDL replication
• origin filter
• synchronize slots (logical failover)
• different names (*)
• multi-master
• always: logging and error handling

Summary