PostgreSQL backups that are restored on a schedule

SafeGrd backs up PostgreSQL 13 and later from any host that can reach the database. It takes the schema with pg_dump and the rows over binary COPY, both from one snapshot. Each backup is compressed and encrypted on that host, written to S3-compatible storage under Object Lock, and then restored on a schedule to check that every table and row came back.

Run your first Fire Drill Read the install guide

curl -fsSL https://safegrd.dev/install.sh | sh
safegrd backup --database-url "$DATABASE_URL" --retention-days 14
safegrd verify --snapshot <newest> --sandbox-target "$SANDBOX_URL"

What each backup holds

• The schema, as PostgreSQL describes it. Types, tables, functions, views, indexes, constraints and triggers come from pg_dump.
• Every row, streamed. Rows are copied table by table over binary COPY, in chunks, so a table larger than the host's memory backs up without staging anything on disk.
• One consistent moment. Schema and rows are read from one snapshot, so row counts and sequence positions agree even while the application writes.
• Encrypted before it leaves the host. The stream is compressed with zstd and encrypted with age X25519. The network and the bucket only see ciphertext.
• Locked in storage. S3 Object Lock in compliance mode refuses to delete or overwrite the backup until its retention date, even for root credentials.

What a Fire Drill checks

A Fire Drill restores the newest backup and compares it with the manifest written when the backup was taken: the number of tables, the rows in each, the columns, and the installed extensions. A sandbox drill loads it into a throwaway database, which also proves the schema, constraints and triggers load. The result is signed and added to your record, and a failed drill alerts your team. Your plan sets how often drills run (pricing).

Where it runs

The daemon runs on a server beside the database, in a Docker container, on Kubernetes through the Helm chart, or in a scheduled GitHub Actions job. It needs a network path to PostgreSQL and a pg_dump at least as new as the server.

• Supabase: back up through the session pooler on port 5432 from GitHub Actions (Supabase backup).
• Neon: use the direct endpoint, without -pooler, and add ?sslmode=require.
• Amazon RDS and Aurora: point it at a read replica to keep the dump off the writer, and verify TLS with Amazon's global-bundle.pem.
• Coolify and other Docker hosts: run the container on the same server (Coolify backup).

How to restore

Create an empty database and point the restore at it. The command works on any host that has the CLI and the key, not only the host that took the backup.

The target has to be empty: the restore refuses a database that already holds tables, so it never writes over data. To put one schema back into a database that has others, add --schema NAME.

The target runs the same PostgreSQL major version as the backup's server, or a newer one. The whole restore is one transaction, and every table's row count is checked against the manifest before it commits (Restore and Fire Drills).

Questions

Does it need a superuser?

A read-only role is enough. On PostgreSQL 14 and later, grant it pg_read_all_data, which covers tables created later as well. Row-level security still applies to that role, so give it BYPASSRLS or back up as the tables' owner to capture every row. The backup lists each table RLS applied to (Databases).

Does it replace pgBackRest or WAL archiving?

SafeGrd takes logical backups, so it restores to the moment of a backup, not to any minute in between. If you need point-in-time recovery, keep pgBackRest or WAL-G for that and run SafeGrd beside it for a tested, encrypted copy in a separate account (SafeGrd vs pgBackRest).

Can I restore into a newer PostgreSQL version?

A logical backup loads into an empty database on the same major version or a newer one, which is how many teams upgrade. safegrd restore refuses a target that already holds tables, so a restore never writes over data.

Who holds the decryption key?

You choose when the key is made. With a SafeGrd-managed key, SafeGrd keeps your key sealed and releases it only to your enrolled hosts, so you can restore even after losing a host. With a customer-managed key, only you can decrypt these backups. The console shows which one you chose.

Related

• How to back up PostgreSQL to S3 with pg_dump, without SafeGrd
• pg_dump command builder, with the matching restore command
• Backup file checker for a dump you already have
• Backup verification: what each kind of check proves