[database] Backup/Restore a dockerized PostgreSQL database

I'm trying to backup/restore a PostgreSQL database as is explained on the Docker website, but the data is not restored.

The volumes used by the database image are:

VOLUME  ["/etc/postgresql", "/var/log/postgresql", "/var/lib/postgresql"]

and the CMD is:

CMD ["/usr/lib/postgresql/9.3/bin/postgres", "-D", "/var/lib/postgresql/9.3/main", "-c", "config_file=/etc/postgresql/9.3/main/postgresql.conf"]

I create the DB container with this command:

docker run -it --name "$DB_CONTAINER_NAME" -d "$DB_IMAGE_NAME"

Then I connect another container to insert some data manually:

docker run -it --rm --link "$DB_CONTAINER_NAME":db "$DB_IMAGE_NAME" sh -c 'exec bash'
psql -d test -h $DB_PORT_5432_TCP_ADDR
# insert some data in the db
<CTRL-D>
<CTRL-D>

The tar archive is then created:

$ sudo docker run --volumes-from "$DB_CONTAINER_NAME" --rm -v $(pwd):/backup ubuntu tar cvf /backup/backup.tar /etc/postgresql /var/log/postgresql /var/lib/postgresql

Now I remove the container used for the db and create another one, with the same name, and try to restore the data inserted before:

$ sudo docker run --volumes-from "$DB_CONTAINER_NAME" --rm -v $(pwd):/backup ubuntu tar xvf /backup/backup.tar 

But the tables are empty, why is the data not properly restored ?

This question is related to database postgresql backup docker

The answer is


dksnap (https://github.com/kelda/dksnap) automates the process of running pg_dumpall and loading the dump via /docker-entrypoint-initdb.d.

It shows you a list of running containers, and you pick which one you want to backup. The resulting artifact is a regular Docker image, so you can then docker run it, or share it by pushing it to a Docker registry.

(disclaimer: I'm a maintainer on the project)


I had this issue while trying to use a db_dump to restore a db. I normally use dbeaver to restore- however received a psql dump, so had to figure out a method to restore using the docker container.

The methodology recommended by Forth and edited by Soviut worked for me:

cat your_dump.sql | docker exec -i your-db-container psql -U postgres -d dbname

(since this was a single db dump and not multiple db's i included the name)

However, in order to get this to work, I had to also go into the virtualenv that the docker container and project were in. This eluded me for a bit before figuring it out- as I was receiving the following docker error.

read unix @->/var/run/docker.sock: read: connection reset by peer

This can be caused by the file /var/lib/docker/network/files/local-kv.db .I don't know the accuracy of this statement: but I believe I was seeing this as I do not user docker locally, so therefore did not have this file, which it was looking for, using Forth's answer.

I then navigated to correct directory (with the project) activated the virtualenv and then ran the accepted answer. Boom, worked like a top. Hope this helps someone else out there!


Backup Database

generate sql:

  • docker exec -t your-db-container pg_dumpall -c -U your-db-user > dump_$(date +%Y-%m-%d_%H_%M_%S).sql

to reduce the size of the sql you can generate a compress:

  • docker exec -t your-db-container pg_dumpall -c -U your-db-user | gzip > ./dump_$(date +"%Y-%m-%d_%H_%M_%S").gz

Restore Database

  • cat your_dump.sql | docker exec -i your-db-container psql -U your-db-user -d your-db-name

to restore a compressed sql:

  • gunzip < your_dump.sql.gz | docker exec -i your-db-container psql -U your-db-user -d your-db-name

PD: this is a compilation of what worked for me, and what I got from here and elsewhere. I am beginning to make contributions, any feedback will be appreciated.


I think you can also use a postgres backup container which would backup your databases within a given time duration.

  pgbackups:
    container_name: Backup
    image: prodrigestivill/postgres-backup-local
    restart: always
    volumes:
      - ./backup:/backups
    links:
      - db:db
    depends_on:
      - db
    environment:
      - POSTGRES_HOST=db
      - POSTGRES_DB=${DB_NAME} 
      - POSTGRES_USER=${DB_USER}
      - POSTGRES_PASSWORD=${DB_PASSWORD}
      - POSTGRES_EXTRA_OPTS=-Z9 --schema=public --blobs
      - SCHEDULE=@every 0h30m00s
      - BACKUP_KEEP_DAYS=7
      - BACKUP_KEEP_WEEKS=4
      - BACKUP_KEEP_MONTHS=6
      - HEALTHCHECK_PORT=81

Okay, I've figured this out. Postgresql does not detect changes to the folder /var/lib/postgresql once it's launched, at least not the kind of changes I want it do detect.

The first solution is to start a container with bash instead of starting the postgres server directly, restore the data, and then start the server manually.

The second solution is to use a data container. I didn't get the point of it before, now I do. This data container allows to restore the data before starting the postgres container. Thus, when the postgres server starts, the data are already there.


This is the command worked for me.

cat your_dump.sql | sudo docker exec -i {docker-postgres-container} psql -U {user} -d {database_name}

for example

cat table_backup.sql | docker exec -i 03b366004090 psql -U postgres -d postgres

Reference: Solution given by GMartinez-Sisti in this discussion. https://gist.github.com/gilyes/525cc0f471aafae18c3857c27519fc4b


Another approach (based on docker-postgresql-workflow)

Local running database (not in docker, but same approach would work) to export:

pg_dump -F c -h localhost mydb -U postgres export.dmp

Container database to import:

docker run -d -v /local/path/to/postgres:/var/lib/postgresql/data postgres #ex runs container as `CONTAINERNAME` #find via `docker ps`
docker run -it --link CONTAINERNAME:postgres  --volume $PWD/:/tmp/  postgres  bash -c 'exec pg_restore -h postgres -U postgres -d mydb -F c /tmp/sonar.dmp'

The below command can be used to take dump from docker postgress container

docker exec -t <postgres-container-name> pg_dump --no-owner -U <db-username> <db-name> > file-name-to-backup-to.sql

cat db.dump | docker exec ... way didn't work for my dump (~2Gb). It took few hours and ended up with out-of-memory error.

Instead, I cp'ed dump into container and pg_restore'ed it from within.

Assuming that container id is CONTAINER_ID and db name is DB_NAME:

# copy dump into container
docker cp local/path/to/db.dump CONTAINER_ID:/db.dump

# shell into container
docker exec -it CONTAINER_ID bash

# restore it from within
pg_restore -U postgres -d DB_NAME --no-owner -1 /db.dump

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to postgresql

Subtracting 1 day from a timestamp date pgadmin4 : postgresql application server could not be contacted. Psql could not connect to server: No such file or directory, 5432 error? How to persist data in a dockerized postgres database using volumes input file appears to be a text format dump. Please use psql Postgres: check if array field contains value? Add timestamp column with default NOW() for new rows only Can't connect to Postgresql on port 5432 How to insert current datetime in postgresql insert query Connecting to Postgresql in a docker container from outside

Examples related to backup

input file appears to be a text format dump. Please use psql How can I backup a Docker-container with its data-volumes? Backup/Restore a dockerized PostgreSQL database Export MySQL database using PHP only Tar a directory, but don't store full absolute paths in the archive How to extract or unpack an .ab file (Android Backup file) mysqldump with create database line Postgresql 9.2 pg_dump version mismatch How to backup Sql Database Programmatically in C# Opening a SQL Server .bak file (Not restoring!)

Examples related to docker

standard_init_linux.go:190: exec user process caused "no such file or directory" - Docker What is the point of WORKDIR on Dockerfile? E: gnupg, gnupg2 and gnupg1 do not seem to be installed, but one of them is required for this operation How do I add a user when I'm using Alpine as a base image? docker: Error response from daemon: Get https://registry-1.docker.io/v2/: Service Unavailable. IN DOCKER , MAC How to fix docker: Got permission denied issue pull access denied repository does not exist or may require docker login Docker error: invalid reference format: repository name must be lowercase Docker: "no matching manifest for windows/amd64 in the manifest list entries" OCI runtime exec failed: exec failed: (...) executable file not found in $PATH": unknown