Postgres Replication: Streaming + Logical

Your Database Is Lonely. Give It Friends.

Most home lab databases are solo acts. One Postgres server, one SSD, one catastrophic hardware failure away from zero backups. You know better than that. You’ve got the spare hardware, the network, the audacity. So why not replicate?

Here’s the thing: Postgres replication sounds like enterprise magic, but it’s practical for anyone running a database they actually care about. Read replicas fix the “single server crushing under SELECT traffic” problem. Standby replicas fix the “production went down on a Tuesday” problem. And if you pick the right replication strategy, you’re not managing a rocket ship control room—you’re managing a second server that Just Works.

This isn’t “set replication and forget it forever.” You need to understand two different animals: streaming replication (byte-for-byte copy, fast, dumb) and logical replication (selective tables, flexible, a little spicy). Both have their place. Pick wrong and you’ll be at 2 AM wondering why your 10 GB table replicated to a server that only needed 2 GB of specific data.

Streaming Replication: The Photocopier Approach

Streaming replication copies every single WAL (Write-Ahead Log) byte from the primary to the replica. It’s a carbon copy. The replica gets everything—every table, every index, every accidental mass update you’re regretting. It’s fast because Postgres just streams bytes and doesn’t think about what they mean.

This is your default play for:

• Failover standby replicas (one takes over if primary dies)
• Read-only replicas serving the same dataset
• Backup verification (“did our backup actually restore cleanly?”)
• Anything where the replica serves the same database as the primary

The catch? The replica starts as a physical copy of the primary. You take a base backup, ship it over, start streaming WAL, and boom—you have a replica. But if your primary is 500 GB and your replica server has only 200 GB, you’re not doing this approach. You need logical replication.

Logical Replication: The Selective Copier

Logical replication is Postgres’s way of saying “I only want these tables, not that bloated raw table you never clean up.” Instead of copying bytes, it decodes the WAL and replays specific changes to specific tables on the replica. The replica doesn’t have to be identical to the primary—it can have a subset of tables, different indexes, even different columns (careful).

Use logical replication when:

• Your replica is a specialized read server (analytics, reporting, a different schema)
• Your primary is 500 GB but the replica only needs 50 GB
• You’re feeding data to a different Postgres instance (not a replica of itself)
• You want to replicate to multiple downstreams without coordinating all of them
• You’re building a staging environment that tracks production selectively

Logical replication is slower than streaming because Postgres has to decode every change and replay it as SQL. But it’s flexible. You can filter tables, columns, even rows (with careful publication setup).

Setting Up Streaming Replication (Primary + Replica)

Let’s walk through the fast path: streaming replication with a read-only replica.

Primary Setup

On your primary server, enable replication in postgresql.conf:

# postgresql.conf
wal_level = replica           # required for any replication
max_wal_senders = 3           # max concurrent replication connections
max_replication_slots = 3     # reserved slots for each replica

Restart Postgres:

systemctl restart postgresql

Create a replication user:

sudo -u postgres psql
CREATE ROLE replicator WITH LOGIN REPLICATION ENCRYPTED PASSWORD 'strong_password_here';

Add the replica’s IP to pg_hba.conf:

host    replication     replicator    192.168.1.20/32    md5

Reload the config:

sudo -u postgres psql -c "SELECT pg_reload_conf();"

Replica Setup: Base Backup

On your replica server, stop any existing Postgres and take a base backup:

sudo systemctl stop postgresql
sudo -u postgres rm -rf /var/lib/postgresql/14/main/*
sudo -u postgres pg_basebackup \
  -h 192.168.1.10 \
  -D /var/lib/postgresql/14/main \
  -U replicator \
  -v -P -R

This connects to the primary, grabs a consistent snapshot, and streams the whole database. It takes as long as your primary is big—100 GB = a while. The pg_basebackup command also creates a standby signal file, telling Postgres on startup “hey, you’re a replica.”

Start the replica:

sudo systemctl start postgresql

Check the replica’s status:

sudo -u postgres psql
SELECT usename, state, write_lsn, flush_lsn, replay_lsn FROM pg_stat_replication;

If you see output: you’re replicating. The replica is now a read-only copy of your primary. You can SELECT from it all day. Writes go nowhere.

Logical Replication: Selective Tables

Logical replication is more fiddly because you’re saying which tables replicate.

Primary: Create a Publication

A publication is just “a list of tables the primary will offer to replicate.”

sudo -u postgres psql
-- Create a publication for specific tables
CREATE PUBLICATION my_pub FOR TABLE users, posts, comments;

Or replicate all tables in a schema:

CREATE PUBLICATION analytics_pub FOR ALL TABLES IN SCHEMA public;

Replica: Create a Subscription

On a different Postgres server (or the same one with a different database), subscribe to the publication:

sudo -u postgres psql -d mydb
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=192.168.1.10 user=replicator password=strong_password_here dbname=sourcedb'
  PUBLICATION my_pub;

Postgres automatically decodes the primary’s WAL, filters for the tables in my_pub, and replays the changes on the replica database. The replica tables must exist (same schema, same columns). Logical replication doesn’t copy table definitions—you have to create them yourself.

Check replication lag:

SELECT subscription_name, latest_end_lsn, latest_end_time FROM pg_stat_subscription;

Replication Slots: Don’t Forget to Manage Them

A replication slot is Postgres’s way of saying “don’t delete WAL files until the replica has caught up.” Slots are per-replica. If you don’t create one, WAL files pile up and your primary disk fills until it explodes at 2 AM.

Create a slot on the primary:

sudo -u postgres psql
SELECT pg_create_physical_replication_slot('replica_one');

For logical replication, slots are created automatically when you define the subscription. But you should still monitor them:

SELECT slot_name, slot_type, wal_status FROM pg_replication_slots;

Watch for slots that say lost or have no wal_status. That means the replica is gone or broken, and you’re leaking WAL files. Drop dead slots:

SELECT pg_drop_replication_slot('dead_slot_name');

Pro tip: set max_slot_wal_keep_size = '10GB' in postgresql.conf so a broken slot doesn’t eat your entire disk. Postgres will warn you before dropping WAL.

Conflicts: What Happens When the Replica Writes?

Streaming replicas are read-only. You can’t (shouldn’t) write to them. They’ll reject writes with “cannot execute UPDATE in a read-only transaction.”

Logical replicas? You can write to them. That’s the point—the replica might be running analytics jobs, staging environments, whatever. But if both primary and replica write to the same table, Postgres needs a conflict resolution strategy.

Logical replication conflict options (in the subscription):

CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=primary ...'
  PUBLICATION my_pub
  WITH (conflict_resolution = 'last_update_wins');  -- other options: apply_remote_change

• last_update_wins — if primary and replica both update the same row, the most recent timestamp wins
• apply_remote_change — always apply the primary’s change (replica loses)

If both servers are writing to the same tables, you’re not doing replication right. But if the replica has different tables, or different rows, no conflict. Logical replication was built for that.

When Do You Actually Need This?

Here’s the spicy truth: most home labs don’t need replication. A single Postgres server with good backups and a second drive for WAL archiving beats a half-baked replica setup. Replication is for when you have:

1. Traffic that matters — Your primary can’t handle all the SELECTs. Spin up read replicas, point your read-heavy apps there. Your primary does writes, replicas do reads. Done.

2. Uptime that matters — You need failover. A standby replica takes over if primary dies. You’ll need a failover orchestrator (Patroni, or manual VIP switching) but the infrastructure is there.

3. Data that can’t live on one server — Your primary is the source of truth for tables A, B, C. A specialist downstream database replicates only A, C for analytics without importing all of B. Logical replication is your tool.

4. Multi-region setups — You’ve got Postgres in the office and Postgres in the cloud, and they need to sync selectively. Logical replication handles this without needing identical hardware.

If you’re running a single server with a single app and decent backups? Replication is overengineering. It’s hiring a forklift to move a couch. But if you’ve got the hardware and the stakes, it’s boring infrastructure that Just Works.

The Decision Tree

Do I need my replica to be identical to my primary?

• Yes → Streaming replication. Fast, simple, transparent.
• No → Logical replication. Selective, flexible, slower.

Do I need my replica to accept writes?

• No → Streaming replication (read-only anyway).
• Yes → Logical replication (but be careful about conflicts).

How much disk space does my replica have?

• Same as primary (or more) → Streaming replication, no problems.
• Less than primary → Logical replication, replicate only what you need.

How many downstreams am I feeding?

• One → Streaming replication is fine.
• Many → Logical replication. Each subscriber manages its own lag independently.

Pick streaming replication and forget it exists—Postgres handles the rest. Pick logical replication and know that you’re managing which tables, which columns, when they sync, and what happens if both sides write. Neither is wrong. One is just a lot less thinking.

Your 2 AM self will appreciate either choice as long as you tested it before the server caught fire.

Related Reading

• DBeaver vs pgAdmin vs DataGrip
• PgBouncer for Connection Pooling
• Postgres in Docker: The Durable Setup
• PostGIS for Self-Hosted Mapping
• Postgres Backups: pg_dump vs pgBackRest vs WAL-G