Test Standby Recovery

You've set up streaming replication, watched pg_stat_replication report a healthy standby, and maybe even slept soundly. But here's the uncomfortable truth: a standby server that has never been tested for recovery is a backup that you hope works. When your primary server bursts into flames at 2 AM, you don't want to discover that your standby is missing a WAL file or that your recovery configuration is subtly wrong. This lesson shows you how to perform a safe, repeatable test recovery with a standby server — so you can fail over with confidence, not hope.

The problem this lesson solves

A standby server's entire purpose is to take over when the primary fails. Yet most teams never actually test that takeover until it's too late. The failure modes are silent and numerous: a network partition that stopped WAL shipping, a missing archive_mode setting, a standby that's actually lagging by hours, or a recovery.conf (or standby.signal) that points to a nonexistent primary. You won't see any of these in normal operation because the standby looks healthy — it's just quietly not keeping up.

The cost of an untested standby isn't just downtime; it's data loss. If your standby is lagging and you fail over to it, you lose every transaction that occurred after the last applied WAL record. That's a permanent hole in your data that no amount of post-incident analysis can fill. The only way to know your standby is truly ready is to test recovery in a controlled environment, and this lesson shows you exactly how to do that.

Core concept / mental model

Think of your primary server as a live performer and your standby as an understudy. The understudy rehearses every scene (applies every WAL record) but never actually goes on stage. Testing recovery is like running a dress rehearsal — you simulate an emergency, let the understudy take over, and verify they can deliver the entire performance without a hitch.

In PostgreSQL terms, the core concept is the promotion of a standby to a primary. When you promote a standby, it does three things:

1. Stops recovery mode — it no longer applies WAL from the old primary.
2. Completes any pending WAL application — it applies all WAL files it has already received.
3. Becomes writable — it starts accepting new transactions as the new primary.

A test recovery is simply a promotion that you do on purpose, in a safe way, so you can verify the standby is functional. There are two mental models to keep in mind:

• Physical recovery: The standby applies WAL blocks byte-for-byte, producing an exact copy of the primary's data files. Testing this ensures the data files are intact and readable.
• Logical readiness: The standby's ability to become a full primary, including all roles, permissions, and application-level state. Testing this ensures your application can actually connect and function.

The golden rule: never test recovery on your production standby unless you're prepared for it to become the new primary. Instead, you clone the standby's data directory to a test instance, or you use a dedicated test environment that mirrors your production setup.

How it works step by step

Here's the mental flow of a test recovery:

1. Simulate a failure — you stop the primary (or pretend it's unreachable). This isn't strictly necessary for the test, but it forces you to think about the failover scenario.
2. Ensure the standby is caught up — check that the standby has applied all WAL it can. You don't want to test a standby that's 50 GB behind.
3. Stop the standby cleanly — so you can safely copy its data directory for a test clone.
4. Clone the data directory to a test location (e.g., on the same machine or a separate test host).
5. Configure the clone to be a primary — remove the standby signal file and any primary connection settings.
6. Start the clone and verify it can accept writes and serve data.
7. Run validation queries — check table row counts, recent transactions, and application connectivity.
8. Shut down the clone and clean up. Your production standby remains untouched and ready.

This step-by-step approach isolates the test from production. If the clone fails, you learn something without risking real data.

Hands-on walkthrough

Let's make this concrete. We'll assume you have a primary and a standby using streaming replication with standby.signal (PostgreSQL 12+). We'll test recovery by cloning the standby to a test instance.

1. Verify the standby is caught up

First, log into the standby and check its last applied WAL location against the primary's current WAL insert location.

-- On the standby
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_is_in_recovery();

-- On the primary
SELECT pg_current_wal_lsn();

If pg_last_wal_replay_lsn() matches pg_current_wal_lsn(), the standby is fully caught up. If not, wait a few seconds and re-check. Note: if the standby has no active connection, pg_last_wal_receive_lsn() will be NULL — a red flag that streaming is not happening.

2. Clone the standby's data directory

Stop the standby cleanly first, then copy its data directory to a test path.

# On the standby server
sudo systemctl stop postgresql@14-main   # or however you manage PostgreSQL

# Create a test clone (make sure you have free disk space!)
sudo cp -a /var/lib/postgresql/14/main /var/lib/postgresql/14/main_test

# Fix ownership and permissions
export PGDATA_TEST=/var/lib/postgresql/14/main_test
sudo chown -R postgres:postgres "$PGDATA_TEST"

Now start the clone as a primary. Remove standby.signal and the primary_conninfo to prevent it from trying to connect back to the (stopped) primary.

# Remove the standby marker and any primary connection settings
sudo rm "$PGDATA_TEST/standby.signal"   # This is what makes it a standby!
# If you use a recovery configuration file (PG 15+), remove or edit recovery.signal, and clear primary_conninfo
# For older versions, edit postgresql.conf to remove primary_conninfo (if present)

# Start the clone as a primary on a different port (e.g., 5444) to avoid conflicts
sudo -u postgres /usr/lib/postgresql/14/bin/pg_ctl -D "$PGDATA_TEST" -o "-p 5444" start

3. Verify recovery by checking write capability

Once the clone is running, verify it's fully out of recovery mode and can accept writes.

-- Connect to the test instance on port 5444
psql -p 5444 -c "SELECT pg_is_in_recovery();"

Expected output:

 pg_is_in_recovery 
-------------------
 f
(1 row)

If it returns f (false), the server is now a primary. Now test a write:

-- Create a test table and insert data
psql -p 5444 -c "CREATE TABLE recovery_test (id serial PRIMARY KEY, msg text);"
psql -p 5444 -c "INSERT INTO recovery_test (msg) VALUES ('we recovered!');"

Expected output (for the INSERT):

INSERT 0 1

4. Validate data consistency

Compare row counts between the clone and the original primary (if it's still available) to ensure no data loss.

-- On the test clone (port 5444)
SELECT count(*) FROM your_app_table;

-- On the original primary (if it's still up)
SELECT count(*) FROM your_app_table;

If the counts match, you've proven the standby has all the data up to the point of your clone.

5. Clean up

Shut down the test clone and remove it. Restart your original standby (or leave it down if you promoted it — but for a test, you want your standby back).

sudo -u postgres /usr/lib/postgresql/14/bin/pg_ctl -D "$PGDATA_TEST" stop
sudo rm -rf "$PGDATA_TEST"
sudo systemctl start postgresql@14-main   # restart the original standby

Pro tip: Instead of copying the entire data directory (which can be huge), you can use pg_basebackup with the -R flag to create a fresh standby and test that. But cloning an existing standby tests the exact state you'll have during a real failover.

Compare options / when to choose what

There are several ways to test recovery. Here's a comparison to help you choose:

When to choose which: - If you just want a periodic health check, clone the standby — it's the safest and most representative. - If you're doing a full disaster recovery drill and can afford downtime, promote the standby (and then re-clone it back as a standby). - If you're building a new test environment, pg_basebackup is your friend. - For production-grade setups, automate failover with tools like Patroni, but still do manual tests to ensure you understand the mechanics.

Troubleshooting & edge cases

Here are the most common problems you'll hit when testing recovery, and how to solve them.

1. pg_is_in_recovery() returns true after promotion

You removed standby.signal, but the server still thinks it's a standby. Possible causes: - You forgot to remove recovery.signal (if present). Look for both files. - Your postgresql.conf has an archive_command still pointing to the old primary's archive — this doesn't prevent promotion, but it can cause issues. - You started the clone with -R flag (if you used pg_basebackup), which adds standby.signal automatically.

Fix: Verify no signal files remain: ls $PGDATA | grep -E 'standby|recovery'.

2. The clone fails to start with "hot standby is not possible after shutdown recovery"

This occurs when the clone's WAL replay is incomplete. It means the standby didn't have all the WAL files when you cloned it.

Fix: Ensure the standby is caught up before cloning. Check pg_last_wal_replay_lsn() as shown earlier. Also, check that restartpoint has occurred — you may need to run a CHECKPOINT on the standby (or primary) and wait for it to be replayed.

3. Can't connect to the test instance — port conflicts

If you start the clone on the default port 5432, it will conflict with the production primary if it's still running.

Fix: Always use a different port (e.g., 5444) and a different Unix socket directory if needed. In postgresql.conf for the clone, set port = 5444 and unix_socket_directories = '/tmp' to avoid conflicts.

4. Data is missing after promotion

You promote the standby, but some recent transactions are gone. That's expected if the standby was lagging; PostgreSQL can't recover data it never received.

Fix: This is a detected issue — you just proved your standby wasn't up-to-date. In a real failover, you'd lose that data. This is why testing recovery is so valuable: it reveals lagging standbys before disaster. Use pg_last_wal_receive_lsn() to monitor lag in production and set alerts.

5. The clone is read-only even after promotion

If pg_is_in_recovery() is false on the clone, it should be writable. But if you see ERROR: cannot execute INSERT in a read-only transaction, check default_transaction_read_only in the clone's postgresql.conf — it may have been copied from a template with read-only set.

Fix: Set default_transaction_read_only = off in the clone's config.

What you learned & what's next

You've learned how to test recovery with a standby server — a critical part of database reliability. You can now:

• Explain why untested recovery is a data-loss risk.
• Describe the mental model of a dress rehearsal for failover.
• Execute a safe, non-disruptive test by cloning the standby data directory.
• Validate that the clone is fully recovered and writable.
• Troubleshoot common issues like lingering signal files and port conflicts.

This skill turns a passive standby into a verified safety net. You've also seen how monitoring lag (via pg_last_wal_receive_lsn()) is essential — which dovetails into the next lesson: monitoring replication lag and ensuring your standby stays current. That lesson will give you the tools to proactively detect the lag we just discovered during recovery testing, so you never face an unwelcome surprise during a real failover.

Remember: a backup you haven't tested is a backup you don't have. Now you know how to prove your standby works.