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  lt CTRL-D gt   lt CTRL-D gt    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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  - gt  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

User · Answer

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

User · Answer

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

User · Answer

Backup Database generate sql   docker exec -t your-db-container pg dumpall -c -U your-db-user  gt  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  gt    dump   date   quot  Y- m- d  H  M  S quot   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  lt  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

User · Answer

Backup your databases  docker exec -t your-db-container pg dumpall -c -U postgres  gt  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

User · Answer

The below command can be used to take dump from docker postgress container  docker exec -t  lt postgres-container-name gt  pg dump --no-owner -U  lt db-username gt   lt db-name gt   gt  file-name-to-backup-to sql

[database] Backup/Restore a dockerized PostgreSQL database

Examples related to database

Examples related to postgresql

Examples related to backup

Examples related to docker