Generic Top

Get started with Spring 5 and Spring Boot 2, through the Learn Spring course:

>> CHECK OUT THE COURSE

1. Overview

In this tutorial, we'll learn to install PostgreSQL with Docker. Generally, we run a Docker container using the public Docker image. Similarly, we can pull pre-configured Docker images of the PostgreSQL database server from Docker Hub. Here, we'll also demonstrate how PostgreSQL can be installed, configured, and run on Docker.

First, we'll run a Docker container with a PostgreSQL database using the PostgreSQL Public Image. Later, we'll create a customized Dockerfile to install the PostgreSQL server in the Docker container. We'll also learn to backup and restore the database using the Docker container.

Let's deep dive into running a Docker container with the PostgreSQL database.

2. Understanding the PostgreSQL Database

Before we move forward to run the Docker container of the PostgreSQL database, let's first understand the PostgreSQL database. PostgreSQL is an Open-Source RDMS similar to MySQL. It is an object-oriented database, but we can process both structured and unstructured data.

The PostgreSQL database engine runs on various platforms, including Windows, Mac OS X, and Linux. It also provides advanced data types and performance optimization features to store and scale complicated database workloads.

3. Setup PostgreSQL Using the Public Image

To run a PostgreSQL using Docker, we first need to pull the postgres public image available on Docker Hub:

$ docker pull postgres
Using default tag: latest
latest: Pulling from library/postgres
1fe172e4850f: Pull complete 
...
c08147da7b54: Pull complete 
Digest: sha256:ab0be6280ada8549f45e6662ab4f00b7f601886fcd55c5976565d4636d87c8b2
Status: Downloaded newer image for postgres:latest
docker.io/library/postgres:latest

In the above command, we pulled the postgres latest stable image. We can also pull a particular version of the postgres image using the below command:

$ docker pull postgres:14.2
14.2: Pulling from library/postgres
Digest: sha256:e3d8179786b8f16d066b313f381484a92efb175d1ce8355dc180fee1d5fa70ec
Status: Downloaded newer image for postgres:14.2
docker.io/library/postgres:14.2

Now, we'll run the Docker container using postgres:latest image using the below command:

$ docker run -itd -e POSTGRES_USER=baeldung -e POSTGRES_PASSWORD=baeldung -p 5432:5432 -v /data:/var/lib/postgresql/data --name postgresql postgres
5aeda2b20a708296d22db4451d0ca57e8d23acbfe337be0dc9b526a33b302cf5

The above command uses environment variables POSTGRES_USER and POSTGRES_PASSWORD to set the username and password for the PostgreSQL database. By default, the PostgreSQL database runs on the 5432 port. We exposed the 5432 port on the host using the “-p 5432:5432” in the docker run command.
To back up the data, we also mounted the /var/lib/postgresql/data directory to the /data directory of the host machine of the postgres container.

psql is a command-line utility used to access PostgreSQL databases interactively. Let's now use the psql to connect with the database:

$ PGPASSWORD=baeldung psql -U baeldung 

In order to get the list out of all the databases, we'll use the command \l :

$ PGPASSWORD=baeldung psql -U baeldung -c '\l' 
                                    List of databases
    Name    |   Owner    | Encoding |  Collate   |   Ctype    |     Access privileges     
------------+------------+----------+------------+------------+---------------------------
 baeldung   | baeldung   | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres   | baeldung   | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0  | baeldung   | UTF8     | en_US.utf8 | en_US.utf8 | =c/baeldung            +
            |            |          |            |            | baeldung=CTc/baeldung
 template1  | baeldung   | UTF8     | en_US.utf8 | en_US.utf8 | =c/baeldung            +
            |            |          |            |            | baeldung=CTc/baeldung
(4 rows)

In the above output, we can get the detail of all the databases present on the PostgreSQL server.

4. Setup PostgreSQL Using Customised Dockerfile

We can also set up the PostgreSQL database server by creating a customized Dockerfile. Here we'll create a Dockerfile that will contain all the required commands to install Postgres using CentOS as the base image:

FROM centos:7
COPY startUpScript.sh /
RUN yum install -y epel-release maven wget \
&& yum clean all \
&& yum install -y  https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm \
&& yum install -y postgresql11-server postgresql11-contrib \
&& chown root /startUpScript.sh \
&& chgrp root /startUpScript.sh \
&& chmod 777 /startUpScript.sh
CMD ["/bin/bash","-c","/startUpScript.sh && tail -f /dev/null"]

In the above Dockerfile, we used startUpScript.sh to start the PostgreSQL database server on successful installation. Let's look into the startUpScript.sh file:

#!/bin/bash
su -l postgres -c /usr/pgsql-11/bin/initdb
su -l postgres -c "/usr/pgsql-11/bin/pg_ctl -D /var/lib/pgsql/11/data -l /tmp/pg_logfile start"
createdb -U postgres baeldung

In the startUpScript.sh, we first initialized the PostgreSQL database and then created a dummy database baeldung.

5. Install pgAdmin on Docker

So far, the PostgreSQL server is active and running on the 5432 port. Now, we'll install pgAdmin, a web-based user interface tool used to manage PostgreSQL databases and services. pgAdmin can be used to run SQL queries on PostgreSQL databases.

To perform all the queries from the UI, we can use the pgAdmin, and for that, we need to pull the pgAdmin image using the following command:

$ docker pull dpage/pgadmin4:latest
latest: Pulling from dpage/pgadmin4
40e059520d19: Pull complete 
...
6d23acfae6ef: Pull complete 
Digest: sha256:f820e5579857a7210599f998c818777a2f6f39172b50fbeb2faaa1a70413e9ac
Status: Downloaded newer image for dpage/pgadmin4:latest
docker.io/dpage/pgadmin4:latest

To demonstrate, let's run the container using the below command:

$ docker run --name pgadmin-baeldung -p 5051:80 -e "PGADMIN_DEFAULT_EMAIL=use[email protected]" -e "PGADMIN_DEFAULT_PASSWORD=baeldung" -d dpage/pgadmin4

In the above command, we provided the PGADMIN_DEFAULT_EMAIL and PGADMIN_DEFAULT_PASSWORD as an environment variable to the pgadmin-baeldung container:

We can easily access PostgreSQL databases using the pgAdmin GUI. In order to access the database, we have to set up a connection to the Postgres server using pgAdmin. We can do this by logging in pgAdmin.

6. BackUp and Restore the Data

In this section, we'll learn to backup and restore the data in PostgreSQL using Docker commands.

First, to back up the data, let's create a dummy database baeldung and a table baeldungauthor.

$ createdb -h localhost -p 5432 -U baeldung baeldung

The command to create a table is as follows:

CREATE TABLE baeldungauthor (
   AUTHOR_ID INT PRIMARY KEY     NOT NULL,
   AUTHOR_NAME           TEXT    NOT NULL,
   AUTHOR_AGE            INT     NOT NULL,
   AUTHOR_LEVEL        INT     NOT NULL
);

Let's list out the created table in the database:

psql -U baeldung -d baeldung -c "\d"
              List of relations
 Schema |      Name      | Type  |   Owner    
--------+----------------+-------+------------
 public | baedlungauthor | table | baeldung
(1 row)

Now use the below command to get the schema detail of table baeldungauthor:

psql -U baeldung -d baeldung -c "\d baedlungauthor"
              Table "public.baedlungauthor"
    Column    |  Type   | Collation | Nullable | Default 
--------------+---------+-----------+----------+---------
 author_id    | integer |           | not null | 
 author_name  | text    |           | not null | 
 author_age   | integer |           | not null | 
 author_level | integer |           | not null | 
Indexes:
    "baedlungauthor_pkey" PRIMARY KEY, btree (author_id)

Till now, we have created a database and a table. Let's look into the command to back up a database for a Docker container:

$ docker exec -t postgresql pg_dumpall -c -U baeldung > dump.sql

Here, in the above command, we used the pg_dumpall to backup the baeldung database. It is a standard PostgreSQL tool for backing up the database. In the command, we provided the username of the DB server to access the privileges.

Now let's check out the command to restore the database:

$ cat dump.sql | docker exec -i postgresql psql -U baeldung

Here, in short, we restored all the tables of the baeldung database using the psql command.

7. Conclusion

In this article, we learned to install the PostgreSQL database using the Docker container. We explored each step to pull, set up, and run a Docker container of Postgres. In addition, we explored both the ways to access the PostgreSQL database server. First, we explored the pgAdmin to access the PostgreSQL database server running on the Docker container. Later, we used psql to execute the queries against the databases in PostgreSQL.

To sum up, we ran the Docker container with the PostgreSQL database using the Postgres public image present on Docker Hub. We also created our customized Dockerfile to install the PostgreSQL server in the Docker container.

Finally, we looked into the backup and restoration of the data in the PostgreSQL database with the Docker container.

Generic bottom

Get started with Spring 5 and Spring Boot 2, through the Learn Spring course:

>> CHECK OUT THE COURSE
Generic footer banner
guest
0 Comments
Inline Feedbacks
View all comments