Backup and restore

Polo keeps its product state in PostgreSQL. The scripts in scripts/ops/ take a portable dump of that database, restore it, and rehearse the whole cycle. They take every secret from the environment and never print URLs, passwords or roles.

What to back up

What	Where	Why
Product database	PostgreSQL database polo_product	Workspaces, tasks, approvals, connections, knowledge, audit records
Encryption keys	POLO_API_OPERATION_KEY, POLO_API_SSO_KEY (in .env or the Kubernetes Secret)	Without them, sealed credentials and SSO configuration in the database are unreadable
The rest of the environment	.env (Compose) or the Secrets (Helm)	Role passwords, worker tokens, storage keys
Artifacts and snapshots	The S3 bucket, and the worker’s snapshots volume	Task artifacts and workspace snapshots
Temporal	Its databases (temporal, temporal_visibility on the same server in Compose)	The state of tasks in flight
Runner storage	Each customer runner’s storage directory	Receipts and runner state; owned by whoever runs the runner

Caution

Back up the encryption keys whenever you back up the database, and keep them in your secret manager rather than beside the dump. A dump without the keys cannot recover connection credentials; a dump together with the keys can, so treat that pair as sensitive.

A managed database’s snapshots, WAL archiving or point-in-time recovery remain the first line of defence. The dump described here is the portable copy.

Which role can take a backup

Every tenant table forces row-level security, which binds the table owner as well. A dump therefore needs a role that bypasses it: a superuser, or a role with BYPASSRLS. For any other role pg_dump stops at the first tenant table. backup.sh checks the connecting role first and exits with code 2, before writing anything, when row-level security applies to it.

• Compose and the local launcher: polo_migration is the PostgreSQL container’s bootstrap superuser, so its URL works as is.

• An external server where polo_migration is a plain owner: either give it the attribute, or create a read-only backup role (PostgreSQL 14 and later). Granting BYPASSRLS normally takes a superuser.

  ALTER ROLE polo_migration BYPASSRLS;
  -- or
  CREATE ROLE polo_backup LOGIN BYPASSRLS PASSWORD '...' IN ROLE pg_read_all_data;

• A managed service that grants neither: rely on its snapshots and point-in-time recovery.

• Never the application role. It cannot bypass row-level security by design, and the API refuses to start otherwise.

Back up

scripts/ops/backup.sh runs pg_dump in custom format and writes polo-<database>-<UTC timestamp>.dump (mode 0600) with a .sha256 file beside it. It prints one JSON line with the file, size, checksum and whether it was uploaded.

POLO_BACKUP_DATABASE_URL='postgresql://polo_migration:<password>@<db-host>:5432/polo_product' \
POLO_BACKUP_DIR=/var/backups/polo \
POLO_BACKUP_S3_URI=s3://<backup-bucket>/product \
POLO_BACKUP_RETENTION_DAYS=14 \
scripts/ops/backup.sh

Variable	Purpose
POLO_BACKUP_DATABASE_URL	The database to dump. Required.
POLO_BACKUP_DIR	Local directory for dumps. Default ./backups, created with mode 0700.
POLO_BACKUP_S3_URI	Optional s3://bucket/prefix destination.
POLO_BACKUP_MC_ALIAS	Upload with mc cp to this alias, when mc is installed.
POLO_BACKUP_S3_ENDPOINT	Endpoint for aws s3 cp, for MinIO. AWS credentials come from the environment.
POLO_BACKUP_REQUIRE_UPLOAD	true fails the run when a destination is set but no upload tool is available. Otherwise the script keeps the local copy and warns.
POLO_BACKUP_RETENTION_DAYS	Removes local dumps older than this many days after a successful run.
POLO_PG_CLIENT_CONTAINER	A Docker container that provides pg_dump, pg_restore and psql when the host has no client tools, for example the PostgreSQL container itself.
POLO_PG_CLIENT_HOST, POLO_PG_CLIENT_PORT	Where that container reaches the server. Default 127.0.0.1:5432.

Record the row counts next to each backup, so a restore can be checked against them:

scripts/ops/rowcounts.sh 'postgresql://polo_migration:<password>@<db-host>:5432/polo_product'

Schedule the backup from cron or a Kubernetes CronJob at least daily.

Compose

Either run backup.sh with POLO_PG_CLIENT_CONTAINER set to the PostgreSQL container, or dump directly:

docker compose exec -T postgres pg_dump -U polo_migration -Fc polo_product > polo_product.dump

Back up .env with it.

Helm with the dependencies chart

deploy/helm/polo-dependencies can take a daily dump to S3 itself. Set backups.enabled, backups.bucket, backups.region and backups.existingSecret (a Secret holding AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY of a user that may only put objects there). The schedule defaults to once a day (backups.schedule), and objects are written under backups.prefix. Expire old dumps with a lifecycle rule on the bucket.

Restore

scripts/ops/restore.sh takes the dump as its first argument (or POLO_RESTORE_FILE). It:

1. verifies the .sha256 file when one is present;
2. optionally creates or replaces the target database;
3. runs pg_restore --exit-on-error;
4. runs the migration check against the target — a complete backup prints "migrations_applied":[], and a backup older than the code applies the missing migrations and lists them;
5. prints table,count rows for every Polo table.

Variable	Purpose
POLO_RESTORE_DATABASE_URL	The target database. Required. Restore as the migration owner so ownership, row-level security policies and functions come back intact.
POLO_RESTORE_CREATE	true creates the target database first (the role needs CREATEDB).
POLO_RESTORE_REPLACE	true replaces an existing target. Refused for a database named polo_product unless POLO_RESTORE_ALLOW_PRODUCTION=true.
POLO_RESTORE_NO_OWNER	true restores without owners and privileges, for a server that lacks the original roles. Run provisioning afterwards.
POLO_RESTORE_JOBS	Parallel jobs. Default 2; forced to 1 when restoring through a container.
POLO_RESTORE_SKIP_MIGRATION_CHECK, POLO_RESTORE_SKIP_COUNTS	true skips that step.

The migration check runs node api/dist/src/platform/database/migrate.js, so the API must be built (cd api && npm run build) on the machine that runs the restore. The restore itself needs no BYPASSRLS.

Production procedure

Use this for a new server or a damaged database.

1. Stop the API, the relay, the scheduler, the watch processes and the runtime worker. The relay and the worker would otherwise write to a half-restored database.

2. Restore:

   POLO_RESTORE_DATABASE_URL='postgresql://polo_migration:<password>@<db-host>:5432/polo_product' \
   POLO_RESTORE_CREATE=true POLO_RESTORE_REPLACE=true POLO_RESTORE_ALLOW_PRODUCTION=true \
   scripts/ops/restore.sh /var/backups/polo/polo-polo_product-<stamp>.dump

3. Compare the printed counts with the counts recorded at backup time.

4. If roles were recreated, run provisioning (polo-api provision, or node api/dist/src/platform/database/provision.js) with the operator environment so the application and dispatch roles get their grants back.

5. Start the API and check GET /readyz. Then start the relay, the scheduler, the watch processes and the worker.

Note

Temporal’s state can stay as it is. Running tasks re-read the API, but they tolerate a restore to an earlier point only if you also cancel the tasks that started after the backup.

Make sure the restored deployment runs with the same POLO_API_OPERATION_KEY and POLO_API_SSO_KEY as the one that took the backup.

Rehearse a restore

scripts/ops/restore-drill.sh dumps a source database, restores it into a scratch database on the same server (default polo_restore_drill), compares the row counts table by table, and then removes the scratch database and the dump.

POLO_DRILL_SOURCE_URL='postgresql://<role>:<password>@<host>:<port>/<source database>' \
POLO_DRILL_SCRATCH_DB=polo_restore_drill \
scripts/ops/restore-drill.sh

A successful run ends with a JSON line that includes "counts_match":true.

Variable	Purpose
POLO_DRILL_SOURCE_URL	The database to rehearse with. Required.
POLO_DRILL_SCRATCH_DB	Scratch database name. It cannot be polo_product.
POLO_DRILL_BACKUP_DIR	Where the dump is written. Default a fresh temporary directory.
POLO_DRILL_KEEP	true keeps the dump and the scratch database for inspection.

What the drill assumes:

• One role does everything on one server, so the source URL names a superuser, or the migration owner with BYPASSRLS and CREATEDB.
• The roles the dump grants to (polo_application, polo_dispatch) exist on that server, and the API is built for the migration check.
• It does not exercise a restore onto a server without the original roles, or onto a different PostgreSQL major version.

Caution

Run the drill against a disposable or staging server, never against the live product server.

Repeat the drill monthly, and after every release whose migration adds a schema, function or role.

Before an upgrade

Take a backup before every upgrade. Migrations are forward-only, so rolling back a release whose migration has run means restoring that backup. See the upgrade order.