Categories
backup database docker postgresql

Backup/Restore a dockerized PostgreSQL database

326

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 ?

1

834

Backup your databases

docker exec -t your-db-container pg_dumpall -c -U postgres > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql

Restore your databases

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

15

  • 4

    Yep, that’s the postgres way to do it, but I think the docker way should always be prefered when you use it

    Apr 29, 2015 at 9:31

  • 80

    To save some space on disk you might want to pipe the dump to gzip: docker exec -t your-db-container pg_dumpall -c -U postgres | gzip > /var/data/postgres/backups/dump_date +%d-%m-%Y”_”%H_%M_%S.gz

    – Tarion

    Oct 24, 2016 at 16:08

  • 3

    Just unzip the data before you restore it. To do it as a one liner you will have to replace the cat your_dump.sql with the unzip command and pipe that instead of the cat result to docker exec.

    – Tarion

    May 4, 2017 at 18:11

  • 17

    For those would couldn’t figure out how to get the date formatting working: docker exec -t your-db-container pg_dumpall -c -U postgres | gzip > ./tmp/dump_$(date +"%Y-%m-%d_%H_%M_%S").gz

    – 9_Dave_9

    May 16, 2020 at 11:27


  • 20

    When restoring the database, make sure you add -d your-db-name to the restore command if your database isn’t named postgres.

    – J86

    Oct 26, 2020 at 22:15


79

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.

1

  • using “cat your_dump.sql | …. ” to restore a db I think has a really low performance, am I wrong?

    Jan 9 at 15:12


75

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
      - [email protected] 0h30m00s
      - BACKUP_KEEP_DAYS=7
      - BACKUP_KEEP_WEEKS=4
      - BACKUP_KEEP_MONTHS=6
      - HEALTHCHECK_PORT=81

8

  • How exaclty do you run this command? new to docker etc

    – Jack B

    Feb 4, 2021 at 3:56

  • This isn’t a command @JackB, it’s a docker-compose file. docs.docker.com/compose

    – Pero P.

    Feb 6, 2021 at 21:47


  • 1

    Does this actually work without downtime? How does the container deal with changes that occur while it’s creating the backup?

    – Chris1309

    Mar 30, 2021 at 16:37

  • 2

    Incredibly useful for people who want to automatically backup their DB at regular intervals.

    – emilaz

    Nov 9, 2021 at 14:07

  • 1

    How to restore the backup?

    Jul 24 at 20:55