How to generate a Postgresql Dump from a Docker container?

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

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

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.

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

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

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

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.

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

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

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

I use this script:

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