postgres/README.md

PostgreSQL Infrastructure Setup

This folder contains the Docker-based PostgreSQL infrastructure running on the internal server (192.168.1.159).
It provides a production-ready PostgreSQL instance, optional pgAdmin UI, and a simple automated backup mechanism.

The setup is designed to be:

• persistent (volume-backed database storage)
• LAN-accessible (port 5432)
• isolated from Traefik (database does not use reverse proxy)
• easy to operate (docker compose up -d)
• safe (controlled permissions and separate admin password)

---

Directory Structure

infra/postgres/
  docker-compose.yml      → main PostgreSQL service
  .env                    → database credentials (not committed)
  data/                   → PostgreSQL data directory (automatically created)
  backup/                 → SQL dumps created by backup containers
  pgadmin/                → optional pgAdmin admin UI exposed via Traefik
  backup/                 → backup runner files (optional)

data/ and backup/ are not included in version control.
They are created on the server when the stack is deployed.

---

Prerequisites

Run on the target server (Tour, 192.168.1.159):

• Docker
• Docker Compose v2
• Access to the traefik-net Docker network (only if pgAdmin is used)

---

Environment File

Create .env inside this directory:

POSTGRES_PASSWORD=<strong_password_here>
PGADMIN_PASSWORD=<admin_ui_password_if_pgadmin_enabled>

Recommended permissions:

chmod 600 .env

---

Initial Setup

Run these commands on the server:

cd ~/infra/postgres
mkdir -p data backup

Ensure correct directory ownership:

sudo chown -R $USER:$USER data backup

---

Starting the Stack

Start PostgreSQL:

docker compose up -d

Check status:

docker ps

Expected:

postgres-db   Up (healthy)   0.0.0.0:5432->5432/tcp

---

Connecting to PostgreSQL

Connection string:

postgresql://auction:<password>@192.168.1.159:5432/auctiondb

Test:

psql "postgresql://auction:<password>@192.168.1.159:5432/auctiondb"

---

pgAdmin (Optional)

If pgAdmin is deployed via Traefik, access it at:

https://pgadmin.appmodel.nl/

Use postgres-db as the hostname inside pgAdmin.

---

Backup Strategy

SQL dumps are stored in:

infra/postgres/backup/

Daily backups produce files like:

backup_YYYY-MM-DD_HH-MM.sql

Restore example:

psql -U auction -d auctiondb < backup_file.sql

---

Stopping or Updating

Stop:

docker compose down

Update:

docker compose pull
docker compose up -d

---

Disaster Recovery

To rebuild the database:

docker compose down
mv data data_old_$(date +%s)
docker compose up -d
psql -U auction -d auctiondb < backup/latest.sql

---

Next Steps

Possible extensions:

• Flyway migration container
• Auto schema sync for Python + Quarkus
• Monitoring exporters
• Retention policy for backups
• Migration from SQLite to PostgreSQL