How to dump and restore a PostgreSQL from a Docker Container

Currently, we don't need to install PostgreSQL to the root system. Alternatively, we can install PostgreSQL with Docker. What is Docker? How to run PostgreSQL with Docker? Is Docker difficult to use?

Docker can run multiple applications with isolated programs, and we can switch applications to other ports without causing conflict with programs from the same port. Overall Docker is the same as Virtual Box by Oracle, However, Docker does not require an Operating System to run it. Docker uses the root system to run Docker. You can check this documentation for details about Docker. https://www.docker.com/resources/what-container/

Sometimes you need to quickly dump and restore a PostgreSQL database, but what's the easiest way to do that when your database is in a Docker container?

For example docker script and docker-compose.yaml

# with cli

docker run -d \
	--name some-postgres \
	-e POSTGRES_PASSWORD=mysecretpassword \
	-e PGDATA=/var/lib/postgresql/data/pgdata \
	-v /custom/mount:/var/lib/postgresql/data \
	postgres

Run this script to create a network proxy for internal docker images.

docker network create proxy

# with docker-compose.yaml

version: '3.8'

services:
  postgres:
    image: postgres:15.3
    container_name: some-postgres
    restart: always
    environment:
      - POSTGRES_PASSWORD=${DB_PASSWORD}
    ports:
      - "5432:5432"
    networks:
      - proxy
    volumes:
      - ./storage/postgres-data:/var/lib/postgresql/data

volumes:
  storage:

networks:
  proxy:
    external: true

Once we have created docker-compose, we can run docker-compose like this:

docker compose up -d

For more information about Postgres doc images, you can read the Postgres registry repository here: https://hub.docker.com/_/postgres

This quick explanation assumes you don't have anything installed directly on your development machine, so everything is run directly from and to the PostgreSQL Docker container you're running.

Dump database using "pg_dump"

docker exec -i pg_container_id /bin/bash -c "PGPASSWORD=your_db_password pg_dump --username your_db_username your_database_name" > /desired/path/on/your/machine/dump.sql

For example like this:

docker exec -i 48b0930423ee /bin/bash -c "PGPASSWORD=mylocalpassword pg_dump --username postgres db_test_1" > /MyApps/Backup/2024-08-23_dump.sql

Restore database using "psql"

docker exec -i pg_container_id /bin/bash -c "PGPASSWORD=your_db_password psql --username your_db_username your_database_name" < /path/on/your/machine/dump.sql

For example like this:

docker exec -i 48b0930423ee /bin/bash -c "PGPASSWORD=mylocalpassword psql --username postgres db_test_1" < /MyApps/Backup/2024-08-23_dump.sql

Restore with custom format dump

If you are attempting to restore data from a custom format dump, you should instead use pg_restore. Assuming you have the Docker container running and a data dump file ready, you can run the following command to import all data. Be sure to substitute ./latest.dump to where your dump file is located, if it's not in the current working directory and called latest.dump.

docker exec -i postgres pg_restore --verbose --clean --no-acl --no-owner -h localhost -U postgres -d your-db-name < ./latest.dump

This command may take a few minutes to complete, depending on how large your database is. Verbose mode is enabled on the above command so you can track what is going on more easily. Once it is complete, you will be ready to move on.

Note:

By default, PostgreSQL continues importing even when errors occur. If you prefer to stop importing completely when an error occurs, be sure to add --set ON_ERROR_STOP=on to the above command.

Dump and Restore with one command.

If you are, for example, moving data from one container (say one created manually) to another, you can use a pipe to do so in one command, as follows:

docker exec -i pg_old_container_id /bin/bash -c "PGPASSWORD=pg_password pg_dump --username pg_username database_name" | docker exec -i pg_new_container_id /bin/bash -c "PGPASSWORD=pg_password psql --username pg_username database_name"

Conclusion

With Docker, we can use PostgreSQL without installing PostgreSQL on the local machine, because PostgreSQL runs on a Docker container. Then with the guide above, we get an alternative to run dump and restore PostgreSQL database with Docker Container. Please keep in mind that you should always make sure your production database is backed up properly, and ideally automatically. If you are using one of these manual steps as a means to create a backup, you are probably doing something not entirely right. These steps are mostly for moving development data or pulling production data (partially) locally for debugging, or something like that.