12.2. Starting Logical Replication

Beta Version: Work in progress.

Before setting up logical replication, the following conditions are assumed:

• The Publisher has the tables that will be replicated to the Subscriber.
• The Subscriber has already created tables with the same structure as those on the Publisher, but without any data.

This section uses the following tables:

CREATE TABLE tbl_1 (id int PRIMARY KEY, name text, data int);
CREATE TABLE tbl_2 (id int NOT NULL UNIQUE, name text, data int);
CREATE INDEX tbl_2_idx on tbl_2 (id, name);
CREATE TABLE tbl_3 (id int, name text, data int);
ALTER TABLE tbl_3 REPLICA IDENTITY FULL;

To set up logical replication, two commands are issued:

• CREATE PUBLICATION on the Publisher.
• CREATE SUBSCRIPTION on the Subscriber.

12.2.1. Creating Publication

The CREATE PUBLICATION command creates the publication data in the following system catalogs and updates pg_class if necessary:

• pg_publication
• pg_publication_rel
• pg_publication_namespace

testdb=# -- Publisher
testdb=# CREATE PUBLICATION mypub FOR TABLE tbl_1, tbl_2, tbl_3;
CREATE PUBLICATION
testdb=# \x
Expanded display is on.
testdb=# SELECT * FROM pg_publication;
-[ RECORD 1 ]+------
oid          | 16460
pubname      | mypub
pubowner     | 10
puballtables | f
pubinsert    | t
pubupdate    | t
pubdelete    | t
pubtruncate  | t
pubviaroot   | f
pubgencols   | n

testdb=# \x
Expanded display is off.
testdb=# SELECT * FROM pg_publication_rel;
  oid  | prpubid | prrelid | prqual | prattrs
-------+---------+---------+--------+---------
 16461 |   16460 |   16438 |        |
 16462 |   16460 |   16446 |        |
 16463 |   16460 |   16455 |        |
(3 rows)

The system view pg_publication_tables shows the mapping between publications and the tables they belong to.

testdb=# -- Publisher
testdb=# SELECT * FROM pg_publication_tables;
 pubname | schemaname | tablename |    attnames    | rowfilter
---------+------------+-----------+----------------+-----------
 mypub   | public     | tbl_1     | {id,name,data} |
 mypub   | public     | tbl_2     | {id,name,data} |
 mypub   | public     | tbl_3     | {id,name,data} |
(3 rows)

12.2.2. Creating Subscription

The CREATE SUBSCRIPTION command on the Subscriber creates a subscription.

testdb=# -- subscriber
testdb=# CREATE SUBSCRIPTION mysub
            CONNECTION 'host=192.168.3.10 port=5432 dbname=testdb'
            PUBLICATION mypub
            WITH (enabled = true, binary = false);

This process consists of three phases (see Figure 12.9):

• Phase 1: Create the subscription and (by default) request a replication slot on the Publisher.
• Phase 2: Connect an apply worker to a walsender on the Publisher.
• Phase 3: Synchronize the tables with those on the Publisher.

The following subsections explore these phases.

12.2.2.1 Phase 1

This phase involves two primary tasks:

• Creating the subscription data in the system catalogs pg_subscription and pg_subscription_rel.
• (By default) requesting a replication slot on the publisher to manage logical replication.

To perform these tasks, the subscriber executes the following sequence:

1. The postgres process issuing ‘CREATE SUBSCRIPTION’ establishes a connection with a walsender.
2. The postgres process interacts with the walsender to:
   • Check the existence of the specified publication.
   • Retrieve target table schemas and OIDs to ensure structural compatibility.
   • (By default) request a replication slot. If the command specifies an existing slot, the process omits this request.
3. The postgres process inserts the subscription data into ‘pg_subscription’ and ‘pg_subscription_rel’.
4. The Publisher terminates the walsender process.

testdb=# -- Subscriber
testdb=# SELECT * FROM pg_subscription;
-[ RECORD 1 ]-------+---------------------------------------
oid                 | 16436
subdbid             | 16388
subskiplsn          | 0/0
subname             | mysub
subowner            | 10
subenabled          | t
subbinary           | f
substream           | p
subtwophasestate    | d
subdisableonerr     | f
subpasswordrequired | t
subrunasowner       | f
subfailover         | f
subconninfo         | host=192.168.3.10 port=5432 dbname=testdb
subslotname         | mysub
subsynccommit       | off
subpublications     | {mypub}
suborigin           | any

testdb=# \x
Expanded display is off.
testdb=# SELECT s.srsubid, s.srrelid, c.relname, s.srsublsn
testdb-#       FROM pg_subscription_rel AS s, pg_class AS c WHERE c.oid = s.srrelid;
 srsubid | srrelid | relname | srsublsn
---------+---------+---------+-----------
   16436 |   16414 | tbl_1   | 0/1BF35E0
   16436 |   16422 | tbl_2   | 0/1BF35E0
   16436 |   16431 | tbl_3   | 0/1BF5320
(3 rows)

The replication slot on the publisher is created as follows:

testdb=# -- Publisher
testdb=# SELECT * FROM pg_replication_slots;
-[ RECORD 1 ]-------+----------
slot_name           | mysub
plugin              | pgoutput
slot_type           | logical
datoid              | 16384
database            | testdb
temporary           | f
active              | t
active_pid          | 2051
xmin                |
catalog_xmin        | 823
restart_lsn         | 0/1BF5320
confirmed_flush_lsn | 0/1BF5358
wal_status          | reserved
safe_wal_size       |
two_phase           | f
two_phase_at        |
inactive_since      |
conflicting         | f
invalidation_reason |
failover            | f
synced              | f

12.2.2.2. Phase 2

This phase initiates the background processes required for logical replication.

5. The Logical Replication Launcher starts an apply worker.
6. The apply worker connects to a walsender on the publisher and initializes its replication origin (creating an entry in pg_replication_origin if it does not exist).

These two processes — the walsender on the publisher and the apply worker on the subscriber — continue running to stream and apply changes.

12.2.2.3. Phase 3

This phase synchronizes the tables with the corresponding tables on the publisher.

To perform this task, the Logical Replication Launcher starts apply workers to retrieve the rows of the target tables.

The launcher starts as many workers as possible to maximize efficiency. The workers used for synchronization are called table sync workers.

7. The Launcher starts the table sync workers.
8. The table sync workers connect to walsenders.
9. The walsenders send table data to the table sync workers, which then insert the data into the target tables.
10. After synchronization, the table sync workers and walsenders terminate.

Walsenders and table sync workers use the COPY ... TO STDOUT and COPY ... FROM STDIN protocols to stream and insert table data efficiently. Refer to tablesync.c for more details.