How to back up a database running in Docker Compose

Before you start

Run the commands from the directory that holds your Compose file, or add -f /path/to/compose.yaml after docker compose. db is the service name from that file, not the container name, and the service must be running. The examples assume a service like this:

services:
  db:
    image: postgres:17
    environment:
      POSTGRES_USER: shop
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: shop
    volumes:
      - db-data:/var/lib/postgresql/data

volumes:
  db-data:

Compose fills in ${POSTGRES_PASSWORD} from the .env file next to the Compose file, so the password stays out of the file you commit.

Why -T matters

docker compose exec allocates a pseudo-terminal (TTY) by default, because it is built for typing into a shell. -T (long form --no-tty) turns it off. Use it whenever you pipe or redirect:

• A TTY merges the command's error output into its normal output, so a warning can end up inside the dump.
• A terminal turns line endings into \r\n on the way out, which corrupts the dump.
• Under cron or in CI there is no terminal at all, and the command fails with the input device is not a TTY.

Standard input stays open without a TTY, so piping a dump back in for a restore needs nothing extra.

Read credentials from the container

The official database images take their user, password and database name from environment variables, and those stay set in the running container. Wrap the command in sh -c '...' with single quotes: your host shell leaves $POSTGRES_USER alone, and the shell inside the container expands it. Your script then holds no passwords of its own. Check what a service has set:

docker compose exec db printenv POSTGRES_USER POSTGRES_DB

The postgres image trusts connections made from inside its container, so pg_dump needs no password there. The password variables only take effect when the image first sets up an empty volume. If someone changed a password since, the variable is out of date.

Back up PostgreSQL

docker compose exec -T db sh -c 'pg_dump -U "$POSTGRES_USER" -d "$POSTGRES_DB"' | gzip > shop-$(date +%F).sql.gz

-U is the database user and -d the database. pg_dump writes plain SQL to standard output, the pipe carries it out of the container, and gzip compresses it on the host. pg_dump reads a consistent snapshot without blocking reads or writes.

For the compressed custom format, use pg_dump -Fc and leave out gzip. Restore that with pg_restore, which reads standard input when no file is given. All the options are in the pg_dump guide.

Back up MySQL or MariaDB

docker compose exec -T db sh -c 'mysqldump --single-transaction --routines --events -uroot -p"$MYSQL_ROOT_PASSWORD" "$MYSQL_DATABASE"' | gzip > shop-$(date +%F).sql.gz

• --single-transaction dumps InnoDB tables from one consistent snapshot without locking them.
• --routines and --events add stored procedures, functions and scheduled events, which mysqldump leaves out by default. Triggers are included by default.
• -uroot -p"$MYSQL_ROOT_PASSWORD" logs in as root. There is no space after -p; with a space, mysqldump prompts for a password and reads the next word as the database name.
• To dump every database, including the mysql system database with its users, replace the database name with --all-databases.

mysqldump prints Using a password on the command line interface can be insecure as a warning on standard error. With -T it lands in your terminal or log, not in the dump. More options are in the mysqldump guide.

The MariaDB image names its tools mariadb-dump and mariadb, and usually sets MARIADB_ROOT_PASSWORD:

docker compose exec -T db sh -c 'mariadb-dump --single-transaction --routines --events -uroot -p"$MARIADB_ROOT_PASSWORD" "$MARIADB_DATABASE"' | gzip > shop-$(date +%F).sql.gz

Back up MongoDB

docker compose exec -T mongo sh -c 'mongodump --archive --gzip -u "$MONGO_INITDB_ROOT_USERNAME" -p "$MONGO_INITDB_ROOT_PASSWORD" --authenticationDatabase admin' > shop-$(date +%F).archive.gz

• --archive with no file name writes a single archive to standard output.
• --gzip compresses it inside mongodump, so there is no gzip on the host side.
• -u, -p and --authenticationDatabase admin log in as the root user the image created in the admin database.
• Add --db shop to dump one database instead of all of them.

Without a replica set, mongodump reads one collection after another, so writes during the dump can leave collections slightly out of step. See the mongodump guide.

Restore

Restore into an empty database. On a new server, or after losing the volume, start only the database service:

docker compose up -d db

On an empty volume the image first creates the user and database, using a temporary server. Wait until the log shows init process complete (PostgreSQL, MongoDB) or init process done (MySQL, MariaDB). A restore piped in earlier is cut off when that temporary server stops. -f follows the log; press Ctrl+C to stop watching:

docker compose logs -f db

Then pipe the dump in. PostgreSQL:

gunzip -c shop-2026-10-03.sql.gz | docker compose exec -T db sh -c 'psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB"'

MySQL:

gunzip -c shop-2026-10-03.sql.gz | docker compose exec -T db sh -c 'mysql -uroot -p"$MYSQL_ROOT_PASSWORD" "$MYSQL_DATABASE"'

MongoDB:

docker compose exec -T mongo sh -c 'mongorestore --archive --gzip --drop -u "$MONGO_INITDB_ROOT_USERNAME" -p "$MONGO_INITDB_ROOT_PASSWORD" --authenticationDatabase admin' < shop-2026-10-03.archive.gz

-v ON_ERROR_STOP=1 makes psql stop at the first error instead of carrying on; mysql stops at the first error by default. --drop drops each collection before restoring it and leaves collections that are not in the backup alone.

Check the dump

A dump that stopped early still looks like a file. gzip -t tests the compressed file, and a complete SQL dump ends with a footer line:

gzip -t shop-2026-10-03.sql.gz

gunzip -c shop-2026-10-03.sql.gz | grep -E 'PostgreSQL database dump complete|Dump completed on'

No output from the second command means the dump is incomplete. The real test is a restore. For PostgreSQL, load the dump into a scratch database next to the real one and count the rows of a table you know:

docker compose exec -T db sh -c 'createdb -U "$POSTGRES_USER" restore_test'

gunzip -c shop-2026-10-03.sql.gz | docker compose exec -T db sh -c 'psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d restore_test'

docker compose exec -T db sh -c 'psql -U "$POSTGRES_USER" -d restore_test -Atc "select count(*) from orders"'

-Atc runs one query and prints just the result. Remove the scratch database with dropdb the same way. For MySQL, run CREATE DATABASE restore_test and pipe the dump into mysql with restore_test as the database. Testing restores covers making this a routine.

Use the client inside the container

Running pg_dump or mysqldump from the host means installing a client and keeping its version in step with the server. pg_dump refuses to dump a server newer than itself, and a dump from a newer pg_dump can fail to load into an older server. The tools inside the database image match the server, so the problem never comes up.

To move to a new major version, dump with the old container, start the new image on an empty volume, and restore into it. A new major version of PostgreSQL cannot start on the old version's data files.

Run it nightly with cron

This script dumps to a temporary name, tests the file, renames it, and deletes dumps older than 14 days:

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

cd /srv/shop
BACKUP_DIR="/var/backups/shop"
KEEP_DAYS=14
OUT="$BACKUP_DIR/shop-$(date +%Y-%m-%d_%H%M).sql.gz"

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

docker compose exec -T db sh -c 'pg_dump -U "$POSTGRES_USER" -d "$POSTGRES_DB"' | gzip > "$OUT.partial"
gzip -t "$OUT.partial"
mv "$OUT.partial" "$OUT"

find "$BACKUP_DIR" -name 'shop-*.sql.gz' -type f -mtime +"$KEEP_DAYS" -delete

sudo chmod 755 /usr/local/bin/shop-db-backup.sh

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

set -o pipefail matters: a pipe reports the exit status of its last command, so without it a failed pg_dump still exits 0 through gzip. cd /srv/shop matters too: cron starts elsewhere, and Compose finds the project, its .env file and its name from the working directory. -mtime +14 keeps about two weeks of dumps.

More on schedules and logs in the cron guide. The dumps share a disk with the database, so copy them off the server too, for example with rclone.

Or keep the job in the Compose file

Some setups add a backup service to the Compose file instead. It uses the database's image, so the client version matches, connects to db over the project network, and writes to a bind-mounted host directory. With a profiles: entry, docker compose up leaves it alone and docker compose run --rm backup runs it on demand from host cron. Over the network it needs the password, so pass it in as a secret.

A long-running backup container with its own scheduler also works, but it is one more service to monitor, and its failures are easy to miss.

Common errors

Frequently asked questions

Why does docker compose exec need -T for backups?

Without it, Compose attaches a terminal. A terminal merges error output into the dump, can change line endings, and does not exist under cron, where the command fails with "the input device is not a TTY".

Can I back up the database volume instead of dumping it?

Only with the database stopped, and the copy only starts on the same major version. A dump runs while the database stays online and loads into newer versions.

How do I back up all databases in a MySQL container?

Replace the database name with --all-databases in the mysqldump command. That includes the mysql system database with its users and privileges.

Does pg_dump lock the database when it runs in Docker?

No more than anywhere else. pg_dump reads a consistent snapshot without blocking reads or writes; it only makes schema changes wait. Running it through docker compose exec changes nothing about that.