What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
PostgreSQL clustering on Linux is normally a high-availability primary/standby design, not a transparent multi-writer database. One server accepts writes, one or more standbys receive WAL (write-ahead log) records, and a promotion process moves the writer role when the primary fails. A production setup also needs client routing, fencing, monitoring, independent backups, and a practiced rejoin procedure. You can operate failover manually with PostgreSQL’s native tools, or automate it with an orchestrator such as Patroni or pg_auto_failover.
Need a server to try this on? Antihost offers Linux VPS plans with NVMe storage, full root access and a 14-day free trial.
What PostgreSQL clustering means on Linux
PostgreSQL does not provide a general-purpose, conflict-free multi-primary cluster in which every node can accept the same writes transparently. The official high-availability guidance emphasizes that propagating writes consistently to every server is difficult. The practical pattern is therefore a single primary plus one or more standbys.
As an Amazon Associate I earn from qualifying purchases.
Primary and standby roles
The primary handles read/write traffic and generates WAL. A standby receives WAL through continuous archiving or streaming replication and replays it locally. If the primary becomes unavailable, an operator or orchestrator promotes a standby to primary. Applications reconnect through a stable endpoint that follows the current leader.
What replication does not solve
Replication improves availability, but it does not replace backups. An accidental DROP TABLE, bad application update, or corrupted transaction can be replicated to every standby. Keep independent backups and verify point-in-time recovery (PITR) separately.
#1 Best Overall
| Design choice | What it provides | Main trade-off |
|---|---|---|
| WAL archiving | Files are copied to another host or archive for standby recovery and PITR. | Recovery and failover are generally slower than live streaming; archive delays create a larger recovery-point gap. |
| Streaming replication | WAL is sent continuously from a running primary to a standby. | Asynchronous streaming can lose transactions that were committed but had not reached the standby when the primary failed. |
| Manual promotion | Simple tooling and explicit human control over failover. | Recovery time depends on detection, decision-making, fencing, and client rerouting. |
| Automated orchestration | Health checks, leader election, promotion, and endpoint integration can happen without a person at the console. | Additional software, a coordination service, and carefully designed fencing are required. |
Define the failure model before installing PostgreSQL
Write down the outcome you require before choosing replication mode or an orchestrator. The correct design depends on the data-loss window and downtime your application can tolerate.
Set recovery objectives
- Recovery point objective (RPO): the maximum amount of acknowledged or recent data you can lose. Asynchronous replication has a loss window bounded by replication lag and the time needed to detect and promote a standby.
- Recovery time objective (RTO): the maximum interruption you can accept while detecting the failure, fencing the old primary, promoting a standby, and reconnecting clients.
- Maintenance objective: whether you need planned switchover for patching, or only emergency failover.
- Read-scaling objective: whether applications may read from standbys, and whether they can tolerate replica lag or read-after-write inconsistency.
Choose a failure domain
A primary and standby in one rack protect against a host failure but not a rack, power, or network event. Separate hosts into different availability zones or data centers when the failure model includes a site outage. Cross-zone synchronous replication can preserve acknowledged writes, but network interruptions can delay or block writes.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Provision Linux hosts and secure the base installation
Start with at least two Linux hosts: one primary and one standby. Production deployments commonly add a third PostgreSQL member or a separate coordination quorum so that losing one machine does not leave the system without redundancy or a decisive vote.
| Requirement | Checks to complete |
|---|---|
| PostgreSQL software | Install the same PostgreSQL major version and compatible extensions on every member. Keep configuration and extension versions aligned during upgrades. |
| Network paths | Give the standby a reliable route to the primary and give clients a route to the routing layer. Measure latency and packet loss between failure domains. |
| Time | Synchronize clocks with the Linux time service. Consistent timestamps simplify logs, certificates, monitoring, and incident reconstruction. |
| Firewall | Allow PostgreSQL traffic only between approved hosts and allow the orchestrator or routing layer to reach the ports it needs. Do not expose database administration ports broadly. |
| Storage | Size data, WAL, temporary space, and backup destinations independently. Leave headroom for replication lag, vacuum activity, and a large transaction. |
| Administration | Use SSH keys or another controlled administrative method, least-privilege operating-system accounts, and an audit trail for promotion actions. |
Install matching versions and configure native replication
1. Create a dedicated replication role
Run the following SQL on the primary, replacing the password with a secret stored outside shell history and source code:
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'use-a-long-secret';Do not reuse the application account for replication or administration. Application users should be non-superusers with only the privileges their services require.
2. Authorize the standby in pg_hba.conf
Add a narrowly scoped rule on the primary for the standby’s address range. Use the authentication method required by your PostgreSQL version and security policy; SCRAM is the usual modern choice.
host replication replicator 10.20.0.12/32 scram-sha-256Reload PostgreSQL after editing the file, and verify that the rule is not shadowed by an earlier, broader rule. Restrict the address to the standby rather than permitting an entire network unnecessarily.
Rank #2
3. Set WAL sender and retention parameters
Configure the primary’s postgresql.conf with values appropriate for the number of standbys and the expected workload. The exact numbers are capacity decisions, not universal defaults.
| Setting or feature | Purpose | Operational decision |
|---|---|---|
wal_level = replica |
Emits the WAL information needed for physical standby replication. | Set consistently on the primary before taking the standby base backup. |
max_wal_senders |
Reserves WAL sender processes for standbys and backup clients. | Allow headroom for every standby, backup job, and planned replacement node. |
archive_mode and archive_command |
Copy completed WAL segments to an independent archive. | Make the destination durable and monitor failures; an archive command that silently fails undermines PITR. |
| Replication slots | Keep WAL required by a specific standby from being removed too early. | Slots prevent a lagging standby from missing WAL, but an abandoned slot can fill the primary’s disk. Alert on retained WAL and remove unused slots deliberately. |
wal_keep_size |
Retain a configured amount of recent WAL locally. | Use it as a safety margin, not as a substitute for slots or archiving; size it for the longest expected outage and write rate. |
Reload settings that support a reload and restart only when PostgreSQL reports that a restart is required. Check the effective values with SHOW commands and record them in your deployment documentation.
4. Take a base backup for the standby
Stop any temporary PostgreSQL instance on the standby, ensure its data directory is empty or safely moved aside, and run pg_basebackup from the standby host. The path differs by distribution and PostgreSQL major version.
pg_basebackup -h primary.example.internal -D /var/lib/postgresql/16/main -U replicator -Fp -Xs -P -RThe -R option writes standby connection information and creates the recovery signal needed by current PostgreSQL releases. Use the data-directory path belonging to your installed major version. Protect the resulting connection string, especially if it contains a password.
5. Configure and start the standby
Confirm that the generated primary_conninfo names the primary, replication role, and an authentication method that matches pg_hba.conf. Give the standby a distinct application_name so it is identifiable in pg_stat_replication. Start PostgreSQL and inspect the logs for connection and replay messages.
On the primary, check the sender view:
SELECT application_name, client_addr, state, sync_state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;On the standby, check replay progress:
SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), now() - pg_last_xact_replay_timestamp() AS replay_delay;A standby should report recovery mode, receive WAL, and advance its replay position while the primary generates traffic. Treat a permanently increasing delay, a disconnected sender, or a full filesystem as an incident rather than assuming replication will catch up.
Choose asynchronous or synchronous replication deliberately
Asynchronous mode
Streaming replication is asynchronous by default. The primary confirms a commit without waiting for a standby to flush and replay that WAL. This keeps write latency independent of standby network health, but a primary crash can lose transactions that were committed locally and not yet replicated. The loss is usually limited by current lag, yet the exact window varies with workload and failure timing.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallSynchronous mode
Synchronous replication makes commit success depend on acknowledgement from a configured synchronous standby. It can reduce the risk of losing acknowledged writes, especially across a planned failure domain, but writes may block or slow when the required standby is unavailable or the network is unhealthy. Define which acknowledgement level you require and test the behavior during a zone or link outage before enabling it in production.
Manual failover with PostgreSQL tools
Native PostgreSQL failover is appropriate when a trained operator can respond within your RTO and you prefer fewer moving parts. It is not a single command; the order of fencing, promotion, routing, and verification matters.
- Confirm the incident. Check host health, PostgreSQL logs, network reachability, and replication state. Do not promote merely because one monitoring probe failed.
- Fence the old primary. Shut it down, isolate its network, or use a power-management control that prevents it from accepting writes. Fencing is essential: an old primary that continues serving writes can create split-brain.
- Confirm the candidate standby. Verify it has received the latest available WAL, has enough disk space, and is not itself corrupted or isolated.
- Promote the standby. You can use the PostgreSQL promotion utility or execute
SELECT pg_promote();as an authorized database user. Watch logs until the server leaves recovery and accepts writes. - Move the writer endpoint. Update HAProxy, DNS, a virtual IP, or another routing layer so new application connections reach the promoted server. Existing connections may need to be closed and retried.
- Validate application behavior. Run a write and read test, check error rates, and confirm that background workers do not continue using a stale connection pool.
- Rebuild or rejoin the old primary. Keep it stopped until its timeline and data are reconciled. Use a supported rewind procedure when prerequisites are met, or take a fresh base backup. Never simply start the old primary and point it at the new leader.
Document who may authorize each step, the exact fencing mechanism, and the endpoint change. A runbook that has not been exercised under load is not a reliable RTO.
Automate failover with Patroni
Patroni is a Python template for PostgreSQL high availability. It manages PostgreSQL members, exposes a REST API, coordinates leadership through a distributed configuration store (DCS), and commonly works with HAProxy to present one application endpoint.
Patroni building blocks
- PostgreSQL members: a primary and standby use streaming replication. Patroni controls startup, role changes, and configuration that must be consistent across members.
- DCS: etcd, ZooKeeper, or Consul stores cluster state and provides leader-election semantics. Production DCS deployments need an odd-numbered, fault-tolerant quorum.
- Health and lag policy: Patroni can use asynchronous replication with a maximum-lag policy, or synchronous replication when stronger durability is worth the write-latency and availability trade-off.
- Routing: HAProxy examples send writes to the current leader and can expose a separate read service for replicas.
Important topology limits
A two-node PostgreSQL pair is common and can fail over automatically, but after one member fails there is temporarily no redundancy until it rejoins. The DCS must still retain quorum; placing a two-node database pair behind a two-node coordination service does not provide a decisive majority after one failure. For multi-zone designs, Patroni documentation recommends an odd number of coordination nodes, PostgreSQL members in separate failure domains, and synchronous mode when the goal is to survive a zone failure without losing acknowledged writes.
Patroni operating precautions
Keep Patroni’s administrative credentials and REST API access separate from application credentials. Restrict the API to trusted management networks and use authentication and TLS where required. Set a maximum acceptable replica lag so an excessively stale standby is not promoted automatically. Test how Patroni behaves when the DCS is unreachable, when the primary is slow rather than completely down, and when a network partition isolates members.
Automate failover with pg_auto_failover
pg_auto_failover uses a monitor and a state machine managed by pg_autoctl. The monitor tracks node health and assigns roles; PostgreSQL nodes follow those assignments and perform controlled transitions.
Packages and supported versions
The project publishes Linux packages for popular distributions and documents compatibility with PostgreSQL 13 through 18. On Debian or Ubuntu, documented package names include pg-auto-failover-cli and a versioned PostgreSQL auto-failover package. Match the package to the PostgreSQL major version installed on each node.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchesStartup and systemd rules
Do not allow the distribution’s default PostgreSQL systemd service to compete with pg_autoctl. Generate and install the pg_autoctl systemd unit, and let pg_autoctl start and stop PostgreSQL so a rebooted node rejoins with the role assigned by the monitor. Running two independent service managers can start a node with an obsolete role and create a split-brain risk.
Typical topology
Deploy a monitor in a failure domain independent of the database pair when possible, then register the primary and standby with pg_autoctl. Verify monitor reachability, replication slots, firewall rules, and service ordering before simulating a failure. As with any automation, fencing and a tested rejoin path remain your responsibility; a state machine cannot safely resolve a network partition if both sides can still accept writes.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Put applications behind a stable endpoint
Applications should not contain a hard-coded hostname for whichever server happened to be primary during installation. Use HAProxy, a virtual IP, service discovery, or another routing layer with explicit health checks.
Writer routing
Expose one writer endpoint that accepts connections only from the current leader. The health check must distinguish a primary from a standby in recovery; a port that merely accepts TCP connections is not sufficient. On promotion, the routing layer should remove the old leader and add the new one without requiring application configuration changes.
Recommended Free Tools
Read routing
Expose standbys for reads only when the application can tolerate replica lag. A read immediately following a write may reach a standby that has not replayed that transaction. Keep strongly consistent reads on the writer, or implement an application-level policy that waits for an appropriate replay position.
Connection and retry behavior
Use bounded connection timeouts, retry with backoff, and make transactions safe to retry. During promotion, existing sessions can fail even when the database has recovered; pools must discard broken connections and establish new ones through the stable endpoint.
Test failover, recovery, and rejoin
Schedule drills before a real outage forces an untested procedure. Record timestamps for detection, fencing, promotion, endpoint change, and the first successful application write.
- Generate representative write traffic and confirm that the standby is receiving and replaying WAL.
- Stop PostgreSQL on the primary, then test the monitoring alert and the documented fencing action.
- Promote the standby manually or let the chosen orchestrator do so, depending on the production mode.
- Verify the writer endpoint, application retries, background jobs, and read behavior.
- Bring the old primary back only as a fenced, non-writer host. Rejoin it with the documented rewind or fresh-base-backup procedure.
- Repeat the test with a network partition, a full disk, and a failed coordination node where practical.
- Perform a separate backup restore and PITR exercise. A successful failover does not prove that backups can be restored.
Monitor the signals that predict failure
| Signal | Why it matters | Action when abnormal |
|---|---|---|
| Write, flush, and replay lag | Shows how far a standby is behind and bounds potential asynchronous data loss. | Check workload spikes, network latency, storage throughput, blocked replay, and standby errors. |
| Replication connection state | A disconnected sender or receiver means the standby is not advancing. | Inspect PostgreSQL logs, credentials, firewall rules, and slot status. |
| WAL retained by slots | An inactive slot can fill the primary’s disk even when no user query is failing. | Identify the owner, repair or remove the slot deliberately, and alert before filesystem exhaustion. |
| Archive success and age | Failed WAL archiving breaks recovery guarantees and may prevent a lagging standby from catching up. | Repair the archive destination and preserve failed segments until they are safely copied. |
| Disk growth | Data, WAL, logs, and temporary files can exhaust storage and stop both primary and standby. | Investigate retention, long transactions, bloat, and backup cleanup before adding capacity. |
| DCS quorum and latency | Patroni decisions depend on a healthy coordination quorum. | Restore quorum or place the cluster in a safe, controlled state; do not bypass fencing casually. |
| Node role and routing health | A stale health check can send writes to a standby or an old primary. | Validate role-aware checks and remove isolated nodes from service. |
| Failed rejoin attempts | A node that repeatedly fails to rejoin may retain an incompatible timeline or stale data. | Keep it fenced, inspect logs, and rebuild from a known-good base backup if rewind prerequisites are not met. |
Common failure modes and fixes
| Symptom | Likely cause | Fix |
|---|---|---|
| Standby cannot connect | Incorrect pg_hba.conf, firewall rule, hostname, password, or TLS setting. |
Test network reachability, inspect the primary log, verify the exact replication role, and reload after correcting access rules. |
| WAL directory grows without bound | A lagging or abandoned replication slot, failed archive command, or disconnected standby. | Find the retaining slot or archive error, restore progress, and remove only slots that are confirmed obsolete. |
| Standby is connected but replay is stalled | Disk saturation, a conflicting recovery setting, a long-running transaction, or a replay error. | Compare receive and replay positions, inspect logs, and resolve the blocking condition before forcing promotion. |
| Both nodes accept writes | Promotion occurred without fencing, or two service managers started conflicting roles. | Stop and isolate the old writer immediately, preserve logs, determine the authoritative timeline, and reconcile data before rejoining. |
| Automatic failover does not occur | Lost DCS quorum, an over-strict lag policy, failed health checks, or an unavailable monitor. | Check coordination health and policy decisions; do not disable safety checks without a fencing plan. |
| Clients still use the failed node | Hard-coded database hostnames, stale DNS, or pools that do not discard dead sessions. | Use a role-aware stable endpoint, lower connection timeouts, and test pool behavior during promotion. |
Patroni, pg_auto_failover, or native tools?
| Choose | Best fit | What you must operate |
|---|---|---|
| Native PostgreSQL | Small teams with a reliable on-call operator, a documented RTO, and a preference for minimal components. | Promotion runbook, fencing, endpoint changes, monitoring, backups, and rejoin procedures. |
| Patroni | Teams that want automatic leader management, REST health APIs, flexible asynchronous or synchronous policies, and common HAProxy integrations. | Patroni configuration, a DCS with odd-number quorum, role-aware routing, lag policy, and split-brain testing. |
| pg_auto_failover | Teams that prefer a monitor-driven state machine and pg_autoctl-managed PostgreSQL services on supported Linux distributions. | Monitor availability, pg_autoctl service units, package/version alignment, fencing, and controlled rejoin. |
Use asynchronous replication when preserving write availability is more important than eliminating every acknowledged-write loss window. Use synchronous replication when the business requires stronger durability and accepts the latency or write unavailability caused by a missing synchronous standby. Regardless of the choice, keep independent backups, expose a stable writer endpoint, monitor lag and retention, and rehearse both promotion and rejoin.
Free tools Windows power users keep installed
One-click scans. No signup required.
Quick Recap
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.
