Introduction

PostgreSQL is a powerful, stable open-source relational database with high concurrency handling and robust transaction mechanisms, widely used in data-intensive systems.

In actual production and development processes, single-node deployment is often used for development testing environments or small to medium-scale business scenarios due to its simple architecture, fast deployment, and low operational costs.

Based on the Ubuntu system, this article details the installation and basic configuration process of a PostgreSQL single node, helping readers quickly complete the database environment setup and provide a foundation for subsequent business access or high-availability architecture expansion.

---

Prerequisites

Create Directory

Create the project directory and enter it

<b>mkdir -p /data/workspace/install-postgres && cd /data/workspace/install-postgres</b>

---

Deployment

Prepare Files

Below is the required docker-compose.yaml file

1. Need to enable postgressql.log
2. Need to enable postgres_backup weekly rotation

<b># version: "3.8"</b>
<b>services:</b>
<b>  postgres:</b>
<b>    image: postgres:18      </b>
<b>    container_name: pg-enterprise</b>
<b>    restart: unless-stopped</b>
<b>    environment:</b>
<b>      POSTGRES_USER: postgres</b>
<b>      POSTGRES_PASSWORD: postgres@!QAZxsw2</b>
<b>      POSTGRES_DB: postgres</b>
<b>    ports:</b>
<b>      - "5432:5432"</b>
<b>    volumes:</b>
<b>      # Key Fix: Only mount to /var/lib/postgresql, let the image create 18/main subdirectory itself</b>
<b>      - pg_data:/var/lib/postgresql</b>
<b>    # Keep other enterprise parameters, health checks, command, etc. unchanged</b>
<b>    command: ></b>
<b>      postgres</b>
<b>      -c max_connections=200</b>
<b>      -c shared_buffers=256MB</b>
<b>      -c effective_cache_size=1GB</b>
<b>      -c statement_timeout=30s</b>
<b>      -c log_min_duration_statement=1000</b>
<b>      -c password_encryption=scram-sha-256</b>
<b>    healthcheck:</b>
<b>      test: ["CMD-SHELL", "pg_isready -U postgres"]</b>
<b>      interval: 10s</b>
<b>      timeout: 5s</b>
<b>      retries: 5</b>
<b>      start_period: 30s</b>

<b>volumes:</b>
<b>  pg_data:</b>

---

Edit Files

Switch to the project directory and vim edit docker-compose.yaml

<b>cd /data/workspace/install-postgres && vim docker-compose.yaml</b>

Insert the docker-compose.yaml file above into the currently edited file

---

Start Service

Execute the start command

<b>cd /data/workspace/install-postgres && docker-compose up -d</b>

View logs

<b>docker ps</b>
<b>docker logs -f <containerd id></b>

---

Verify Deployment

Test connection: Use the built-in pg_isready to detect the port, use psql to execute simple SQL and print version/time

<b>docker exec -it pg-enterprise psql -U postgres -c "select version(); select current_timestamp; select 'Hello from PG' as msg;"</b>

Expected output:

<b>                                                      version                                                       </b>
<b>--------------------------------------------------------------------------------------------------------------------</b>
<b> PostgreSQL 18.1 (Debian 18.1-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit</b>
<b>(1 row)</b>

<b>       current_timestamp       </b>
<b>-------------------------------</b>
<b> 2025-11-21 07:24:12.303782+00</b>
<b>(1 row)</b>

<b>      msg      </b>
<b>---------------</b>
<b> Hello from PG</b>
<b>(1 row)</b>