Droplet → Managed PostgreSQL: Standby readiness timing after automatic failover

Answer

After a primary node becomes unreachable, DigitalOcean’s Managed PostgreSQL cluster initiates a failover. The new standby promotes to primary only after it has replayed the WAL backlog and reached a consistent state. In practice the node is write‑unavailable for roughly 30–60 seconds, though the window can grow to several minutes if the replication lag is large or the cluster is heavily loaded.

Typical window

• Small (<10 GB) clusters: 30–45 s
• Medium (10–50 GB) clusters: 45–90 s
• Large (>50 GB) or high‑traffic clusters: 90–180 s (or longer if WAL backlog > 1 GB)

Reliable readiness indicators

1. API role & state

   Call /v2/databases/{id}/status (or /v2/databases/{id}/replication). The promoted node will report:

   {
     "role": "primary",
     "state": "running",
     "replication_lag": 0
   }

   Only when role is primary, state is running, and replication_lag is zero should the node be considered write‑ready.

2. PostgreSQL internal flag

   From any client connected to the cluster, run:

   SELECT pg_is_in_recovery();

   It should return false on a ready primary.

3. Log confirmation

   The server logs the line "database system is ready to accept connections" once the promotion completes. Monitoring tools can tail this message or query pg_stat_replication for write_lag=0 and state='streaming'.

4. Write test

   Attempt a simple INSERT or UPDATE and verify that the transaction commits without a could not connect to server error.

Monitoring setup

• Use DigitalOcean’s database_cluster_status metric – it flips to running when the primary is ready.
• Configure Prometheus to scrape pg_stat_replication and alert when write_lag is zero and state is streaming.
• Set up a lightweight script that queries pg_is_in_recovery() every minute and logs the result; a transition from true to false marks readiness.

Verification steps

1. Simulate a failover (e.g., bring the primary down).
2. Immediately poll the status API until role='primary' and state='running' appear.
3. From a client, run SELECT pg_is_in_recovery(); – expect false.
4. Attempt a write; a successful commit confirms readiness.
5. Compare the timestamp of the API role change to the log line “ready to accept connections” for exact lag measurement.

Missing diagnostic detail

What PostgreSQL version is running in your Managed PostgreSQL cluster? The replication lag behavior and API schema can differ between major releases.