Using Docker with Postgres: Tutorial and Best Practices – Earthly Blog

We are earthly. We make construction software simpler and therefore faster. This article is about data management for Postgres containers. If you’re interested in a simple, containerized approach to building software, check us out.

Relational databases have been an easy way to store relational data for the past few decades. Over the years, many popular database management systems have been created, but installing them can be complicated.

To avoid all the hassle of installing and configuring database servers, users can now take advantage of specially developed Docker containers to support database solutions.

In this article, you’ll learn more about what Docker is and how to use it. Here are some best practices for running PostgreSQL databases in Docker containers. For more information about Docker, you can check out the official guide.

For more information about Docker, you can check out the official guide.

Use cases for

running PostgreSQL on Docker

When considering running PostgreSQL, you need to consider portability. If you are a developer who works on multiple machines, every time you change a machine, you must install and configure the database separately. If you use PostgreSQL inside a Docker container, you can quickly use Docker to activate PostgreSQL containers and focus on actual development rather than configuration.

However, note that the data is not persistent and is deleted as soon as the container is shut down when you use PostgreSQL inside a Docker container. To work around this issue, you can mount a local directory as a volume and store PostgreSQL data from the container on the local volume. You will learn more about this below.

On top of that, databases are stateful applications, while containers are designed to run stateless applications. In addition, databases are resource-intensive applications, and running such databases in a production workload is not ideal. A workaround might be to use any database-as-a-service offering from a cloud provider, such as AWS, GCP, or Azure in production, but use containers for rapid development.

How to run

PostgreSQL using Docker

In this section, you run a PostgreSQL instance using Docker and use a graphical user interface (GUI) and pgAdmin to connect to the database. To get started, you need to have Docker installed on your machine. You can check if you already have Docker installed on your machine by running the following command in your terminal:

If you don’t have Docker installed, you can install it from the official website

.

Download PostgreSQL

Docker image

With Docker, you can create or own your images or use images from the repository. In this case, since you are using a

PostgreSQL Docker image, you can pull it from Docker Hub using the following command:

This command connects you to the Docker Hub and pulls the PostgreSQL image to your machine. By default, Docker pulls the latest image from the Docker Hub.

Note that Docker uses the default latest tag if there are no tags defined in the pull request. However, depending on your requirements, you can always specify a specific version of the image you want to work with.

Verify installed Docker images Once the PostgreSQL Docker image

has been checked out of the Docker Hub, you can verify the image using the following command:

This command lists all the images that are installed on the local computer

.

Run the PostgreSQL Docker container Now that you have the

PostgreSQL Docker

image

on your machine, you can start the container. As mentioned earlier, a container is an instance of a Docker image. To start the PostgreSQL container, there are a few parameters that Docker must provide, which are explained below

: -name: The

  • name of the PostgreSQL container. -rm: Deletes the container when it stops. -e: The
  • only required environment variable is the database password that must be provided before creating the container.
  • Q: Port mapping must be provided for the machine’s host port to map to the PostgreSQL container port within the container.

As soon as you run the above command, Docker starts the PostgreSQL container and makes it available. Once your container is up and running, you can open another terminal window and connect to the PostgreSQL database running inside the container.

Now, open another terminal window and type the following command:

This command allows you to connect to the PostgreSQL CLI running inside the Docker container. Once the interactive terminal is started, you can connect

to the PostgreSQL instance with the following command:

This command connects you to the PostgreSQL database using the default PostgreSQL user. The configurations are as follows

: Hostname

  • : You must provide the hostname on which the PostgreSQL Docker container runs. It is usually “localhost” if run locally.
  • Username

  • : By default, the PostgreSQL image uses a “postgres” username that you can use to log in to the database.

You can now run SQL queries against this database as usual:

Use a persistent volume to store data

In the previous section, you learned how to create and run a Docker container for PostgreSQL. An important point to note is that when you perform any DDL or DML within your database, all data is written inside the container. This means that as soon as you remove the container from your machine, there is no way to access the data.

To overcome this, you may want to write the data outside the Docker container to your disk drive. This way, you can run your PostgreSQL container on Docker and store all data in a directory that is not affected by Docker operations. At a later time, you can also use another PostgreSQL instance to read this persistent data by mounting a volume for your Docker container.

Basically, you need to map the PostgreSQL data directory from within the container to a directory on your local machine. This can be done using the following command

:

As you can see above, it has two new parameters

: Separate mode:

  • indicated by d, the separate mode will allow the container to run in the background
  • .

  • Volume: The local directory on the host maps to the data directory for PostgreSQL within the container.

Note that both paths are separated by two points

.

When you run the above command, you will see that the container has started and that the data files are available in the directory that you configured when you started the container. (You might need superuser permission to view the contents of the directory.)

Best practices for running

PostgreSQL on Docker

Docker makes it easy to set up a PostgreSQL database in seconds. But there are few best practices that can ensure a smooth experience and offer additional security.

  1. Use a persistent volume to store data. As mentioned earlier, without a persistent volume, you will lose data if the container is restarted.
  2. Use alpine images if possible. They are usually smaller in size. For example, postgres:14.2 has a size of 131mb, while postgres:14.2-alpine has only 78mb with the same functionality. In addition, alpine imagery is secure because all userspace binaries are compiled to protect against common vulnerabilities.
  3. Back up your data periodically. You can do this by running the pg_dump command from the database container

:

  1. If there is no database when PostgreSQL is started in a container, a default database will be created and will not accept incoming connections during that time. This can cause problems with automation tools that may try to access the database as soon as the container is started. To mitigate this, you must ensure that the database accepts connections before attempting to connect to it. If you use Docker Compose, you can use the health check feature: Limitations of

running

PostgreSQL database with

Docker

While Docker’s quick start and easy setup are a boon for development and testing, it’s generally not recommended to run production databases on Docker. The main reason is that Docker containers are great for running stateless applications. Containers can be terminated at any time and returned instantly. Multiple containers of the same application can run at the same time, and because they are stateless, this does not affect the workflow.

However, a database

is stateful, so any disruption caused to a database application can have catastrophic consequences. Even if you use a volume to preserve data, a database container crashing and ending up in the middle of a transaction could spell disaster. For production, it is always recommended to choose platform-as-a-service solutions such as GCP, AWS, or Azure.

Conclusion

In this article, you learned how to run a PostgreSQL instance on Docker. You deployed the instance using Docker and used a GUI and pgAdmin to connect to the database. You also learned about some best practices, such as using a persistent volume to store data so you can deliver a seamless experience and protect your data.

To deploy container-based applications, you must deploy a CI/CD pipeline that will continuously build the Docker image and deploy it when new code releases become available. Earthly is a popular CI tool that can be used to automate your container deployments. It provides integrations with most popular CI tools. You can learn more about Earthly on their website.

Contact US