Skip to main content

Backups with shards

With the engine on the app database alone, one backup holds everything and any tool works, see Operations → Backups. This page is for a deployment with extra shards, where the databases have to be restored to the same point.

Why the databases go together​

  • A message, a signal or a call activity between shards is written to the receiving shard first and removed from the sending shard after. A receiving shard restored to an earlier point than the sending one has lost it, and the instance waiting for it never continues.
  • Deployed processes and decisions live in the app database. A shard restored to a later point than the app database can hold instances of versions the app database does not know.
  • The partition map lives in the app database. Databases restored from both sides of a rebalance disagree about which shard holds which partition. The backend refuses to start on such a set.

Two ways give every database the same point.

Offline backup​

  1. Stop every replica.
  2. Back up every database, the app database and each shard, with any tool: pg_dump, a volume snapshot, a base backup.
  3. Start the replicas.

Restore the whole set together, with the replicas stopped.

Restore points​

A restore point is a name in a database's WAL that point-in-time recovery can stop at. The restore-point command creates one in every database while the backend keeps running:

docker run --rm --env-file backend.env <backend image> restore-point

The name is optional and defaults to quantumbpm-<UTC time>, for example quantumbpm-20261102T120000Z. The command holds messages, signals and call activities between shards back for a moment, creates the restore point in every extra shard and in the app database last, then lets them go. It waits up to 5 seconds for those already under way and fails with try again if they take longer. It logs one Created restore point line per database. Run it on a schedule next to your base backups, the chart's README.md has it as a CronJob.

Every database needs:

  • continuous WAL archiving and regular base backups,
  • wal_level of replica or logical,
  • GRANT EXECUTE ON FUNCTION pg_create_restore_point(text) TO <backend user>, run in that database, unless the backend's user is a superuser.

To restore:

  1. Stop every replica.
  2. Restore every database, the app database and each shard, to the same restore point name, each from its own base backup and WAL archive.
  3. Point the backend at the restored databases. The shard IDs stay the same, only the URLs change.
  4. Start the replicas. They check that the databases agree before they start.

CloudNativePG​

One CloudNativePG cluster per database, archiving WAL through the Barman Cloud plugin. The grant runs once when the cluster is created. CloudNativePG sets wal_level to logical by default.

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: shard2
spec:
  instances: 3
  storage:
    size: 100Gi
  bootstrap:
    initdb:
      database: bpm
      owner: bpm
      postInitApplicationSQL:
        - GRANT EXECUTE ON FUNCTION pg_create_restore_point(text) TO bpm
  plugins:
    - name: barman-cloud.cloudnative-pg.io
      isWALArchiver: true
      parameters:
        barmanObjectName: shard2-store

shard2-store is an ObjectStore (barmancloud.cnpg.io/v1) pointing at the bucket, and a ScheduledBackup takes the base backups. On a cluster that already exists, grant it once by hand:

kubectl cnpg psql shard2 -- bpm -c "GRANT EXECUTE ON FUNCTION pg_create_restore_point(text) TO bpm"

To restore, create a new cluster per database, each recovering to the same name:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: shard2-restored
spec:
  instances: 3
  storage:
    size: 100Gi
  bootstrap:
    recovery:
      source: shard2
      recoveryTarget:
        backupID: 20261102T110000          # a base backup of this database taken before the restore point
        targetName: quantumbpm-20261102T120000Z
  externalClusters:
    - name: shard2
      plugin:
        name: barman-cloud.cloudnative-pg.io
        parameters:
          barmanObjectName: shard2-store
          serverName: shard2
  • backupID is required with targetName. Each database has its own base backups, so pick one per database, the latest taken before the restore point.
  • A restored cluster archives no WAL until you add plugins with isWALArchiver: true to it. Give it its own ObjectStore or serverName, not the archive it recovered from.

After a rebalance​

Take a base backup of every shard that received partitions, or a fresh offline backup of every database, as soon as the rebalance finishes. A complete set from before the rebalance still restores with the configuration from before it, but nothing restores the new shard's rows until it has a backup of its own.

Not supported​

  • Restoring one shard, or the app database, alone while keeping the others.
  • pg_dump of a running deployment with extra shards. Each dump is consistent only within its own database.
  • Point-in-time recovery to a timestamp instead of a restore point. The databases commit independently, so one moment in time can fall between the two halves of a delivery.