Setting up PostgreSQL

To set up a PostgreSQL server with Docker, first download and execute the postgres official Docker image to run PostgreSQL in a Docker container. After that, access the container interactive shell, connect to the PostgreSQL server, and start using PostgreSQL for data management. Users can also connect and access the PostgreSQL server with the pgAdmin4 web server.

Let’s first discuss what PostgreSQL is and then dive into methods to set up and use PostgreSQL with Docker.

What is PostgreSQL?

PostgreSQL is a robust, open-source, and powerful object-relational database management system(ORDBMS). In Postgres, the user can store data in objects and it is also emphasized with SQL compliance. It can store more data than SQL (typical database system).

In application or software development, the developer usually needs to manage the application data. For this purpose, PostgreSQL is the best choice that uses simple SQL queries to manage and store the project data.

How to Set up PostgreSQL With Docker?

Docker is a well-like containerization tool that allows us to develop and deploy software and applications in isolated environments inside the containers. For project management, Docker offers a wide range of management systems and PostgreSQL is one of them. To set up and use the PostgreSQL server in the Docker container, take a look at the provided steps.

Step 1: Download “postgres” Image

First, download the “postgres” official PostgreSQL docker image from Docker Hub through the “docker pull postgres” command:

docker pull postgres

Step 2: Set up PostgreSQL in Container

Next, set up and run the PostgreSQL in a Docker container. For this purpose, execute the mentioned command:

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

In the given command:

• The “-d” flag is utilized to execute the container as a backend service (in detached mode).
• “–name” is utilized to set the container name in which the PostgreSQL server will execute. For this purpose, we have set “postgres” as a container name.
• The “-p” option will publish the “postgres” container on the default port of PostgreSQL which is “5432”.
• “-e” is utilized to set the environment variables in the container. Here, we have set the “POSTGRES_PASSWORD” variable to set the PostgreSQL password. The default user of PostgreSQL is “postgres”:

For confirmation, list down the Docker executing containers through the “docker ps” command:

docker ps

Here, the “postgres” container is successfully executing and accessible on port “5432”:

Step 3: Access Container Interactive Shell

Now, access the postgres container interactive shell to run commands inside the container:

docker exec -it postgres sh

Here, the “-it” option in which “i” is used to access the container shell interactively and “t” is used to open the TTY pseudo terminal to run commands inside the container: 

Step 4: Connect to PostgreSQL Server

Connect to the PostgreSQL server, for this purpose, use the below command:

psql -h localhost -U postgres

Here, “-h” is used to specify the hostname, and “-U” is used to specify the user:

Step 5: View Databases

View or list down all databases in the PostgreSQL server, use the “\l” command:

\l

Here, you can see the “postgres” database is automatically created in the PostgreSQL database server:

Step 6: Use PostgreSQL to Create and Manage Database

Now, use the PostgreSQL server to create a new database and store data inside the database. For this purpose, first create a database, then connect to it. After that, create a new table and store records in it. Lastly, view the table data. For demonstration, go through the given procedures and SQL queries:

Create New Database

First, create a new database in the PostgreSQL ORDBMS. For this purpose, execute the “CREATE DATABASE <database name>;” query:

CREATE DATABASE handson_users;

Here, we have created the “handson_users” database that will store user data:

Connect to New Database

Next, connect to the newly created “handson_users” database through “\c <database name>;” query:

\c handson_users;

Create New Table

In order to store data in the table, create a new table and specify proper constraints through “CREATE TABLE <table name>( <column name> <Constraint>, <column name> <Constraint>…);” query:

CREATE TABLE handson_emp(ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, DESIGNATION TEXT NOT NULL);

In the above query, we have created a “handson_emp” table that will store “ID”, “Name”, and, “Designation”: 

Insert Values

After creating the table, now insert the value using “INSERT INTO <table name> VALUES ( Id, ‘name’, ‘designation’);” query:

INSERT INTO handson_emp VALUES (1, 'Jenny', 'Senior-Author');
INSERT INTO handson_emp VALUES (2, 'John', 'Administrator');

In the above code block, we have stored the two records (2 rows):

View Table

For confirmation, view the table data through the “SELECT * FROM <table name>;” query. The “*” will fetch and show all records of the specified table:

SELECT * FROM handson_emp;

The below output shows that we have effectively stored data in the “handson_users” database through PostgreSQL ORDBMS:

Step 7: Exit PostgreSQL and Container Shell

To exit the PostgreSQL server shell, simply use the “exit” command, and to exit the “postgres” container’s interactive shell, again use the “exit” command:

exit
exit

That is how users can set up PostgreSQL with Docker. To connect the PostgreSQL with the pgAdmin management tool to interactively manage the PostgreSQL’s databases, follow the below section.

Bonus Tip: How to Connect PostgreSQL Server With pgAdmin4 in Docker

The pgAdmin is a Postgres management tool that provides an integrative and user-friendly Graphical user interface through which PostgreSQL users can easily create, maintain, and use the database objects. In this regard, Docker offers an official pgAdmin4 image to set up and run pgAdmin inside the Docker container.

To set up the pgAdmin tool in Docker and to connect PostgreSQL to the pgAdmin management tool, follow the below step-by-step procedure.

Step 1: Download pgAdmin4 Image

First, pull the pgAdmin4 “dpage/pgadmin4:latest” image through the “docker pull” command:

docker pull dpage/pgadmin4:latest

Here, we have pulled the latest image for pgAdmin4:

Step 2: Set up and Run PgAdmin4 Container

Execute the pgAdmin management tool inside the container through the following command:

docker run -d --name pgadmin -p 82:80 -e 'PGADMIN_DEFAULT_EMAIL=user@handson.com' -e 'PGADMIN_DEFAULT_PASSWORD=handson' dpage/pgadmin4

In the above command, we have set the container name as “pgadmin” and the default port of pgAdmin is “82”. We have set two environment variables “PGADMIN_DEFAULT_EMAIL” and “PGADMIN_DEFAULT_PASSWORD”  through the “-e” option. These credentials will be used to access the pgAdmin 4 user interface:

For confirmation, list down the containers:

docker ps

Here, the pgadmin container is effectively executing and is exposed on port “82”:

Step 3: Login to pgAdmin

Open the browser and navigate to the “http://localhost:82”  URL. This will open the pgAdmin login interface. Provide the email and password that is set in the above step in the “PGADMIN_DEFAULT_EMAIL” and “PGADMIN_DEFAULT_PASSWORD” environment variables. Then, press the “Login” button:

Here, you can see we have effectively set and accessed the pgAdmin user interface with Docker:

Step 4: Inspect PostgreSQL Container

In the next step, inspect the container in which the PostgreSQL is running. In our case, we will inspect the “postgres” container through the “docker inspect <container name/container id>” command:

docker inspect postgres

Scroll down to “Networks” information and note the IP address of the container:

Step 5: Connect PostgreSQL Server With pgAdmin4

To connect the PostgreSQL server with pgAdmin, move to the pgAdmin user interface, right-click on the “Servers”, and choose the “Register” option to register the new server. From the sub-context menu, choose the “Server” option to connect to the PostgreSQL server:

From the “Register-Server” wizard, set the name of the server. For this purpose, we have set “Postgres”:

Navigate to the “Connection” menu, and add the following information:

• Add the noted IP address in the “Host name/address” field. 
• Then, provide the port on which the PostgreSQL server is accessible. The default port is “5432”. 
• Next, provide the username and password. In the above section, the default user is “postgres” and we have set the password as “handson”.
• Lastly, hit the “Save” button to save the connection information and to connect to PostgreSQL server:

Here, the below output shows that we have successfully connected the PostgreSQL server with pgAdmin. Currently, the “Postgres” server contains two databases one is “handson_users” which is created in the above section and the other is the “postgres” default database:

We have covered how to set up PostgreSQL in a Docker container and how to connect the PostgreSQL server with pgAdmin4.

Conclusion

To set up the PostgreSQL server in Docker, first pull the official “postgres” Docker image. After that, execute the image to set up and run the PostgreSQL server inside the container through the “docker run -d –name <cont name> -p 5432:5432 -e POSTGRES_PASSWORD=<password> postgres” command. Next, access the container interactive shell, connect to the PostgreSQL server, and start managing data inside the PostgreSQL ORDBMS. Additionally, users can set up the pdAdmin management tool and connect the PostgreSQL server to manage the PostgreSQL databases interactively. This blog has covered the techniques to set up PostgreSQL in a Docker container and connect the PostgreSQL server with pgAdmin4.