How to Set Up PostgreSQL Primary–Standby Replication on Ubuntu

iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more

Need a server to try this on? Antihost offers Linux VPS plans with NVMe storage, full root access and a 14-day free trial.

To set up PostgreSQL replication on Ubuntu, configure the writable server as the primary, create a dedicated replication login, permit the standby’s IP address in pg_hba.conf, and initialize the standby with pg_basebackup. PostgreSQL then streams write-ahead log (WAL) records from the primary to the standby. The default is asynchronous streaming: a healthy standby normally trails by a small amount, but a primary failure can lose commits that had not reached the standby.

This guide uses PostgreSQL’s current terms—primary and standby—while retaining “master-slave” only to match the older wording of the topic. It is written for Ubuntu packages but does not assume one Ubuntu release or PostgreSQL major version. Replace <major> with the installed major version, and confirm the service name and paths on both machines before changing files.

#	Preview	Product	Price
1		PostgreSQL 16 Administration Cookbook: Solve real-world Database Administration challenges with 180+...	$31.99	Buy on Amazon
2		PostgreSQL: Up and Running: A Practical Guide to the Advanced Open Source Database	$38.86	Buy on Amazon
3		PostgreSQL Mistakes and How to Avoid Them	$49.09	Buy on Amazon
4		Learn PostgreSQL: Use, manage, and build secure and scalable databases with PostgreSQL 16	$44.19	Buy on Amazon
5		Mastering PostgreSQL 17: Elevate your database skills with advanced deployment, optimization, and...	$41.99	Buy on Amazon

What PostgreSQL primary–standby replication does

Physical streaming replication copies the primary’s WAL records to one or more standby servers. The standby replays those records against a copy of the entire cluster, keeping databases, schemas and tables physically synchronized. It is useful for read scaling, disaster recovery and planned maintenance, but it is not an automatic failover system or a substitute for independent backups.

The primary accepts writes. A standby remains in recovery mode and normally rejects writes, although it can serve read-only queries when hot standby is enabled. Replication is at the cluster level: you replicate the PostgreSQL instance, not a selected database or table.

#1 Best Overall

Sale

PostgreSQL 16 Administration Cookbook: Solve real-world Database Administration challenges with 180+ practical recipes and best practices

$31.99

Buy on Amazon

Prerequisites and version checks

Prepare two hosts

• Use separate Ubuntu hosts with stable network connectivity between them on PostgreSQL’s listening port, normally TCP 5432.
• Install the same PostgreSQL major version on both hosts. Physical replication is not a migration mechanism between different major versions.
• Provide enough standby storage for a full copy of the primary data directory, future WAL retention and normal database growth.
• Synchronize clocks with a time service. Accurate timestamps make lag and incident diagnosis easier.
• Protect administrative access, restrict the database firewall rule to the standby address and use a private network where possible.
• Decide the standby’s permanent name, such as standby1, before configuring synchronous replication or a slot.

Find the installed paths and service

Ubuntu packages commonly place configuration under /etc/postgresql/<major>/main/ and the data directory under /var/lib/postgresql/<major>/main/, but packaging, clusters and custom installations can differ. On each host, run:

psql --version
pg_lsclusters
systemctl list-units --type=service | grep -i postgres

Use the cluster and service reported by those commands instead of assuming that postgresql or a particular versioned unit is correct.

Configure the primary

1. Set WAL and sender capacity

Edit the primary’s active postgresql.conf. Set a WAL level suitable for physical replication and reserve enough sender processes for every standby plus concurrent base-backup operations:

wal_level = replica
max_wal_senders = 4

The value of max_wal_senders must be greater than the number of standbys if you also run base backups or need spare capacity. PostgreSQL 17 documentation lists 10 as the documented default, but inspect the value on your installed version with SHOW max_wal_senders;. A value of zero disables replication.

wal_level and sender-capacity changes generally require a restart. Apply them with the service name for your cluster:

sudo systemctl restart postgresql
# Or use the versioned unit reported by pg_lsclusters

Restarting interrupts client connections, so schedule it appropriately. A later pg_hba.conf change requires only a reload.

2. Create a dedicated replication role

Do not use the application owner or the superuser for streaming. Create a login role with only the replication attribute, then set its password interactively so it is not exposed in shell history:

sudo -u postgres psql
CREATE ROLE replicator WITH REPLICATION LOGIN;
password replicator
q

Use a long, unique secret and store it on the standby with restrictive permissions.

3. Permit only the standby in pg_hba.conf

Add a narrowly scoped rule to the primary’s active pg_hba.conf. Replace STANDBY_IP with the standby’s actual address:

Rank #2

Sale

PostgreSQL: Up and Running: A Practical Guide to the Advanced Open Source Database

$38.86

Buy on Amazon

host    replication    replicator    STANDBY_IP/32    scram-sha-256

The special replication database field limits this rule to replication connections. Use an authentication method compatible with the role’s stored password and your PostgreSQL version. Avoid broad entries such as 0.0.0.0/0; a replication account should be reachable only from approved hosts.

Reload after saving:

sudo systemctl reload postgresql

Restrict the host firewall as well. For Ubuntu’s uncomplicated firewall, an example is:

sudo ufw allow from STANDBY_IP to any port 5432 proto tcp

Keep SSH and other administration paths separate from the database rule.

4. Create a physical replication slot when you will use one

A physical slot records the WAL position required by a particular standby. Create one on the primary before the base backup:

sudo -u postgres psql -c "SELECT pg_create_physical_replication_slot('standby1');"

Use a unique slot name for each physical standby. A slot prevents PostgreSQL from recycling WAL that the standby still needs, including the interval between completing the base backup and beginning streaming. It also creates a disk-retention responsibility: an abandoned slot can retain WAL until the primary fills its pg_wal filesystem.

Initialize the standby with pg_basebackup

1. Stop PostgreSQL and protect the target directory

Stop the standby cluster before replacing its data directory:

sudo systemctl stop postgresql

Confirm the real data directory with pg_lsclusters. If it contains data you may need, move it aside rather than deleting it:

sudo mv /var/lib/postgresql/<major>/main /var/lib/postgresql/<major>/main.old
sudo install -d -o postgres -g postgres -m 700 /var/lib/postgresql/<major>/main

Do not run pg_basebackup into a nonempty directory. A base backup replaces the standby’s existing cluster contents.

2. Store the replication password safely

On the standby, create a password file owned by the PostgreSQL operating-system account:

Rank #3

PostgreSQL Mistakes and How to Avoid Them

$49.09

Buy on Amazon

sudo -u postgres sh -c 'cat > /var/lib/postgresql/.pgpass <<EOF
PRIMARY_IP:5432:replication:replicator:REPLICATION_PASSWORD
EOF
chmod 600 /var/lib/postgresql/.pgpass'

Verify ownership and mode. A password embedded directly in a reusable configuration file can be exposed through backups or inappropriate file permissions; a protected .pgpass file or an equivalent secret manager is safer.

3. Run the consistent base backup

Run the utility from the standby, connecting to the primary through the replication protocol:

sudo -u postgres env PGPASSFILE=/var/lib/postgresql/.pgpass 
  pg_basebackup 
  -h PRIMARY_IP 
  -p 5432 
  -U replicator 
  -D /var/lib/postgresql/<major>/main 
  -Fp -Xs -P -R -S standby1

• -Fp requests plain files in the target data directory.
• -Xs streams required WAL while the backup runs.
• -P displays progress.
• -R writes standby startup metadata, including standby.signal and a connection setting.
• -S standby1 tells the backup to use the previously created physical slot.

pg_basebackup takes a consistent copy of the running cluster. If you are not using slots, omit -S, but ensure another WAL-retention method can keep all records needed by a disconnected standby. If the slot does not exist or the primary rejects the connection, the command stops without producing a usable replica.

Configure standby connection and startup

Check the generated connection settings

With -R, inspect the copied data directory for standby.signal and the generated primary_conninfo. The connection should identify the primary host, port, replication user and the protected password file. A typical manually maintained setting is:

primary_conninfo = 'host=PRIMARY_IP port=5432 user=replicator passfile=/var/lib/postgresql/.pgpass application_name=standby1'
primary_slot_name = 'standby1'

Set primary_slot_name only when that slot exists on the primary. If you use a different slot name, the two values must match exactly. Keep primary_conninfo readable only by the PostgreSQL account when it contains sensitive connection information.

Enable read-only queries if required

For a standby that should serve reads, set this in the standby configuration:

hot_standby = on

Hot standby does not make the server writable. Queries can be canceled when recovery must apply conflicting changes, and a long-running report can increase replay delay.

Start the standby and confirm recovery mode

sudo systemctl start postgresql
sudo -u postgres psql -c 'SELECT pg_is_in_recovery();'

The query should return t. If the service immediately stops, inspect the cluster log and verify ownership of every file in the data directory, the existence of standby.signal, the primary address and the password-file permissions.

Verify that streaming works

Check the primary’s replication view

Run this on the primary:

sudo -u postgres psql -c "SELECT application_name, client_addr, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag FROM pg_stat_replication;"

You should see a row for the standby. The important initial state is streaming. The sync_state value distinguishes asynchronous and synchronous candidates after synchronous settings are configured.

Rank #4

Learn PostgreSQL: Use, manage, and build secure and scalable databases with PostgreSQL 16

$44.19

Buy on Amazon

Check the standby receiver and replay position

On the standby, inspect receiver status and recovery positions:

sudo -u postgres psql -c "SELECT status, slot_name, latest_end_lsn, latest_end_time FROM pg_stat_wal_receiver;"
sudo -u postgres psql -c "SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();"

View columns can vary between major versions, so use d+ pg_stat_wal_receiver if a column is not present. A receiver row with a recent end position indicates that the standby is receiving WAL; replay position can still trail receive position while it applies work.

Perform a harmless end-to-end test

In a test database or a controlled maintenance window, write a probe row on the primary and read it on the standby:

CREATE TABLE IF NOT EXISTS replication_probe (
  id bigint PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO replication_probe (id) VALUES (1)
ON CONFLICT (id) DO UPDATE SET created_at = clock_timestamp();

After the primary acknowledges the transaction, query replication_probe on the standby. Do not attempt an insert there; a physical standby must remain read-only.

Choose asynchronous or synchronous streaming

Streaming replication is asynchronous by default. The primary can acknowledge a commit before the standby has received or flushed the corresponding WAL. If the primary crashes, the possible loss is every acknowledged commit not yet replicated to durable storage on a surviving standby at the failure moment. There is no universal number of seconds or transactions: the bound is the actual transport and replay lag under your workload.

Configure a synchronous standby deliberately

Give the standby a stable application_name in primary_conninfo, then configure the primary, for example:

synchronous_standby_names = 'FIRST 1 (standby1)'
synchronous_commit = on

FIRST 1 requires one currently connected, streaming member from the listed set. With synchronous_commit = on, the primary waits for the configured synchronous acknowledgement (remote flush). PostgreSQL also supports policies such as remote_write and remote_apply; remote_apply waits until the standby has replayed the transaction and therefore adds more latency. Choose the level to match whether durability or read-after-write visibility is the requirement.

Change synchronous settings carefully: requiring a single standby without a fallback can make production writes wait indefinitely during a standby outage. Test the failure behavior before relying on it.

Protect WAL and compare slots with archiving

A disconnected standby needs the WAL records between its last replayed position and the primary’s current position. Without a slot or a working archive, the primary may recycle those records. Once that happens, the standby cannot catch up and must be rebuilt from a new base backup.

Best Value

Sale

Mastering PostgreSQL 17: Elevate your database skills with advanced deployment, optimization, and security strategies

$41.99

Buy on Amazon

Many production designs use both: a slot for normal streaming continuity and archiving for recovery from longer outages or primary loss. Neither method replaces a separate backup and point-in-time recovery strategy.

Monitor replication as an operating requirement

Watch connection and lag

• Alert when a standby disappears from pg_stat_replication or its state is no longer streaming.
• Track receive, flush and replay positions, not just whether a TCP connection exists.
• Measure replay delay with WAL-position differences and the age of pg_last_xact_replay_timestamp(), while recognizing that a quiet primary can make timestamp age look misleading.
• For synchronous deployments, alert when no configured synchronous candidate is connected and streaming.

Watch slots, archives and disk

On the primary, inspect physical slots and the WAL they retain:

sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained FROM pg_replication_slots WHERE slot_type = 'physical';"

• Alert on an inactive slot whose retained size keeps growing.
• Alert on low free space in the filesystem containing pg_wal.
• Alert on failed or delayed WAL archive commands if archiving is enabled.
• Test alerts by stopping a non-production standby and confirming that the incident is visible before storage is exhausted.

Do not delete a slot merely to recover disk without first deciding whether the standby will be rebuilt. Dropping it can make the standby’s required WAL unavailable.

Failover, promotion and rejoining

Replication supplies a copy of the primary; it does not provide automatic leader election, client routing, fencing or split-brain prevention. Write a runbook that names the person or system authorized to promote, the client endpoint that changes, and the checks that prevent both servers from accepting writes.

Promote only after fencing the old primary

1. Confirm that the old primary is failed or isolate it from clients and the network. Fencing prevents it from accepting writes after promotion.
2. Assess the standby’s receive and replay positions and understand the potential asynchronous loss window.
3. Promote the standby with the command supported by your installed version, such as sudo -u postgres pg_ctl -D /var/lib/postgresql/<major>/main promote, or the SQL function SELECT pg_promote(); on versions that provide it.
4. Move applications to the new primary through your controlled DNS, service-discovery or connection-routing mechanism.
5. Verify that the promoted server is writable and that clients cannot still reach the fenced server.

Rebuild the old primary before rejoining

Do not simply start the old primary and point it at the new one. Its timeline and data may diverge. After the incident, stop it, preserve logs needed for diagnosis, remove or move aside its old data directory, and seed it again with pg_basebackup from the current primary. Then configure a new slot if required, start it as a standby and verify streaming before treating it as a failover target.

Troubleshoot a standby that is not streaming

No row in pg_stat_replication

• Check that the standby service is running and pg_is_in_recovery() returns true.
• Test TCP reachability from standby to the primary’s address and port.
• Confirm the primary listens on an address reachable from the standby and that the firewall permits only the intended source.
• Check the primary log for an authentication or pg_hba.conf rejection.

Authentication failures

• Verify the replication role exists, has LOGIN and REPLICATION, and has the password you expect.
• Confirm the HBA address matches the standby’s source address and that the authentication method matches the stored password format.
• Check that .pgpass belongs to the PostgreSQL operating-system user and is mode 600.
• Ensure primary_conninfo uses the same host, port, user and passfile.

The standby reports missing WAL

If logs say that a requested WAL segment has already been removed, the standby fell behind beyond local retention. Restore the missing records from a working archive if one exists; otherwise stop and reinitialize the standby with a fresh base backup. A slot prevents normal premature recycling but cannot recover WAL that was already removed or a slot that was dropped.

Streaming works but replay is behind

Separate receive lag from replay lag. Slow standby storage, long-running queries, recovery conflicts, heavy primary write volume or an undersized standby can let the receiver remain connected while replay falls behind. Inspect both LSNs, query activity and disk space before changing retention settings.

Replication is not a backup

A standby can replicate accidental deletes, corruption and operator mistakes. Promotion can also be performed at the wrong time. Maintain independent full backups and WAL archives, and regularly test point-in-time recovery to a separate environment. Define recovery objectives explicitly: asynchronous replication has a variable loss window, while synchronous replication trades latency and write availability for stronger commit durability.

Quick Recap

SaleBestseller No. 1

PostgreSQL 16 Administration Cookbook: Solve real-world Database Administration challenges with 180+ practical recipes and best practices

$31.99

SaleBestseller No. 2

PostgreSQL: Up and Running: A Practical Guide to the Advanced Open Source Database

$38.86

Bestseller No. 3

PostgreSQL Mistakes and How to Avoid Them

$49.09

Bestseller No. 4

Learn PostgreSQL: Use, manage, and build secure and scalable databases with PostgreSQL 16

$44.19

SaleBestseller No. 5

Mastering PostgreSQL 17: Elevate your database skills with advanced deployment, optimization, and security strategies

$41.99

Deployment checklist

• Both hosts run compatible PostgreSQL major versions and have verified network, storage and clock prerequisites.
• The primary has an appropriate wal_level, enough max_wal_senders and a narrowly scoped replication HBA rule.
• A dedicated replication login uses a protected secret.
• The standby was initialized from a consistent pg_basebackup, with -R or equivalent standby settings.
• The configured slot name, if used, exists on the primary and is monitored for retained WAL.
• pg_stat_replication shows state = 'streaming', and standby receive/replay positions advance.
• The team has selected asynchronous or synchronous behavior with an explicit data-loss and availability decision.
• Alerts cover connection loss, replay lag, archive failure, slot growth and low pg_wal space.
• A tested failover runbook covers fencing, promotion, client routing and rebuilding the old primary.
• Independent backups and point-in-time recovery have been tested.

Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.