Skip to main content

Logical database dumps

Maintenance::Database::DumpTablesTask takes a logical dump of some tables, or of the whole database, and uploads it to S3. Run it before dropping tables or before a risky data change, when you want a copy that outlives the CloudNativePG backups.

Those backups are physical, continuous, with point-in-time recovery, and are not an application concern. They restore the whole cluster at a point in time. A dump restores single tables, wherever you want.

Running it​

From /maintenance_tasks, pick Maintenance::Database::DumpTablesTask and choose the tables in the tables list, which shows the database's current tables. Hold Ctrl (Cmd on macOS) to select more than one. Select none to dump the whole database.

From a console, tables also takes an array or a comma-separated string. Names that are not tables of the database fail validation.

The task runs a single pg_dump --format=custom --compress=zstd, so all the tables come from one snapshot. The output goes straight to S3 as a multipart upload, without touching the pod's disk.

There is no progress and no resume. If a deploy interrupts the worker, the task is re-enqueued and the dump starts again from scratch.

The S3 key is in the task's log line Database dump uploaded:

s3://aleteia-downloads/db-dumps/<environment>/<YYYYMMDDTHHMMSS>-<tables>.dump

<environment> is ROLLBAR_ENV: production, staging or review. Staging and the review apps also run RAILS_ENV=production and share the bucket and its keys with production, so ROLLBAR_ENV is what keeps their dumps apart.

The label is all for a full dump, the table names joined by - for up to three tables, and <n>-tables for more.

Retention and access​

  • Dumps never expire. They exist to outlive the database backups, so delete the ones you no longer need by hand. Interrupted multipart uploads are aborted after one day, by the db-dumps/abort-incomplete-uploads lifecycle rule in infra/s3.tf.
  • Objects are private, and the application keys cannot read them. ActiveStoragePolicy, which every IAM user of the bucket carries (the app's, and the dailymotion user that feeds the BigQuery import), denies GetObject on db-dumps/*. Download dumps with admin credentials of the Aleteia account. They hold personal data: download them only where you need them and delete the local copy afterwards.

Restoring​

Download the dump with credentials for the Aleteia AWS account:

aws s3 cp s3://aleteia-downloads/db-dumps/production/20261010T090000-wordpress_pictures.dump .
pg_restore --list 20261010T090000-wordpress_pictures.dump   # what is inside

Restore into a scratch database to inspect it:

createdb scratch
pg_restore --no-owner --dbname=scratch 20261010T090000-wordpress_pictures.dump

To restore a table into the cluster, stream the file into pg_restore running in a pod. The pods already have the PG* variables, and the single quotes keep $PGDATABASE for the pod's shell:

kubectl -n reports-production exec -i deploy/reports -- \
  sh -c 'pg_restore --no-owner --dbname="$PGDATABASE" --table=wordpress_pictures' < 20261010T090000-wordpress_pictures.dump

--table restores the table's definition and data but not its indexes or foreign keys. Drop --table to restore everything in the archive. If the archive's tables still exist in the database, add --data-only, or --clean, which drops and recreates them.