Backup/Restore a dockerized PostgreSQL database

Question

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 ?

Answer 1

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!

Answer 2

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

Answer 3

I think you can also use a potgres 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

Answer 4

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.

Answer 5

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'