← Back to Blog Index

Drain Mode: Zero-Downtime Upgrades for Stream Tables

Graceful quiesce before maintenance, rolling restarts, and extension upgrades

You need to upgrade pg_trickle. Or restart PostgreSQL for a configuration change. Or run a maintenance operation that requires no active refreshes.

If you just restart PostgreSQL while a refresh is in progress, the refresh is interrupted. The stream table is left in a partially-updated state. pg_trickle recovers on the next startup — it detects the interrupted refresh and either retries or marks the table for repair — but it’s not clean.

Drain mode provides a clean shutdown path. pgtrickle.drain() tells the scheduler to stop dispatching new refreshes and wait for in-flight refreshes to complete. When all refreshes are done, pgtrickle.is_drained() returns true, and you can safely restart.

The API

-- Signal drain
SELECT pgtrickle.drain();

-- Check status
SELECT pgtrickle.is_drained();
-- false (still waiting for in-flight refreshes)

-- Wait a moment...
SELECT pgtrickle.is_drained();
-- true (all refreshes complete, scheduler idle)

After drain completes: - The scheduler is running but not dispatching new work. - All in-flight refreshes have completed. - Change buffers continue accumulating (CDC triggers still fire). - Stream tables are still queryable.

The Upgrade Workflow

# Step 1: Drain
psql -c "SELECT pgtrickle.drain();"

# Step 2: Wait for drain
while ! psql -qtAc "SELECT pgtrickle.is_drained();" | grep -q 't'; do
  sleep 2
done

# Step 3: Upgrade
psql -c "ALTER EXTENSION pg_trickle UPDATE;"

# Step 4: Resume
psql -c "SET pg_trickle.enabled = on;"

Between steps 2 and 4, no refreshes are running. The ALTER EXTENSION UPDATE can safely migrate schema, catalog tables, and internal state without racing against active refresh operations.

What Happens During Drain

When drain() is called:

  1. No new refreshes are dispatched. The scheduler’s dispatch loop skips all tables.
  2. In-flight refreshes continue. Any refresh that’s already executing (running a delta query, applying a MERGE) completes normally.
  3. IMMEDIATE mode refreshes still fire. Since they’re synchronous within user transactions, they can’t be deferred. Drain only affects background-scheduled refreshes.
  4. CDC continues. Triggers keep writing to change buffers. WAL decoder keeps running. Changes accumulate.

The drain is “soft” — it doesn’t kill any processes or abort any transactions. It just stops starting new work.

Drain Duration

How long does drain take? It depends on the longest currently-running refresh.

In practice: - Most refreshes complete in under 1 second. - A complex FULL refresh on a large table might take 10–30 seconds. - If a refresh is stuck (waiting on a lock, for example), drain waits indefinitely.

You can set a timeout:

-- Drain with 60-second timeout
SELECT pgtrickle.drain(timeout_seconds => 60);

If in-flight refreshes don’t complete within 60 seconds, drain() returns false and the in-flight refreshes continue. You can then decide: wait longer, or proceed with the restart (accepting the interrupted-refresh recovery cost).

CloudNativePG and Rolling Restarts

In Kubernetes deployments with CloudNativePG, rolling restarts are the norm. The operator restarts pods one at a time, waiting for each to be ready before restarting the next.

Drain mode integrates with this:

  1. A preStop hook calls pgtrickle.drain().
  2. The readiness probe checks pgtrickle.is_drained().
  3. Once drained, the pod is marked unready, and the operator proceeds with the restart.
# CloudNativePG Cluster manifest (excerpt)
spec:
  postgresql:
    preStop:
      exec:
        command:
          - psql
          - -c
          - "SELECT pgtrickle.drain();"

This ensures zero interrupted refreshes during rolling restarts. Change buffers accumulate during the restart window and are processed on the next scheduler cycle after the pod comes back.

Drain and HA Failover

During a PostgreSQL failover (primary → standby promotion), drain mode isn’t typically used — failovers are unplanned. But for planned failovers (maintenance, OS patching):

  1. Drain the current primary: SELECT pgtrickle.drain();
  2. Wait for drain: SELECT pgtrickle.is_drained();
  3. Promote the standby.
  4. pg_trickle’s launcher on the new primary detects promotion via pg_is_in_recovery() and starts the scheduler.

The change buffers on the old primary are replicated to the standby via WAL. No changes are lost.

Monitoring Drain State

SELECT * FROM pgtrickle.health_summary();

During drain, health_summary() includes:

 scheduler_state | drain_requested | inflight_refreshes | drain_elapsed_seconds
-----------------+-----------------+--------------------+------------------------
 draining        | t               | 2                  | 4.7

When inflight_refreshes reaches 0 and scheduler_state changes to drained, it’s safe to proceed.

Resuming After Drain

Drain is not permanent. To resume normal operation without restarting:

-- Cancel the drain
SET pg_trickle.enabled = on;

The scheduler resumes dispatching. The accumulated change buffers are processed in the next cycle. Depending on how long the drain lasted, the first post-drain refresh may be larger than usual (more accumulated changes).

Summary

Drain mode is the safe shutdown path for pg_trickle. drain() stops new refreshes. In-flight refreshes complete. is_drained() confirms it’s safe to proceed.

Use it before: - ALTER EXTENSION pg_trickle UPDATE - PostgreSQL restart - Planned failover - CloudNativePG rolling restart

The alternative — restarting mid-refresh — works (pg_trickle recovers), but it’s not clean. Drain mode is one function call for a clean cutover.