Running PostgreSQL in Docker

In the previous post, I showed you how to setup a postgres server on a Debian based operating system. In this post I’ll show you an easier way to run postgres using Docker and how to backup and restore databases in docker containers. Using docker to setup postgres is faster than building, installing and configuring it from scratch.

Run a postgres container

docker run -d \
--name postgres-container \
-e POSTGRES_USER=your_username \
-e POSTGRES_PASSWORD=your_secret_password \
-e POSTGRES_DB=test_database \
-p 5432:5432 \
-v /path/on/host:/var/lib/postgresql/data \
postgres:latest

This docker command runs a PostgreSQL container with specific configuration options.

• docker run: This creates and runs a new docker container
• -d: This runs the container in detached mode or in the background. A detached container does not print logs to the terminal and allows you to keep using the terminal after running the command.
• --name postgres-container: This assigns the name postgres-container to the container. You can use any name you like as long as it doesn’t conflict with the name of another container on your system. Naming containers this way makes it easy to reference and manage them in subsequent commands.
• -e POSTGRES_USER=your_username: This sets an environment variable that will set the PostgreSQL user to ‘your_username’. Replace this with a username of your choosing. This is the user account that applications and clients will use to connect to postgres.
• -e POSTGRES_PASSWORD=your_secret_password: Sets the password for the PostgreSQL user specified above. Change the password to something more secure.
• -e POSTGRES_DB=test_database: Sets the name of an initial database to create within PostgreSQL. In this case it is test_database.
• -p 5432:5432: Maps port 5432 on the host to port 5432 on the container. This allows you to connect to the PostgreSQL server running in the container from the host machine. PostgreSQL runs on port 5432 by default.
• -v /path/on/host:/var/lib/postgresql/data : This creates a volume. This flag binds a volume (folder) on the host machine to the specified path on the container. Replace /path/on/host with a full file path on your host machine to where you want to store the postgres data. Binding volumes this way ensures that the postgres data is stored on the host machine, making it persistent even when the container is killed or removed.
• postgres:latest: Specifies which Docker image to use. In this case, it is using the latest version of the official PostgreSQL image in Docker Hub.

Check the container

After running the container, verify that it started by running:

docker logs -f postgres-container

Wait until the log says “ready to to start accepting connections”. At this point you have a functional postgres container running.

Backing up the postgres Database

To backup the database, you can run a command like this one

docker exec -t postgres-container pg_dump -U username -d test_database -F p > /path/to/backup.sql

• docker exec : This command allows you to execute commands in a running docker container. In this case we’re running the pg_dump utility used to back up postgres databases.
• -t: Allocates a pseudo TTY to the container. This is needed to run interactive commands.
• postgres-container is the name of the container we want to back up data from
• pg_dump is the utility for making database backups
• -U username : Specifies the username to use to connect to the database.
• -d test_database : Specifies the database to connect to.
• -F p > path/to/backup.sql : This specifies the format of the backup. In this case, it is set to “plain” format. The plain format creates a SQL script that can be used to recreate the database. Replace path/to/backup.sql with an actual path to where you want to back up the database to.

Restoring the backup

To restore a database, first create a new one and restore data to it:

docker exec -it postgres-container psql

create database yourdatabase;
\q

docker exec -i postgres-container psql -U username -d yourdatabase -f /path/to/backup/file

The first command connects to the postgres-container interactively and runs the psql shell. Psql is a terminal based front end and client for postgreSQL. In the psql shell, type create database and the name of the new database. Add a “;” at the end and hit enter. This creates the new database. The next exits out of the psql shell and the container.

Next, run the backup SQL script against the database you just created. This will restore the database from the backup file.

Conclusion

In this post, you saw how to create a postgreSQL server using Docker and how to back up and restore data from it. In the next post, I’ll show you how to enable streaming replication in postgres with docker.