Logical Replication

Migrate a single table live between two independent clusters with CREATE PUBLICATION and CREATE SUBSCRIPTION — no full standby required

Every replication lab so far in this block copies an entire cluster, byte for byte — every database, every table, everything. Logical replication works completely differently: it decodes WAL back into individual row-level changes — this specific INSERT, that specific UPDATE — and replays exactly those changes against a target that can be an entirely separate, independently-created cluster, potentially running a different PostgreSQL major version, with its own unrelated tables that were never part of the source at all.\n\nThis lab builds a completely fresh, independent second cluster from scratch with initdb — not a copy of the primary the way pg_basebackup made one — publishes a single table from the primary, subscribes to it from the new cluster, and watches the initial data copy and the ongoing change stream both work, before cleanly tearing the subscription down.

Publishing on the Source

CREATE PUBLICATION products_pub FOR TABLE products;

A publication names exactly which tables' changes are available to subscribers — here, just one table out of the entire database, unlike a physical standby which always receives everything.

A Genuinely Independent Target

Unlike Lab 2.5.2's standby, the target here is built with initdb, not pg_basebackup — it starts as a completely empty, unrelated cluster:

initdb -D /tmp/target_data --auth=trust
echo "port = 5433" >> /tmp/target_data/postgresql.conf
pg_ctl start -D /tmp/target_data -l /tmp/target_log.txt -w

Because it is not a copy of anything, the target's database and table both have to be created by hand first — logical replication streams row-level data changes, never the DDL that created the table in the first place.

Subscribing

CREATE SUBSCRIPTION products_sub
  CONNECTION 'host=127.0.0.1 port=5432 dbname=beer_db user=postgres'
  PUBLICATION products_pub;

This one statement does two things: it creates a replication slot on the source automatically, and it copies every existing row from the published table before switching to streaming ongoing changes — the "initial copy phase" this lab's objectives monitor directly.

Monitoring and Cleanup

-- On the target:
SELECT subname, pid FROM pg_stat_subscription;

-- Once the migration is done:
DROP SUBSCRIPTION products_sub;

DROP SUBSCRIPTION also drops the replication slot it created on the source automatically — no separate cleanup step on the source side is needed.

Publication

A named, table-level allowlist of what a source database is willing to logically replicate. Unlike physical replication, which always ships everything, a publication can cover a single table, several specific tables, or every table in a schema — giving fine-grained control over exactly what leaves the source.

What Logical Replication Does Not Replicate

DDL (schema changes like CREATE TABLE or ALTER TABLE), sequence values (a subscriber's sequences advance independently based on its own inserts), and unlogged tables (which generate no WAL to decode in the first place) are all outside what logical replication carries. A migration plan has to account for all three explicitly — this lab's target table was created by hand for exactly this reason.

📤 Publish the Table

Create a publication on the primary for just the products table.

psql -U postgres -d beer_db -c "CREATE PUBLICATION products_pub FOR TABLE products;"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE PUBLICATION products_pub FOR TABLE products;" SET CREATE PUBLICATION

🆕 Build a Genuinely Independent Cluster

Use initdb, not pg_basebackup, to create a brand-new, unrelated cluster and start it on port 5433.

as-postgres initdb -D /tmp/target_data -U postgres --auth=trust

as-postgres sh -c "echo 'port = 5433' >> /tmp/target_data/postgresql.conf"

as-postgres pg_ctl start -D /tmp/target_data -l /tmp/target_log.txt -w

student@lab:~$ as-postgres initdb -D /tmp/target_data -U postgres --auth=trust The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "C". The default database encoding has accordingly been set to "SQL_ASCII". The default text search configuration will be set to "english". Data page checksums are enabled. creating directory /tmp/target_data ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... sysv selecting default "max_connections" ... 100 selecting default "shared_buffers" ... 128MB selecting default time zone ... UTC creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... sh: locale: not found 2026-06-26 16:02:48.495 UTC [164] WARNING: no usable system locales were found ok syncing data to disk ... ok Success. You can now start the database server using: pg_ctl -D /tmp/target_data -l logfile start student@lab:~$ as-postgres sh -c "echo 'port = 5433' >> /tmp/target_data/postgresql.conf" student@lab:~$ as-postgres pg_ctl start -D /tmp/target_data -l /tmp/target_log.txt -w waiting for server to start.... done server started

🏗️ Create the Matching Table by Hand

Create the target database and an identical products table — logical replication never ships schema changes, only row data.

psql -U postgres -h 127.0.0.1 -p 5433 -d postgres -c "CREATE DATABASE beer_db;"

psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "CREATE TABLE products (id serial PRIMARY KEY, sku text, name text, price numeric(10,2));"

student@lab:~$ psql -U postgres -h 127.0.0.1 -p 5433 -d postgres -c "CREATE DATABASE beer_db;" SET CREATE DATABASE student@lab:~$ psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "CREATE TABLE products (id serial PRIMARY KEY, sku text, name text, price numeric(10,2));" SET CREATE TABLE

📥 Subscribe, in the Background

Create the subscription backgrounded and check its own log — the initial copy and slot creation both happen as part of this one statement.

psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "CREATE SUBSCRIPTION products_sub CONNECTION 'host=127.0.0.1 port=5432 dbname=beer_db user=postgres' PUBLICATION products_pub;" > /tmp/sub_result.log 2>&1 &

sleep 6

cat /tmp/sub_result.log

student@lab:~$ psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "CREATE SUBSCRIPTION products_sub CONNECTION 'host=127.0.0.1 port=5432 dbname=beer_db user=postgres' PUBLICATION products_pub;" > /tmp/sub_result.log 2>&1 & [1] 199 student@lab:~$ sleep 6 student@lab:~$ cat /tmp/sub_result.log SET NOTICE: created replication slot "products_sub" on publisher CREATE SUBSCRIPTION

✅ Confirm the Initial Copy Finished

Check pg_stat_subscription and the row count on the target to confirm the existing 20 rows made it across.

psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT subname, pid FROM pg_stat_subscription;"

psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT count(*) FROM products;"

student@lab:~$ psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT subname, pid FROM pg_stat_subscription;" SET subname | pid --------------+----- products_sub | 199 (1 row) student@lab:~$ psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT count(*) FROM products;" SET count ------- 20 (1 row)

⚡ Confirm Ongoing Streaming

Insert a new row on the primary and confirm it appears on the target within a couple of seconds.

psql -U postgres -d beer_db -c "INSERT INTO products (sku,name,price) VALUES ('SKU-NEW','Brand New Product', 9.99);"

sleep 2

psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT * FROM products WHERE sku='SKU-NEW';"

student@lab:~$ psql -U postgres -d beer_db -c "INSERT INTO products (sku,name,price) VALUES ('SKU-NEW','Brand New Product', 9.99);" SET INSERT 0 1 student@lab:~$ sleep 2 student@lab:~$ psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "SELECT * FROM products WHERE sku='SKU-NEW';" SET id | sku | name | price ----+---------+--------------------+------- 21 | SKU-NEW | Brand New Product | 9.99 (1 row)

🧹 Drop the Subscription Cleanly

Once a migration is complete, drop the subscription and confirm its replication slot on the primary is gone too.

psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "DROP SUBSCRIPTION products_sub;"

psql -U postgres -d beer_db -c "SELECT slot_name, active FROM pg_replication_slots;"

student@lab:~$ psql -U postgres -h 127.0.0.1 -p 5433 -d beer_db -c "DROP SUBSCRIPTION products_sub;" SET NOTICE: dropped replication slot "products_sub" on publisher DROP SUBSCRIPTION student@lab:~$ psql -U postgres -d beer_db -c "SELECT slot_name, active FROM pg_replication_slots;" SET slot_name | active -----------+-------- (0 rows)

Lab 2.5.5 complete — and Block 2.5 complete. You migrated one table, live, between two fully independent clusters:\n\n\n CREATE PUBLICATION : ✅ one table, out of the whole database\n Independent target built : ✅ initdb, not a copy\n CREATE SUBSCRIPTION : ✅ initial copy + ongoing streaming both confirmed\n Clean teardown : ✅ DROP SUBSCRIPTION removed both sides\n

Enable JavaScript to run the live terminal and track your progress.