PostgreSQL logical replication: a tested two-cluster guide

PostgreSQL logical replication copies selected table data from a publisher to a subscriber, then delivers later changes. A publication selects the tables and change types; a subscription connects to that publication and applies the data.

This guide follows a tested, one-way example using two separate PostgreSQL 18.6 clusters on Windows. We checked the initial copy, subsequent row changes, a schema mismatch and its repair, and removal of the replication slots. All 17 runtime checks passed. The tables contain invented data; no production database was involved.

Prepare two disposable clusters

Use separate clusters, with publisher_demo on the publisher and subscriber_demo on the subscriber. Our test bound both servers to 127.0.0.1 on different ports and used SCRAM password authentication. Run each SQL block against the database named in its section, with psql autocommit enabled unless a block explicitly starts a transaction.

The configuration requirements include wal_level=logical on the publisher and capacity for replication slots, WAL senders, and subscriber workers. Allow room for initial table synchronization as well as the main subscription. Restart when a changed setting requires it. Our disposable setup used eight slots and eight senders on the publisher, plus four logical replication workers and ten total worker processes on the subscriber; these are lab settings, not production sizing advice.

The lab uses its administrator for object creation and a separate non-superuser account for the replication connection. For a hosted database, confirm which settings and privileges your service permits. Our PostgreSQL hosting guide explains the division of responsibility between a managed service and a database you operate yourself.

Create matching tables on both sides

Run this block once in each database. Start with an empty subscriber table and stop if the lab schema already exists:

CREATE SCHEMA replication_lab;
CREATE TABLE replication_lab.items (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    quantity integer NOT NULL
);

The primary key will identify rows for updates and deletes. The subscription documentation explains how tables are matched by their schema-qualified names. This example deliberately uses identical table definitions. Creating the subscription will not create this schema for you.

Seed the publisher and define the publication

Run the following on publisher_demo only. It creates two rows before replication begins, grants the connection role access to this table, and publishes inserts, updates, and deletes:

INSERT INTO replication_lab.items (name, quantity)
VALUES ('alpha', 10), ('beta', 20);

CREATE ROLE replica_reader WITH LOGIN REPLICATION;
GRANT CONNECT ON DATABASE publisher_demo TO replica_reader;
GRANT USAGE ON SCHEMA replication_lab TO replica_reader;
GRANT SELECT ON replication_lab.items TO replica_reader;

CREATE PUBLICATION items_pub FOR TABLE replication_lab.items
WITH (publish = 'insert, update, delete');

The publication explicitly excludes truncation. Its operation list controls ongoing changes, not whether the initial data copy occurs. A publication also needs a replica identity for published updates and deletes. Our primary key supplies it. A separate negative test confirmed that updating a published table without a replica identity fails on the publisher. REPLICA IDENTITY FULL is a fallback with different costs, not a setting to apply automatically to every table.

Before connecting, set a password for replica_reader, for example with psql’s \password replica_reader, and allow its connection to publisher_demo in the publisher’s authentication configuration. The replication security requirements explain the LOGIN, REPLICATION, and initial-copy permissions. A publication name alone is not an access-control boundary.

Create the subscription on the subscriber

Prepare a psql variable named publisher_conn containing the publisher’s libpq connection string: host, port, database, user, and an absolute passfile path. The password file must be on the subscriber host and readable by its PostgreSQL operating-system account. Follow the password-file format and permissions; do not put a reusable password into a shared SQL example. Our runner supplied a temporary file and removed it after the test.

Run this on subscriber_demo, outside an explicit transaction. The :'publisher_conn' expression is psql variable substitution, not server-side SQL syntax:

CREATE SUBSCRIPTION items_sub
CONNECTION :'publisher_conn'
PUBLICATION items_pub
WITH (copy_data = true, slot_name = 'logical_lab_slot');

CREATE SUBSCRIPTION creates the publisher slot and starts the workers under these settings. With copy_data=true, existing rows are copied before normal change delivery catches up. Command completion does not mean that copying has finished.

Verify the initial copy, then change rows

On the subscriber, inspect both the data and table synchronization state:

SELECT id, name, quantity
FROM replication_lab.items ORDER BY id;

SELECT r.srrelid::regclass AS table_name, r.srsubstate
FROM pg_subscription_rel AS r
JOIN pg_subscription AS s ON s.oid = r.srsubid
WHERE s.subname = 'items_sub';

Our first completed check showed rows 1, alpha, 10 and 2, beta, 20, with state r. In pg_subscription_rel, r means ready for normal replication. It is not a permanent guarantee of zero delay for every later transaction.

Now run this transaction on the publisher:

BEGIN;
INSERT INTO replication_lab.items (name, quantity) VALUES ('gamma', 30);
UPDATE replication_lab.items SET quantity = 11 WHERE id = 1;
DELETE FROM replication_lab.items WHERE id = 2;
COMMIT;

Repeat the subscriber data query until the expected committed changes appear. With psql’s unaligned output, our result was:

1|alpha|11
3|gamma|30

The new gamma row arrived, alpha’s quantity changed to 11, and beta disappeared. The test waited for those specific values rather than assuming a fixed sleep was sufficient. It measured correctness for this small fixture, not replication throughput or a latency guarantee.

Handle sequence state separately

On the subscriber, inspect the sequence backing the identity column:

SELECT last_value, is_called
FROM replication_lab.items_id_seq;

It still reported last_value=1 and is_called=false, while the publisher reported 3 and true. The row IDs were copied, but the subscriber sequence was not advanced. Do not start generating local IDs there and assume they will follow the replicated maximum.

The documented restrictions include sequence state and schema changes. Any planned transfer of writes needs separate sequence, schema, application, and consistency checks. This walkthrough does not establish a failover procedure.

Observe a schema mismatch and repair it

The next block deliberately creates a mismatch in the disposable lab. Run it on the publisher:

ALTER TABLE replication_lab.items ADD COLUMN note text;
INSERT INTO replication_lab.items (name, quantity, note)
VALUES ('delta', 40, 'review');

The subscriber did not acquire the new column. Its log identified a missing replicated column named note, its apply-error count increased, and row 4 did not appear. Repair the subscriber schema:

ALTER TABLE replication_lab.items ADD COLUMN note text;

The pending row then arrived with name=delta, quantity=40, and note=review. We did not recreate the subscription, skip a transaction, or discard data. For a planned additive change, preparing a compatible subscriber schema first can avoid this particular interruption. More complex migrations need their own tested ordering.

Monitor the worker and the publisher slot

On the subscriber, these queries show worker activity and accumulated error counts:

SELECT subname, pid, received_lsn,
       last_msg_receipt_time, latest_end_lsn
FROM pg_stat_subscription
WHERE subname = 'items_sub';

SELECT subname, apply_error_count, sync_error_count
FROM pg_stat_subscription_stats
WHERE subname = 'items_sub';

The monitoring guide describes subscription workers. Initial synchronization can involve additional workers. An error counter records past events; it does not by itself say whether replication is currently healthy. Compare observations over time, read the server log, and verify the data you expect.

On the publisher, inspect the named slot:

SELECT slot_name, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_name = 'logical_lab_slot';

pg_replication_slots exposes the consumer’s confirmed position and the oldest WAL position the slot may still need. An active connection alone does not prove that it is keeping up. Monitor retained WAL and available disk space as well as progress; a stopped or failing consumer can leave the publisher retaining WAL.

Remove the subscription before shutting down the lab

Keep the publisher reachable and run this on the subscriber, outside an explicit transaction:

DROP SUBSCRIPTION items_sub;

DROP SUBSCRIPTION normally connects back to remove its associated slot. Our test verified that the subscription and all lab slots were gone afterward, while the three copied rows remained in the subscriber table. If the publisher is unreachable, cleanup needs explicit reconciliation of the remaining slots; do not treat an incomplete removal as finished.

After verifying slot removal, drop items_pub on the publisher, remove the disposable tables and role, and stop both servers. Our runner completed that cleanup and removed its temporary password files. It left stopped data directories and redacted test records available for inspection.

Replication also delivered the delete in this exercise. It therefore does not provide an independent historical recovery point. Keep backup and restore testing as a separate task. The 17 checks establish local, one-way replication behavior; they do not cover remote networking, TLS, concurrent writers, cross-version migration, production load, or failover.