Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 4 · Lesson 24 · 4 min read

PostgreSQL Replication vs SQL Server Always On

On this page
  1. Always On and transactional replication mapped
  2. Streaming replication: the AG equivalent
  3. Synchronous or asynchronous commit
  4. Replication slots, and the disk-full trap
  5. Logical replication: the transactional replication equivalent
  6. Check yourself

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 ServerPostgreSQLReplicates
Always On availability groupStreaming (physical) replicationThe whole cluster, block for block
Readable secondaryHot standbyRead-only queries on the standby
Transactional replicationLogical replicationChosen tables, as row changes
AG listener and automatic failoverNot 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:

SQL
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'change-me';
Code
# 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-256

On the standby, one command copies the primary and configures itself as a standby:

Shell
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 start

Compared 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:

SQL
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:

AGPostgreSQL
Asynchronous commitDefault. The primary doesn't wait for standbys.
Synchronous commitsynchronous_standby_names set, so commits wait for standby confirmation
Config
synchronous_standby_names = 'ANY 1 (standby1, standby2)'
synchronous_commit = on        # wait until a standby has flushed the WAL to disk

ANY 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:

SQL
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:

SQL
-- 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.