Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Backup/restore postgres in docker container
Backup:
docker exec -t -u postgres your-db-container pg_dumpall -c > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql
Restore:
cat your_dump.sql | docker exec -i your-db-container psql -Upostgres
@tlvenn

This comment has been minimized.

Copy link

@tlvenn tlvenn commented Apr 5, 2017

As explained in this article: http://durandom.de/docker/postgres/2016/12/20/pg_dump/ you should not pass -t to docker exec to backup your DB.

@EvAlex

This comment has been minimized.

Copy link

@EvAlex EvAlex commented Jun 3, 2017

Missing whitespace in restore command between "-U" and "postgres"

@ChunAllen

This comment has been minimized.

Copy link

@ChunAllen ChunAllen commented May 14, 2018

To restore your dump file with specified user and database_name

cat your_dump.sql | docker exec -i {docker-postgres-container} psql -U {user} -d {database_name}

@hackaprende

This comment has been minimized.

Copy link

@hackaprende hackaprende commented Jul 31, 2018

I am getting a lot of these when I try to restore:
ERROR: constraint "something" for relation "something" already exists

@romulo-soares

This comment has been minimized.

Copy link

@romulo-soares romulo-soares commented Aug 15, 2018

Ao fazer isso:
cat your_dump.sql | docker exec -i {docker-postgres-container} psql -U {user} -d {database_name}

Obtive o seguinte erro:
Got permission denied while trying to connect to the Docker daemon socket at unix:///var/run/docker.sock: Post http://%2Fvar%2Frun%2Fdocker.sock/v1.26/containers/banco/exec: dial unix /var/run/docker.sock: connect: permission denied

@GMartinez-Sisti

This comment has been minimized.

Copy link

@GMartinez-Sisti GMartinez-Sisti commented Sep 4, 2018

@romulo-soares You need to use sudo before docker if you are not root (tens de usar sudo se não estás logado com root):

cat your_dump.sql | sudo docker exec -i {docker-postgres-container} psql -U {user} -d {database_name}

@Zauxst

This comment has been minimized.

Copy link

@Zauxst Zauxst commented Oct 23, 2018

@romulo-soares You need to use sudo before docker if you are not root (tens de usar sudo se não estás logado com root):

cat your_dump.sql | sudo docker exec -i {docker-postgres-container} psql -U {user} -d {database_name}

Hello, you can use the user that has permissions to run docker commands and not root.
It is recommended to run docker as a different user then root.

@oleksand

This comment has been minimized.

Copy link

@oleksand oleksand commented Jan 4, 2019

if you provided a label (i.e. foo=bar) you can also use the following (useful for autobackup in a cron script):
docker exec $(docker ps --filter label=foo=bar | awk '{print $11}') pg_dumpall -c -U postgres > test_dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql

@marcelobbfonseca

This comment has been minimized.

Copy link

@marcelobbfonseca marcelobbfonseca commented Apr 8, 2019

im getting

invalid command \":
ERROR:  syntax error at or near "OCI"
OCI runtime exec failed: exec failed: container_linux.go:344: starting container process caused "exec: \"-c\": executable file not found in $PATH": unknown

when trying to run cat dump.sql | docker exec -i project_db_1 psql -U postgres -d project_dev

@karimkawambwa

This comment has been minimized.

Copy link

@karimkawambwa karimkawambwa commented May 25, 2019

To restore your dump file with specified user and database_name

cat your_dump.sql | docker exec -i {docker-postgres-container} psql -U {user} -d {database_name}

This is pretty helpful

@rluts

This comment has been minimized.

Copy link

@rluts rluts commented Jun 14, 2019

can i load data in dockerfile?

@Walt-H

This comment has been minimized.

Copy link

@Walt-H Walt-H commented Jun 25, 2019

can i load data in dockerfile?

@rluts When creating dockerized containers, there are typically intermediate containers that do not persist any data into the next step of building. See this Stack Overflow question and accepted answer for an example.

With that said, after building your image, what you can do is import the data using the method proposed by @ChunAllen:
cat your_dump.sql | docker exec -i {docker-postgres-container} psql -U {user} -d {database_name}

After importation, create an image of that container, which will have a copy of your data.
You can then distribute this image, upload it to ECR, include it in a docker-compose, etc.

I was able to accomplish this using a dump file that was ~85 Gb's :)

@marcegl

This comment has been minimized.

Copy link

@marcegl marcegl commented Jul 22, 2019

for pg_restore

cat file | docker exec -i {docker-postgres} pg_restore -U {user} -d {database_name}

@eduardorr97

This comment has been minimized.

Copy link

@eduardorr97 eduardorr97 commented Feb 11, 2020

When I do

docker-compose exec -T db pg_restore --verbose --clean --no-acl --no-owner -U postgres -d engine < latest.dump

all I get is pg_restore: [archiver] input file is too short (read 0, expected 5)

Can someone help me, I new to docker-compose.

I know the engine db exists and its container is running, I can connect to it, and latest.dump also exists, is in the same folder Im running the command

@Shtangatopor

This comment has been minimized.

Copy link

@Shtangatopor Shtangatopor commented Jul 14, 2020

Thx for a restore command :)

@RobertHeim

This comment has been minimized.

Copy link

@RobertHeim RobertHeim commented Nov 17, 2020

@eduardorr97: I think you need to place the file within the container (e.g. via a volume).

@YakhyaevRasul

This comment has been minimized.

Copy link

@YakhyaevRasul YakhyaevRasul commented Nov 18, 2020

To restore your dump file with specified user and database_name

cat your_dump.sql | docker exec -i {docker-postgres-container} psql -U {user} -d {database_name}

this problem:
invalid command \":
ERROR: syntax error at or near "OCI"
LINE 1: OCI runtime exec failed: exec failed: container_linux.go:349...

@eduardorr97

This comment has been minimized.

Copy link

@eduardorr97 eduardorr97 commented Nov 18, 2020

@eduardorr97: I think you need to place the file within the container (e.g. via a volume).

Hey thanks for the reply, I actually remember solving this by using a different command than pg_restore, but I don't remember which command is was, this was long ago...

@RobertHeim

This comment has been minimized.

Copy link

@RobertHeim RobertHeim commented Nov 19, 2020

@eduadorr97: actually it would also be possible to keep the *.dump file outside of the container, but it is important that the container is actually running.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment