Set up a PostgreSQL server and pgAdmin with Docker

In this article, I am going to show you how to use Docker Compose to create a PostgreSQL container and access it using pgAdmin 4, the web-based PostgreSQL admin interface. I will also show you how to access the PostgreSQL database server running in a Docker container from DataGrip IDE. So, let’s get started.

Requirements:

In order to follow this article, you must have Docker installed on your computer. LinuxHint has a lot of articles that you can follow to install Docker on your desired Linux distribution if you don’t have it installed already. So, be sure to check LinuxHint.com in case you’re having trouble installing Docker.

Installing Docker Compose:

You can download Docker Compose binary file very easily with the following command:

NOTE: curl may not be installed on your Linux distribution. If that’s the case, you can install curl with the following command:

Ubuntu/Debian/Linux Mint:

CentOS/RHEL/Fedora:

Once docker-compose binary file is downloaded, run the following command:

Now, check whether docker-compose command is working as follows:

It should print the version information as shown in the screenshot below.

Setting Up Docker Compose for the Project:

Now, create a project directory (let’s say ~/docker/pgdev) as follows:

Now, navigate to the project directory ~/docker/pgdev as follows:

Now, create a docker-compose.yaml file in the project directory ~/docker/pgdev and type in the following lines in the docker-compose.yaml file.

The docker-compose.yaml file should look as follows.

Here, I have created 2 services db and pgadmin.

db service will run the postgres:12.2 image (from DockerHub) in a Docker container.

pgadmin service will run the dpage/pgadmin4:4.18 image (from DockerHub) in another Docker container.

In db service, the POSTGRES_DB, POSTGRES_USER, POSTGRES_PASSWORD environment variables are used to set the default database name, admin username and admin user password for the PostgreSQL server respectively. The PGDATA environment variable is used to configure the PostgreSQL server to store the data to /var/lib/postgresql/data directory of the container.

In pgadmin service, the PGADMIN_DEFAULT_EMAIL, PGADMIN_DEFAULT_PASSWORD environment variables are used to set the login email and password of pgAdmin web interface respectively. The PGADMIN_LISTEN_PORT is used to set the pgAdmin port 80 in the container.

In db service, all the contents of the /var/lib/postgresql/data directory will be saved permanently in the db-data volume.

In the pgadmin service, all the contents of the /var/lib/pgadmin directory will be saved permanently in the pgadmin-data volume.

In the db service, the container port 5432 (right) is mapped to the Docker host port 5432 (left).

In the pgadmin service, the container port 80 (right) is mapped to the Docker host port 8080 (left).

In the pgadmin service, a hostname alias pgsql-server to the db container is created. So, you can access the PostgreSQL server using pgsql-server as the hostname (no IP address required).

Starting PostgreSQL server and pgAdmin:

Now, to start the db and pgadmin services, run the following command:

The services should start in the background.

As you can see, the port 8080 and 5432 are opened by the docker-proxy service.

To see how the ports are mapped, run the following command:

As you can see, for the db service, the Docker host port 5432 is mapped to the container TCP port 5432.

For the pgadmin service, the Docker host port 8080 is mapped to the container TCP port 80.

Accessing pgAdmin 4 or PostgreSQL server from Other Computers:

If you want to access pgAdmin 4 or PostgreSQL database server from another computer on your network, then you must know the IP address of your Docker host.

To find the IP address of your Docker host, run the following command:

In my case, the IP address of my Docker host 192.168.20.160. It will be different for you. So, make sure to replace it with yours from now on.

Accessing pgAdmin from Web Browser:

Now, you can easily access pgAdmin 4 from your web browser.

Visit http://localhost:8080 from your Docker host or http://192.168.20.160:8080 from any computer on your network. You should see the pgAdmin login page. Login with your email and password.

Once you login, you should see the pgAdmin dashboard.

Now, to add the PostgreSQL server running as a Docker container, right click on Servers, and then go to Create > Server…

In the General tab, type in your server Name.

Now, go to the Connection tab and type in pgsql-server as Host name/address, 5432 as Port, postgres as Maintenance database, admin as Username, secret as Password and check Save password? checkbox. Then, click on Save.

pgAdmin 4 should be connected to your PostgreSQL database. Now, you can work with your PostgreSQL database as much as you want.

Accessing PostgreSQL from DataGrip:

You can also access your PostgreSQL database server from DataGrip IDE or any other SQL IDEs.

In case of DataGrip, click on + from the Databases section and go to Data Source > PostgreSQL.

PostgreSQL driver may not be installed. In that case, click on Download.

The driver should be installed. Now, type in the Docker host IP address 192.168.20.160 (in my case) as the Host, 5432 as the Port, admin as the User, secret as the Password, postgres as the Database and click on Test Connection.

You should see the PostgreSQL database server details if everything works.

Now, click on OK.

Now, you can manage your PostgreSQL databases from DataGrip.

Stopping PostgreSQL and pgAdmin:

Now, to stop the db and pgadmin services, run the following command:

The db and pgadmin services should be stopped.

Cleaning Up PostgreSQL and pgAdmin Data:

If you want to remove all the PostgreSQL database data and settings, and all the pgAdmin settings, you must remove the db-data and pgadmin-data volumes.

You can find the actual name of the data volumes with the following command:

As you can see, there are 2 volumes for the pgdev project, pgdev_db-data and pgdev_pgadmin-data.

You can remove these volumes with the following command:

References:

[1] https://hub.docker.com/_/postgres?tab=description
[2] https://hub.docker.com/r/dpage/pgadmin4/
[3] https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html