189 lines
3.0 KiB
Markdown
189 lines
3.0 KiB
Markdown
# 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
|
|
|