How to back up a managed database (DigitalOcean, AWS RDS)

What the provider's backups do

Both providers back up the whole cluster or instance and can restore it to a point in time. The details differ:

Retained RDS automated backups still expire at the end of their retention period; a final snapshot does not. Google Cloud SQL can take a final backup when you delete an instance, kept 30 days by default. Separately, it keeps a deleted instance's backups for four days, and recovering from those means contacting Google Cloud Customer Care.

What they don't cover

• Mistakes found late. On DigitalOcean the window is seven days. A bad migration noticed on day eight cannot be undone from the provider's backups.
• The account. Backups sit in the same account as the database. Whoever can delete the cluster can take its backups with it, and if you lose access to the account you lose both.
• Small restores. A provider restore brings back the whole cluster as a new one. To recover one table, you restore everything, copy the table out and delete the copy.
• Portability. Provider backups restore into the same provider's service. A dump is a file you can load into any server that runs the same engine.

So keep the provider's backups for fast whole-cluster recovery, and add a nightly dump stored outside the account. That is the 3-2-1 rule applied to a database you don't run yourself.

Run the dump from a server the database trusts

Managed databases accept connections only from sources you allow. Pick a server in the same region, ideally on the same private network, and allow it in:

• DigitalOcean: the cluster's trusted sources, in the control panel or with doctl. The rule type can be droplet, k8s, ip_addr, tag or app.
• AWS RDS: the DB instance's VPC security group. Add an inbound rule for port 5432 (PostgreSQL) or 3306 (MySQL) with the server's security group or IP address as the source.

doctl databases firewalls append <cluster-id> --rule droplet:<droplet-id>

Copy the host and port from the cluster's connection details. DigitalOcean clusters typically take client connections on port 25060.

Connect directly, not through a pool

DigitalOcean PostgreSQL clusters can have PgBouncer connection pools, each with its own connection details. Pools default to transaction mode, which hands each transaction to whichever server connection is free. pg_dump needs one session for the whole dump, and DigitalOcean's documentation warns that running it against a transaction-mode pool causes errors. Use the cluster's own connection details, not a pool's.

Install a client that matches the server

pg_dump must be the same major version as the server or newer. It refuses to dump a newer server rather than risk a broken dump. Compare the two:

psql "host=<db-host> port=25060 dbname=mydb user=doadmin sslmode=require" -Atc "show server_version"

pg_dump --version

On Ubuntu, the PostgreSQL project's apt repository has every supported client version. Its setup script adds the repository, then you install the client that matches your server:

sudo apt install -y postgresql-common

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

sudo apt install -y postgresql-client-17

For MySQL, use MySQL's own mysqldump from the same major version as the server, for example an 8.4 client for an 8.4 server.

Create a read-only backup user

Don't dump as the admin user (doadmin on DigitalOcean, the master user on RDS). A read-only user can't damage anything if its password leaks. PostgreSQL 14 and newer have a predefined role for exactly this:

CREATE ROLE backup WITH LOGIN PASSWORD 'long-random-password';
GRANT pg_read_all_data TO backup;

pg_read_all_data reads every table, view and sequence in every schema. It does not bypass row-level security, and pg_dump stops with an error on a table with RLS policies unless the user can bypass them. If your admin user can't grant the role, grant per schema instead. Run the ALTER DEFAULT PRIVILEGES lines as the role that creates your tables, because default privileges only apply to objects the current role creates:

GRANT USAGE ON SCHEMA public TO backup;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO backup;

For MySQL, --routines needs the global SELECT privilege, so grant on *.*:

CREATE USER 'backup'@'%' IDENTIFIED BY 'long-random-password';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT ON *.* TO 'backup'@'%';

That is enough with the mysqldump options below: --no-tablespaces removes the need for PROCESS, and --set-gtid-purged=OFF the need for RELOAD or FLUSH_TABLES. The trusted sources or security group still decide which machines can connect.

Dump PostgreSQL over TLS

Put the password in ~/.pgpass of the user that runs the dump, one line per connection, and make the file private:

<db-host>:25060:mydb:backup:long-random-password

chmod 600 ~/.pgpass

pg_dump "host=<db-host> port=25060 dbname=mydb user=backup sslmode=verify-full sslrootcert=/etc/ssl/certs/db-ca.crt" -Fc -w -f mydb.dump

• sslmode=require encrypts the connection but does not check who answered. It is what DigitalOcean's copied connection details use by default.
• sslmode=verify-full also checks the certificate chain and that the host name matches. For DigitalOcean Standard Edition clusters, download the CA certificate from the cluster's Overview page. For RDS, use AWS's bundle, https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem.
• With libpq 16 or newer, sslrootcert=system trusts the system's CA store instead of a file. DigitalOcean's Advanced Edition clusters need it for verify-full.
• -Fc writes a compressed custom-format archive; -w never prompts for a password, so a cron job fails at once instead of hanging.

RDS for PostgreSQL 15 and newer rejects unencrypted connections by default, because rds.force_ssl is on. See the pg_dump guide for formats and compression.

Dump MySQL over TLS

Keep the connection settings and password in an option file, readable only by its owner:

[client]
user=backup
password="long-random-password"
host=<db-host>
port=25060
ssl-mode=VERIFY_IDENTITY
ssl-ca=/etc/ssl/certs/db-ca.crt

sudo chmod 600 /etc/mysql/managed-backup.cnf

mysqldump --defaults-extra-file=/etc/mysql/managed-backup.cnf --single-transaction --set-gtid-purged=OFF --no-tablespaces --routines --events --triggers mydb | gzip > mydb.sql.gz

• ssl-mode=VERIFY_IDENTITY refuses an unencrypted connection and checks the CA and host name. REQUIRED only encrypts. MySQL's default, PREFERRED, falls back to plain text if TLS fails.
• --set-gtid-purged=OFF: DigitalOcean's import docs say to set it, or the import may fail. Without it, a dump from a server with GTIDs sets sql_log_bin and gtid_purged on the target, which needs privileges a managed admin may not have. Keep those statements only if the restored server will become a replica.
• --no-tablespaces leaves out tablespace statements, so the user needs no PROCESS privilege.
• --single-transaction reads InnoDB tables from one consistent snapshot without locking them.

Restore a dump into a new managed database

Create the new cluster or instance on the same major version or newer, then the empty database. The admin user on a managed service is not a real superuser: DigitalOcean says its managed PostgreSQL doesn't give full superuser access, and the RDS master user is created NOSUPERUSER. So restore without ownership and grant commands:

pg_restore --no-owner --no-privileges -d "host=<new-host> port=25060 dbname=mydb user=doadmin sslmode=require" mydb.dump

--no-owner makes the restoring user own every object, and --no-privileges skips GRANT and REVOKE for roles that may not exist yet. To carry roles over, add --no-role-passwords to pg_dumpall --globals-only. It then reads pg_roles instead of pg_authid, which only superusers can read; set the passwords again afterwards. See backing up PostgreSQL roles.

For MySQL, create the database and load the dump:

mysql --defaults-extra-file=/etc/mysql/new-cluster.cnf -e "CREATE DATABASE mydb"

gunzip < mydb.sql.gz | mysql --defaults-extra-file=/etc/mysql/new-cluster.cnf mydb

DigitalOcean MySQL clusters created after 8 April 2020 require a primary key on every new table. Tables without one fail with error 3750, Unable to create or change a table without a primary key. Add primary keys before you need the restore, or turn the requirement off with a configuration request through DigitalOcean's API.

Schedule it and keep a copy elsewhere

This script dumps to a temporary name, reads the whole archive back to prove it is complete, renames it and keeps seven days. It runs as root, so the password goes in /root/.pgpass.

#!/usr/bin/env bash
set -euo pipefail

CONN="host=<db-host> port=25060 dbname=mydb user=backup sslmode=verify-full sslrootcert=/etc/ssl/certs/db-ca.crt"
BACKUP_DIR="/var/backups/managed-db"
KEEP_DAYS=7
OUT="$BACKUP_DIR/mydb-$(date +%Y-%m-%d_%H%M).dump"

mkdir -p "$BACKUP_DIR"
trap 'rm -f "$OUT.partial"' EXIT

pg_dump "$CONN" -Fc -w -f "$OUT.partial"
pg_restore -f /dev/null "$OUT.partial"
mv "$OUT.partial" "$OUT"

find "$BACKUP_DIR" -name 'mydb-*.dump' -type f -mtime +"$KEEP_DAYS" -delete

30 2 * * * root /usr/local/bin/managed-pg-backup.sh >> /var/log/managed-pg-backup.log 2>&1

Make the script executable with chmod 755. Then copy /var/backups/managed-db to storage in a different account or provider, for example with rclone. Once a month, restore a dump into a scratch database and compare row counts, as in testing a restore.

Common errors

Frequently asked questions

Are managed database backups enough?

They cover hardware failure and recent mistakes. They live in the same account, go away with the database on DigitalOcean (and on RDS unless you retain them), and reach back 7 days on DigitalOcean or up to 35 on RDS. Add your own dumps stored elsewhere.

What happens to RDS backups when I delete the DB instance?

Automated backups are deleted unless you choose to retain them, and retained ones still expire with their retention period. Final and manual snapshots stay until you delete them.

Can I run pg_dump through PgBouncer?

Not in transaction mode; DigitalOcean's docs say it causes errors. Connect to the cluster directly.

Does restoring a managed database backup overwrite the existing one?

No. On DigitalOcean and RDS a restore creates a new cluster or instance, and you point your app at it.