How to Migrate Your PostgreSQL Database to Docker: A Practical Guide

Pankaj kushwaha
2 min readMar 22, 2023

--

Migrating your PostgreSQL database to a Docker container can help simplify deployment, increase portability, and streamline management. Here’s a practical guide on how to migrate your PostgreSQL database to Docker:

  1. Backup your existing PostgreSQL database: a. Use pg_dump to create a SQL dump of your existing database: pg_dump -U <username> -W -F t <database_name> > backup.tar b. Alternatively, use pg_dumpall to create a SQL dump of all databases and global objects: pg_dumpall -U <username> -W > backup.sql c. Ensure the backup is successful and store it in a secure location.
  2. Install Docker: a. Install Docker Engine on your local machine or the target server following the official Docker installation instructions. b. Verify the Docker installation by running: docker --version
  3. Pull the official PostgreSQL Docker image: a. Run: docker pull postgres:<version> b. Replace <version> with the desired PostgreSQL version that matches your existing database.
  4. Prepare the PostgreSQL data directory: a. Create a local directory to store the PostgreSQL data on the host machine: mkdir -p /path/to/your/data/directory b. Set the appropriate permissions for the data directory: chown -R 999:999 /path/to/your/data/directory
  5. Create and start a new PostgreSQL Docker container:

a. Run the following command to create and start a new container:

  • docker run --name <container_name> -e POSTGRES_USER=<username> -e POSTGRES_PASSWORD=<password> -e POSTGRES_DB=<database_name> -v /path/to/your/data/directory:/var/lib/postgresql/data -p <host_port>:5432 -d postgres:<version>

b. Replace <container_name>, <username>, <password>, <database_name>, /path/to/your/data/directory, <host_port>, and <version> with the appropriate values.

  1. Import the database backup into the new PostgreSQL container:

a. For a single database dump (.tar format), use the following command:

  • cat backup.tar | docker exec -i <container_name> pg_restore -U <username> -d <database_name> -F t

b. For the pg_dumpall backup (.sql format), use the following command:

  • cat backup.sql | docker exec -i <container_name> psql -U <username>

c. Replace <container_name>, <username>, and <database_name> with the appropriate values.

2. Verify the migration:

a. Connect to the new PostgreSQL container using psql or any other PostgreSQL client.

b. Check the schema, tables, and data to ensure everything has been migrated successfully.

3. Update your application configuration:

a. Update your application’s database connection settings to point to the new PostgreSQL Docker container.

b. Use the container’s hostname (if on the same network) or the host’s IP address and the mapped <host_port> for the connection.

By following this practical guide, you can successfully migrate your existing PostgreSQL database to a Docker container. This process helps you take advantage of the benefits of containerization, such as simplified deployment, increased portability, and streamlined management.

--

--

Pankaj kushwaha
Pankaj kushwaha

Written by Pankaj kushwaha

Database/System Administrator | DevOPS | Cloud Specialist | DevOPS