PostgreSQL Administration, Part 1 - Installing and Running PostgreSQL in Docker

I’m opening a new series of articles: we’ll figure out PostgreSQL administration from scratch, from installation to replication, and in the first lesson we’ll set up the server in the official Docker image 🐧.

Preamble

Such materials are traditionally started with a definition and historical background. We’ll follow this tradition.

What is a DBMS and what is PostgreSQL

A DBMS (database management system) is software that stores structured data, provides concurrent access to it, maintains integrity, and responds to queries through the SQL language. Simply put, it’s a reliable intermediary between your data and the applications that need that data.

PostgreSQL is a free object-relational DBMS developed by an international open-source community (PostgreSQL license, close in spirit to MIT/BSD). Among open-source DBMSs, it stands out with arguably the richest out-of-the-box functionality - full transactions, flexible type system, JSON, full-text search, dozens of index types, and an extension mechanism through which you can add practically anything to the database, from geospatial data (PostGIS) to vector search (pgvector).

A bit of history

The story begins in 1986 at UC Berkeley: Michael Stonebraker, who had already created the Ingres DBMS by that time, launches the Postgres research project - “Post-Ingres”, an attempt to create a DBMS with support for user-defined data types and rules. In 1994, the project acquired its own SQL interpreter and got the name Postgres95, and in 1996, with the transition to an open development model, it was renamed PostgreSQL - to explicitly reflect SQL support. Since then, nearly three dozen major versions have been released, and the project itself follows a predictable annual release cycle.

Contribution of Russian developers

It’s worth noting separately that Russian developers have also left a notable mark on PostgreSQL development. Oleg Bartunov and Fedor (Theodore) Sigaev from Moscow State University are the authors of the GiST- and GIN-index infrastructure, full-text search (tsearch2), the hstore type and a number of other extensions without which it’s hard to imagine modern PostgreSQL. Alexander Korotkov is an active member of the PostgreSQL Core Team (the highest governing body of the project consisting of just a few people) and has made significant contributions to the query planner and index access methods. And Postgres Professional, which grew out of the same scientific group, is one of the largest corporate contributors to the project, maintaining its own Postgres Pro build and regularly sending patches upstream.

PostgreSQL today is the de facto standard choice for both your own infrastructure and cloud services, and sooner or later any Linux administrator has to manage it, even if there’s a separate DBA on staff. This series of articles aims to improve mutual understanding between different specialists so they can speak a “common language”.

This is also a way for me (and hopefully for you) to learn or refresh knowledge about how PostgreSQL works internally, how to configure it, maintain it, back it up and replicate it - while not forgetting about SQL itself, because without it, DBMS administration is rather pointless.

The format will be like this: each lesson is a separate article, from simple to complex. We’ll roughly go through installation, working with psql and graphical clients, architecture and MVCC, configuration, maintenance (VACUUM, WAL), data storage, system catalog, monitoring, access rights, backups and replication. Along the way, something will definitely be added or removed.

The first difference from classical courses: our working environment will not be a “live” PostgreSQL installed from packages directly on the host, but the official Docker image. First, it’s closer to how PostgreSQL is actually deployed in 2026. Second, for educational purposes it’s simply more convenient: if we break the cluster with an experiment, we just delete the volume and bring it up again in 10 seconds, without reinstalling the system.

The example data for practice (tables, queries, homework assignments 😀) will be built around a fictional infrastructure - something like server and data center accounting for a hypothetical hosting company. I called the database simply raven. I’ll show the table schema itself in one of the next lessons when we get to CREATE TABLE.

PostgreSQL DBMS Structure

Initial Data

Software used in the article:

All commands are executed on a Linux host with Docker Engine already installed and the docker compose plugin. The host distribution doesn’t matter - the container behaves the same whether on Debian or any other Linux, and that’s the point of containerization.

Environment Preparation

Check that Docker and the Compose plugin are in place:

sudo docker version

sudo docker compose version

Create the project directory:

sudo install -m 700 -d /etc/compose/postgres

Choosing a PostgreSQL Image

On Docker Hub, the official postgres image is built in two main variants: regular, based on Debian, and -alpine, based on Alpine Linux. The Alpine variant is noticeably lighter, but uses musl instead of glibc, which periodically causes locale and collation issues - and we still have a lesson ahead about tablespaces and low-level storage, where these nuances matter. So for the course I’m using the regular image, without -alpine.

The version tag is 18, the current stable branch of PostgreSQL at the time of writing. If by the time you read this, 19 has been released - within the scope of our educational environment you can safely use it, there won’t be any significant differences for our lessons.

Running PostgreSQL in Docker Compose

Create the compose file:

sudo vim /etc/compose/postgres/compose.yaml

Fill it with:

---
# https://hub.docker.com/_/postgres

networks:
  postgres_net:
    name: postgres_net
    driver: bridge
    driver_opts:
      com.docker.network.bridge.name: br-postgres

volumes:
  postgres_data:
    name: postgres_data

services:
  postgres:
    image: postgres:18
    container_name: postgres
    restart: unless-stopped
    stop_grace_period: 1m
    cpus: 2
    mem_limit: 2G
    hostname: postgres
    env_file: ./.env
    networks:
      - postgres_net
    ports:
      - "127.0.0.1:5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - /etc/timezone:/etc/timezone:ro
      - /etc/localtime:/etc/localtime:ro
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U $$POSTGRES_USER -d $$POSTGRES_DB"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 10s

Port 5432 is published only on 127.0.0.1 - this is sufficient for an educational environment, and it’s better not to expose the database externally without critical need.

Create the env file:

sudo vim /etc/compose/postgres/.env

Fill it with:

# https://hub.docker.com/_/postgres

TZ=Europe/Moscow
POSTGRES_USER=ivan
POSTGRES_PASSWORD=r4ven_me
POSTGRES_DB=raven
PGDATA=/var/lib/postgresql/data/pgdata

Where:

• TZ - container timezone;
• POSTGRES_USER - cluster superuser, created on first initialization;
• POSTGRES_PASSWORD - its password;
• POSTGRES_DB - database that will be created by default (we’ll work in it throughout the course);
• PGDATA - subdirectory inside the volume for cluster files. Without it, initdb on first run sometimes complains about the system lost+found that Docker might create at the root of the volume.

Start the stack:

sudo docker compose -f /etc/compose/postgres/compose.yaml up -d

sudo docker compose -f /etc/compose/postgres/compose.yaml logs -f

Check:

sudo docker compose -f /etc/compose/postgres/compose.yaml ps

If everything is normal, you should see output like this:

Now let’s set up autostart and management of our DBMS using Systemd:

sudo tee /etc/compose/postgres/postgres.service << EOF
[Unit]
Description=PostgreSQL course lab service
Requires=docker.service
After=docker.service

[Service]
Restart=always
RestartSec=5
User=root
Group=root
WorkingDirectory=/etc/compose/postgres
ExecStart=/usr/bin/docker compose up --remove-orphans
ExecStop=/usr/bin/docker compose down

[Install]
WantedBy=multi-user.target
EOF

sudo chmod 600 /etc/compose/postgres/postgres.service

sudo ln -vs /etc/compose/postgres/postgres.service /etc/systemd/system

sudo systemctl daemon-reload

sudo systemctl enable --now postgres

sudo systemctl status postgres

Now you can manage the postgres service with the systemctl utility, and the service log with journalctl:

sudo systemctl stop postgres
sudo systemctl start postgres
sudo systemctl restart postgres
sudo systemctl status postgres
sudo journalctl -fu postgres

Testing the Connection

You can look at the DBMS in two ways - through the console psql and through a graphical client. In the next lesson we’ll cover both tools in detail, but for now let’s just make sure the server is alive and responding.

Via psql

psql is already inside the image, no need to install anything separately - we execute it directly in the container:

sudo docker compose -f /etc/compose/postgres/compose.yaml exec postgres psql -U ivan -d raven -c "SELECT version();"

If everything is normal, you should see output like this:

Via DBeaver

As a graphical client in this course I’ll use DBeaver Community - it’s open-source, free, cross-platform, and can do practically everything needed for PostgreSQL administration. Download the required version, install and run it.

Next, create a new database connection (New Database Connection → PostgreSQL), specifying the following parameters:

• Host - 127.0.0.1 (or the address of your server if Docker is running not locally);
• Port - 5432;
• Database - raven;
• Username - ivan;
• Password - r4ven_me (or what you specified in POSTGRES_PASSWORD, you can check it like this: grep POSTGRES_PASSWORD ./.env).

Test the connection with the Test Connection... button:

Then open the SQL editor and execute the same query as in psql:

SELECT version();
SELECT current_date;

Perfect! The server is up, accessible from both console and GUI - let’s move on.

Resetting the Environment for Experiments

It’s worth saying separately about how to quickly return the environment to a clean state - over the course you’ll probably want to intentionally break everything and start over more than once:

sudo systemctl stop postgres

sudo systemctl disable postgres

sudo docker image rm postgres:18

sudo docker volume rm postgres_data

sudo docker network rm postgres_net

sudo rm -rf /etc/compose/postgres

Possible Issues

• port 5432 is already in use

Error response from daemon: driver failed programming external connectivity on endpoint postgres: Bind for 127.0.0.1:5432 failed: port is already allocated

Most often this means that there’s already a “live” PostgreSQL running on the host, installed from packages. Either stop it, or change the publication port in compose.yaml, for example to 127.0.0.1:5433:5432 (don’t forget to also change the port in the DBeaver connection).

• password authentication failed for user “ivan”

If you changed POSTGRES_PASSWORD in .env after the first startup - this is expected, see the warning above about cluster initialization. The easiest solution is either to change the password from inside (ALTER ROLE ivan WITH PASSWORD '...'), or if you don’t care about the data, recreate the volume.

Afterword

An expected simple first step, but the foundation is important - the whole course will revolve around this container. Fortunately, rebuilding the training environment is easy.

In the next lesson, we’ll take a detailed look at the psql utility, and along the way get more hands-on with the DBeaver graphical client.

For extracurricular work, I recommend watching a very interesting interview with Ivan Panchenko, co-founder of Postgres Pro. The video discusses the ideology of open source, the contribution of our compatriots to the development of the Postgres project, a comparison with Oracle, and much more. It’s very interesting to follow Ivan’s train of thought regarding the use of the open-source software model for business. And of course, it’s interesting to hear about the various nuances of this model, as well as the history of the DBMS’s development.

Overall, I recommend it 👍.

The video is over an hour and a half long, but it’s an easy watch 😌.

Thanks for reading. Good luck learning PostgreSQL! 🐧

References

• Official PostgreSQL image on Docker Hub
• PostgreSQL Documentation
• My article on installing Docker Engine on Debian
• DBeaver Community Edition

👨‍💻And…

Don’t forget about our Telegram channel 📱 and chat

Or maybe you want to become a co-author? Then click here🔗

💬 All the best ✌️

That should be it. If not, check the logs 🙂