---
title: "PostgreSQL Replication"
description: "Setting up streaming replication with primary and standby servers, choosing synchronous vs asynchronous mode, monitoring replication lag, and using logical replication for selective table syncing."
url: https://agent-zone.ai/knowledge/databases/postgres-replication/
section: knowledge
date: 2026-02-22
categories: ["databases"]
tags: ["postgresql","replication","streaming-replication","logical-replication","high-availability","standby"]
skills: ["postgres-replication","high-availability","failover-management"]
tools: ["postgresql","psql","pg_basebackup"]
levels: ["intermediate"]
word_count: 521
formats:
  json: https://agent-zone.ai/knowledge/databases/postgres-replication/index.json
  html: https://agent-zone.ai/knowledge/databases/postgres-replication/?format=html
  api: https://api.agent-zone.ai/api/v1/knowledge/search?q=PostgreSQL+Replication
---


# PostgreSQL Replication

Streaming replication gives you a full binary copy for high availability and read scaling. Logical replication gives you selective table-level syncing between databases that can run different PostgreSQL versions.

## Streaming Replication Setup

### Configure the Primary

```
# postgresql.conf
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB
```

Create a replication role and allow connections:

```sql
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'repl-secret';
```

```
# pg_hba.conf
host  replication  replicator  10.0.0.0/8  scram-sha-256
```

### Initialize the Standby

```bash
sudo systemctl stop postgresql-16
sudo rm -rf /var/lib/postgresql/16/main/*
pg_basebackup -h primary-host -U replicator -D /var/lib/postgresql/16/main \
  --checkpoint=fast --wal-method=stream -R -P
sudo chown -R postgres:postgres /var/lib/postgresql/16/main
sudo systemctl start postgresql-16
```

The `-R` flag creates `standby.signal` and writes connection info to `postgresql.auto.conf`. The standby now continuously receives and replays WAL from the primary, accepting read-only queries by default.

## Replication Slots

Without a slot, the primary can recycle WAL before the standby consumes it, forcing a full re-sync.

```sql
-- On primary
SELECT pg_create_physical_replication_slot('standby1');
```

Add `primary_slot_name = 'standby1'` on the standby. Warning: if the standby goes offline, the slot prevents WAL cleanup and the primary's disk fills up. Monitor and drop unused slots:

```sql
SELECT slot_name, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots;
SELECT pg_drop_replication_slot('standby1');  -- if no longer needed
```

## Synchronous vs Asynchronous

Default is **asynchronous** -- the primary commits without waiting for standby confirmation. The standby can lag slightly, and you lose recent transactions if the primary crashes.

**Synchronous** makes the primary wait for standby confirmation before acknowledging commits:

```
# postgresql.conf on primary
synchronous_standby_names = 'standby1'
synchronous_commit = on
```

The standby's `application_name` in `primary_conninfo` must match. Trade-off: zero data loss but every write adds network latency. If the standby is unreachable, writes on the primary stall. For most setups, asynchronous with replication slots is the right default.

## Monitoring Replication Lag

On the primary:

```sql
SELECT client_addr, application_name, state, sync_state,
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
  replay_lag
FROM pg_stat_replication;
```

On the standby:

```sql
SELECT pg_is_in_recovery() AS is_standby,
  now() - pg_last_xact_replay_timestamp() AS replay_delay;
```

Alert if `replay_lag_bytes` exceeds a few MB or `replay_delay` exceeds a few seconds.

## Promoting a Standby

When the primary fails:

```bash
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main
# Or via SQL: SELECT pg_promote();
```

After promotion: update application connection strings to the new primary. The old primary cannot rejoin automatically -- use `pg_rewind` to re-sync it:

```bash
pg_rewind --target-pgdata=/var/lib/postgresql/16/main \
  --source-server="host=new-primary port=5432 user=postgres" -P
```

Then create `standby.signal` and configure `primary_conninfo` pointing to the new primary.

## Logical Replication

Publishes changes from specific tables. The subscriber applies changes as SQL, so it can have different indexes or a different PostgreSQL version.

On the publisher:

```
wal_level = logical
```

```sql
CREATE PUBLICATION my_pub FOR TABLE orders, customers;
```

On the subscriber:

```sql
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=publisher-host port=5432 dbname=myapp user=replicator password=repl-secret'
  PUBLICATION my_pub;
```

The subscriber does an initial sync, then applies changes in real time. Monitor:

```sql
SELECT subname, received_lsn, latest_end_lsn FROM pg_stat_subscription;
```

## Common Failures

**Standby cannot connect:** Check `pg_hba.conf` allows `replication` type, network/firewall, and that the replicator role has the `REPLICATION` attribute.

**Standby falls behind permanently:** WAL files recycled before the standby read them (no slot). Re-initialize with `pg_basebackup`.

**Replication slot causing disk bloat:** Offline standby with active slot prevents WAL cleanup. Drop the slot if the standby is gone.

**Split-brain after failover:** Two servers accepting writes. Pick the authoritative server, `pg_rewind` the other. Proper fencing (STONITH) prevents this.