Day 11: Logical Replication
Goal:
pg2subscribes to selected tables, rows and columns frompg1; 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(databasetag11), the receiver is18/mainonpg2.
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;
SQLExpected (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;
SQLExpected:
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.logExpected (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;
SQLExpected:
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;
SQLExpected:
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;
SQLOn 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.logExpected (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:
- Build a new server with the new version, take over the schema with
pg_dump -s. - Publish
FOR ALL TABLES, subscribe, wait for the initial sync. - Watch the lag (Drill 6), stop writes to the old server.
- Bring sequences up to date – they are not replicated – for example
with
setval(). - 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
- On
pg1, show which tables are published with which filter. Target time: 20 s - On
pg2, show the sync state of every subscribed table. Target time: 20 s - On
pg2, show the subscription’s error and conflict counters. Target time: 20 s - 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/.pgpassExpected:
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_errorprevents 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_sizeas 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 = trueandsync_replication_slotson the standby (since 17).