75

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

Cesar Augusto Nogueira
  • 1,911
  • 1
  • 14
  • 14

10 Answers10

84

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
Cesar Augusto Nogueira
  • 1,911
  • 1
  • 14
  • 14
  • 9
    where you specify the user and password of the user? – VaTo Feb 09 '17 at 21:15
  • 9
    @VaTo you can specify the user like this: `docker exec pg_dump -U --column-inserts --data-only > inserts.sql` – LondonAppDev May 18 '18 at 08:34
  • If you dont have the PGUSER set in your container you need to `export PGUSER=postgres` to make it work (Testet in postgres 9.6). – Stefan Höltker Mar 16 '21 at 08:16
  • This also works from windows cmd – Tobias Grunwald Oct 12 '21 at 15:09
  • If you need to hand in a password, run the identical command above but add "-it" after the docker exec (i.e. "docker exec -it ..."). After entering this command you will get a blank cursor. Type in your database password and hit enter again. It will now execute the pd_dump. Note: i stands for interactive, so adding this flag allows you to input your password when it is required by pg_dump. Unfortunately you will not see a "password" prompt, but if you just type in your password and hit enter, it will work. – Andrew Einhorn Sep 14 '22 at 06:59
30

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
Jekis
  • 4,274
  • 2
  • 36
  • 42
  • 1
    I'm getting a `no such file or directory: /backups/my_db-2017-03-19.tar.gz`, do you have an idea why ? – drskullster Mar 19 '17 at 10:17
  • 3
    I managed to work around it with `docker exec -t postgres bash -c 'pg_dump -U postgres -F t my_db | gzip >/backups/my_db-$(date +%Y-%m-%d).tar.gz'` – drskullster Mar 19 '17 at 10:29
  • Maybe you don't have /backups dir and/or it's not mounted? – Jekis Mar 20 '17 at 05:51
  • It was mounted. The problem seems to come from docker not understanding the command: http://stackoverflow.com/questions/37929190/docker-exec-printf-gives-no-such-file-or-directory-error – drskullster Mar 20 '17 at 10:37
  • @drskullster command string works fine, thanks for your reference – Pham Dec 06 '18 at 02:59
  • 3
    Minor note: This isn't actually doing a `tar gz`, it's doing just a gz but you're naming the SQL file with the extension tar and then gzipping it. If you gunzip it, you get an unreadable file called `my_db-date.tar`, but if you rename the .tar to .sql, it's actually an SQL file. You should probably make it output as just "filename.gz" or use `tar -cz` if you actually want a `.tar.gz` file extension – Matt Fletcher Jan 31 '22 at 18:40
17

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.

jonathanjg
  • 366
  • 2
  • 6
6

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

Hamid ER-REMLI
  • 200
  • 1
  • 7
3

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
Digao
  • 520
  • 8
  • 22
2

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
johansenj
  • 71
  • 4
1

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

Pratik
  • 1,216
  • 11
  • 18
0

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

Eriawan Kusumawardhono
  • 4,796
  • 4
  • 46
  • 49
learner
  • 41
  • 6
0

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.

Ders
  • 1,068
  • 13
  • 16
0

I use this script:

docker exec postgres-1 pg_dump -U postgres --column-inserts --data-only  postgres > inserts.sql
Axel
  • 21
  • 2