Automatic PostgreSQL backups to cloud storage
Back up a PostgreSQL database every night, or every 6 hours, into Google Drive, Amazon S3, OneDrive or any other cloud, with old backups removed and no server of your own running cron.
The do-it-yourself way: pg_dump and cron
If you have a server that is always on, a short script and a cron line will do it. This one dumps the database, compresses it, copies it to a cloud with rclone and deletes local copies older than two weeks:
#!/usr/bin/env bash
set -euo pipefail
stamp=$(date +%Y-%m-%d-%H%M%S)
file="/var/backups/shop-$stamp.sql.gz"
pg_dump --clean --if-exists "$DATABASE_URL" | gzip > "$file"
rclone copy "$file" remote:db-backups/
find /var/backups -name 'shop-*.sql.gz' -mtime +14 -delete
# crontab -e: every day at 02:00
0 2 * * * /usr/local/bin/pg-backup.sh
What usually goes wrong with it:
- Without
pipefail, apg_dumpthat fails still leaves a small, valid .gz file, and the backup looks fine until the day you need it. pg_dumprefuses a server newer than itself (server version mismatch). When a managed database is upgraded to a new major version, the job stops until somebody upgrades the client too.- Nobody hears about a failure. Cron mails a local user nobody reads.
- The old copies in the cloud are never deleted, only the local ones.
For one database on a server you look after, that is enough. The rest of this page is for when you would rather not keep that server.
Steps
- Sign in, open Databases and press Connect a database. Choose PostgreSQL, or your hosting service by name.
- Paste the connection string (
postgresql://user:password@host:5432/dbname), or fill in the server, database, username and password one by one. - If the database only answers on its own server, fill in the SSH server fields too; it is then reached through that server, and the database address is usually
localhost. - Press Schedule on its card. Choose the cloud and folder, how many backups to keep (1 to 365), and how often: every 6 hours, every 12 hours, every day, every Sunday or on the 1st of every month.
- Save. The first backup starts at once, and its progress shows in the job list.
What each backup is
- A plain SQL dump from
pg_dump --clean --if-exists, gzipped as it streams: one dated file such asshop-2026-11-04-020000.sql.gz. It restores over an existing database without errors about tables that already exist, and it carries no owners or grants, so any user that can create the tables can restore it. - Made with a current
pg_dumpfrom the PostgreSQL project's own packages, not an operating system's older copy, so a server on a recent major version backs up without a version mismatch. - After each good run, the oldest backups of this database past the number you keep are removed. Nothing else in the folder is touched.
- To back up part of a database, list tables as
schema.table;*matches any characters, sopublic.order*takes every table starting with order. - The Databases page shows each database's last backup, its size and when it ran, and every schedule with its next run.
Encrypted backups
Give the database a backup passphrase (12 characters or more) and every backup is encrypted with AES-256 before it leaves our server; its name gains .enc. The cloud you save into then holds nothing readable. To open one on your own computer, with the OpenSSL that comes with macOS, Linux and Git for Windows:
openssl enc -d -aes-256-cbc -pbkdf2 -iter 600000 -in shop-2026-11-04-020000.sql.gz.enc -out shop-2026-11-04-020000.sql.gz
Keep the passphrase somewhere safe. Without it nobody, including us, can open the backups.
Restoring a backup
From the app: press Restore on the database's card, pick a backup (newest first), type the database's name to confirm, and start. Every table in the backup is dropped and recreated; tables that are not in it are left alone.
By hand, anywhere psql is installed:
gunzip -c shop-2026-11-04-020000.sql.gz | psql -v ON_ERROR_STOP=1 "postgresql://user@host:5432/shop"
ON_ERROR_STOP makes psql stop at the first error rather than carrying on and leaving half a database.
Supabase, Neon, Amazon RDS and DigitalOcean
Each is on the connect dialog by name, with TLS switched on and the usual port filled in.
- Supabase: use the Session pooler connection string, on port 5432. The direct host answers over IPv6 only.
- Neon: use the direct connection string, not the pooled one (the host with
-poolerin it). - Amazon RDS and Aurora: allow our server in the instance's security group, or keep the instance private and connect through a bastion host with the SSH fields.
- DigitalOcean Managed PostgreSQL: add our server to the cluster's trusted sources.
Hosted databases usually accept connections only from addresses on an allow list. Add our server's address there, or keep the database private and use the SSH fields to reach it through a server that can. Your provider's own backups are worth keeping too, but they live inside the same account as the database: a copy in storage you control is what is left if that account is closed, compromised or deleted.
Limits
- A backup is the data at one moment, not a continuous replica or point-in-time recovery.
- A backup stops after 2 hours, or past 20 GB compressed, unless the operator has changed those limits.
- Each backup's size counts towards your transfer allowance.
Questions
Does a backup lock the database?
No. pg_dump reads from one consistent snapshot and does not block ordinary reads and writes. It does hold off schema changes, such as ALTER TABLE, on the tables it is copying until it has finished with them.
Which database user should it use?
One that can read every table. On PostgreSQL 14 and later, a user granted the pg_read_all_data role is enough for backups. Restores need a user that can drop and create the tables.
Is this point-in-time recovery?
No. Each backup is the database as it was when that backup ran. For recovery to any second, use your provider's point-in-time recovery alongside these backups.
Try it
Prepaid: 30 GB free, then 500 GB for ₹100 or 2,500 GB for ₹500, each valid a year. No card is asked for to start.