ShiftMyData

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:

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

  1. Sign in, open Databases and press Connect a database. Choose PostgreSQL, or your hosting service by name.
  2. Paste the connection string (postgresql://user:password@host:5432/dbname), or fill in the server, database, username and password one by one.
  3. 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.
  4. 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.
  5. Save. The first backup starts at once, and its progress shows in the job list.

What each backup is

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.

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

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.

Get started

Related