Docker - Setting PostgreSql

---

---

PostgreSQL, famously known as Posgres, is a modern, free-to-use, and open-source relational database management system (RDBMS). This software focuses on trustworthiness, performance, and robustness. The uniqueness of this database system is that it supports both SQL queries and JSON files.

Docker provides a powerful solution for containerizing the Postgres database. This helps to manage, deploy, and scale the database. In this chapter, we will learn about dockerizing the Postgres database in an isolated environment.

Prerequisites for Dockerizing PostgreSql

Before you start setting up Postgres into docker, the following concepts need to be revised −

• Basic information about Docker − Knowledge about Docker concepts such as containers, docker files, and images.
• Postgres Basics − Basic understanding of Postgres and how to handle users and databases.
• Docker Installation − Make sure that the docker is up and running. If not installed yet on your system, you can refer to Docker's official website.
• Existence of Text Editor − Ensure that a text editor such as Vscode, Notepad++, or Sublime has been installed on the system.
• CLI (Command Line Interface) − Comfort with handling CLI commands is extremely necessary.

Steps to Configure and Connect to PostgreSQL Database in Docker

Follow the steps given below to configure and connect to PostgreSQL in Docker −

Step 1: To pull the Postgres Image from the official hub

To begin with, all we have to do is pull the official Postgres image from the dockers website. This can be done by opening a command prompt or terminal and by running the following command.

$ docker pull postgres

This command will pull the latest image of the Postgres to your local environment.

Step 2: To create a Postgres container and run it

Now its time to create a container using the Postgres image of the docker. To achieve this use the following command −

$ docker run -d name my-postgres-container -p 5432:5432 -e POSTGRES_PASSWORD=password postgres

Let's understand the command in detail −

• Docker run − This command is used to start the Docker container.
• -d − This flag indicates the detached mode. It means that the container runs in the background without interfering with other processes.
• name − This flag is used to name the container. You can name the container whatever you like.
• -p 5432:5432 − This is the port flag. It binds the machine port and the container port together so that they can communicate with each other with ease. 5432 is the default port of Postgres.
• -e POSTGRES_PASSWORD="your password" − This flag is used to set an environment variable. In this case, we are setting the environment variable known as POSTGRES_PASSWORD inside the container to the value of the password indicated by your password
• postgres − This is the name of the docker image being used in your local machine for the container.

Step 3: To verify that the container is running properly

To check whether the container is running properly, we can use the following command −

$ docker ps

This will list the docker containers or processes. Here ps stands for the status of the process.

The output of the above command will be a table giving information about the container ID, image, command, created, status, ports, and names.

Step 4: To connect to the Postgres Database

Now that the Postgres container is up and running, lets connect to the database. This can be achieved by running the following psql command inside the container.

$ docker exec -it my-postgres-container psql -U postgres

Lets break down the above command to understand better −

• docker exec − This is used to execute a command inside the docker environment
• -it − This flag is used to open an interactive terminal inside the docker container.
• Postgres − This is the name of the running docker
• psql − This is the client for the Postgres which helps you to interact with the database system.
• -U postgres − This defines the user the container is connected to as with the Postgres server.

Step 5: To create a database inside the Postgres environment

Once we attain a successful connection with the Postgres server, let us proceed with the creation of the database within it. To create a new database the following SQL command can be used −

CREATE DATABASE <database name>

In the above command, the <database name> can be replaced by any suitable database name defined by the user.

To list all the databases present, we can make use of the following command −

\l

To get out of the display mode and quit it, we can use the following command −

\q

To connect to any database, the following command can be used −

\c postgres

After being connected to the database, we can run SQL queries and create tables inside the database to get the desired outputs.

Step 6. To create a table in the database

Now that our database is ready, lets create a table inside it and insert some sample data rows into it −

CREATE TABLE people_details (
   id SERIAL PRIMARY KEY,
   name VARCHAR(50),
   email VARCHAR(80)
);

INSERT INTO people (name, email) VALUES (myname, myemail@email.com);

Step 7: Query the Data

To ensure that data has been successfully inserted into the table we can make use of the following SQL command −

SELECT * FROM <table name>

Let's replace the <table name> with people_details. This should list all the rows present in the table people_details as shown below −

Conclusion

In this chapter, we explained how to dockerize a Postgres server and how to build and run it inside the container with ease. This environment is consistent, portable, and easily deployable. This setup can be easily shared with other users and integrated with any other large docker-related infrastructures.

FAQs on Dockerizing a Postgres Server

In this section, we have collected a couple of Frequently Asked Questions on how to dockerize a Postgres server, followed by their answers.

1. How to upgrade a Postgres version in the docker?

We can upgrade the Postgres version in Docker by connecting to the shell of the container using the Docker exec command and manually updating the version.

We can also pull the latest image of the docker using the command "docker pull postgres". You can also specify the version in the Dockerfile that you want to upgrade to and rebuild the image.

2. How to troubleshoot Postgres-related errors in Docker?

We can troubleshoot Postgres-related errors in Docker by checking the container logs using the command "docker logs". Ensure that the container has enough CPU and storage memory available. It is important to check that no other service is running on port 5432 on your machine.