Step-by-step guide to setup a two-node PostgreSQL cluster on Ubuntu using streaming replication. Includes pg_basebackup, config tips, and failover gotchas.
Table of Contents
Introduction
To set up a two-node PostgreSQL cluster on Ubuntu, you’ll configure streaming replication between a primary and a standby server. You install PostgreSQL on both nodes, enable WAL archiving on the primary, use pg_basebackup to seed the standby, and verify replication health using pg_stat_replication.
TL;DR / Quick Answer
- Install
postgresqlandpostgresql-contribon both Ubuntu nodes (22.04/24.04). - Configure the primary’s
postgresql.confforwal_level = replicaandpg_hba.confto allow the replica’s IP. - Seed the standby using
pg_basebackup -R(the-Rflag writes thestandby.signalfile automatically). - Verify success by querying
pg_stat_replicationon the primary andpg_is_in_recovery()on the standby. - Caveat: This is asynchronous replication. If you need automatic failover without manual intervention, you must add a quorum manager like Patroni or repmgr.
Key Takeaways
- Two nodes is not a true HA cluster. It’s a primary-standby pair. You have data redundancy, but failover is manual unless you script it or add a third witness/quorum manager.
- Always use
pg_basebackupwith the-Rflag on modern PostgreSQL (v12+). It eliminates the old, error-prone method of manually creatingrecovery.conf. - The
pg_hba.conffile is the #1 source of replication failures. I’ve seen more “connection refused” errors from a missinghost replicationentry than any other issue. - Replication Slots are your friend. They prevent the primary from deleting WAL segments that the standby hasn’t received yet. Without them, your standby breaks the moment it falls behind.
- Test the failure. A standby that is “streaming” in
pg_stat_replicationisn’t guaranteed to be usable. Stop the primary and try to promote the standby in a staging environment first.
Prerequisites and Environment
Before we touch a single config file, get the basics right. I’ve seen teams waste hours because they forgot to sync time or open a firewall port.
- Two Ubuntu Servers: I’m using Ubuntu 22.04 LTS for this guide. The commands work on 24.04 as well. Ensure they can ping each other by hostname or IP.
- IP Addressing: Let’s assume Primary is
10.0.1.10and Standby is10.0.1.11. We are in theus-east-1region if you’re on AWS EC2. - PostgreSQL Version: We are using PostgreSQL 14 or 16 (both are common on Ubuntu LTS). The paths in this guide use
16/main, but adjust to14/mainif you’re on Jammy. - Time Sync: Run
timedatectlto ensure both servers have NTP active. Replication relies on WAL timestamps; skewed clocks make debugging a nightmare.
Pro Tip: If you are on AWS, ensure your Security Group allows TCP port
5432between the two nodes. Don’t open it to0.0.0.0/0. In my experience, 90% of “replication won’t connect” tickets are just security group misconfigurations.
Recommended Lab Hardware (If You’re Building This at Home)
Not everyone has a spare rack in a datacenter. If you’re labbing this on a desk—like I did for my first CKA prep—here’s the gear I’d actually buy. Nothing fancy. Just reliable hardware that won’t corrupt your WAL stream mid-test.
1. Beelink Mini PC (SER8 or EQR5) — Your Second Node
Your prerequisites call for two Ubuntu servers, but most people only have one workstation. Don’t buy a $1,200 tower just to practice replication. I’ve used Beelink mini PCs as staging nodes in my home lab for years—they’re cheap enough to buy two, they sip power, and the Ryzen 5/7 models handle PostgreSQL streaming replication without breaking a sweat. The SER8 (Ryzen 7 8745HS, 16GB DDR5) is my pick because the 2.5G LAN port keeps replication throughput healthy. The EQR5 (Ryzen 5 5500U, 16GB DDR4) is the budget-friendly alternative if you’re watching spend.
2. CyberPower CP1500PFCLCD UPS — Power Protection for Both Nodes
Here’s what most tutorials get wrong: they never mention power. If your primary loses power mid-transaction and your standby doesn’t, you’re testing crash recovery, not replication. And if pg_basebackup gets interrupted by a brownout, you’re rebuilding from scratch. A UPS solves this.
The CyberPower CP1500PFCLCD (1500VA/1000W, pure sine wave) has 12 outlets—enough for both mini PCs plus your switch and monitor on battery backup. Pure sine wave matters because it’s safe for the active PFC power supplies in modern mini PCs. I’d recommend against cheaper modified-sine-wave units for this use case; they can stress sensitive PSUs over time. The Tripp Lite OMNI1500LCDT is a decent fallback if the CyberPower is out of stock, but check your PSU spec first.
Real-World Gotcha: Don’t skip the UPS “just for a lab.” I watched a colleague lose three hours of replication testing because a thunderstorm knocked out power to his primary node mid-
pg_basebackup. The standby came up half-seeded and refused to start streaming. A $180 UPS would have saved that entire afternoon.
Disclosure: The hardware links above are Amazon affiliate links. If you buy through them, I earn a small commission at no extra cost to you. I only recommend gear I’ve actually used or would put in my own lab.
Step 1: Install PostgreSQL on Both Nodes
What it does: Installs the PostgreSQL server and the contrib package (which includes useful extensions) on both the primary and standby machines.
# Run on BOTH primary and standby
sudo apt update
sudo apt install -y postgresql postgresql-contribExpected Output: You’ll see systemd starting the default cluster on port 5432. By default, PostgreSQL on Ubuntu binds to localhost only. We’ll fix that in the next step.
Gotcha: Don’t skip postgresql-contrib. I’ve seen scripts fail because they assume pg_stat_statements or pgcrypto is available out of the box. It’s not.
Step 2: Configure the Primary Node for Replication
What it does: This edits the core configuration to allow the primary to send WAL data to other servers and tells the authentication system to accept replication connections from the standby.
First, open the main config file on the Primary Ubuntu node:
sudo nano /etc/postgresql/16/main/postgresql.confFind and modify these lines (use Ctrl+W to search):
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
wal_keep_size = 128MB
hot_standby = onWhy these settings?
listen_addresses = '*'allows remote connections. If you’re security-conscious, use the specific primary IP.wal_level = replicais the minimum requirement for physical streaming replication.max_wal_senderscontrols how many standby connections the primary can handle. 10 is plenty for a two-node setup.wal_keep_sizeis a safety net. If the standby disconnects, the primary keeps this much WAL data for it to catch up.
Next, edit the client authentication file:
sudo nano /etc/postgresql/16/main/pg_hba.confAdd this line to the bottom (replace the IP with your Standby’s IP):
host replication all 10.0.1.11/32 scram-sha-256Gotcha: The order matters. If you have a host all all 0.0.0.0/0 md5 line above your replication line, PostgreSQL might try to authenticate replication using the wrong rule. Always put specific rules above generic ones.
Restart the primary to apply:
sudo systemctl restart postgresqlStep 3: Create a Replication User (Security Best Practice)
What it does: Creates a dedicated user with the REPLICATION privilege. Don’t use the postgres superuser for replication. That’s a bad habit I’ve seen persist in “quick” setups.
sudo -u postgres psqlInside the psql shell:
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'YourStrongPasswordHere';
\qReal-World Gotcha: If you are using scram-sha-256 (the default on Ubuntu 22.04+), you must set a password. If you try to use peer or trust authentication for replication, it will fail because the standby connects over TCP/IP, not a Unix socket.
Step 4: Seed the Standby with pg_basebackup
What it does: pg_basebackup takes a consistent snapshot of the primary’s data directory and transfers it to the standby. The -R flag is critical: it writes the standby.signal file and appends the primary_conninfo to the auto-conf file, telling PostgreSQL to run as a standby.
⚠️ WARNING: This command is destructive on the standby. It overwrites the existing data directory.
On the Standby node:
First, stop the local PostgreSQL service:
sudo systemctl stop postgresqlClear the default data directory (be absolutely sure you are on the standby):
sudo rm -rf /var/lib/postgresql/16/main/*Run the backup (replace IP and password):
sudo -u postgres pg_basebackup \
-h 10.0.1.10 \
-U replicator \
-D /var/lib/postgresql/16/main \
-Fp -Xs -P -RSample Output:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
...
pg_basebackup: base backup completedWhat just happened? The -R flag created a file called standby.signal in the data directory. That file is the modern way (Postgres 12+) to tell the server “you are a replica.” It also added primary_conninfo to postgresql.auto.conf with the connection string.
Start the standby:
sudo systemctl start postgresqlStep 5: Verify Replication Health
What it does: Queries the primary’s system view to confirm the standby is connected and streaming. Then checks the standby to confirm it’s in recovery mode.
On the Primary:
sudo -u postgres psql -c "SELECT client_addr, state, sync_state, sent_lsn, replay_lsn FROM pg_stat_replication;"Expected Output:
client_addr | state | sync_state | sent_lsn | replay_lsn
-------------+-----------+------------+-----------+------------
10.0.1.11 | streaming | async | 0/3000148 | 0/3000148If the state is streaming, you’re golden. If it’s startup or empty, check the logs on both nodes.
On the Standby:
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"Expected Output:
pg_is_in_recovery
-------------------
tA result of t (true) means the server is a standby.
Comparison: Manual Streaming vs. Patroni
Here’s the thing: what we just built is a manual setup. It gives you a copy of your data, but if the primary dies, you have to promote the standby yourself. Here’s how the options stack up.
| Feature | Manual Streaming (This Guide) | Patroni + etcd |
|---|---|---|
| Automatic Failover | No (Manual pg_ctl promote) | Yes (10–30 seconds) |
| Split-Brain Protection | None (Requires manual fencing) | Yes (via DCS Quorum) |
| Complexity | Low | High (Requires etcd cluster) |
| Best For | Read scaling, warm standby, tight budgets | Production HA where uptime is SLA-critical |
In my experience: If you are running a small internal app where a 5-minute manual failover is acceptable, stick with manual streaming. If you are running a customer-facing API in us-east-1 that needs to survive an AZ failure automatically, do not try to build your own failover script. Install Patroni. I’ve seen homegrown failover scripts split-brain a database and corrupt data during a network partition. It’s not worth the risk.
Common Mistakes & How to Avoid Them
- Forgetting the
standby.signalfile: If you don’t use-Rwithpg_basebackup, the standby will start as a primary on the same data, causing a split-brain scenario. Always use-R. - Firewall blocking the replication port: The standby initiates the connection to the primary on port 5432. If
ufwis active on the primary (sudo ufw status), you mustsudo ufw allow from 10.0.1.11 to any port 5432. - Not setting a password for the replication user:
pg_hba.confwithscram-sha-256requires a password. If you create the user without a password and try to usetrustremotely, you’ll get a “no pg_hba.conf entry” error. - Ignoring replication lag: Just because
stateisstreamingdoesn’t mean the standby is keeping up. Checkpg_wal_lsn_diff(sent_lsn, replay_lsn)to see the lag in bytes. If it’s growing, your standby disk or network is too slow.
FAQ
Q: Can I write to the standby node?
A: No. A standby in hot_standby = on mode accepts read-only queries, but any INSERT, UPDATE, or DELETE will fail with ERROR: cannot execute INSERT in a read-only transaction.
Q: How do I promote the standby to primary manually?
A: On the standby, run sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main. The standby.signal file is removed, and the server opens for writes. You must then reconfigure your application connection strings to point to the new IP.
Q: What happens if the primary crashes and restarts?
A: The standby keeps streaming once the primary comes back. However, if the standby was promoted in the meantime, the old primary will refuse to start replication because the timelines have diverged. You’d need to rebuild the old primary from the new one using pg_basebackup.
Q: Is replication synchronous or asynchronous here?
A: By default, it is asynchronous. There is a tiny window where the primary can commit a transaction and crash before the standby receives it. If you need zero data loss (RPO=0), you must set synchronous_standby_names = 'standby1' on the primary, but expect higher write latency.
Q: Do I need a third node for quorum?
A: For pure streaming replication, no. But for automatic failover (Patroni), you absolutely need a third node (or a witness) running etcd. A two-node etcd cluster cannot maintain quorum if one node fails, so Patroni will refuse to failover, leaving you with a dead primary and a passive standby.
Conclusion
Setting up a two-node PostgreSQL cluster on Ubuntu isn’t rocket science, but it requires attention to detail in pg_hba.conf and a healthy respect for the difference between “replication” and “high availability.” This manual setup is perfect for read scaling or a warm standby where you can tolerate a few minutes of manual intervention.
Your next step: Don’t let this sit untested. Stop the primary right now (sudo systemctl stop postgresql) and practice promoting the standby. The first time you do it shouldn’t be during a Black Friday outage.
Recommended Courses
If you’re serious about mastering databases and want to level up your skills, I highly recommend “SQL and PostgreSQL: The Complete Developer’s Guide” by Stephen Grider. This course takes you from the fundamentals of SQL to advanced PostgreSQL concepts, helping you write efficient queries, design robust databases, and build applications that scale.
Whether you’re a beginner exploring databases for the first time or a developer looking to strengthen your backend expertise, this course provides practical, hands-on knowledge you can apply right away. Enroll now through my affiliate link and start building real-world skills that will give you a competitive edge in your career.
Disclosure: This post contains affiliate links. If you purchase through these links, I may earn a small commission at no extra cost to you, which helps support this site.
References
- PostgreSQL Official Docs: Log-Shipping Standby Servers (postgresql.org/docs/current/warm-standby.html)
- Ubuntu Server Guide: PostgreSQL (ubuntu.com/server/docs/databases-postgresql)
- Red Hat Documentation: Configuring PostgreSQL Streaming Replication (access.redhat.com/documentation)
Leave a Reply
You must be logged in to post a comment.