Deploying PostgreSQL and pgAdmin4 with Docker on CentOS 7: A Step-by-Step Guide

In this article, I will guide you through running pgAdmin4 locally and transferring a sample dataset from PostgreSQL to perform some basic queries. Our goal is to two separate Docker containers (PostgreSQL and pgAdmin4) on CentOS 7 for utilization.

Note: If you use MobaXterm, it will be very convenient for you. (For those wondering what this is, basic linux shell and ofcourse just go ask chatgpt ^^)

Why are we doing this: Linux and Docker usage are quite easy, and it’s always great to learn new technologies. :)

1. We will continue assuming that you have installed CentOS 7 and Docker

• Installation on CentOS 7: https://medium.com/free-code-camp/getting-started-with-centos-15eac7215c99
• Docker Installation: https://docs.docker.com/engine/install/centos/
• MobaXterm Installation: https://mobaxterm.mobatek.net/

2. Installing the PostgreSQL Container

3. Installing the pgAdmin 4 Container

4. PostgreSQL (Creating Database and Roles)

5. Connecting to pgAdmin 4 via the Browser and Adding a Server

6. Importing and Reading a Sample .csv File

Postgresql: https://hub.docker.com/_/postgres (this is offical dockerhub link!)

When you execute the command, PostgreSQL will be installed on your system.

docker pull postgres

Starting the downloaded files with port forwarding will always make your work easier.

docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres

If you want to check your program, type: docker ps -a

pgAdmin 4: https://hub.docker.com/r/dpage/pgadmin4 (The most downloaded pgAdmin 4 application. Note that this is not official!)

We are following the same steps as in the PostgreSQL instructions.

docker pull dpage/pgadmin4

We continue with the steps to start. You can also refer to the documentation.

dpage/pgadmin4: https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html

“user@domain.com” and “SuperSecret” can remain the same for now.

docker run -p 80:80 \
    -e 'PGADMIN_DEFAULT_EMAIL=user@domain.com' \ 
    -e 'PGADMIN_DEFAULT_PASSWORD=SuperSecret' \
    -d dpage/pgadmin4

Reminder: Don’t forget to set up port forwarding in your VirtualBox:

PostgreSQL and pgAdmin4 have been installed, port forwarding has been configured, and both containers are running smoothly.

PostgreSQL (Database and Role) Assignments:

1. Entering the Container:

docker exec -it some-postgres bash

2. Switching to Postgres:

psql -U postgres

There are three steps we need to take in PostgreSQL.

1. Creating a Database:

CREATE DATABASE mydb;

2. Creating a User and Password: (For now, the USER (myuser) and PASSWORD (mypassword) can remain constant.)

CREATE USER myuser WITH PASSWORD 'mypassword' CREATEDB;

3. Granting Permissions to the “myuser” User:

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

GRANT CREATE ON SCHEMA public TO myuser;

ALTER USER myuser WITH SUPERUSER;

Connecting to pgAdmin4 via a Web Browser:

You can connect using your preferred web browser by navigating to localhost:80

Reminder: During pgAdmin4 installation, we set the username as user@domain.com and the password as SuperSecret!

After accessing the panel, we can add our server. :)

Servers << Register << Server… (We only fill in the Name field. Then we move to the Connection section.)

Remember: we will use the username and password we created in the User and Password creation step.

Then let’s query it since it won’t accept 127.0.0.1.

The Host name/address will be our localhost, but since we’re running PostgreSQL in a Docker container, we need to find the IP address of the Docker container. No worries, though! :)

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

NOT: You must also need to correct the VirtualBox port forwarding section. Initially, we saved our own localhost, but we didn’t notice that the Docker container’s IP address is different. (I didn’t do this initially for you to see the error.) Let’s then correct the pgAdmin section we opened in the browser. Use 172.17.0.2.

And… ta… ta… ta… taaaa… :)

Creating a table and importing a sample .csv file.

We will use a sample dataset from Erkan Şirin ‘s GitHub repository: https://github.com/veribilimiokulu/udemy-apache-spark/blob/master/data/simple_data.csv

Let’s go to the section indicated by the red arrow on the panel and open the Query section. We’ll map the variables from the .csv file to the table. After pressing the F5 key to create the table, you should see “CREATE TABLE.”

1. Transferring the .csv file into the Docker container:

Let’s first find the path of the location where you transferred the .csv file using the “pwd” command.

2. Now let’s transfer the .csv file that you kept in the second location into the Docker container:

docker cp /home/maktas/postgresql_my/simple_data.csv some-postgres:/home/

After the copying process is done, we enter the container using the bash command. Since we connected to pgAdmin4 with myuser, after entering the Docker container,

docker exec -it some-postgres bash

we connect to the database like this:

psql -U myuser -d mydb -h localhost -p 5432

Then we transfer the datasets.

\copy mytable FROM '/home/simple_data.csv' DELIMITER ',' CSV HEADER;

When we query through pgAdmin 4, we see that everything is working smoothly…

I hope this has been useful and beneficial. If you have any questions, I’m just an email away. :) You can reach me at mucagriaktas@gmail.com

Cheers in peace :)