Setting up PostgreSQL and pgAdmin 4 with Docker

Unrelated photo of recent hike in Minnewaska State Park (NY)

In this article, we’ll walk through the steps of installing PostgreSQL and pgAdmin 4 in a Docker environment. As an optional last step, we’ll test the database connection by creating a database and table using pgAdmin 4, and then accessing them using the Terminal. This guide is designed to help you get your database environment up and running.

Installing Docker

How Docker can be installed depends on your operating system, follow the guidelines depending on your operating system (Note that there is a paid version of Docker, but the free version is sufficient for our purposes):

Install Docker Engine

Setting up PostgreSQL

To install PostgreSQL for Docker, you will need to open the terminal, command line, or PowerShell, depending on your operating system. We will then proceed to install the Postgres image. A Docker image is a lightweight, standalone, and executable software package that includes everything needed to run a piece of software, including the code, runtime, libraries, environment variables, and system tools.

Use the following command to get the Postgres image:

docker pull postgres

Now you should see the Postgres Image in your Docker desktop application in the Image tab.

Postgres Image

After pulling the Docker image, the next step is to start the Docker container to initiate the database and make it operational.

A Docker container is a lightweight and isolated runtime environment that encapsulates an application’s necessary dependencies, configurations, and code, allowing it to run consistently across different systems.

Use the following command to create a docker container (remember to change the password):

docker run --name sqltutorial -e POSTGRES_PASSWORD=marviniscool -p 5432:5432 -d postgres

In the Docker Desktop App you should now see the following:

List of Containers

We created a container using the docker run command, we use the name to choose a name for our container, and -e allows us to set environment variables, in this case, we set the database password to “marviniscool” (remember to change the password), lastly we use -d to let our container run in detached mode, and specify that we want to use the Postgres image. In this command, we’re keeping it straightforward by mapping port 5432 on our computer to port 5432 inside the container, which is handy for accessing the database later without any port-related confusion. Alternatively, you can add “POSTGRES_USER=mycustomuser” to change the username from “postgres” to a name of your choice. If you want to learn more about Docker or the Docker run command, check out the documentation:

docker run

Alternatively, we can use the command line to check if the docker container is running by running the following command:

docker ps

If you see the following you can be sure that your container is up and running:

Installing PgAdmin4

Now, let’s move on to installing pgAdmin 4. PgAdmin 4 is a popular web-based administration and management tool for PostgreSQL. It provides a user-friendly interface that lets you interact with your databases, execute SQL queries, monitor database performance, and much more, without having to navigate complex command lines.

The installation process is analogous to the process of installing PostgreSQL. We are going to pull the pgAdmin 4 image using Docker, which simplifies the setup process.

Input the following command into your terminal to install pgAdmin 4:

docker pull dpage/pgadmin4

You should be able to see the image in the Image tab of your Docker Desktop App.

Once we’ve downloaded the image, we can create and run our Docker container. The command below sets up a new Docker container named ‘pgadmin-container’, maps port 5050 on your machine to port 80 on the container, and sets the default email and password to access the pgAdmin 4 interface.

Here is the command to run the pgAdmin 4 docker container:

docker run --name pgadmin-container -p 5050:80 -e PGADMIN_DEFAULT_EMAIL=user@domain.com -e PGADMIN_DEFAULT_PASSWORD=catsarecool -d dpage/pgadmin4

Remember to replace “user@domain.com” and “catsarecool” with your email and password.

Connecting to Database Container using pgAdmin 4

Log in to pgAdmin 4

Once the container is successfully running (if you encounter any issues, it’s a good idea to check the Docker Desktop app to ensure the container is running), you can access pgAdmin by navigating to localhost:5050 in a web browser of your choice.

Login prompt

You will then see a login prompt, you will be able to log in with the e-mail address and password that you specified previously, in our case “user@domain.com” and “catsarecool”.

Connect to Database Container/ Add Server

In the next step, we are going to connect to the database container. For this, you need to click on Add New Server:

Click on Add New Server

And enter the relevant information to connect to our database, in the Name field we can choose an Alias to refer to our database in pgAdmin:

Set the name to SQL tutorial

Before we proceed, it’s important to obtain the IP address of the “sqltutorial” container. To find the IP address, you can execute the following command in your terminal (Linux/macOS) or PowerShell (Windows):

docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' sqltutorial

In our case, it returned the IP address 172.17.0.2 (this may or may not be true for you). Now we enter all the connection information:

• Host name/address: 172.17.0.2 (might be different for you)
• Port: 5432
• Maintenance database: postgres
• Username: postgres (or another name if you changed it)
• Password: marviniscool (or whatever password you selected)
• Optional: Set save password to true

Enter Database Connection Information

Click Save and you will be able to select your database server from the menu on the left side:

Congratulations, now your environment should be up and running. For questions about pgAdmin 4 refer to the documentation:

pgAdmin 4 - pgAdmin 4 7.4 documentation

Optional: Test Connection

As an optional last step, we are going to create a database and table using pgAdmin 4 and then try to read the table from the terminal. If you never worked with databases before, some of those concepts might be overwhelming, but I will go through everything in detail in succeeding tutorials.

Creating a database and table from pgAdmin 4

Right-click on ‘Databases’ and select ‘Create’ -> ‘Database’. Name your database, for instance, ‘my_new_database’, and then click ‘Save’.

Now, navigate to the database you just created, right-click on “Tables”, and select ‘Create’ -> ‘Table’. Name your table, e.g., “catbase”.

Create a database

After we created our database we will create a Table, to do this we select catbase -> Schemas -> public -> Tables -> Create -> Table…

Select Create Table

Now we set the name of the table to “cattable”:

Set table name

And in the next step, we will create two columns, the first one is an id column, which is our Primary Key (if you don’t know what that is don’t worry, I will explain it in a later tutorial) and the other one is a text column which represents the cat’s name. We can also specify constraints such as “Not Null?” which guarantees that all cells in this column have a value.

We set the first name to id, data type to serial, and check the “Not Null?” and “Primary Key?” constraints. Then we click on the Plus symbol and give the second column the name “catname”, data type text and check the “Not Null?” constraint as well.

Create columns

Click Save. Now that we created the table we want to add one row of data. We can either do this using SQL, but to be consistent we will also use pgAdmin in this case. We need to right-click on:

cattable -> View/Edit Data -> All Rows

Look at our database table

Next, we need to click on Add row in the Data Output Panel:

Add row to table

Now we will click the text field and set the name to a cool cat name, such as “Bam Bam” and in the next step, we will save our row to the database. (Please note that we do not need to enter a value in the key field, since we set the data type to serializer it will automatically select an id)

Save row to database

Querying the Table from the Terminal

With our database and table set up in pgAdmin 4, let’s switch over to the terminal to read the data from the table.

First, we need to connect to our database server in our docker container:

docker exec -it sqltutorial psql -U postgres

Second, we need to connect to the database “catbase” that we created earlier in pgAdmin 4.

\c catbase

Last but not least, we can use a select statement to select all the rows from the table:

SELECT * FROM CATTABLE;

And there you have it! You should see the contents of the ‘cattable’ within ‘catbase’ that you created in pgAdmin 4. This validates that pgAdmin 4 and the terminal are both successfully interacting with the PostgreSQL database inside your Docker container.

Photo by Kim Davies on Unsplash