# 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:

```bash
chmod 600 .env
```

---

## Initial Setup

Run these commands on the server:

```bash
cd ~/infra/postgres
mkdir -p data backup
```

Ensure correct directory ownership:

```bash
sudo chown -R $USER:$USER data backup
```

---

## Starting the Stack

Start PostgreSQL:

```bash
docker compose up -d
```

Check status:

```bash
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:

```bash
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:

```bash
psql -U auction -d auctiondb < backup_file.sql
```

---

## Stopping or Updating

Stop:

```bash
docker compose down
```

Update:

```bash
docker compose pull
docker compose up -d
```

---

## Disaster Recovery

To rebuild the database:

```bash
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