PostgreSQL Replication vs SQL Server Always On
On this page
Always On and transactional replication mapped
PostgreSQL has two built-in replication mechanisms, and they line up with two SQL Server features you already know:
| SQL Server | PostgreSQL | Replicates |
|---|---|---|
| Always On availability group | Streaming (physical) replication | The whole cluster, block for block |
| Readable secondary | Hot standby | Read-only queries on the standby |
| Transactional replication | Logical replication | Chosen tables, as row changes |
| AG listener and automatic failover | Not built in. Use Patroni or similar (Day 25). |
Ask what the current setup does. If it keeps a copy ready for failover or serves read traffic, that's streaming replication. If it feeds selected tables to a reporting server or another system, that's logical replication.
Streaming replication: the AG equivalent
The primary sends its write-ahead log (WAL) to each standby as it's written. The standby replays it and stays an exact copy of the primary, including every database, table and index. Standbys accept read-only queries while they replay, which is hot standby.
On the primary:
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'change-me';# postgresql.conf (wal_level = replica is the default)
max_wal_senders = 10
# pg_hba.conf
host replication replicator 10.0.0.0/24 scram-sha-256On the standby, one command copies the primary and configures itself as a standby:
pg_basebackup -h db-primary -U replicator -D /var/lib/postgresql/18/main \
-Fp -Xs -P -R --slot=standby1 --create-slot
pg_ctl -D /var/lib/postgresql/18/main startCompared with setting up an AG, there's no seeding wizard, no endpoints, and no Windows cluster underneath. That simplicity is real, but so is what's missing: no listener and no automatic failover. Day 25 adds those.
Check replication from the primary, much as you'd use the AG dashboard:
SELECT application_name, client_addr, state, sync_state, replay_lag
FROM pg_stat_replication;On a standby, SELECT pg_is_in_recovery(); returns true.
Synchronous or asynchronous commit
These are the AG availability modes under other names:
| AG | PostgreSQL |
|---|---|
| Asynchronous commit | Default. The primary doesn't wait for standbys. |
| Synchronous commit | synchronous_standby_names set, so commits wait for standby confirmation |
synchronous_standby_names = 'ANY 1 (standby1, standby2)'
synchronous_commit = on # wait until a standby has flushed the WAL to diskANY 1 across two standbys means one slow or failed standby doesn't stall commits. With a single named synchronous standby, writes on the primary wait whenever that standby is down. Use remote_apply instead of on if reads on the standby must see each committed change immediately.
Replication slots, and the disk-full trap
The --slot option above creates a replication slot. It makes the primary keep WAL until that standby has received it, so a standby that's offline for a while can catch up instead of being rebuilt.
The trap: if a standby is decommissioned but its slot isn't dropped, the primary keeps WAL forever and eventually fills its disk. It's similar to the SQL Server log growing because an AG replica stopped syncing. Protect against it:
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;Set max_slot_wal_keep_size to cap how much WAL a slot can hold, and alert on inactive slots.
Logical replication: the transactional replication equivalent
Logical replication sends row changes for the tables you choose. It uses a publication on the source and a subscription on the target:
-- on the source, which needs wal_level = logical (requires a restart)
CREATE PUBLICATION sales_pub FOR TABLE sales.orders, sales.customers;
-- on the target
CREATE SUBSCRIPTION sales_sub
CONNECTION 'host=db-primary dbname=salesdb user=replicator'
PUBLICATION sales_pub;Because it works at row level, the target can run a different major version, have extra indexes or tables, and accept its own writes. That makes it the standard way to upgrade major versions with minimal downtime: replicate to a new cluster, then switch over.
Know its limits before choosing it:
- Schema changes don't replicate. Apply DDL to both sides yourself.
- Sequences don't replicate. Set them on the target before it takes over writes.
- Tables need a primary key, or a replica identity, so updates and deletes can find their row.
- Conflicts stop replication. A duplicate key on the target halts the subscription until you fix it.
In DataGrip, add the primary and each standby or subscriber as separate data sources, and keep the replication queries above in a query file for each.
Check yourself
A primary with a streaming-replication hot standby, used for reporting reads, plus a failover tool such as Patroni and a stable endpoint for clients (Day 25).
A replication slot makes the primary keep every WAL segment its consumer hasn't confirmed, so an abandoned slot retains WAL forever. Once you've confirmed old_dr_site is no longer used, drop it with SELECT pg_drop_replication_slot('old_dr_site');. Then set max_slot_wal_keep_size to cap retention, and alert on inactive slots.
Logical replication, with a publication containing those three tables and a subscription on the reporting server. It replicates row changes for chosen tables and works across major versions. Streaming replication copies the entire cluster byte for byte and requires the same major version, so it can't do either.
Each commit waits until a standby confirms it received the WAL, which adds a network round trip to every write, and if no synchronous standby is available, commits stop. Use synchronous_standby_names with ANY 1 (...) and several standbys so one standby's maintenance doesn't halt writes.