Docker PostgreSQL Basics

Content

  • Introduction

  • Installing Docker

  • Installing pgAdmin

  • Deploying a PostgreSQL container

  • Console connection

  • Creating a local server

  • Transferring files from and to a container

  • Executing SQL Scripts

Introduction

This article discusses some of the features of getting started with DBMS PostgreSQL in Docker, which can be useful to the reader if he is just starting to learn the art of working with containers. I was prompted to write this work by the lack of articles that completely cover the basic things at the beginning of working with PostgreSQL in Docker. In this article, we will not go into detail about the principles of PostgreSQL, but we will write a few basic queries in order to test the operation of the container.

Installing Docker

In order to install Docker on your personal computer, you need to register on the official website… The registration process itself is standard and there should be no problems with it. After you have registered on the site, you need to download the Docker Desktop application. At the moment, the download button looks like this:

Application download button
Application download button

Once you’ve downloaded the installer file, run it to install Docker Desktop on your computer. The installation procedure is relatively standard, so I will not comment on it in detail.

Successful installation window
Successful installation window

When you start the program for the first time, you will be prompted to confirm the user agreement, you need to confirm it in order to start working with Docker:

After confirmation, a Docker Desktop window will open:

Installing pgAdmin

pgAdmin is an application that allows you to visualize working with databases. Its use is optional, but in my experience it can make your life a lot easier, especially if you are not used to using the console on a regular basis. You can download the installer of this program either from the official site of pgAdmin, or by this link. The installation procedure is standard and if you follow the instructions of the installer, there should be no problems. When you start pgAdmin for the first time, you will be prompted for a master password. This password will need to be remembered, as it will be used when connecting to the DBMS.

Deploying a PostgreSQL container

In order to start a PostgreSQL container, we need to get an image (image), which is the base for the container being created. This image will contain the basic tools of the DBMS, which will allow us to work with databases without installing PostgreSQL on the system. This, in fact, is one of the advantages of containers. The official PostgreSQL image can be obtained by executing the following command on the command line

docker pull postgres

After the command finishes executing, check out Docker Desktop. If the image was loaded successfully, then the images tab will look like this:

As you can see, the postgres image is now in there.

Launching and connecting using the console

In order to create a new container, you need to enter the following command in the command line:

docker run -p 5432:5432 -d --name=postgres_cont -e POSTGRES_PASSWORD=1234 -e POSTGRES_USER=postgres postgres

-p – connection port, in our case it is localhost
-d – the option to detach from the image so that the container can run in the background
-e – configure passwords and user

After running this command, the container appears in Docker Desktop:

In order to finish working with the container, you can either click on the stop button, or use the following command:

docker stop [имя контейнера]

Connecting to the database in pgAdmin

In order to connect to the database in pgAdmin, you need to create a new server. To do this, right-click on the line labeled Server on the left side of the program window, select Create -> Server from the drop-down menu:

In order to read the container port (5432), go to the Connections tab and enter “localhost” in the Host / name adress column:

You also need to set a password. The saved server will look like this:

As a test, let’s create a database via the console and connect to it. To do this, we need to run bash in a container, we can do this using the following command:

docker exec -it [имя контейнера] bash

Then we need to start the PostgreSQL console, this is done in this way:

psql -U postgres

Now let’s check the databases that currently exist in the container space:

postgres=# l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

In order to create a new empty database, use the following command:

CREATE DATABASE TEST_BASE;

The command is executed directly in the console, and as a result of its execution we get a new base:

postgres-# l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 test_base | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
(4 rows)

In order to connect to this database, we use the c command:

postgres-# c test_base
You are now connected to database "test_base" as user "postgres".

Let’s check pgAdmin:

The test base is present in the list of bases, which indicates its successful creation and connection. As a bonus, I will say that using the d command, you can view the tables in the database to which you are currently connected:

test_base=# CREATE TABLE TEST_TABLE (test_1 text, test_2 text, test_3 text);
CREATE TABLE
test_base=# d
           List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | test_table | table | postgres
(1 row)

Remember to drop the database after testing is complete. This can be done either in pgAdmin by right-clicking on the database name and selecting the Delete / Drop option, or using this command:

DROP DATABASE TEST_TABLE;

File exchange between container and host computer

The cp command is used to transfer individual files. In case of transferring files to a container:

docker cp foo.txt <container_id>:/file/path/within/container/

In case of transferring a file from a container:

docker cp <containerId>:/file/path/within/container/foo.txt /host/path/target

To transfer multiple files, it is possible to create shared folders and use volumes, but we will not cover this in this article.

Executing SQL Scripts

To execute a psql script, you must have a script file with the .psql permission and loaded into the container file system. To execute the file, run the following command on the command line with the psql console open:

i path_to_sql_file

Similar Posts

Leave a Reply