5

I create backups like this: docker exec DOCKER pg_dump -U USER -F t DB | gzip > ./FILE.tar.gz

What's the best way to restore the database given that the database runs within a container?

hurturk
  • 5,214
  • 24
  • 41
user706838
  • 5,132
  • 14
  • 54
  • 78
  • well, you can point a volume containing the backup at first run and then call `pg_restore`, but that doesn't have to go through docker way so you can do it [remotely](http://stackoverflow.com/a/26378243/1233686) – hurturk Mar 24 '17 at 17:24
  • cool, but as far as I understand, my problem is that I cannot get the unpacking of the compressed file right. Any ideas? – user706838 Mar 24 '17 at 17:29
  • would `gunzip < backup.tar.gz | pg_restore -F t ..` help for your case? – hurturk Mar 24 '17 at 17:43
  • I think we're very close but something is missing. the above command just prints out the (uncompressed) content of the file. – user706838 Mar 24 '17 at 17:43
  • just to be more explicit `gunzip < backup.tar.gz | pg_restore -U USER -F t DB`. Other than the output, have you connect and check if any data has written? – hurturk Mar 24 '17 at 17:47
  • cool, it works but I had to add `-d` before `DB`. – user706838 Mar 24 '17 at 18:16

2 Answers2

8

For your case:

docker exec -it <CONTAINER> gunzip < backup.tar.gz | pg_restore -U <USER> -F t -d <DB>

Remote restore is also available if your container is public facing and remote connections are allowed in pg_hba.conf for postresql:

gunzip < backup.tar.gz | pg_restore -U <USER> -F t -d <DB> -h <HOST_IP> -p 5432

As a rule of thumb, it is good idea to document your backup and restore commands specific to the project.

hurturk
  • 5,214
  • 24
  • 41
  • Lovely docker expertise! – Nam G VU Feb 17 '19 at 03:22
  • @hurturk Oddly, I can get the backup the OP posted to work just fine: docker exec postgres-local-15 pg_dump -U postgres -F t dvdrental | gzip > /Users/me/Downloads/dvdrental.tar.gz But I get "zsh: command not found: pg_restore" when trying either of your methods to restore, like so: docker exec postgres-local-15 gunzip < /Users/me/Downloads/dvdrental.tar | pg_restore -U postgres -F t -d dvdrental -or- gunzip < /Users/me/Downloads/dvdrental.tar | pg_restore -U postgres -F t -d dvdrental -h 127.0.0.1 -p 5432 Could the "zsh" mean that it's looking on my local for pg_restore? – Tsar Bomba Jul 15 '23 at 15:09
0

How take backup of the data which is existing in the running PostgreSQL container

  1. Create some folder in your root

    mkdir -p '/myfolder/bdbackup'

  2. download the postgres image which you are using and execute the following command

    docker run --name demo1 -e POSTGRES_PASSWORD=passowrd -v /myfolder/bdbackup:/var/lib/postgresql/data -d postgres 

docker exec -it demo1 psql -U postgres

  3. Back up will be stored in the following folder /myfolder/bdbackup

  4. you can kill the container and stop the container any time but data will be stored in the host.

  5. and once again re-run the postgres the container with same command

    docker run --name demo2 -e POSTGRES_PASSWORD=passowrd -v /myfolder/bdbackup:/var/lib/postgresql/data -d postgres 

docker exec -it demo1 psql -U postgres

    and execute following query select * from emp;

  6. you can see the data has restored...

Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
Deeraj V
  • 1
  • 1