Backing Up and Recovering a Sharded PostgreSQL Database: The Citus Problem
A green backup job on every Citus node does not prove that the distributed database can be recovered. It may only prove that several PostgreSQL servers were backed up at different moments.
Situation
RetailCo’s checkout platform has outgrown one PostgreSQL writer. Orders, order items, payments, inventory reservations, and shipments are distributed by tenant_id across four Citus workers. Each worker primary has a streaming standby. The coordinator also has a standby.
The data now exceeds what RetailCo can restore casually during an incident. The backup dashboard still looks reassuring: every node has a recent base backup, every node archives WAL, and storage snapshots complete successfully.
flowchart TD
App[RetailCo checkout platform] --> Coord[Citus coordinator primary]
Coord --> W1[worker one primary]
Coord --> W2[worker two primary]
Coord --> W3[worker three primary]
Coord --> W4[worker four primary]
Coord --> CS[coordinator standby]
W1 --> S1[worker one standby]
W2 --> S2[worker two standby]
W3 --> S3[worker three standby]
W4 --> S4[worker four standby]
Coord --> B[backup repository]
W1 --> B
W2 --> B
W3 --> B
W4 --> B
That topology is highly available only if failover works. It is recoverable only if RetailCo can reconstruct one valid distributed state after corruption, operator error, or regional loss.
Citus documents the separation clearly. Workers hold distributed shards, while the coordinator tracks nodes and shard locations in metadata tables. Citus uses PostgreSQL streaming replication for worker high availability, and the coordinator metadata can also be protected by streaming replication or PostgreSQL backup tools: Citus cluster management.
RetailCo is a reference architecture, not a claimed customer deployment. The topology and data volume are scenario inputs. This article does not claim a measured recovery point objective, recovery time objective, backup duration, or restore duration.
Version matters here, and this is one of the few database topics where the version gap changes the recommended design rather than only the syntax:
| Citus line | Backup-relevant capability |
|---|---|
| 13.0 / 13.2 | citus_create_restore_point() for a cluster-wide PITR marker; no cluster-change barrier |
| 14.0 (February 2026) | PostgreSQL 18 support; citus_create_restore_point() strengthened for multi-writer (MX) metadata mode by taking ExclusiveLock on pg_dist_transaction across all metadata nodes |
| 14.1 / 13.3 (May 2026) | Explicit cluster-wide change coordination for backups via citus_cluster_changes_block() and related functions |
This article targets the 13.x and 14.0 lines. The public stable API manual labels itself 13.0.1, so the runbook must be matched to the installed Citus minor, PostgreSQL major, and backup-tool version: Citus 14.0 release notes, Citus 14.1 and 13.3 release notes, and Citus 13.2 release notes.
The Problem
A normal PostgreSQL physical backup protects one PostgreSQL cluster. A Citus database is a set of PostgreSQL clusters that participate in distributed transactions.
Suppose one checkout transaction updates an order placement on one worker and an inventory placement on another. Citus can use two-phase commit so both changes become one distributed decision. Now suppose the infrastructure snapshots the coordinator at 10:37:20, worker one at 10:37:22, and worker two at 10:37:27.
Each snapshot can be locally valid. The set can still be wrong. Citus describes the exact failure: a two-phase transaction can be committed in one node’s snapshot while still prepared but not committed in another. Restoring those independent moments can reconstruct an inconsistent distributed transaction: Citus cluster-wide backup coordination.
flowchart TD
Tx[one distributed checkout transaction] --> C1[commit decision across nodes]
C1 --> A[coordinator snapshot — earlier]
C1 --> B[worker one snapshot — middle]
C1 --> C[worker two snapshot — later]
A --> R[locally valid files]
B --> R
C --> R
R --> Risk[restored cluster may disagree about one transaction]
The coordinator introduces a second consistency problem. Its metadata says which shards exist and where they live. If the restored coordinator remembers a shard move that the restored workers do not—or forgets a move that the workers contain—the data files and ownership map no longer describe the same cluster.
The recovery question is therefore not:
Did every node finish a backup?
It is:
Can RetailCo restore the coordinator, every required worker, WAL history, global objects, configuration, and Citus metadata to one provably compatible distributed state?
Back Up a Recovery Unit, Not a Fleet
RetailCo should treat the entire Citus deployment as one recovery unit. Each backup generation needs one cluster manifest that ties together:
- the coordinator backup identifier;
- every worker backup identifier;
- PostgreSQL and Citus versions on every node;
- node names, ports, system identifiers, timelines, and tablespaces;
- the Citus node and shard-placement inventory;
- the WAL archive namespace and required WAL ranges for every node;
- roles, tablespaces, grants, extension packages, and configuration;
- the coordination method used for the backup;
- the named restore point, if the generation supports PITR;
- checksums, completion status, and a retention relationship between base backups and WAL.
flowchart TD
M[one Citus recovery manifest] --> C[coordinator physical backup]
M --> W[all worker physical backups]
M --> WAL[per-node WAL archives and timelines]
M --> G[roles tablespaces grants and configuration]
M --> P[Citus topology and placement evidence]
M --> T[restore target and validation plan]
The manifest is not documentation added after the backup. It is part of the backup. A collection of objects in storage without a machine-readable relationship is an investigation, not a restore plan.
Use three layers, not one backup mechanism
The recommended design combines physical recovery, logical recovery, and configuration recovery because they solve different incidents.
| Layer | Best use | What it does not solve alone |
|---|---|---|
| Physical base backup or storage snapshot plus WAL | Full node recovery, cluster PITR, regional reconstruction | Selective tenant repair; cluster-wide coordination |
| Logical export | Schema portability, object inspection, selected-data recovery, tenant extraction | WAL replay; practical full recovery of a very large cluster |
| Infrastructure and configuration backup | Packages, parameters, access rules, certificates, service discovery, backup policy | Database pages, transactions, Citus shard data |
PostgreSQL states that pg_dump creates a consistent logical export of one database, but it does not capture cluster-wide global objects such as roles and tablespaces. Those require pg_dumpall, commonly with --globals-only. PostgreSQL also warns that logical dumps do not contain the information required for WAL replay: PostgreSQL pg_dump, pg_dumpall, and continuous archiving.
For Citus, run logical-export and restore tests through the canonical coordinator for the installed version. Inspect the archive contents and prove that the restored target contains logical distributed tables, correct distribution metadata, reference data, sequences, functions, privileges, and row counts. Do not assume that a generic PostgreSQL command has captured the intended distributed abstraction merely because it exited successfully.
At RetailCo’s scale, a logical export is a complementary control and a selective-recovery tool. It should not be the only disaster-recovery path for the full distributed database.
Establish a validated maintenance boundary for storage snapshots
Citus added purpose-built barrier functions for exactly this problem in 14.1 and 13.3 (May 2026), after this article’s March 31, 2026 cutoff. Where they are available, they are the correct control and should replace the hand-built boundary below:
| Function | Behavior |
|---|---|
citus_cluster_changes_block() | Halts distributed 2PC commits and topology and schema changes cluster-wide while snapshots are taken |
citus_cluster_changes_unblock() | Resumes normal operation once every snapshot is recorded |
citus_cluster_changes_block_status() | Reports the current block state, its initiator, and its timestamp |
The important property is that the block is narrow. Reads and single-shard writes continue during the barrier; only multi-node writes pause. That is a materially smaller business impact than pausing every writer, which is what the conservative boundary below has to do.
Two operational cautions apply. A block that is never unblocked is an outage, so the snapshot controller needs guaranteed cleanup on every exit path, an independent watchdog reading citus_cluster_changes_block_status(), and an alert on block age. And the barrier coordinates the cluster; it does not make independent cloud volume snapshots atomic with each other. It creates a window in which no new distributed commit decision is made, which is what makes the set of snapshots mutually restorable.
On Citus lines without those functions — including the 13.0 and 13.2 clusters this article targets — a snapshot controller cannot claim cluster consistency merely because it requested every volume snapshot within a few seconds. Those deployments need the conservative maintenance boundary:
- Reject the run if any required node, standby, archive stream, or snapshot API is unhealthy.
- Pause new application writes, scheduled mutations, schema changes, shard movement, and topology automation.
- Drain or terminate in-flight distributed transactions using an operator-defined timeout.
- Create and record one Citus named restore point across the cluster.
- Request snapshots of the coordinator and every worker, recording immutable identifiers in one manifest.
- Verify that every request was accepted and that the provider’s snapshot semantics satisfy the tested capture procedure.
- Resume writers only after the manifest is complete or the failed generation is abandoned safely.
- Mark the generation usable only after an isolated restore proves topology and transaction consistency.
This is a proposed operational boundary, not a claim that independent cloud snapshots become atomic. If the business cannot tolerate the write pause or the restore drill cannot prove consistency, use coordinated backup tooling with per-node WAL and a tested restore-point workflow instead of weakening the boundary on paper.
Use a Citus restore point for the PITR cut
Storage snapshots provide a base. WAL provides recovery after that base. Each node must continuously archive its own WAL into a collision-safe namespace, and the recovery manifest must preserve each node’s PostgreSQL system identifier and timeline history.
PostgreSQL physical recovery combines a base backup with an unbroken WAL sequence. It can stop at a timestamp, transaction, or named restore point. It restores an entire PostgreSQL cluster, not a selected table or tenant, and it does not restore manually edited files such as postgresql.conf, pg_hba.conf, and pg_ident.conf: PostgreSQL PITR.
Citus adds the cluster-wide marker:
SELECT citus_create_restore_point('retailco_2026_03_31_103722');
citus_create_restore_point() temporarily blocks cluster writes and creates a consistent named restore point on all nodes. The function returns the coordinator LSN, but the marker is created across the cluster. Citus documents it as suitable for PITR and cluster forking: Citus utility functions.
That distinction matters. A coordinator LSN cannot be reused as though it were the worker LSN. Each PostgreSQL node owns its own WAL stream. Recovery should target the same restore-point name on every restored node, using the correct WAL archive for that node.
Citus 14.0 strengthened this function for clusters that can initiate distributed work from metadata workers. It blocks distributed commit decisions across metadata nodes and protects topology and distribution metadata while the restore points are created. If RetailCo uses multi-writer metadata mode, the older coordinator-only behavior must not be assumed sufficient.
A named marker is the cleanest documented cut for a cluster-wide drill. For an arbitrary request such as “restore to 10:37:22,” RetailCo should first recover the cluster in an isolated environment and prove that every node reaches a compatible state. A matching wall-clock timestamp on independent WAL streams is not by itself a distributed transaction protocol.
Separate high availability from recovery
When one worker primary fails, the fastest safe path is normally promotion of its streaming standby, not restoration of yesterday’s backup. Citus documents whole-worker streaming replication as its worker-failure mechanism. Patroni can automate Citus coordinator and worker group failover and maintain coordinator node metadata, but HA automation still does not replace an offline, independently retained backup: Patroni and Citus.
Keep the decisions separate:
| Event | First recovery path | Backup role |
|---|---|---|
| One primary host fails; standby is healthy | Fence old primary, promote standby, update or verify Citus routing | Last resort if replication copy is unusable |
| Storage corruption replicated to standby | Restore affected PostgreSQL group, then prove cluster consistency | Primary recovery source |
| Coordinator primary fails | Promote its standby and verify topology metadata | Restore metadata if no valid standby exists |
| Region is lost | Rebuild the entire recovery unit in isolation | Primary recovery source |
| Tenant deletes data accidentally | Restore a separate cluster, extract tenant rows, reconcile forward | Source for selective repair |
A replica protects service continuity. It can also reproduce operator mistakes, logical corruption, and destructive DDL. A backup protects a previous state. Neither is a substitute for the other.
Restore in quarantine and in dependency order
Do not point application traffic at the first node that starts. Restore into an isolated network with outbound controls so subscriptions, CDC connectors, email jobs, payment calls, and event publishers cannot act on historical data.
flowchart TD
Select[select one complete recovery manifest] --> Build[build isolated matching infrastructure]
Build --> Globals[restore packages roles config and certificates]
Globals --> Nodes[restore coordinator and every worker base]
Nodes --> Replay[replay each node WAL to the shared named point]
Replay --> Resolve[start nodes and allow Citus recovery logic]
Resolve --> Topology[compare node shard and placement metadata]
Topology --> Data[validate reference data tenant paths and invariants]
Data --> Failover[test coordinator and worker behavior]
Failover --> Cutover[choose controlled repair or production cutover]
The operational sequence is:
- Choose one manifest; never mix a convenient worker backup from one generation with a coordinator from another.
- Provision the same PostgreSQL major, compatible Citus binary, extension dependencies, collation environment, and storage layout.
- Restore roles, tablespaces, certificates, access rules, and required configuration without enabling application access.
- Restore the coordinator and all required workers from their recorded bases.
- Configure each node to read only its own WAL namespace and recover to the shared named restore point.
- Start the restored nodes under controlled access and allow PostgreSQL and Citus transaction recovery to complete.
- Compare
pg_dist_node, distributed-table metadata, shard placements, reference tables, extension versions, and prepared transactions against the manifest. - Execute tenant-local and cross-node invariant checks before any cutover.
- Test the application through the normal coordinator endpoint with every external side effect disabled.
- Decide whether the result is for full cutover, selective repair, evidence collection, or disposal.
Recovery ordering is a control-plane decision, not a claim that workers must finish WAL replay serially. Nodes can often be restored and replayed in parallel. The gate is that no application traffic is admitted until the complete distributed state passes validation.
Recover one tenant without rewinding every tenant
Tenant 418 accidentally deletes today’s orders at 10:42. A full in-place PITR to 10:41 would recover tenant 418 by deleting every legitimate transaction that all other tenants committed after 10:41. That is usually the wrong blast radius.
Citus row-based sharding commonly puts multiple tenant values in the same shard. Citus documents that sharing explicitly; a tenant can be isolated to a dedicated shard for workload management, but isolation does not convert physical cluster backup into a safe row-level restore feature: Citus tenant isolation.
Use an isolated-recovery workflow:
flowchart TD
Delete[tenant 418 data is deleted] --> Freeze[stop further destructive workflow for that tenant]
Freeze --> Restore[restore full Citus cluster in quarantine]
Restore --> Target[recover to point before deletion]
Target --> Export[export tenant 418 rows by distribution key]
Export --> Compare[compare historical rows with current production]
Compare --> Repair[apply reviewed idempotent repair]
Repair --> Verify[verify tenant and cross-table invariants]
Export the complete colocated tenant graph, not only orders. For RetailCo that may include order items, payment state, inventory reservations, shipments, and audit references. Then classify rows:
- deleted rows that should be reinserted;
- rows recreated after the incident that must not be duplicated;
- rows changed legitimately after the restore point that must win;
- external side effects that cannot be replayed blindly;
- sequences, idempotency keys, and uniqueness constraints affected by the repair.
The repair should use a reviewed, restartable process with explicit conflict handling. It should not attach old shard files to production or copy a restored Citus shard behind the coordinator’s metadata. Even a dedicated tenant shard participates in colocation, reference data, metadata, and transactions whose correctness must be reconciled logically.
In Practice
The documented behavior leads to a practical recovery program, but it does not produce RetailCo’s RPO or RTO on paper.
Decision 1 — make backup coordination version-aware
For Citus 14.0, the enhanced restore point is available for cluster-wide WAL markers, including multi-writer metadata mode. Other installed lines need a separately proven write-quiescence control, one manifest, per-node WAL continuity, and an upgrade plan when their recovery semantics cannot meet the requirement.
Result to verify: a restored snapshot generation contains no unresolved distributed decision caused by the capture window, and topology metadata matches the worker placements.
No result is claimed here until a restore drill verifies it.
Decision 2 — test PITR as a cluster operation
Create a uniquely named Citus restore point, record it in the manifest, generate controlled writes before and after it, then recover every node to that name in quarantine.
The test should include:
- a single-shard transaction before the marker;
- a multi-worker transaction before the marker;
- equivalent transactions after the marker;
- a topology or schema change near, but not during, the marker;
- worker and coordinator restarts during the drill;
- a check for unresolved prepared transactions;
- application invariants proving that pre-marker work exists and post-marker work does not.
Result to verify: all participating nodes agree on the intended distributed state, not merely that PostgreSQL starts.
Decision 3 — measure the real recovery path
Run at least three different drills:
| Drill | What it proves |
|---|---|
| Worker rebuild | A failed worker group can be reconstructed and rejoined safely |
| Full-cluster regional restore | Infrastructure, backups, WAL, packages, secrets, and topology can form a working cluster elsewhere |
| Tenant repair | The team can extract and reconcile one tenant without rewinding unrelated tenants |
Capture discovery time, infrastructure time, bytes restored, WAL replay time, validation time, decision time, and cutover time separately. The observed total—not a tool’s backup timestamp—determines the achievable recovery time. The newest fully restorable manifest and continuous WAL coverage—not a configured retention value—bound the achievable recovery point.
Decision 4 — make validation executable
A successful startup is only the first gate. Automate checks for:
- the expected node groups and active primaries;
- shard counts and placements for every distributed relation;
- missing, extra, inactive, or unhealthy placements;
- matching Citus and PostgreSQL versions;
- reference-table availability on required nodes;
- no unexpected prepared transactions;
- row counts and financial totals by tenant and time window;
- order-to-item, order-to-payment, and reservation invariants;
- read and write paths for canary tenants;
- external integrations remaining disabled until explicitly released.
The documented pattern is simple: recovery proof must test the distributed database and the application boundary, not only the backup files.
Where It Breaks
| Failure in the design | Why the backup can still look healthy | Consequence during recovery | Control |
|---|---|---|---|
| Independent snapshots | Every node snapshot succeeds locally | Distributed transaction states can disagree | Use a validated maintenance boundary and one named restore point |
| Stale coordinator generation | Metadata is small and restores quickly | It can describe the wrong shard topology | Restore from one manifest and compare placements |
| Missing worker WAL | Base backup is present | Node cannot reach the common restore point | Isolate WAL namespaces and alert on archive gaps |
| One shared WAL prefix | File names look valid | Different node histories can collide | Namespace by cluster, node system identifier, and timeline |
| Logical dump as the only DR path | Dump command succeeds | Restore may be too slow and cannot replay WAL | Maintain physical backup and WAL; test logical restore separately |
| Replica treated as backup | Standby is current | Corruption or destructive SQL is replicated | Keep isolated, immutable, independently retained backups |
| Config omitted | Database pages are intact | Node starts with wrong access, extensions, or archive settings | Version and back up configuration and packages |
| Writers not resumed | Snapshot request fails midway | The maintenance boundary becomes an outage | Use guaranteed cleanup, explicit ownership, and an abort runbook |
| Untested arbitrary-time PITR | Every node reaches the same clock time | A distributed decision may not be proven consistent | Prefer a Citus named restore point and validate in quarantine |
| Direct shard-file tenant restore | Old rows exist in recovered storage | Metadata, colocation, and current rows can diverge | Extract tenant data logically and reconcile |
| Restore drill tests startup only | All PostgreSQL processes are green | Application or data invariants can still be wrong | Run topology, transaction, tenant, and side-effect tests |
When I would not accept self-managed Citus recovery
I would not recommend this architecture when the organization cannot own all-node backup coordination, per-node WAL lifecycle, version-aligned rebuilds, distributed validation, and regular full-scale restore drills.
I would also reject a design whose RTO assumes that dozens of terabytes can be restored and replayed faster than the environment has demonstrated. At that point the options are to reduce the recovery unit through tenant or domain decomposition, maintain tested warm capacity, accept a different recovery objective, or choose a managed platform whose documented recovery model fits the requirement. Managed does not remove the need for application-level validation or tenant-repair planning; it changes who operates the underlying backup machinery.
What to Do Next
For RetailCo, the recommendation is a layered design: streaming standbys for single-node availability, coordinated physical backups plus per-node WAL for disaster recovery, logical exports for portability and selective repair, and versioned infrastructure for reconstruction. A deleted tenant should be repaired from an isolated full-cluster restore; the live cluster should not be rewound for everyone.
The main advantage is recoverability at distributed scale without pretending that each node is an independent database. The main limitation is operational: the recovery unit grows with the cluster, and the proof requires a full distributed restore. Operational complexity is high. Migration complexity is moderate for backup tooling but high for teams that currently have only node-by-node jobs.
The conclusion is not that Citus cannot be backed up. It is that the backup boundary must be as distributed as the transaction boundary.
- Problem: Per-node backup success does not prove a recoverable Citus state.
- Solution: Treat the coordinator, every worker, WAL, metadata, globals, configuration, and one consistency boundary as a single recovery unit.
- Proof: Restore a complete generation to quarantine, replay every node to one Citus restore-point name, validate distributed transactions and topology, and run a separate tenant-repair drill.
- Action: Inventory the installed versions first. Use
citus_create_restore_point()for the cluster-wide PITR marker — orcitus_cluster_changes_block()on Citus 14.1 and later — enforce a tested maintenance boundary for snapshot generations, and do not publish an RPO or RTO until the measured restore meets it.