CODEWITHSUNDEEP
postgresqldockercontainers
Cesar Augusto Nogueira
Cesar Augusto Nogueira

Reputation: 2221

How to generate a Postgresql Dump from a Docker container?

I would like to have a way to enter into the Postgresql container and get a data dump from it.

Upvotes: 89

Views: 129081

Answers (11)

chacha kairu
chacha kairu

Reputation: 151

At-least this's what worked for me

docker exec "container name/id" pg_dump -U "DB username" -d "db_name" > exported_file_name.sql
ie: docker exec 9cddbbf7c279 pg_dump -U odoo16 -d SeattleMobi > dump.sql

Upvotes: 0

Axel
Axel

Reputation: 151

I use this script:

docker exec postgres-1 pg_dump -U postgres --column-inserts --data-only  postgres > inserts.sql

Upvotes: 0

Ders
Ders

Reputation: 1762

See the PostgreSQL Documentation for Backup and Restore. As others have described, use docker exec to run commands on containers, in this case either pg_dump or pg_dumpall to create dump files. Write them to a docker volume to prevent increasing the container size and provide access to the dump from outside the container.

TLDR

docker exec <container_name> pg_dump [-U db_user] -f [filepath] <db>

e.g.

docker exec db pg_dump -U admin -f /db-backups/db.pg_dump.bak nextcloud

Explanation

Although output redirection is ubiquitous throughout the documentation, you will have trouble with it when using docker exec.

docker exec db pg_dump db > db.pg_dump.bak

What you want to happen

docker exec db (pg_dump db > db.pg_dump.bak)

What is actually happening

(docker exec db pg_dump db) > db.pg_dump.bak

I had trouble trying to use shell quoting to fix this, maybe because of how it is treated in the container. Fortunately, we don't have to use output redirection at all. The man page for pg_dump documents a -f option that takes the destination instead. This avoids the problem entirely.

Upvotes: 2

johansenj
johansenj

Reputation: 71

This will mount the pwd and include your environment variables

docker run -it --rm \
--env-file <(env) \
-w /working \
--volume $(pwd):/working \
postgres:latest /usr/bin/pg_dump -Fc -h localhost -U postgres MY_DB_NAME" > /working/db-$(date +%d-%m-%y).backup

Upvotes: 2

Cesar Augusto Nogueira
Cesar Augusto Nogueira

Reputation: 2221

Use the following command from a UNIX or a Windows terminal:

docker exec <container_name> pg_dump <schema_name> > backup

The following command will dump only inserts from all tables:

docker exec <container_name> pg_dump --column-inserts --data-only  <schema_name> > inserts.sql

Upvotes: 101

Digao
Digao

Reputation: 560

for those who suffered with permissions, I used this following command with success to perform my dump:

docker exec -i MY_CONTAINER_NAME /bin/bash -c "PGPASSWORD=MY_PASSWORD pg_dump -Fc -h localhost -U postgres MY_DB_NAME" > /home/MY_USER/db-$(date +%d-%m-%y).backup

Upvotes: 6

Hamid ER-REMLI
Hamid ER-REMLI

Reputation: 210

This one, using container_name instead of database_scheme's one, works for me:

docker exec {container_name} pg_dump -U {user_name} > {backup_file_name}

In instance, for me, database name, user and password are supposed declared in docker-compose.yaml

I wish it could help someone

Upvotes: 7

learner
learner

Reputation: 61

To run the container that has the Postgres user and password, you need to have preconfigured variables as container environment variable. For example:

docker run -it --rm --link <container_name>:<data_container_name> -e POSTGRES_PASSWORD=<password> postgres /usr/bin/pg_dump -h <data_container_name> -d <database_name> -U <postgres_username> > dump.sql

Upvotes: 0

jonathanjg
jonathanjg

Reputation: 406

Although the mountpoint solution above looked promising, the following is the only solution that worked for me after multiple iterations:

docker run -it  -e PGPASSWORD=my_password postgres:alpine  pg_dump  -h hostname -U my_user my_db > backup.sql

What was unique in my case: I have a password on the database that needs to be passed in; needed to pass in the tag (alpine); and finally the hosts version of the psql tools were different to the docker versions.

Upvotes: 21

Jekis
Jekis

Reputation: 4695

I have container named postgres with mounted volume -v /backups:/backups

To backup gziped DB my_db I use:

docker exec postgres pg_dump -U postgres -F t my_db | gzip >/backups/my_db-$(date +%Y-%m-%d).tar.gz

Now I have

user@my-server:/backups$ ls
my_db-2016-11-30.tar.gz

Upvotes: 36

Pratik
Pratik

Reputation: 1216

Another workaround method is to start postgre sql with a mountpoint to the location of the dump in docker.

like docker run -v <location of the files>. Then perform a docker inspect on the docker running container

docker inspect <image_id>

you can find "Volumes" tag inside and a corresponding location.Go to the location and you can find all the postgresql/mysql files.It worked for me.Let us know if that worked for you also.

Good luck

Upvotes: 1

Related Questions