How to back up Amazon RDS for PostgreSQL, and keep a copy outside the account
RDS takes a snapshot of the instance every day and keeps the transaction log, so you can
restore to any point in the retention window. That covers a bad migration or a dropped
table. It does not cover the instance and its automated backups being deleted together, an
AWS account that is closed or taken over, or a copy you need to restore on something that
is not RDS. For those, take a pg_dump and keep it somewhere the account cannot
delete. This page shows where to run it (a private RDS is not reachable from GitHub's
runners), the role, the command, the restore and the check. The last section is how
SafeGrd runs it.
What RDS backups cover, and what they do not
From the RDS User Guide, read on 2026-10-09:
| Backup | How long it is kept | When the instance is deleted |
|---|---|---|
| Automated backups | 0 to 35 days. The console defaults to 7, the API and the AWS CLI to 1. 0 turns them off | Deleted with the instance, unless Retain automated backups is chosen |
| Point-in-time restore | Within the same window. The log is uploaded every 5 minutes | Goes with the automated backups |
| Manual snapshots | Until someone deletes them. Up to 100 per Region | Kept |
- A restore makes a new instance. Point-in-time restore and snapshot restore both create an instance from the backup. You cannot restore one table, and you cannot restore anywhere but RDS.
- Everything is in the one account. Anyone with
rds:DeleteDBInstanceandrds:DeleteDBSnapshotin that account can remove the instance and its snapshots. AWS Backup with a Vault Lock closes that gap for snapshots, and the copy is still an RDS snapshot. - The snapshot export is not a dump. Exporting a snapshot to S3 writes Apache Parquet files per table, which analytics tools read. It does not restore into PostgreSQL.
1. Where to run it
Most RDS instances have Publicly accessible: No and a security group that admits only the application. The backup has to run inside the VPC:
- An EC2 instance in the same VPC, with the database's security group
admitting it on 5432. The smallest instance type is enough for a database of a few GB,
since
pg_dumpspends most of its time waiting on the database. - A scheduled ECS task or a Kubernetes CronJob in the VPC, if you already run one. No instance to keep patched.
- GitHub Actions only with a self-hosted runner inside the VPC. GitHub's hosted runners cannot reach a private address.
Point the backup at a read replica when you have one, so the read stays off
the writer. A long pg_dump on a replica can be cancelled with
canceling statement due to conflict with recovery when the writer vacuums rows
the dump still needs. Raise max_standby_streaming_delay in the replica's
parameter group, or turn on hot_standby_feedback, before the first large dump.
2. Make a read-only role
The master user can write, and RDS does not give out superuser. A backup needs neither. Connect as the master user and run:
pg_read_all_data is in PostgreSQL 14 and later, and covers tables made after
the grant. On 13 and older, grant SELECT on each schema's tables and set
default privileges for new ones. A table with row-level security dumps only the rows its
policies show this role; give it BYPASSRLS if you use RLS. Run the CREATE ROLE on the writer: a
replica copies it.
3. Dump it
verify-full with Amazon's certificate bundle checks that the host is the RDS
endpoint, not only that the connection is encrypted. Match the client to the instance's
major version: an older pg_dump refuses a newer server. One dump is one
database, so an instance with several is one command each, plus
pg_dumpall --roles-only if you want the roles. --no-owner
--no-privileges leaves out rds_superuser and the other RDS roles, which
do not exist anywhere else.
4. Encrypt it, ship it out of the account, schedule it
The copy is only outside the blast radius if it is in another account or another provider. Encrypt it on the host and write it with a credential that can put objects and nothing else:
The bucket lives in a separate AWS account (or at Backblaze B2, Wasabi or Cloudflare R2),
with Object Lock on, so a key leaked from the production account cannot delete it. The
bucket policy, a key that cannot delete and the lock are in
the S3 guide and
the Object Lock guide. Schedule it
with cron on the EC2 instance or an EventBridge rule for the ECS task;
set -o pipefail makes a failed dump fail the run instead of uploading a cut-off
file.
The dump crosses the network once per run. Inside one Availability Zone that costs nothing;
across zones and out of AWS it is billed as data transfer. safegrd estimate
prints what a nightly, weekly or hourly schedule would move for your database
(estimate).
5. Restore
Into RDS: create an instance of the same or a newer major version, create an empty database with the same name, and restore over its endpoint. Into anything else: any PostgreSQL of the same or a newer major version.
--exit-on-error stops at the first failure instead of carrying on and
reporting success. The dump says CREATE EXTENSION for each extension the
database used. RDS supports a fixed list; a target outside RDS needs each one installed
first. One table from the dump is pg_restore --table=orders, which RDS's own
restore cannot do.
6. Check what came back
Compare each table's row count in the restored database with the source, as How to test that a backup restores shows, and do it on a schedule. AWS Backup's restore testing does this for RDS snapshots at a charge per test plus the restored instance's hours (SafeGrd and AWS Backup).
How SafeGrd does it
SafeGrd's daemon runs on the EC2 instance inside the VPC, from the install script, or as
the ghcr.io/safegrd/cli image on ECS or Kubernetes, reading the replica. It
encrypts each backup on that host and writes it under Object Lock to your bucket in
another account, with a credential that can write and never delete, or to SafeGrd's hosted
storage. Each backup uploads only what changed since the last one. A drill restores the
newest snapshot into a throwaway PostgreSQL of the same major version and records the
tables and rows that came back, and the console alerts when a backup fails or does not
run. SafeGrd does the same job on every platform; the
PostgreSQL page has what it dumps, how it encrypts, and what a drill checks.
Run your first Fire Drill Read the install guide
The enrolment asks for the database's connection string, with the replica endpoint and
sslmode=verify-full. The ECS task definition is not on this page: the image and
its volume are the same as the Docker install, and the
Helm chart
runs it on EKS. An instance that is publicly accessible, with
its security group open to SafeGrd's addresses, can skip the host: Back up on
SafeGrd under Surfaces in the console takes the connection string, and a machine
SafeGrd starts for each backup dumps and encrypts it, then is destroyed
(how).
Questions
Should I turn off RDS automated backups?
Keep them. Point-in-time restore inside the window is the fastest way back from a bad migration, and it costs little up to the size of the database. Set the window to the 35 days RDS allows if a mistake can go unnoticed for longer than a week. The dump outside the account is for when the instance, its backups or the account is what was lost.
Aurora?
The same, with the cluster's reader endpoint in place of the replica. Aurora's automated backups have the same 1 to 35 day window and also go with the cluster unless they are retained.
Why not copy snapshots to a second account?
That works and is worth doing: a cross-account snapshot copy survives the first account. It is still an RDS snapshot, so it restores only into RDS, a whole instance at a time, and nothing checks that it restores until someone tries.
Related
- How to back up a PostgreSQL database: pg_dump's formats and flags, restore and the check
- SafeGrd and AWS Backup: restore testing, Vault Lock and what each records
- RDS and Aurora in the docs: the replica, the certificate and the role