Switchover and Failover Procedures

You've built a solid PostgreSQL deployment, but now the nagging question keeps you up at night: what happens when the primary database goes down? Whether it's a routine maintenance window or a sudden hardware failure, unplanned downtime means angry users, lost revenue, and frantic 3 AM calls. This lesson demystifies switchover (planned) and failover (unplanned) procedures, giving you the playbook to keep your PostgreSQL cluster available and your sanity intact.

The Problem This Lesson Solves

High availability is not a feature you bolt on at the end; it's a discipline you design into your operations from day one. Many teams run a single PostgreSQL instance and only think about redundancy after the first outage. When that happens, they're forced to restore from backups — a slow, error-prone process that can lose minutes or hours of data. Even teams with a standby replica often fumble the promotion: they issue pg_ctl promote without understanding promotion semantics, fail to update application connection strings, and end up with split-brain or data loss.

The core pain this lesson addresses is the undefined procedure: you don't have a step-by-step, tested runbook for switching roles between your primary and standby. Without a defined switchover for planned maintenance (like applying a major version upgrade) and a failover for unplanned outages (like a server crash), you're gambling with your data and your users' trust. This lesson gives you the exact commands, the mental model, and the edge cases you need to turn chaos into a controlled, repeatable process.

Core Concept / Mental Model

Think of your PostgreSQL cluster as a relay race. The baton is the primary role — the only node that accepts read-write connections and processes transactions. Your standby replicas are runners waiting for the baton, continuously streaming the primary's write-ahead log (WAL) to keep their data current. Switchover is a planned hand-off: you signal the standby to take the baton, the primary gracefully steps back, and no data is lost because both nodes are in sync. Failover is an emergency hand-off: the primary runner trips and drops the baton, and a standby picks it up without waiting for permission — with the risk that the last few transactions may not have reached the standby yet.

Here are the key terms you'll use throughout this lesson:

• Primary: The read-write leader of the cluster. Only one primary exists at a time.
• Standby: A read-only follower that applies WAL data from the primary. It can be promoted to primary.
• WAL (Write-Ahead Log): The journal of every change. Standbys replay these logs to stay current.
• Promotion: The act of converting a standby into a new primary.
• Split-brain: A dangerous state where two nodes both think they are primary — you must avoid this at all costs.
• Synchronous vs. asynchronous replication: Whether the primary waits for a standby to confirm writes before reporting success. Synchronous replication reduces data loss but adds latency.

Pro tip: The mental model to internalize: switchover = "graceful role swap," failover = "emergency takeover." Every procedure in this lesson is built around making the right one happen at the right time.

How It Works Step by Step

The Anatomy of a Switchover

A switchover is a planned event. You're going to promote a standby and demote the current primary, and you want zero data loss and minimal disruption. Here's the logical flow:

1. Prepare the standby: Ensure the standby is healthy and fully caught up with the primary's WAL. Check replication lag and confirm pg_is_in_recovery() returns true.

2. Quiesce writes: Stop new write traffic to the primary. You can do this by setting the database to read-only, or by taking the application offline momentarily. The goal is to reach a quiet point where no new transactions are in flight.

3. Flush and switch to a new WAL segment: On the primary, run SELECT pg_switch_wal(); to force WAL to rotate, ensuring the standby receives all remaining data.

4. Promote the standby: On the standby, run pg_ctl promote (or use pg_promote()). This ends recovery mode and makes it the new primary.

5. Redirect traffic: Update connection strings, DNS, or connection poolers to point to the new primary.

6. Repurpose the old primary: The old primary now becomes a standby. Reconfigure it to follow the new primary (e.g., add a primary_conninfo and start streaming).

The Anatomy of a Failover

The unplanned version skips the polite steps. When the primary dies, you promote a standby immediately:

1. Detect the failure: This might be a monitoring alert or an application timeout.

2. Verify the primary is truly down: Don't promote if the primary is merely partitioned — you could get split-brain. Use a fencing mechanism (like pg_ctl kill or the cluster manager) to ensure it's offline.

3. Identify the best standby: The one with the most recent WAL data (lowest lag) is ideal.

4. Promote the standby: Run pg_ctl promote on the chosen standby. It stops applying WAL and begins accepting writes.

5. Redirect traffic: Point applications to the new primary. If you're using a proxy or pgbouncer, update the config or rely on automatic failover from a coordinator like Patroni.

6. Recover the old primary: When the old primary comes back, it will need to be rebuilt as a standby of the new primary — it might have missed data.

The Role of Synchronous Replication

If you configure synchronous_standby_names, the primary waits for a specified standby to confirm each commit. This eliminates data loss in a failover scenario, because the promoted standby is guaranteed to have all acknowledged transactions. The tradeoff is performance: every write waits for the network round trip to the standby.

Hands-On Walkthrough

Let's get your hands dirty. In these examples, we'll use a two-node cluster: primary1 and standby1. We'll practice a switchover first, then a failover simulation.

Prerequisites

Ensure PostgreSQL 10+ is installed on both nodes, and that streaming replication is configured. Your postgresql.conf on the primary should include:

wal_level = replica
max_wal_senders = 10
hot_standby = on

On the standby, standby.signal (or recovery.conf in older versions) should point to the primary:

primary_conninfo = 'host=primary1 port=5432 user=repl'

Performing a Switchover

Follow these steps as the postgres user.

Step 1: Verify replication status

On the primary, check that the standby is streaming:

SELECT pid, state, sync_state, replay_lag FROM pg_stat_replication;

You should see a row with state = 'streaming' and a small replay_lag (ideally 00:00:00).

Step 2: Quiesce writes

Put the primary into read-only mode. This requires setting the default_transaction_read_only parameter or, more simply, stopping the application writes. For our exercise, we'll just set a flag:

ALTER SYSTEM SET default_transaction_read_only = on;
SELECT pg_reload_conf();

Now all new transactions are read-only. Check that the WAL replay on the standby has caught up:

psql -h standby1 -c "SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();"

If pg_last_wal_reply_lsn equals pg_last_wal_receive_lsn, you're in sync.

Step 3: Force a WAL switch

On the primary, run:

SELECT pg_switch_wal();

This ensures any remaining WAL is flushed and sent to the standby.

Step 4: Promote the standby

On the standby node, execute:

pg_ctl promote

If you're using a connection inside the database, you could also run SELECT pg_promote();. After promotion, verify the standby is now a primary:

psql -h standby1 -c "SELECT pg_is_in_recovery();"

It should return f (false).

Step 5: Redirect applications

Update your application's connection string to point to standby1 instead of primary1. If you use a DNS CNAME, you can simply update the record.

Step 6: Convert the old primary to a standby

On primary1, stop the server, create a standby.signal file, and configure primary_conninfo to point to standby1. Then start the server. It will begin streaming from the new primary.

pg_ctl stop -D /var/lib/postgresql/data
echo "primary_conninfo = 'host=standby1 port=5432 user=repl'" >> /var/lib/postgresql/data/postgresql.conf
touch /var/lib/postgresql/data/standby.signal
pg_ctl start -D /var/lib/postgresql/data

Simulating a Failover

To simulate an unplanned failure, we'll force-kill the primary and promote the standby.

Step 1: Kill the primary

On primary1, issue a hard kill (do not do a clean shutdown; we want to simulate a crash):

kill -9 $(head -1 /var/lib/postgresql/data/postmaster.pid)

Step 2: Confirm the standby is ready

On standby1, check that it's still in recovery and has received WAL up to the last known LSN:

psql -c "SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();"

If the difference is small, you may lose a few transactions. With synchronous replication, this gap should be zero.

Step 3: Promote the standby

Run pg_ctl promote on standby1. This takes effect immediately, and the new primary starts accepting writes.

Step 4: Point clients to the new primary

Update your connection pooling or DNS as in the switchover.

Step 5: Rebuild the old primary

Once primary1 is back online, you cannot simply reattach it — it may have unapplied WAL that would conflict with the new primary. The safest approach is to wipe the data directory and reinitialize from a base backup of the new primary, or use pg_rewind if timelines match.

Example Output from a Successful Switchover

When you run pg_ctl promote, you'll see output similar to:

server promoting

After promotion, querying pg_is_in_recovery() on the new primary returns f.

Compare Options / When to Choose What

When to choose what:

• Use manual switchover for low-scale deployments where you don't mind a bit of downtime and you need full control.
• Use automated failover (Patroni, repmgr) when you need sub-minute recovery and many replicas.
• Use cloud-managed when you want to offload operational overhead entirely.

Troubleshooting & Edge Cases

Common Pitfalls and Fixes

Pitfall: Promoting a lagging standby loses data

• Symptom: After failover, the new primary is missing recent transactions.
• Fix: Configure synchronous replication (synchronous_standby_names) to guarantee zero loss, or accept the lag and use a tool like pg_rewind to reconcile the old primary later.

Pitfall: Split-brain after failover

• Symptom: Old primary comes back and both nodes accept writes.
• Fix: Use a fencing mechanism (e.g., pg_ctl kill, STONITH in cluster managers) to ensure the old primary is stopped before promoting a standby. Never promote a standby unless you're certain the primary is down.

Pitfall: Old primary won't rejoin as a standby

• Symptom: After a failover, when you start the old primary, it shows an error like FATAL: requested WAL segment has already been removed.
• Fix: The old primary's timeline diverged. Use pg_rewind on the old primary to rewind it to the new primary's timeline, or rebuild it from a fresh base backup.

Edge case: replay_lag doesn't decrease

• Symptom: The standby is stuck in a lag state.
• Fix: Check network connectivity, confirm the repl user has proper privileges, and ensure wal_keep_size or max_slot_wal_keep_size is set high enough to retain WAL while the standby catches up.

Manual Commands Quick Reference

# Promote a standby
pg_ctl promote -D /var/lib/postgresql/data

# Force WAL switch on primary
psql -c "SELECT pg_switch_wal();"

# Check recovery status
psql -c "SELECT pg_is_in_recovery();"

What You Learned & What's Next

You now understand the distinction between switchover and failover, the role of WAL and synchronous replication, and the exact steps to promote a standby safely. You've practiced a manual switchover with zero data loss and a simulated failover that tolerates some loss. You also know how to avoid split-brain and how to bring the old primary back into the cluster.

Key takeaways to remember:

• Switchover is planned and should be data-loss-free; failover is emergency and may lose data unless synchronous replication is used.
• Always verify the standby is caught up before promoting.
• Use fencing or a cluster manager to prevent split-brain.
• After failover, rebuild or rewind the old primary to avoid timeline conflicts.
• Automation via Patroni or repmgr is worth it for production if you need speed and reliability.

What's next: In the next lesson, you'll dive into backup and restore strategies — the natural complement to high availability. You'll learn how to take consistent base backups, archive WAL, and perform point-in-time recovery, so you're prepared even if both nodes fail. Take a moment to practice a switchover on your own cluster, and then move on to the next step in the PostgreSQL Tutorial.