How to Transfer a PostgreSQL Database Between Docker Containers

Here are the steps to copy the content from one Postgres database, running within a Docker container to a separate database, also running inside of a Docker container. In order to do this, we can dump and restore the data of a database.

Note: Replace <container_name> and other placeholders with the specific values of your setup.

Generate a Backup File for the Initial Database

To execute commands within a Docker container, prefix the command with docker exec, followed by the name of the container.

The command used to create a backup of a database is pg_dump. This command can be executed even while the database is running and in active use. There are several flags available to set as options for the command. One such flag determines the format of the output. In this example, we'll use the -Fd flag to produce a directory format backup. This format is compatible with pg_restore, we'll need later on.

You can find a comprehensive list of all available options for the pg_dump command in the official PostgreSQL documentation here.

Execute the following command to create the backup:

docker exec <container_name> pg_dump -U <username> -f <path/to/dump> <dbname> -Fd

Transfer the Backup to the Host Machine

We now need to transfer the backup directory we created onto our host machine. Currently, the backup resides within the container's file system. To accomplish this, we can utilize a special command to copy files from a Docker container to the host machine's file system and vice versa:

docker cp <container_name>:</path/to/dump> </path/to/dump/in/host>

Transfer the Backup from the Host Machine to the Second Container

We can use the same command to copy the backup from the host machine into our second container:

docker cp </path/to/dump/in/host> <second_container_name>:</path/to/dump>

Restore Data into the Second Database

This command will overwrite the existing database in the second container, utilizing the -c or --clean flag. Additionally, you can use the -C or --create flag to create the database before restoring into it.

As with pg_dump, you can find a comprehensive list of all available options for the pg_restore command in the official PostgreSQL documentation here.

docker exec <second_container_name> pg_restore -d <dbname> -U <username> -c <path/to/dump>

Note: While this method is ideal as a quick way for copying data from a production database to a local development environment, for database backups, it's advisable to automate this process.