Backup/Restore a dockerized PostgreSQL database

Question

I'm trying to backup/restore a PostgreSQL database as is explained on the Docker website, but the data is not restored.

The volumes used by the database image are:

VOLUME  ["/etc/postgresql", "/var/log/postgresql", "/var/lib/postgresql"]

and the CMD is:

CMD ["/usr/lib/postgresql/9.3/bin/postgres", "-D", "/var/lib/postgresql/9.3/main", "-c", "config_file=/etc/postgresql/9.3/main/postgresql.conf"]

I create the DB container with this command:

docker run -it --name "$DB_CONTAINER_NAME" -d "$DB_IMAGE_NAME"

Then I connect another container to insert some data manually:

docker run -it --rm --link "$DB_CONTAINER_NAME":db "$DB_IMAGE_NAME" sh -c 'exec bash'
psql -d test -h $DB_PORT_5432_TCP_ADDR
# insert some data in the db
<CTRL-D>
<CTRL-D>

The tar archive is then created:

$ sudo docker run --volumes-from "$DB_CONTAINER_NAME" --rm -v $(pwd):/backup ubuntu tar cvf /backup/backup.tar /etc/postgresql /var/log/postgresql /var/lib/postgresql

Now I remove the container used for the db and create another one, with the same name, and try to restore the data inserted before:

$ sudo docker run --volumes-from "$DB_CONTAINER_NAME" --rm -v $(pwd):/backup ubuntu tar xvf /backup/backup.tar 

But the tables are empty, why is the data not properly restored ?

Wynnie

https://mcmap.net/q/88199/-pg_dump-postgres-database-from-remote-server-when-port-5432-is-blocked – Waver

Answer 1

Backup your databases

docker exec -t your-db-container pg_dumpall -c -U postgres > dump_`date +%Y-%m-%d"_"%H_%M_%S`.sql

Creates filename like dump_2023-12-25_09_15_26.sql

If you want a smaller file size, use gzip:

docker exec -t your-db-container pg_dumpall -c -U postgres | gzip > dump_`date +%Y-%m-%d"_"%H_%M_%S`.sql.gz

If you want even smaller file sizes use brotli or bzip2:

docker exec -t your-db-container pg_dumpall -c -U postgres | brotli --best > dump_`date +%Y-%m-%d"_"%H_%M_%S`.sql.br

or

docker exec -t your-db-container pg_dumpall -c -U postgres | bzip2 --best > dump_`date +%Y-%m-%d"_"%H_%M_%S`.sql.bz2

Restore your databases

cat your_dump.sql | docker exec -i your-db-container psql -U postgres

Khalsa

Yep, that's the postgres way to do it, but I think the docker way should always be prefered when you use it – Wynnie

To save some space on disk you might want to pipe the dump to gzip: docker exec -t your-db-container pg_dumpall -c -U postgres | gzip > /var/data/postgres/backups/dump_date +%d-%m-%Y"_"%H_%M_%S.gz – Strenuous

@Strenuous How can I restore .xz or .gz packed this way? – Prevaricate

Just unzip the data before you restore it. To do it as a one liner you will have to replace the cat your_dump.sql with the unzip command and pipe that instead of the cat result to docker exec. – Strenuous

when I run the backup command it says "docker exec" requires at least 2 argument(s). – Openeyed

I'm running Docker on Windows and this works fine. But I'm wondering... what would a regular backup schedule look like? I don't even want to lose my data. – Tactics

Problem with this approach is providing the password. If the password prompt is displayed the backup will fail. But it's possible to provide password through postgres ENV variables. – Missionary

The date format is messed up, so double check that before you copy and paste. – Intercurrent

Docker is often an insidious, low quality, leaky abstraction over running processes. I struggled to do this simple task for over an hour, and the answer is something that's never used in a normal postgres flow, which we have to do because docker doesn't support better command execution. – Delusion

For those would couldn't figure out how to get the date formatting working: docker exec -t your-db-container pg_dumpall -c -U postgres | gzip > ./tmp/dump_$(date +"%Y-%m-%d_%H_%M_%S").gz – Prescriptive

When restoring the database, make sure you add -d your-db-name to the restore command if your database isn't named postgres. – Eberta

With the given commands, you may run into an ugly surprise if your DB contains UTF-8 characters. See this question for more details and a solution. – Nardi

'gzip' is not recognized as an internal or external command, operable program or batch file. on Windows. – Nahuatl

looks like this may produce some problem with encodings, I've got ????? instead of some cyrillics texts – Lopsided

Note: For docker-compose users, that's docker-compose exec -T (capital T) – Baryton

How do we use this approach with DB volumes? I currently have the following volume for my PostgreSQL container; ` volumes: - database_data:/var/lib/postgresql ` – Lan

@AndyRay at any time you can docker exec -it ${container_name} bash -il and break into a fully functional CLI for postgres. Any work can be saved with docker commit. Any files can be copied with docker cp. A LOT has changed in the 4 years since your comment. – Shellyshelman

when restoring im facing this issue ``` ERROR: database "template1" does not exist \connect: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: database "template1" does not exist ``` – Enviable

Answer 2

Backup Database

generate sql:

• docker exec -t your-db-container pg_dumpall -c -U your-db-user > dump_$(date +%Y-%m-%d_%H_%M_%S).sql

to reduce the size of the sql you can generate a compress:

• docker exec -t your-db-container pg_dumpall -c -U your-db-user | gzip > ./dump_$(date +"%Y-%m-%d_%H_%M_%S").gz

Restore Database

• cat your_dump.sql | docker exec -i your-db-container psql -U your-db-user -d your-db-name

to restore a compressed sql:

• gunzip < your_dump.sql.gz | docker exec -i your-db-container psql -U your-db-user -d your-db-name

PD: this is a compilation of what worked for me, and what I got from here and elsewhere. I am beginning to make contributions, any feedback will be appreciated.

Romeliaromelle

using "cat your_dump.sql | .... " to restore a db I think has a really low performance, am I wrong? – Hadfield

You forgot a sql extension before gz: docker exec -t your-db-container pg_dumpall -c -U your-db-user | gzip > ./dump_$(date +"%Y-%m-%d_%H_%M_%S").sql.gz – Hamo

Scenario: user table has 2 users. We make a backup. We register another user, so table has 3 entries. We restore backup with cat your_dump.sql | docker exec -i your-db-container psql -U your-db-user -d your-db-name. Result: user table still has 3 entries; backup did not restore database state at backup. P.s. I'm beginner too, maybe I hold incorrect assumptions how backups work. – Rie

Answer 3

I think you can also use a postgres backup container which would backup your databases within a given time duration.

  pgbackups:
    container_name: Backup
    image: prodrigestivill/postgres-backup-local
    restart: always
    volumes:
      - ./backup:/backups
    links:
      - db:db
    depends_on:
      - db
    environment:
      - POSTGRES_HOST=db
      - POSTGRES_DB=${DB_NAME} 
      - POSTGRES_USER=${DB_USER}
      - POSTGRES_PASSWORD=${DB_PASSWORD}
      - POSTGRES_EXTRA_OPTS=-Z9 --schema=public --blobs
      - SCHEDULE=@every 0h30m00s
      - BACKUP_KEEP_DAYS=7
      - BACKUP_KEEP_WEEKS=4
      - BACKUP_KEEP_MONTHS=6
      - HEALTHCHECK_PORT=81

Keithakeithley

How exaclty do you run this command? new to docker etc – Naivete

This isn't a command @JackB, it's a docker-compose file. docs.docker.com/compose – Murdock

Yes. correct just use this to insert in your docker-compose file and then run the docker-compose file – Keithakeithley

@TharinduPradeep Thanks. I can see the container but where do the backups go? – Trauner

Does this actually work without downtime? How does the container deal with changes that occur while it's creating the backup? – Stephan

@AneeshPanoli to /backups folder defined at - ./backup:/backups – Zugzwang

Incredibly useful for people who want to automatically backup their DB at regular intervals. – Teresa

How to restore the backup? – Parkway

Answer 4

cat db.dump | docker exec ... way didn't work for my dump (~2Gb). It took few hours and ended up with out-of-memory error.

Instead, I cp'ed dump into container and pg_restore'ed it from within.

Assuming that container id is CONTAINER_ID and db name is DB_NAME:

# copy dump into container
docker cp local/path/to/db.dump CONTAINER_ID:/db.dump

# shell into container
docker exec -it CONTAINER_ID bash

# restore it from within
pg_restore -U postgres -d DB_NAME --no-owner -1 /db.dump

Agriculture

this approach, unlike the accepted one, helped me to get correct encodings when doing this in Windows. (Actually, I applied the dump by going cat /home/db.sql | psql -U postgres -d DB_NAME -p DB_PORT) – Lopsided

This works much faster. One could make a backupscript based on this, build an image with the script included, and fire the script from the host through a cronjob. Mount a host-volume onto the container, and have a backupserver pull the daily sql-dumps. – Scar

I appreciate this because I had the exact same problem with cat db.dum |... – Hadfield

Answer 5

Okay, I've figured this out. Postgresql does not detect changes to the folder /var/lib/postgresql once it's launched, at least not the kind of changes I want it do detect.

The first solution is to start a container with bash instead of starting the postgres server directly, restore the data, and then start the server manually.

The second solution is to use a data container. I didn't get the point of it before, now I do. This data container allows to restore the data before starting the postgres container. Thus, when the postgres server starts, the data are already there.

Wynnie

flocker or convoy might help with data container dealing. – Khalsa

Please fill in more details. This sounds more like a sketch of a solution than an actual solution – Membranophone

Answer 6

The below command can be used to take dump from docker postgress container

docker exec -t <postgres-container-name> pg_dump --no-owner -U <db-username> <db-name> > file-name-to-backup-to.sql

Trinatrinal

Caution: I experienced a broken backup file when I used pg_dump -F c (custom format) with the docker exec -t option. I assume the terminal mode interferes with the piped binary output. Do not use docker exec -t (or -i). – Downspout

Answer 7

The top answer didn't work for me. I kept getting this error:

psql: error: FATAL:  Peer authentication failed for user "postgres"

To get it to work I had to specify a user for the docker container:

Backup

docker exec -t --user postgres your-db-container pg_dumpall -c -U postgres > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql

Restore

cat your_dump.sql | docker exec -i --user postgres your-db-container psql -U postgres

Deferment

Answer 8

Another approach (based on docker-postgresql-workflow)

Local running database (not in docker, but same approach would work) to export:

pg_dump -F c -h localhost mydb -U postgres export.dmp

Container database to import:

docker run -d -v /local/path/to/postgres:/var/lib/postgresql/data postgres #ex runs container as `CONTAINERNAME` #find via `docker ps`
docker run -it --link CONTAINERNAME:postgres  --volume $PWD/:/tmp/  postgres  bash -c 'exec pg_restore -h postgres -U postgres -d mydb -F c /tmp/sonar.dmp'

Blindstory

this worked for me: pg_dump mydb -U postgres > export.psql in docker container bash – Outstand

Answer 9

I had this issue while trying to use a db_dump to restore a db. I normally use dbeaver to restore- however received a psql dump, so had to figure out a method to restore using the docker container.

The methodology recommended by Forth and edited by Soviut worked for me:

cat your_dump.sql | docker exec -i your-db-container psql -U postgres -d dbname

(since this was a single db dump and not multiple db's i included the name)

However, in order to get this to work, I had to also go into the virtualenv that the docker container and project were in. This eluded me for a bit before figuring it out- as I was receiving the following docker error.

read unix @->/var/run/docker.sock: read: connection reset by peer

This can be caused by the file /var/lib/docker/network/files/local-kv.db .I don't know the accuracy of this statement: but I believe I was seeing this as I do not user docker locally, so therefore did not have this file, which it was looking for, using Forth's answer.

I then navigated to correct directory (with the project) activated the virtualenv and then ran the accepted answer. Boom, worked like a top. Hope this helps someone else out there!

Pelletier

Answer 10

dksnap (https://github.com/kelda/dksnap) automates the process of running pg_dumpall and loading the dump via /docker-entrypoint-initdb.d.

It shows you a list of running containers, and you pick which one you want to backup. The resulting artifact is a regular Docker image, so you can then docker run it, or share it by pushing it to a Docker registry.

(disclaimer: I'm a maintainer on the project)

Macdonell

great! looking forward for "A non-graphical CLI interface that's scriptable." so that I can use it from Robot Framework tests :) – Algebraic

Answer 11

This is the command worked for me.

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

for example

cat table_backup.sql | docker exec -i 03b366004090 psql -U postgres -d postgres

Reference: Solution given by GMartinez-Sisti in this discussion. https://gist.github.com/gilyes/525cc0f471aafae18c3857c27519fc4b

Jungjungfrau

I am getting back invalid command \N in the terminal when i run the command cat your_dump.sql | sudo docker exec -i {docker-postgres-container} psql -U {user} -d {database_name} – Deviled

Answer 12

Another way to do it is to run the pg_restore (of course if you have postgres set up in your host machine) command from the host machine.

Assuming that you have port mapping "5436:5432" for the postgres service in your docker-compose file. Having this port mapping will let you access the container's postgres (running on port 5432) via your host machine's port 5436

pg_restore -h localhost -p 5436 -U <POSTGRES_USER> -d <POSTGRES_DB>  /Path/to/the/.psql/file/in/your/host_machine 

This way you do not have to dive into the container's terminal or copy the dump file to the container.

Rennet

Answer 13

Using a File System Level Backup on Docker Volumes

Example Docker Compose

version: "3.9"

services:
  db:
    container_name: pg_container
    image: platerecognizer/parkpow-postgres
    # restart: always
    volumes:
      - postgres_data:/var/lib/postgresql/data/
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: admin
      POSTGRES_DB: admin

volumes:
  postgres_data:

Backup Postgresql Volume

docker run --rm \
   --user root \
   --volumes-from pg_container \
   -v /tmp/db-bkp:/backup \
   ubuntu tar cvf /backup/db.tar /var/lib/postgresql/data

Then copy /tmp/db-bkp to second host

Restore Postgresql Volume

docker run --rm \
   --user root \
   --volumes-from pg_container \
   -v /tmp/db-bkp:/backup \
   ubuntu bash -c "cd /var && tar xvf /backup/db.tar --strip 1"

Multiform

Answer 14

Solution for docker-compose users:

1. At First run the docker-compose file by any on of following commands: $ docker-compose -f loca.yml up OR docker-compose -f loca.yml up -d
2. For taking backup: $ docker-compose -f local.yml exec postgres backup
3. To see list of backups inside container: $ docker-compose -f local.yml exec postgres backups
4. Open another terminal and run following command: $ docker ps
5. Look for the CONTAINER ID of postgres image and copy the ID. Let's assume the CONTAINER ID is: ba78c0f9bcee
6. Now to bring that backup into your local file system, run the following command: $ docker cp ba78c0f9bcee:/backups ./local_backupfolder

Hope this will help someone who was lost just like me..

N.B: The full details of this solution can be found here.

Fleece

Answer 15

What worked for me. This could be helpful if you're trying to do this the docker way.

1. STOP the DB_CONTAINER

   docker stop DB_CONTAINER_ID_OR_NAME
   

2. BACKUP DB data

   docker run --rm 
   --user root 
   --volumes-from DB_CONTAINER_ID_OR_NAME 
   -v /path/to/local_storage:/backup 
   ubuntu tar cvf /backup/db.tar /var/lib/postgresql/data
   

3. MOVE your backup file that's stored in your /path/to/local_storage in any other server or your local host

4. RESTORE DB DATA

   docker run --rm 
     --user root 
     --volumes-from DB_CONTAINER_ID_OR_NAME 
     -v /path/to/local_storage:/backup 
     ubuntu bash -c "cd /var && tar xvf /backup/db.tar --strip 1"
   

5. START THE DB_CONTAINER

   docker start
   

Eada

Please use markdown to highlight the syntax accordingly, e.g. backticks this is code – Swimmingly

Answer 16

I would like to add the official docker documentation for backups and restores. This applies to all kinds of data within a volume, not just postegres.

Backup a container

Create a new container named dbstore:

$ docker run -v /dbdata --name dbstore ubuntu /bin/bash

Then in the next command, we:

• Launch a new container and mount the volume from the dbstore container

• Mount a local host directory as /backup

• Pass a command that tars the contents of the dbdata volume to a backup.tar file inside our /backup directory.

  $ docker run --rm --volumes-from dbstore -v $(pwd):/backup ubuntu tar cvf /backup/backup.tar /dbdata

When the command completes and the container stops, we are left with a backup of our dbdata volume.

Restore container from backup

With the backup just created, you can restore it to the same container, or another that you made elsewhere.

For example, create a new container named dbstore2:

$ docker run -v /dbdata --name dbstore2 ubuntu /bin/bash

Then un-tar the backup file in the new container`s data volume:

$ docker run --rm --volumes-from dbstore2 -v $(pwd):/backup ubuntu bash -c "cd /dbdata && tar xvf /backup/backup.tar --strip 1"

You can use the techniques above to automate backup, migration and restore testing using your preferred tools.

Adhesion

Answer 17

Main 2 steps:

1. Get docker container name with docker ps. You will get something like:

CONTAINER ID   IMAGE       ...   PORTS                                         NAMES
b36b6a66659f   postgres    ...   0.0.0.0:5066->5432/tcp, :::5066->5432/tcp     my-db

2. And create a dump for my-db to local path:

docker exec -i my-db pg_dump --dbname=postgresql://postgres:DB_PASSWORD@localhost:5432/postgres > ./my_db_dump.sql

For someone it can be helpful to know dbname schema:

postgresql://DB_USER:DB_PASSWORD@DB_HOST:5432/DB_NAME

Another related and useful commands:

To run sql command within container:

docker exec -i my-db psql -U postgres --dbname postgres -c "select * from users"

To restore dump:

cat ./my_db_dump.sql | docker exec -i my-db psql -U postgres

Grilse