Docker helps create an isolated environment for various services, including databases. In this article, we will describe how to install PostgreSQL with Docker, configure it, and set up a database connection.
The main advantage of containerization is that it creates an isolated environment that helps avoid any dependency conflicts.
Let's see what dependent packages are installed on a Debian server along with PostgreSQL.
We ordered a server from Hostman, connected to it via SSH, and now run this command to install PostgreSQL:
sudo apt install postgresql postgresql-contrib
When executing the command, pay attention to the lines at the beginning:
The following additional packages will be installed:
libllvm11 libpq5 libsensors-config libsensors5 libxslt1.1 libz3-4 postgresql-13 postgresql-client-13
Postgres download comes with six more libraries: libllvm11
, libpq5
, libsensors-config
, libsensors5
, libxslt1.1
and libz3-4
.
Now let's look at the list of PostgreSQL 13 dependencies:
sudo apt-cache depends postgresql-13
We get a list of 25 dependencies, only one of which is recommended, the rest are required.
If there is no required package or we have the wrong version, it leads to conflicts. Postgres run in Docker helps eliminate most of the problems. All dependencies are stored inside the container. When we download the image, we get a fully compatible environment, ready to go.
This leads to another advantage: reproducibility. For example, a developer wants to test a new feature in a product. Using an isolated environment, he can deploy a working environment on a local machine in a few clicks.
And these are not all the advantages of PostgreSQL in Docker. With containers, fault tolerance also increases. If the server stops working, you can run the container on another machine and not worry about configuring the parameters.
Let's see how PostgreSQL is installed on Ubuntu. First, let's install Docker.
Add a key to work with Docker Hub:
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
Add the repository to the list of local ones and install Docker:
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"
sudo apt install docker-ce
Launch Docker and configure it to start at boot using the systemctl system utility:
sudo systemctl start docker
sudo systemctl enable docker
Next, create a Docker Postgres Volume, a directory for storing data.
mkdir -p $HOME/docker/volumes/postgres
Install the PostgreSQL image from Docker Hub using the docker pull
command:
sudo docker pull postgres
PostgreSQL installation is complete.
Setup and manage your cloud database with ease
Run the command:
sudo docker run --rm --name hostman-pgdocker -e POSTGRES_PASSWORD=hostman -e POSTGRES_USER=hostman -e POSTGRES_DB=hostman -d -p 5531:5531 -v $HOME/docker/volumes/postgres:/var/lib/ postgresql/datapostgres
Here we use the following flags:
--rm
tells the system to delete the container and its file system after the container is stopped. It helps saving server space.--name
is the container name, which must be unique within one server, regardless of status.-e
points to the environment variables: name and password of the superuser, default database name.-d
launches the container in background (offline) mode. After the launch, control is returned to the user.-p
binds the Postgres port to the server port.-v
creates a mount point.When executing the command, the error "Error starting userland proxy: listen tcp4 0.0.0.0:5531: bind: address already in use" may occur. In this case, check what process is occupying port 5531:
sudo ss -lptn' sport = :5531'
To terminate the process, use the kill
command. Specify the PID of the process that occupies port 5531. In our case, the PID os 2592:
sudo kill 2592
After fixing the error, try to restart Docker:
sudo docker run --rm --name hostman-pgdocker -e POSTGRES_PASSWORD=hostman -e POSTGRES_USER=hostman -e POSTGRES_DB=hostman -d -p 5531:5531 -v $HOME/docker/volumes/postgres:/var/lib/ postgresql/datapostgres
However, the command is rarely used this way. It is much more convenient to store the configuration in a Dockerfile. There you can specify Docker ADD
, CMD
, LABEL
, ENV
and other instructions to be applied at startup.
First, let's connect to the isolated environment using the psql
utility. It is included in the postgresql-client
. Run:
sudo apt install postgresql-client
Connect to the database and execute a test query:
psql -h 127.0.0.1 -U hostman -d hostman
The output should be:
Password for user hostman:
Type "help" for help.
psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1), server 13.2 (Debian 13.2-1.pgdg100+1))
hostman=#
A prompt with a username indicates that the connection has been established correctly.
You can also connect to the container using docker exec
with the following keys:
sudo docker exec -it hostman-pgdocker psql -U hostman
The result is the same: an invitation to enter queries.
psql (12.6 (Debian 13.2-1.pgdg100+1))
Type "help" for help.
hostman=#
In the command above we used the arguments:
-i
activates interactive work with the terminal.-t
launches a pseudo-terminal.hostman-pogdocker
is the name of the container to which queries will be made.psql
launches the utility for connecting to the database.-U
is a user name for connecting to the database.To test the connection, let's create a new table, fill it with data and run the query:
hostman=# create table cities (name varchar(80));
CREATE TABLE
hostman=# insert into cities values ('Seattle');
INSERT 0 1
hostman=# select * from cities;
name
--------
Seattle
(1 row)
After completing the request, stop the container with the docker stop
command:
sudo docker stop hostman-pgdocker
This is where we discover a security hole. The files and processes that the container creates are owned by the internal postgres
user. Due to the lack of a namespace within the container, the UID and GID can be arbitrary.
The problem is that there may be other privileged users and groups on the server itself with the same UID and GID. This potentially leads to a situation where the user can access host directories and kill any processes. You can avoid this situation by specifying the USERMAP_UID
and USERMAP_GID
variables at startup.
Example command:
sudo docker run --rm --name hostman-pgdocker -e POSTGRES_PASSWORD=hostman -e POSTGRES_USER=hostman -e POSTGRES_DB=hostman -e USERMAP_UID=999 -e USERMAP_GID=999 -d -p 5432:5432 -v $HOME/docker /volumes/postgres:/var/lib/postgresql/data postgres
Let's consider another option for launching PostgreSQL: using docker-compose
.
First, create a YAML file:
version: '3.1'
volumes:
pg_hostman:
services:
pg_db:
image:postgres
restart: always
environment:
- POSTGRES_PASSWORD=hostman
- POSTGRES_USER=hostman
- POSTGRES_DB=hostman
volumes:
- pg_project:/var/lib/postgresql/data
ports:
- ${POSTGRES_PORT:-5531}:5531
Then install Docker Compose:
sudo apt install docker-compose
And run it:
sudo docker-compose up -d
Check that you can connect to the database:
psql -h 192.168.0.4 -U hostman -d hostman
Output example:
Password for user hostman:
psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1), server 13.2 (Debian 13.2-1.pgdg100+1))
Type "help" for help.
hostman=#
If a prompt to enter a request appears in the terminal, then the connection was successful.
To stop the container, run:
sudo docker-compose stop
To quickly find out that something is wrong with the isolated environment, we use healthcheck.
Let's set up an automatic restart when problems are detected.
Example PostgreSQL settings in a YAML file:
version: "3.9"
services:
postgres:
image: postgres:13.3
environment:
POSTGRES_DB: "twdb"
POSTGRES_USER: "twpguser"
POSTGRES_PASSWORD: "785564tw"
PGDATA: "/var/lib/postgresql/data/pgdata"
volumes:
- ../2. Init Database:/docker-entrypoint-initdb.d
- .:/var/lib/postgresql/data
ports:
- "5531:5531"
healthcheck:
test: ["CMD-SHELL", "pg_isready -U twpguser -d twdb"]
interval: 15s
timeout: 10s
retries: 7
start_period: 12s
restart: unless-stopped
deploy:
resources:
limits:
cpus: '1'
memory: 4GB
Pay attention to the resources
section. It allows you to limit resources for the database. This approach makes sense for local launch and experimentation.
Deploy your apps with a Dockerfile easily
In this guide, we studied the Docker and Docker Compose methods to run PostgreSQL within a Docker container. We also discussed deploying the container and connecting to the local machine.
Depending on the application architecture, you can deploy the database using Docker or Docker Compose. The second scenario will be more common, so pay more attention to the configuration that needs to be described in the docker-compose.yaml file.