Quick Tour of Postgres Logical Replication
Part 1
Sync systems can get super complex pretty quickly.
Fortunately, there are plenty of solutions to choose from. Unfortunately, each one comes with its own set of tradeoffs. 😢
You can call an API, add a caching layer, build an ETL pipeline, send webhooks, or reach for something like SCIM, Redis, RabbitMQ, etc.
All of these can make sense depending on the problem, but sometimes things get complicated enough that you end up implementing several different ways of getting the same data from A to B, all at the same time.
If you’re already using Postgres and you have a simple case where you basically want a downstream database to keep a subset of your Postgres data in sync, Postgres already has a pretty cool solution built right in: logical replication.
I recently had to do some research on this topic, so I’m cleaning up my notes here so I don’t forget what I learned lol.
Table of Contents
Create instances
For this quick tour/demo, we need two Postgres instances: one publisher (the real source of truth) and one subscriber.
I’m going to use Docker here for this quick demo, but feel free to use whatever setup you prefer.
Since the two db instances need to talk to each other over the network, let’s first create a shared Docker network:
1docker network create pg-network
Second, spin up a Postgres instance. This will be the publisher:
1docker run --name pg-publisher \
2 --network pg-network \
3 -e POSTGRES_PASSWORD=postgres \
4 -p 5432:5432 \
5 -d postgres:18 \
6 -c wal_level=logical
Now let’s also create the subscriber instance:
1docker run --name pg-subscriber \
2 --network pg-network \
3 -e POSTGRES_PASSWORD=postgres \
4 -p 5433:5432 \
5 -d postgres:18
Check if they are running:
1docker ps
1❯ docker ps --format 'table {{.Names}}\t{{.Networks}}\t{{.Ports}}'
2
3NAMES NETWORKS PORTS
4pg-subscriber pg-network 0.0.0.0:5433->5432/tcp, [::]:5433->5432/tcp
5pg-publisher pg-network 0.0.0.0:5432->5432/tcp, [::]:5432->5432/tcp
So far so good.
To run SQL on it, exec into the container:
1docker exec -it pg-publisher psql -U postgres
You’ll get a postgres=# prompt. Or run a single command directly:
1docker exec -it pg-publisher psql -U postgres -c "SHOW wal_level;"
2
3 wal_level
4-----------
5 logical
6(1 row)
It should print logical, meaning the publisher is configured for logical replication so we’re good to go.
Initial setup on the publisher
Create tables
Now let’s set up the schema.
I’m keeping the schema deliberately simple for this demo, but the same replication setup works with pretty much any schema.
So, we have three tables:
1-- run this on the publisher
2
3-- A group can contain many users
4CREATE TABLE groups (
5 id SERIAL PRIMARY KEY,
6 name TEXT NOT NULL,
7 created_at TIMESTAMPTZ NOT NULL DEFAULT now()
8);
9
10-- A user can belong to many groups
11CREATE TABLE users (
12 id SERIAL PRIMARY KEY,
13 name TEXT NOT NULL,
14 email TEXT UNIQUE NOT NULL,
15 password TEXT NOT NULL,
16 enabled BOOLEAN NOT NULL DEFAULT TRUE,
17 created_at TIMESTAMPTZ NOT NULL DEFAULT now()
18);
19
20-- Join table that connects users and groups
21-- Note the ON DELETE CASCADE: deleting a group or user also deletes
22-- the corresponding rows from this table
23CREATE TABLE groups_users (
24 group_id INTEGER NOT NULL REFERENCES groups (id) ON DELETE CASCADE,
25 user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
26 added_at TIMESTAMPTZ NOT NULL DEFAULT now(),
27 PRIMARY KEY (group_id, user_id)
28);
Add some data
Now let’s put some rows on the publisher:
1-- on the publisher instance
2
3INSERT INTO users (name, email, password)
4VALUES
5 ('Biscuit', '[email protected]', 'woof123'),
6 ('Cannoli', '[email protected]', 'woof456'),
7 ('Momo', '[email protected]', 'meow789');
8
9INSERT INTO groups (name)
10VALUES ('dogs'), ('cats');
11
12-- Biscuit and Cannoli are dogs; Momo is a cat.
13INSERT INTO groups_users (group_id, user_id)
14VALUES
15 (1, 1),
16 (1, 2),
17 (2, 3);
Check what the publisher has:
1-- on the publisher
2
3SELECT count(*) FROM users;
4
5 count
6-------
7 3
8(1 row)
1-- on the publisher
2
3SELECT count(*) FROM groups;
4
5 count
6-------
7 2
8(1 row)
1-- on the publisher
2
3SELECT name FROM users;
4
5 name
6-----------------
7 Biscuit
8 Cannoli
9 Momo
10(3 rows)
Create a publication
Now we need a way to tell PostgreSQL what exactly to replicate between instances, so let’s create a publication.
1-- on the publisher instance
2
3CREATE PUBLICATION demo_pub
4FOR TABLE users (id, name, email, enabled, created_at),
5 groups,
6 groups_users;
Notice the column users.password is deliberately excluded because you can filter what gets replicated downstream.
Check the newly created publication:
1-- on the publisher
2
3SELECT * FROM pg_publication;
4
5 oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot | pubgencols
6-------+----------+----------+--------------+-----------+-----------+-----------+-------------+------------+------------
7 16443 | demo_pub | 10 | f | t | t | t | t | f | n
8(1 row)
Here you can see we have a new publication called demo_pub. (Ignore all the other junk for now.)
And that’s pretty much it for the publisher setup. Anything written to these tables is now tracked in the WAL and available for any subscriber to consume.
By the way, if you ever screw this up and want to get rid of a publication, you can just drop it like any other database object:
1-- on the publisher
2
3DROP PUBLICATION demo_pub;
That’s it.
Initial setup on the subscriber
Create tables
The subscriber needs a compatible schema with the publisher.
In our case, that means creating the same 3 tables, but only with the columns that we’re actually replicating.
We excluded the users table column password from the publication, so the subscriber instance doesn’t need that column.
1-- on the subscriber instance
2
3CREATE TABLE groups (
4 id SERIAL PRIMARY KEY,
5 name TEXT NOT NULL,
6 created_at TIMESTAMPTZ NOT NULL DEFAULT now()
7);
8
9CREATE TABLE users (
10 id SERIAL PRIMARY KEY,
11 name TEXT NOT NULL,
12 email TEXT UNIQUE NOT NULL,
13 enabled BOOLEAN NOT NULL DEFAULT TRUE,
14 created_at TIMESTAMPTZ NOT NULL DEFAULT now()
15);
16
17CREATE TABLE groups_users (
18 group_id INTEGER NOT NULL REFERENCES groups (id) ON DELETE CASCADE,
19 user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
20 added_at TIMESTAMPTZ NOT NULL DEFAULT now(),
21 PRIMARY KEY (group_id, user_id)
22);
Now, at this point, the subscriber has the schema but (of course) no data:
1-- on the subscriber instance
2
3SELECT count(*) FROM users;
4
5 count
6-------
7 0
8(1 row)
Set up a subscription
Creating a subscription is a single command:
1-- on the subscriber instance
2
3CREATE SUBSCRIPTION demo_sub
4 CONNECTION 'host=publisher port=5432 dbname=postgres user=postgres password=postgres'
5 PUBLICATION demo_pub;
In this example dbname=postgres and user=postgres are the defaults created by the Docker image. In production you’d just use your own database name and credentials.
Each subscription opens its own TCP connection to the publisher, creates a replication slot, and starts streaming.
Here’s the quickest check to see if the subscription is working:
1-- on the subscriber
2
3SELECT subname, worker_type, pid FROM pg_stat_subscription;
4
5 subname | worker_type | pid
6----------+-------------+-----
7 demo_sub | apply | 100
8(1 row)
The subscription demo_sub was created correctly, but what about the data? Was it really synced or what?
Let’s have a quick check:
1-- on the subscriber
2
3SELECT * FROM users;
4
5 id | name | email | enabled | created_at
6----+-----------------+------------------------------+---------+-------------------------------
7 1 | Biscuit | biscuit@featurefactory.lol | t | 2026-08-09 09:22:42.527945+00
8 2 | Cannoli | cannoli@featurefactory.lol | t | 2026-08-09 09:22:42.527945+00
9 3 | Momo | momo@featurefactory.lol | t | 2026-08-09 09:22:42.527945+00
10
11SELECT * FROM groups;
12
13 id | name | created_at
14----+------+------------------------------
15 1 | dogs | 2026-08-09 09:22:42.54744+00
16 2 | cats | 2026-08-09 09:22:42.54744+00
17
18SELECT * FROM groups_users;
19
20 group_id | user_id | added_at
21----------+---------+-------------------------------
22 1 | 1 | 2026-08-09 09:22:42.565506+00
23 1 | 2 | 2026-08-09 09:22:42.565506+00
24 2 | 3 | 2026-08-09 09:22:42.565506+00
25(3 rows)
And there you have it!
We have successfully replicated the initial data from the publisher into the subscriber!
INSERT/UPDATE/DELETE
This is all cool and fine, but what we really want to check is whether we can keep the two instances in sync with very little delay.
So let’s verify if real-time change propagation actually works.
INSERT
Let’s add another cat on the publisher:
1-- on the publisher
2
3INSERT INTO users (name, email, password)
4VALUES ('Kimuchi', '[email protected]', '5up3r5p1cy');
5
6-- Kimuchi is a cat so obviously:
7INSERT INTO groups_users (group_id, user_id)
8VALUES (2, 4);
Check the subscriber:
1-- on the subscriber
2
3SELECT id, name, email, enabled FROM users WHERE name = 'Kimuchi';
4
5 id | name | email | enabled
6----+---------+----------------------------+---------
7 4 | Kimuchi | kimuchi@featurefactory.lol | t
8(1 row)
The row instantly appears. Pretty cool, eh?
UPDATE
Kimuchi has just earned a PhD and, obviously, wants everyone to know about it.
Let’s update its name:
1-- on the publisher
2
3UPDATE users
4SET name = 'Dr. Kimuchi'
5WHERE email = '[email protected]';
Check the subscriber:
1-- on the subscriber
2
3SELECT name
4FROM users
5WHERE email = '[email protected]';
6
7 name
8-------------
9 Dr. Kimuchi
10(1 row)
The update was replicated automatically. No UPDATE was run on the subscriber.
DELETE
Dr. Kimuchi has decided to leave the company. After getting bored sick of web CRUD and API plumbing, it’s time for a career change: farming.
Let’s remove it from the publisher:
1-- on the publisher
2
3DELETE FROM users
4WHERE email = '[email protected]';
And, just like that, Dr. Kimuchi is gone from the subscriber too:
1-- on the subscriber
2
3SELECT count(*)
4FROM users
5WHERE email = '[email protected]';
6
7 count
8-------
9 0
10(1 row)
But holup a sec, Kimuchi was also a member of the cats group. What happened to that?
1-- on the subscriber
2
3SELECT count(*)
4FROM groups_users
5WHERE user_id = 4;
6
7 count
8-------
9 0
10(1 row)
The ON DELETE CASCADE on groups_users kicked in on the publisher, removing Kimuchi’s group membership along with the user. That resulting delete was replicated to the subscriber too.
Well then, good luck, Dr. Kimuchi! Hopefully your customers’ crazy specs are strictly limited to growing potatoes.
Conclusion
And that’s pretty much it for a basic introduction to how logical replication works.
I’m writing Part 2 right now, so please look forward to that! 🙏