Skip to content
Hands-on IT IT Drills

Day 11: Logical Replication

Goal: pg2 subscribes to selected tables, rows and columns from pg1; you create a conflict, resolve it and monitor the slot and the subscription. · Duration: approx. 90 min · Prerequisite: Day 10

Briefing

  • Logical replication transfers row changes (INSERT, UPDATE, DELETE, TRUNCATE) of individual tables instead of WAL blocks. The receiver is a normal, writable server – even with a different major version.
  • The sender offers a publication, the receiver creates a subscription. In between, a logical slot on the sender keeps track of the position.
  • Only data is replicated: schema, sequences and DDL remain manual work.
  • The sender is pg1 (database tag11), the receiver is 18/main on pg2.
  pg1 18/main  wal_level=logical             pg2 18/main
  PUBLICATION tag11_de   (kunden, filtered)
  PUBLICATION tag11_best (bestellungen)  ---->  SUBSCRIPTION tag11_sub
  Slot tag11_sub (pgoutput)                     apply worker, tables writable

Drill 1: wal_level = logical

For logical decoding the WAL has to carry more information. wal_level requires a restart. On pg1:

psql -Atc 'SHOW wal_level'
echo 'wal_level = logical' \
  | sudo -u postgres tee /etc/postgresql/18/main/conf.d/tag11-logical.conf
sudo systemctl restart postgresql@18-main
psql -Atc 'SHOW wal_level'

Expected:

replica
wal_level = logical
logical

Test data and a role for the subscriber. It needs REPLICATION and SELECT for the initial copy of the tables. Logical replication connects to a real database, which is why the pg_hba line names tag11 instead of replication:

createdb tag11
psql -d tag11 <<'SQL'
CREATE TABLE kunden (id int PRIMARY KEY, name text, land text, email text);
CREATE TABLE bestellungen (id int PRIMARY KEY, kunde_id int, betrag numeric);
INSERT INTO kunden VALUES (1, 'Anna', 'DE', '[email protected]'),
  (2, 'Ben', 'AT', '[email protected]'), (3, 'Cem', 'DE', '[email protected]');
INSERT INTO bestellungen SELECT i, 1 + i % 3, i * 1.5 FROM generate_series(1, 500) i;
CREATE ROLE tag11_repl LOGIN REPLICATION PASSWORD 'lab-logrepl-123';
GRANT SELECT ON kunden, bestellungen TO tag11_repl;
SQL
echo 'host  tag11  tag11_repl  172.16.0.123/32  scram-sha-256' \
  | sudo tee -a /etc/postgresql/18/main/pg_hba.conf
psql -Atc 'SELECT pg_reload_conf()'

Expected:

CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 500
CREATE ROLE
GRANT
host  tag11  tag11_repl  172.16.0.123/32  scram-sha-256
t

Save your progress? Sign up for free

Drill 2: Publications with row filter and column list

tag11_de publishes only German customers and without the email address, tag11_best publishes all orders:

psql -d tag11 <<'SQL'
CREATE PUBLICATION tag11_de FOR TABLE kunden (id, name, land) WHERE (land = 'DE');
CREATE PUBLICATION tag11_best FOR TABLE bestellungen;
\dRp+ tag11_de
SELECT pubname, tablename, attnames, rowfilter FROM pg_publication_tables;
SQL

Expected (abridged):

CREATE PUBLICATION
CREATE PUBLICATION
                                     Publication tag11_de
 Owner  | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | ...
--------+------------+---------+---------+---------+-----------+-------------------+----
 ubuntu | f          | t       | t       | t       | t         | none              | ...
Tables:
    "public.kunden" (id, name, land) WHERE (land = 'DE'::text)

  pubname   |  tablename   |       attnames       |      rowfilter
------------+--------------+----------------------+---------------------
 tag11_de   | kunden       | {id,name,land}       | (land = 'DE'::text)
 tag11_best | bestellungen | {id,kunde_id,betrag} |
(2 rows)

Column lists keep sensitive data away from the receiver; row filters distribute data per tenant or region, for example. FOR ALL TABLES or FOR TABLES IN SCHEMA public publish everything at once, including future tables.

Save your progress? Sign up for free

Drill 3: Subscription and initial sync

On pg2. pg_dump -s copies the schema from pg1; owners and privileges are left out because those roles don’t exist on pg2. The password goes into the postgres user’s .pgpass instead of the connection string – otherwise it would be readable in pg_subscription:

echo '172.16.0.182:5432:tag11:tag11_repl:lab-logrepl-123' \
  | sudo -u postgres tee -a /var/lib/postgresql/.pgpass
sudo -u postgres chmod 600 /var/lib/postgresql/.pgpass
sudo -u postgres createdb tag11
sudo -u postgres pg_dump -h 172.16.0.182 -U tag11_repl -d tag11 -s \
  -t kunden -t bestellungen --no-owner --no-privileges \
  | sudo -u postgres psql -q -d tag11
sudo -u postgres psql -d tag11 <<'SQL'
CREATE SUBSCRIPTION tag11_sub
  CONNECTION 'host=172.16.0.182 dbname=tag11 user=tag11_repl'
  PUBLICATION tag11_de, tag11_best;
SQL

Expected:

 set_config
------------

(1 row)

NOTICE:  created replication slot "tag11_sub" on publisher
CREATE SUBSCRIPTION
sudo -u postgres psql -d tag11 -c \
  'SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel'
sudo -u postgres psql -d tag11 -c 'SELECT * FROM kunden ORDER BY id'
sudo -u postgres psql -d tag11 -Atc 'SELECT count(*) FROM bestellungen'
sudo grep 'synchronization worker' /var/log/postgresql/postgresql-18-main.log

Expected (abridged):

   srrelid    | srsubstate
--------------+------------
 kunden       | r
 bestellungen | r
(2 rows)

 id | name | land | email
----+------+------+-------
  1 | Anna | DE   |
  3 | Cem  | DE   |
(2 rows)

500
LOG:  logical replication table synchronization worker for subscription "tag11_sub",
table "kunden" has finished

For each table a sync worker first copies the existing data (COPY), then the apply worker takes over the ongoing changes; r means “ready”. Ben (AT) is missing, email stays empty – filter and column list already apply during the copy.

Save your progress? Sign up for free

Drill 4: Live changes – and the replica identity

Changes on pg1, including two that push a row across the filter boundary:

psql -d tag11 <<'SQL'
INSERT INTO kunden VALUES (4, 'Dora', 'DE', '[email protected]'),
  (5, 'Emil', 'CH', 'e@x');
UPDATE kunden SET name = 'Anna M.' WHERE id = 1;
DELETE FROM bestellungen WHERE id > 490;
SQL

Expected:

INSERT 0 2
ERROR:  cannot update table "kunden"
DETAIL:  Column used in the publication WHERE expression is not part of the replica
identity.
DELETE 10

For UPDATE and DELETE the sender includes the replica identity of the old row, by default the primary key. A row filter may only check columns from it; otherwise PostgreSQL can’t tell whether the old row was on the receiver – and refuses the UPDATE on pg1. REPLICA IDENTITY FULL (all columns) doesn’t help here:

psql -d tag11 <<'SQL'
ALTER TABLE kunden REPLICA IDENTITY FULL;
UPDATE kunden SET name = 'Anna M.' WHERE id = 1;
ALTER TABLE kunden REPLICA IDENTITY DEFAULT;
SQL

Expected:

ALTER TABLE
ERROR:  cannot update table "kunden"
DETAIL:  Column list used by the publication does not cover the replica identity.
ALTER TABLE

The column list excludes email, but FULL requires all columns. The solution is a unique index on id and land as the replica identity (its columns must be NOT NULL):

psql -d tag11 <<'SQL'
ALTER TABLE kunden ALTER COLUMN land SET NOT NULL;
CREATE UNIQUE INDEX kunden_id_land ON kunden (id, land);
ALTER TABLE kunden REPLICA IDENTITY USING INDEX kunden_id_land;
UPDATE kunden SET name = 'Anna M.' WHERE id = 1;
UPDATE kunden SET land = 'AT' WHERE id = 3;
UPDATE kunden SET land = 'DE' WHERE id = 2;
SQL

On pg2:

sudo -u postgres psql -d tag11 -c 'SELECT * FROM kunden ORDER BY id'
sudo -u postgres psql -d tag11 -Atc 'SELECT count(*) FROM bestellungen'

Expected:

 id |  name   | land | email
----+---------+------+-------
  1 | Anna M. | DE   |
  2 | Ben     | DE   |
  4 | Dora    | DE   |
(3 rows)

490

Cem moves to AT and leaves the filter – on the receiver this becomes a DELETE. Ben moves to DE and shows up as an INSERT. Emil (CH) never arrived.

Save your progress? Sign up for free

Drill 5: Create and resolve a conflict

The receiver is writable – and that is exactly what creates conflicts. With disable_on_error the subscription stops at the first error instead of restarting endlessly. On pg2, create an order that will soon exist on pg1 too:

sudo -u postgres psql -d tag11 \
  -c 'ALTER SUBSCRIPTION tag11_sub SET (disable_on_error = true)' \
  -c 'INSERT INTO bestellungen VALUES (1000, 1, 99.99)'

On pg1, two separate transactions:

psql -d tag11 -c 'INSERT INTO bestellungen VALUES (1000, 2, 10.00)' \
  -c 'INSERT INTO bestellungen VALUES (1001, 2, 20.00)'

On pg2:

sudo grep -A3 'conflict detected' /var/log/postgresql/postgresql-18-main.log
sudo -u postgres psql -d tag11 -c 'SELECT subname, subenabled FROM pg_subscription'
sudo -u postgres psql -d tag11 -x -c 'SELECT apply_error_count, confl_insert_exists
  FROM pg_stat_subscription_stats'

Expected (abridged):

ERROR:  conflict detected on relation "public.bestellungen": conflict=insert_exists
DETAIL:  Key already exists in unique index "bestellungen_pkey", modified in
transaction 888.
  Key (id)=(1000); existing local row (1000, 1, 99.99); remote row (1000, 2, 10.00).
CONTEXT:  processing remote data for replication origin "pg_16763" during message
type "INSERT" for replication target relation "public.bestellungen" in transaction
83049, finished at 1/874CBDF0
LOG:  subscription "tag11_sub" has been disabled because of an error
  subname  | subenabled
-----------+------------
 tag11_sub | f
(1 row)

-[ RECORD 1 ]-------+--
apply_error_count   | 1
confl_insert_exists | 1

Since PostgreSQL 18 the log names the conflict type and shows the local and the remote row; pg_stat_subscription_stats counts each type. Two ways out: delete the local row (then the one from pg1 arrives), or skip the remote transaction. We keep the local one and skip using the LSN from finished at:

sudo -u postgres psql -d tag11 \
  -c "ALTER SUBSCRIPTION tag11_sub SKIP (lsn = '1/874CBDF0')" \
  -c 'ALTER SUBSCRIPTION tag11_sub ENABLE'
sudo -u postgres psql -d tag11 -c 'SELECT * FROM bestellungen WHERE id >= 1000'
sudo grep 'skipping transaction' /var/log/postgresql/postgresql-18-main.log

Expected (abridged):

ALTER SUBSCRIPTION
ALTER SUBSCRIPTION
  id  | kunde_id | betrag
------+----------+--------
 1000 |        1 |  99.99
 1001 |        2 |  20.00
(2 rows)

LOG:  logical replication starts skipping transaction at LSN 1/874CBDF0
LOG:  logical replication completed skipping transaction at LSN 1/874CBDF0

The whole transaction is skipped – here only the one row, because psql -c commits each command separately. 1001 went through. From now on pg1 and pg2 differ on order 1000: SKIP repairs the data stream, not the data.

Save your progress? Sign up for free

Drill 6: Monitoring – subscription and slot

On pg2, the receiver’s view:

sudo -u postgres psql -d tag11 -x -c 'SELECT subname, worker_type,
  pid IS NOT NULL AS laeuft, received_lsn, latest_end_time FROM pg_stat_subscription'

Expected:

-[ RECORD 1 ]---+------------------------------
subname         | tag11_sub
worker_type     | apply
laeuft          | t
received_lsn    | 1/877EA9D8
latest_end_time | 2026-11-19 05:21:26.586054+00

On pg1, the slot. Then pause the subscription on pg2 and write 50,000 rows on pg1:

psql -x -c "SELECT slot_name, plugin, slot_type, database, active,
  confirmed_flush_lsn, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
  confirmed_flush_lsn)) AS rueckstand FROM pg_replication_slots"

Expected:

-[ RECORD 1 ]-------+-----------
slot_name           | tag11_sub
plugin              | pgoutput
slot_type           | logical
database            | tag11
active              | t
confirmed_flush_lsn | 1/877EA9D8
rueckstand          | 0 bytes
sudo -u postgres psql -d tag11 -c 'ALTER SUBSCRIPTION tag11_sub DISABLE'
psql -d tag11 -c 'INSERT INTO bestellungen SELECT i, 1, 1
  FROM generate_series(2000, 52000) i'
psql -c "SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(
  pg_current_wal_lsn(), restart_lsn)) AS behalten FROM pg_replication_slots"

Expected:

INSERT 0 50001
 slot_name | active | behalten
-----------+--------+----------
 tag11_sub | f      | 7770 kB
(1 row)

The slot holds on to all WAL as long as the subscriber is missing. Enable it again on pg2, and the lag on pg1 is gone within seconds:

sudo -u postgres psql -d tag11 -c 'ALTER SUBSCRIPTION tag11_sub ENABLE'
sudo -u postgres psql -d tag11 -Atc 'SELECT count(*) FROM bestellungen'

Expected:

ALTER SUBSCRIPTION
50493

Warning: A forgotten, disabled subscription – or a receiver that no longer exists – makes the slot on the sender collect WAL until the disk is full. When a receiver is decommissioned, its slot has to go too.

Save your progress? Sign up for free

Drill 7: Logical replication for upgrades and migrations

Because sender and receiver only exchange rows, they may run different major versions, platforms or hosts. The pattern for an upgrade with almost no downtime:

  1. Build a new server with the new version, take over the schema with pg_dump -s.
  2. Publish FOR ALL TABLES, subscribe, wait for the initial sync.
  3. Watch the lag (Drill 6), stop writes to the old server.
  4. Bring sequences up to date – they are not replicated – for example with setval().
  5. Switch the application over, drop the subscription.

Step 4 on pg1: pg_sequences produces ready-made setval() commands for the new server. tag11 has no sequence yet, so create one briefly:

psql -q -d tag11 -c 'CREATE TABLE seqtest (id serial PRIMARY KEY)' \
  -c 'INSERT INTO seqtest SELECT FROM generate_series(1, 42)'
psql -d tag11 -Atc "SELECT format('SELECT setval(%L, %s);', schemaname || '.' ||
  sequencename, last_value) FROM pg_sequences WHERE last_value IS NOT NULL"
psql -q -d tag11 -c 'DROP TABLE seqtest'

Expected:

SELECT setval('public.seqtest_id_seq', 42);

More tools: pg_createsubscriber (since 17) turns a physical standby (Day 10) directly into a logical subscriber and saves the initial sync of large databases; since 17, pg_upgrade carries over logical slots on the sender so that subscriptions survive an upgrade. Day 13 shows the classic upgrade with pg_upgrade.

Save your progress? Sign up for free

Exercise: Speed Drills

  1. On pg1, show which tables are published with which filter. Target time: 20 s
  2. On pg2, show the sync state of every subscribed table. Target time: 20 s
  3. On pg2, show the subscription’s error and conflict counters. Target time: 20 s
  4. On pg1, show the lag of the logical slot. Target time: 40 s

Solution

psql -d tag11 -c 'SELECT pubname, tablename, rowfilter FROM pg_publication_tables'
sudo -u postgres psql -d tag11 -c \
  'SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel'
sudo -u postgres psql -d tag11 -c \
  'SELECT apply_error_count, confl_insert_exists FROM pg_stat_subscription_stats'
psql -Atc "SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
  confirmed_flush_lsn)) FROM pg_replication_slots"

Expected (abridged):

  pubname   |  tablename   |      rowfilter
------------+--------------+---------------------
 tag11_de   | kunden       | (land = 'DE'::text)
 tag11_best | bestellungen |
 kunden       | r
 bestellungen | r
 apply_error_count | confl_insert_exists
-------------------+---------------------
                 1 |                   1
tag11_sub|0 bytes

Save your progress? Sign up for free

Checkpoint

On pg2:

sudo -u postgres psql -d tag11 -Atc 'SELECT subname, subenabled FROM pg_subscription'

Expected: tag11_sub|t

sudo -u postgres psql -d tag11 -Atc 'SELECT count(*) FROM kunden'

Expected: 3 – only customers from DE.

sudo -u postgres psql -d tag11 -Atc 'SELECT betrag FROM bestellungen WHERE id = 1000'

Expected: 99.99 – the local row survived the conflict.

On pg1:

psql -c 'SELECT slot_name, slot_type, active FROM pg_replication_slots'

Expected: tag11_sub | logical | t

Challenge

Additionally replicate a new table notiz (t timestamptz DEFAULT now(), txt text) – without a primary key – and prove that an UPDATE on pg1 arrives on pg2. Mind the order.

Hint: Without a replica identity, pg1 refuses UPDATEs on a published table, and pg2 has to know the table before its changes arrive.

Solution

On pg1, create the table:

psql -d tag11 -c 'CREATE TABLE notiz (t timestamptz DEFAULT now(), txt text)' \
  -c "INSERT INTO notiz (txt) VALUES ('eins')" -c 'GRANT SELECT ON notiz TO tag11_repl'

On pg2, create the table first:

sudo -u postgres psql -d tag11 \
  -c 'CREATE TABLE notiz (t timestamptz DEFAULT now(), txt text)'

On pg1, set the replica identity and publish:

psql -d tag11 -c 'ALTER TABLE notiz REPLICA IDENTITY FULL' \
  -c 'ALTER PUBLICATION tag11_best ADD TABLE notiz'

On pg2, refresh the subscription, then change the data on pg1 and check on pg2:

sudo -u postgres psql -d tag11 -c 'ALTER SUBSCRIPTION tag11_sub REFRESH PUBLICATION'
psql -d tag11 -c "UPDATE notiz SET txt = 'zwei'"
sudo -u postgres psql -d tag11 -Atc 'SELECT txt FROM notiz'

Expected:

ALTER SUBSCRIPTION
UPDATE 1
zwei

The wrong order costs you: without REPLICA IDENTITY the UPDATE on pg1 fails with cannot update table "notiz" because it does not have a replica identity and publishes updates. If the table is missing on pg2, the subscription stops with logical replication target relation "public.notiz" does not exist, and REFRESH only works after ENABLE. FULL compares whole rows on the receiver – slow for large tables; a primary key is always better.

Save your progress? Sign up for free

Cleanup

No later day needs logical replication. On pg2, drop the subscription first – it also drops the slot on pg1:

sudo -u postgres psql -d tag11 -c 'DROP SUBSCRIPTION tag11_sub'
sudo -u postgres dropdb tag11
sudo -u postgres rm /var/lib/postgresql/.pgpass

Expected:

NOTICE:  dropped replication slot "tag11_sub" on publisher
DROP SUBSCRIPTION

On pg1, set wal_level back to replica (restart):

psql -c 'SELECT slot_name FROM pg_replication_slots'
dropdb tag11
psql -c 'DROP ROLE tag11_repl;'
sudo sed -i '/^host  tag11  tag11_repl  172.16.0.123\/32/d' \
  /etc/postgresql/18/main/pg_hba.conf
sudo rm /etc/postgresql/18/main/conf.d/tag11-logical.conf
sudo systemctl restart postgresql@18-main
psql -Atc 'SHOW wal_level'

Expected:

 slot_name
-----------
(0 rows)

DROP ROLE
replica

If pg1 is unreachable, DROP SUBSCRIPTION fails. In that case run ALTER SUBSCRIPTION tag11_sub DISABLE and ALTER SUBSCRIPTION tag11_sub SET (slot_name = NONE) first – and drop the slot on the sender by hand later.

Interview Note

  • “What does logical replication not replicate?” – DDL, sequences and large objects; apply schema changes on the receiver first, then on the sender.
  • “What is the replica identity?” – The columns UPDATE and DELETE use to find the old row on the receiver: primary key, unique index or FULL. Without it the sender refuses UPDATE/DELETE on published tables.
  • “How do you resolve a conflict?” – Find the cause in the log, clean up the local row or skip the transaction with ALTER SUBSCRIPTION … SKIP (lsn = …); disable_on_error prevents endless loops.

Enterprise Note

  • Keep subscribers read-only (privileges, default_transaction_read_only), otherwise conflicts like the one in Drill 5 occur.
  • Slot monitoring on the sender with alerts on lag and active = f; max_slot_wal_keep_size as an emergency brake.
  • Typical uses: zero-downtime upgrades, moving to the cloud or to Kubernetes, subsets for reporting or a data warehouse, change data capture (Debezium uses the same mechanism).
  • In the pipeline, apply schema migrations to the subscriber first, then to the sender; add new tables with REFRESH PUBLICATION.
  • On a failover of the sender, logical slots are lost without failover = true and sync_replication_slots on the standby (since 17).