How to backup a PostgreSQL database using Docker

Docker backup PostgreSQL

Before you begin

In this tutorial, we’ll learn how to backup a PostgreSQL database. A Linux machine and Docker will be required to follow this tutorial.

Backup a PostgreSQL local or remote database

Command to backup a local or remote PostgreSQL database using Docker:

$ docker run -i postgres /usr/bin/pg_dump \
  -h [POSTGRESQL_HOST] \
  -U [POSTGRESQL_USER] [POSTGRESQL_DATABASE] > backup.sql

Command to backup multiple PostgreSQL databases using Docker:

$ docker run -i postgres /usr/bin/pg_dumpall \
  -h [POSTGRESQL_HOST] \
  -U [POSTGRESQL_USER] > backup.sql

Command to backup a local or remote PostgreSQL database using Docker with compression (using gzip):

$ docker run -i postgres /usr/bin/pg_dump \
  -h [POSTGRESQL_HOST] \
  -U [POSTGRESQL_USER] [POSTGRESQL_DATABASE] | gzip -9 > backup.sql.gz

Same command below but providing PostgreSQL password as environment variable:

$ docker run -i -e PGPASSWORD=[POSTGRESQL_PASSWORD] postgres /usr/bin/pg_dump \
  -h [POSTGRESQL_HOST] \
  -U [POSTGRESQL_USER] [POSTGRESQL_DATABASE] | gzip -9 > backup.sql.gz

Backup a containerized PostgreSQL database

Command to backup a containerized PostgreSQL database creating a compressed file using Docker and gzip:

$ docker exec [POSTGRESQL_CONTAINER] /usr/bin/pg_dump \
  -U [POSTGRESQL_USER] [POSTGRESQL_DATABASE] | gzip -9 > backup.sql.gz

Same command below but setting PostgreSQL password environment variable to existing container:

$ docker exec [POSTGRESQL_CONTAINER] /bin/bash \
  -c "export PGPASSWORD=[POSTGRESQL_PASSWORD] \
      && /usr/bin/pg_dump -U [POSTGRESQL_USER] [POSTGRESQL_DATABASE]" \
  | gzip -9 > backup.sql.gz

Bonus track: How to dump a portion of a table?

Using Postgres functions you can dump all databases, a database, a schema, only schema data or even a single table dataset. These are some of the available options:

pg_dumpall: Retrieves all databases.

pg_dump: Retrieves specific database.

pg_dump --schema-only DATABASE_NAME: Retrieves only schema/structure of a database.

pg_dump --table TABLE_NAME: Dumps the content of the table TABLE_A.

COPY: Retrieves data from a table and outputs it to a file or stdout. E.g.:

$ docker exec -i [POSTGRESQL_CONTAINER] /usr/bin/psql -U \
  [POSTGRESQL_USER] [POSTGRESQL_DATABASE] \
  -c "COPY (SELECT * FROM [TABLE_NAME] order by time desc limit 1000)
  TO 'dest/folder/filename.txt';"

In previous example we are retrieving last 1000 records from a table TABLE_NAME sorted by time and saving it to a text file dest/folder/filename.txt.

Lately this data file can be restored using the same command COPY as the following example does:

$ docker exec -i [POSTGRESQL_CONTAINER] /usr/bin/psql -U \
  [POSTGRESQL_USER] [POSTGRESQL_DATABASE] \
  -c "COPY [TABLE_NAME] from 'dest/folder/filename.txt' WITH (FORMAT text);"