Skip to content

Instantly share code, notes, and snippets.

@gilyes
Last active October 8, 2024 11:59
Show Gist options
  • Save gilyes/525cc0f471aafae18c3857c27519fc4b to your computer and use it in GitHub Desktop.
Save gilyes/525cc0f471aafae18c3857c27519fc4b to your computer and use it in GitHub Desktop.
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
@karimkawambwa
Copy link

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
Copy link

rluts commented Jun 14, 2019

can i load data in dockerfile?

@Walt-H
Copy link

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
Copy link

marcegl commented Jul 22, 2019

for pg_restore

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

@eduardorr97
Copy link

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
Copy link

Thx for a restore command :)

@RobertHeim
Copy link

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

@YakhyaevRasul
Copy link

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
Copy link

@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
Copy link

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

@hnakao
Copy link

hnakao commented Dec 2, 2022

for pg_restore

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

thanks!! You save my day!

Copy link

ghost commented Dec 15, 2023

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

thanks

@zkenstein
Copy link

cat your_dump.sql | docker exec -i {docker-postgres-container} psql -U {user} -d {database_name} try this but got Internal server error while access the query page

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