How to restore postgres within a docker?

Question

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?

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 — hurturk
– hurturk, Commented Mar 24, 2017 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
– user706838, Commented Mar 24, 2017 at 17:29
would gunzip < backup.tar.gz | pg_restore -F t .. help for your case? — hurturk
– hurturk, Commented Mar 24, 2017 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
– user706838, Commented Mar 24, 2017 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
– hurturk, Commented Mar 24, 2017 at 17:47

hurturk · Accepted Answer · 2017-12-04 09:09:10Z

9

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.

edited Dec 4, 2017 at 9:09

answered Mar 24, 2017 at 19:08

hurturk

5,45427 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nam G VU Over a year ago

Lovely docker expertise!

Tsar Bomba Over a year ago

@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?

Suraj Rao · Accepted Answer · 2018-11-19 12:02:12Z

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

Create some folder in your root
```
mkdir -p '/myfolder/bdbackup'
```

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

Back up will be stored in the following folder /myfolder/bdbackup
you can kill the container and stop the container any time but data will be stored in the host.

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;

you can see the data has restored...

Collectives™ on Stack Overflow

How to restore postgres within a docker?

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related