Set Up PostgreSQL Streaming Replication Without Losing Your Sanity

Before diving into PostgreSQL replication setup, let's talk about what you actually need and what will go wrong if you skip these steps. I've been paged at 3am enough times to know which corners you can't cut.

Infrastructure Requirements

Two or more servers running identical PostgreSQL versions. And I mean IDENTICAL - not "close enough" versions. PostgreSQL 14.8 and 14.9 might look the same but will cause headaches when you try to failover. Use PostgreSQL 15+ if you can - the monitoring improvements and enhanced replication features alone are worth the upgrade.

Network connectivity that doesn't suck. Seriously, this is where most replication setups die. One network hiccup and your standby falls behind. I've seen WAL files pile up to 200GB during a weekend outage because someone's "enterprise-grade" network couldn't stay up for 48 hours. Use dedicated NICs for replication traffic if you have them - your database will thank you.

Sufficient disk space on both servers. Here's the thing nobody tells you: WAL files will eat your disk alive when the network shits the bed. I've seen 50GB databases generate 200GB of WAL over a weekend when the standby couldn't connect. Size your disk like you're planning for a disaster, because you are. Plan for at least 3x your current database size if you want to sleep well at night.

Security Considerations

Dedicated replication user with minimal privileges. Don't be an idiot and use postgres superuser for replication. Create a dedicated replication user with only REPLICATION privileges. I've seen entire databases compromised because someone thought "it's just internal traffic." Follow the principle of least privilege religiously.

SSL/TLS encryption for replication traffic. If you're sending unencrypted database traffic across any network that's not a directly connected cable, you're asking for trouble. Use SSL even on "private" networks - network segmentation is rarely as private as you think.

Firewall configuration restricting replication ports. Lock down port 5432 to specific IPs only. I've seen DBAs who opened it to 0.0.0.0/0 "temporarily" and forgot about it for months. Configure iptables properly or use cloud security groups.

Version Compatibility and Timing

Same major PostgreSQL versions required for streaming replication. PostgreSQL 14 and 15 won't play nice together with streaming replication. If you need cross-version, use logical replication instead - though that has its own set of gotchas.

Minimal downtime planning - this is a lie. While the docs say "brief restart," plan for 15-30 minutes because something will go wrong. First time I did this, had a typo in postgresql.conf that took me 2 hours to find. Turned a quick restart into a Saturday afternoon debugging clusterfuck. Test in staging first - production is not for learning.

These aren't just best practices - they're lessons learned from production failures. Skip them at your own risk. Next, we'll configure the primary server and watch things break in creative ways.

Streaming Replication Architecture Overview:

[Primary Server] ---> WAL Records ---> [Standby Server]
      |
   Generates
   WAL Data
      |
   Accepts
   Connections

The primary server needs to be configured to generate enough WAL data and accept replication connections. Don't expect this to work on the first try - PostgreSQL config files are designed by people who hate DBAs.

Essential postgresql.conf Settings

Configure WAL level for replication:

wal_level = replica

Set this to replica or your standby won't get enough WAL data to stay in sync. Don't use minimal unless you enjoy broken replication and 3am phone calls.

Set maximum WAL sender processes:

max_wal_senders = 5

Each standby eats one WAL sender process. Set this higher than you think you need because backup tools like pg_basebackup also consume senders. Found out the hard way at 2am when pg_basebackup failed with 'too many clients' and I had to explain to my boss why the backup window failed.

Configure WAL retention:

wal_keep_size = 2GB

This prevents WAL segments from being recycled before standbys can process them. 2GB sounds like a lot until your standby falls behind on a Friday night and you come back Monday to find all the WAL it needs has been deleted. Size this based on your write volume and how long you can afford to have broken replication. I've seen systems need 20GB+ during busy periods.

Enable archive mode (recommended):

archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/archive/%f && cp %p /var/lib/postgresql/archive/%f'

WAL archiving is your backup plan when streaming replication shits the bed. Make sure the archive directory actually exists and has proper permissions, or you'll get cryptic archive command errors that tell you nothing useful. Test your archive command manually before you need it.

Network and Connection Configuration

Configure listening addresses:

listen_addresses = '*'  # DON'T DO THIS IN PRODUCTION

Don't be lazy and use '*' in production. Specify exact IP addresses to limit your attack surface. Your security team will thank you, and you won't accidentally expose PostgreSQL to the internet like those MongoDB incidents and Elasticsearch breaches you read about.

Optimize connection limits:

max_connections = 200

Each replication connection eats one from your connection pool. Plan accordingly or watch your applications get connection errors when replication kicks in. I've seen production apps fail because nobody counted replication connections in their capacity planning.

Create Dedicated Replication User

Create a user specifically for replication - don't be an idiot and use the postgres superuser:

CREATE ROLE replication_user WITH REPLICATION LOGIN PASSWORD 'secure_random_password';

Store that password somewhere secure, not in a Slack channel named #passwords or Dave's desktop folder called 'important_stuff'. This user needs only REPLICATION privileges - don't give it more permissions because you're feeling generous.

Configure Host-Based Authentication

Edit pg_hba.conf to allow replication connections from standby servers. This is where most setups break:

## Allow replication from standby server
host    replication    replication_user    10.0.1.100/32    scram-sha-256

Replace 10.0.1.100 with your actual standby IP. Use scram-sha-256 authentication in PostgreSQL 13+ because md5 is deprecated and trust is for development only.

The moment of truth - restart PostgreSQL:

sudo systemctl restart postgresql

If PostgreSQL doesn't start, you probably have a typo in postgresql.conf. Check the logs and fix your mistake. Configuration troubleshooting errors will waste an hour of your Saturday.

The primary server is theoretically ready to accept replication connections. Next, we'll set up the standby and watch everything break in creative ways.

Time to set up the standby server and watch pg_basebackup fail in creative ways. This is where you'll learn why PostgreSQL replication documentation is optimistically vague about timelines.

Prepare the Standby Environment

Install identical PostgreSQL version on the standby server. And I mean IDENTICAL - not "close enough" or "probably compatible." PostgreSQL 15.3 and 15.4 might seem similar but will cause mysterious replication failures that will waste your entire weekend. Use the exact same packages from the exact same repository.

Stop PostgreSQL service on the standby before beginning:

sudo systemctl stop postgresql

Clear existing data directory (this is your point of no return):

sudo -u postgres rm -rf /var/lib/postgresql/15/main/*

Double-check your data directory path before running this. I've seen DBAs accidentally wipe their primary server because they copy-pasted the wrong command. Make sure you're on the RIGHT SERVER.

Ensure directory exists with correct permissions:

sudo -u postgres mkdir -p /var/lib/postgresql/15/main
sudo chmod 700 /var/lib/postgresql/15/main

Wrong permissions here will cause PostgreSQL to refuse to start with cryptic error messages.

Execute Base Backup with pg_basebackup

Here's where the fun begins. pg_basebackup will copy your entire database over the network. For a 1TB database, cancel your weekend plans:

sudo -u postgres pg_basebackup \
    -h 10.0.1.99 \
    -p 5432 \
    -U replication_user \
    -D /var/lib/postgresql/15/main \
    -Fp \
    -Xs \
    -P \
    -R \
    -W

Here's what these flags actually do:

• -h: Primary server IP (make sure it's reachable)
• -U: Replication user (that you actually created)
• -D: Where to dump the data (double-check this path)
• -Fp: Plain format (because tar would be too easy)
• -Xs: Stream WAL during backup (or your backup will be inconsistent)
• -P: Show progress (so you can watch it fail slowly)
• -R: Auto-create standby config (saves you manual work)
• -W: Force password prompt (because authentication always breaks)

Reality check: This command will fail the first 2-3 times because:

1. Wrong IP address or firewall blocking connection
2. Authentication failure in pg_hba.conf
3. Permission denied on destination directory
4. Network timeout during large database copy
5. Primary server out of WAL sender slots

For a 100GB database, expect 2-6 hours depending on your network. Watch the progress and pray your network doesn't hiccup.

Automatic Standby Configuration

The -R option creates the files you need (when it works):

standby.signal - Tells PostgreSQL this is a standby (don't delete this file)
postgresql.auto.conf - Contains connection info to the primary

Check what pg_basebackup actually generated:

sudo -u postgres cat /var/lib/postgresql/15/main/postgresql.auto.conf

Should look like:

primary_conninfo = 'user=replication_user host=10.0.1.99 port=5432 sslmode=prefer'

If it's wrong, edit it manually. The -R option sometimes generates incorrect connection strings.

Configure Standby-Specific Settings

Edit the standby's postgresql.conf for production:

## Enable read-only queries on standby
hot_standby = on

## Feedback to primary about long queries (causes primary bloat)
hot_standby_feedback = off

## Lower connections since this is read-only
max_connections = 100

## WAL receiver timeout settings
wal_receiver_timeout = 60s
wal_retrieve_retry_interval = 5s

Reality check:

• hot_standby_feedback = on prevents query cancellations but causes bloat on your primary
• Set max_connections lower on standby since it's read-only
• Timeout settings: shorter timeouts detect failures faster but are less tolerant of network hiccups

SSL Configuration for Secure Replication

For production, copy SSL certificates to the standby (don't skip this):

## Copy certificates from primary (or use your own CA certs)
sudo cp server.crt /var/lib/postgresql/15/main/
sudo cp server.key /var/lib/postgresql/15/main/
sudo chown postgres:postgres /var/lib/postgresql/15/main/server.*
sudo chmod 600 /var/lib/postgresql/15/main/server.key

Update postgresql.auto.conf to require SSL:

primary_conninfo = 'user=replication_user host=10.0.1.99 port=5432 sslmode=require'

Test SSL connectivity before starting replication or you'll waste time debugging SSL connection errors.

Start Standby Server (Moment of Truth)

Cross your fingers and start PostgreSQL:

sudo systemctl start postgresql

If it doesn't start, check the logs:

sudo tail -f /var/log/postgresql/postgresql-15-main.log

Look for these messages (or error messages explaining why everything's broken):

• "database system is ready to accept read only connections"
• "started streaming WAL from primary"
• "consistent recovery state reached"

If you see "FATAL: could not connect to the primary server", welcome to debugging replication connection problems. Check your network, pg_hba.conf, and authentication settings.

If everything worked (unlikely on first try), your standby is receiving changes from the primary. Test it by creating a table on primary and seeing if it appears on standby.