Restoring a Sharded Database into a Staging Environment

A realistic staging environment is the cheapest place to test a rebalance, a migration or a schema change — and building one from production backups is where several dangerous mistakes live. This guide restores a fleet in parallel, rewrites the shard map so nothing points back at production, masks personal data before the application starts, and trims partitions so the environment is affordable. It applies the recovery machinery from Backup & Restore for Partitioned Databases inside Shard Migration & Rebalancing Operations.

Prerequisites

• Read-only credentials to the production backup store — never write access
• Staging hosts sized for the trimmed data set, one per production shard
• A masking specification agreed with whoever owns the data — columns, method, and what may stay real
• The staging shard-map template, with staging hostnames
• An automated pipeline; a documented manual sequence will be executed wrongly at least once

Step 1 — Restore every shard in parallel

Sequential restore is the difference between two hours and sixteen:

#!/usr/bin/env bash
# restore_fleet.sh — one restore per shard, all at once
set -euo pipefail
TARGET_TIME=${1:?usage: restore_fleet.sh 'YYYY-MM-DD HH:MM:SS+00'}

for i in 0 1 2 3 4 5 6 7; do
  (
    pgbackrest --stanza="shard${i}" \
      --repo1-path=/prod-backups --repo1-host-user=readonly \
      --pg1-path="/staging/shard${i}/pgdata" \
      --type=time --target="${TARGET_TIME}" --target-action=promote \
      --process-max=4 \
      restore
    pg_ctl -D "/staging/shard${i}/pgdata" -o "-p 54${i}2" start
  ) &
done
wait
echo "fleet restored to ${TARGET_TIME}"

Operational note: --process-max parallelises the download and decompression within a shard, while the shell backgrounding parallelises across shards. Both matter; either alone leaves most of the recovery time on the table.

SRE tip: Use a read-only role for the backup repository. A staging pipeline with write access to production backups is one typo away from deleting them.

Step 2 — Rewrite the shard map before anything reads it

This is the step that prevents staging from talking to production:

-- run on the staging coordinator immediately after restore, before the app starts
BEGIN;
UPDATE shard_map
   SET host = replace(host, '.prod.internal', '.staging.internal'),
       port = 54000 + shard_index * 10 + 2,
       readonly_host = NULL;
UPDATE shard_map SET updated_at = now(), updated_by = 'staging-restore';

-- fail loudly if anything still points at production
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM shard_map WHERE host LIKE '%.prod.%') THEN
     RAISE EXCEPTION 'shard_map still references production hosts';
  END IF;
END $$;
COMMIT;

Operational note: Rotate the restored database passwords too. They came from production, and a staging environment is not held to production’s access controls.

DBA tip: Assert on the absence of production references rather than on the success of the rewrite. The two differ whenever a new column holding a hostname is added and the rewrite is not updated.

Step 3 — Mask before the application starts

Masking must be part of the pipeline, gated the same way:

-- deterministic masking keeps referential integrity across shards
UPDATE customers
   SET email      = 'user' || id || '@example.invalid',
       full_name  = 'Test User ' || id,
       phone      = '+10000000' || lpad((id % 1000)::text, 3, '0'),
       tax_id     = NULL
 WHERE email NOT LIKE '%@example.invalid';

-- assert nothing real survived
DO $$
DECLARE leaked int;
BEGIN
  SELECT count(*) INTO leaked FROM customers WHERE email NOT LIKE '%@example.invalid';
  IF leaked > 0 THEN
    RAISE EXCEPTION 'masking incomplete: % rows still hold real addresses', leaked;
  END IF;
END $$;

Operational note: Deterministic masking — deriving the fake value from the id — means the same customer masks identically on every shard, so cross-shard joins and directory lookups still line up.

SRE tip: Mask on every shard in parallel, and include the assertion on each. A fleet where seven shards are masked and one is not is worse than one that failed loudly.

Step 4 — Trim partitions so staging stays affordable

Staging rarely needs 25 months of history:

-- keep the newest two partitions per table; drop the rest
DO $$
DECLARE child text;
BEGIN
  FOR child IN
    SELECT c.relname
    FROM   pg_class c JOIN pg_inherits i ON i.inhrelid = c.oid
    WHERE  i.inhparent = 'events'::regclass
    ORDER  BY c.relname DESC
    OFFSET 2
  LOOP
    EXECUTE format('ALTER TABLE events DETACH PARTITION %I', child);
    EXECUTE format('DROP TABLE %I', child);
  END LOOP;
END $$;

Operational note: Trim after restoring rather than restoring selectively. Physical restore is all-or-nothing, and dropping partitions afterwards is a fast catalog operation.

DBA tip: Keep the partition count realistic even when trimming volume, if plan shape matters to what is being tested. Empty partitions cost almost nothing on disk and preserve planner behaviour.

Verification

Confirm the environment is isolated and usable before handing it over:

# no staging process may hold a connection to a production host
ss -tnp | grep -c 'prod.internal' || echo "isolated"

# the app's own health check against the staging coordinator
curl -sS http://staging-api.internal/healthz | jq .

{
  "status": "ok",
  "shards": 8,
  "shard_map_version": 41,
  "sample_shard_host": "shard3.staging.internal",
  "masked": true
}

Then run one cross-shard query and one write, confirming both land on staging hosts. A staging environment that has never been written to has not proven its shard map.

Failure mode table

FAQ

Related

• Backup & Restore for Partitioned Databases — the parent topic and the backups this pipeline consumes
• Verifying Backup Integrity Across Shards — the drills that use the same restore path
• Rebalancing Shards After Adding Nodes to a Consistent Hash Ring — the kind of operation a realistic staging fleet exists to rehearse