# PostgreSQL with Hot Backup

PostgreSQL 18 with automated weekly full backups and continuous WAL archiving for point-in-time recovery.

## Services

| Service | Description |
|---------|-------------|
| `postgres-db` | PostgreSQL 18 with WAL archiving enabled |
| `postgres-backup` | Sidecar that runs weekly full backups via `pg_basebackup` |

## Quick Start

```bash
cp .env.example .env  # edit with your credentials
docker compose up -d
```

## Configuration

Environment variables in `.env`:

| Variable | Description | Default |
|----------|-------------|---------|
| `POSTGRES_USER` | Database user | `postgres` |
| `POSTGRES_PASSWORD` | Database password | — |
| `POSTGRES_DB` | Default database name | `postgres` |
| `PGPORT` | Host port for PostgreSQL | `5432` |

## Backup Strategy

### Full Backups (Weekly)
- `pg_basebackup` runs on startup, then every 7 days
- Stored in `/data/backup/postgres/full/full_<timestamp>/`
- Hot backup — no downtime required

### Incremental via WAL Archiving (Continuous)
- PostgreSQL archives Write-Ahead Log segments as they complete (~16MB each)
- Stored in `/data/backup/postgres/wal/`
- Captures every transaction between full backups

### Backup Location

```
/data/backup/postgres/
├── full/
│   └── full_2026-03-06_12-00-00/   # weekly full snapshots
└── wal/
    ├── 000000010000000000000001      # archived WAL segments
    └── ...
```

## Restore

To perform point-in-time recovery:

1. Stop PostgreSQL
2. Replace the data directory with a full backup:
   ```bash
   cp -r /data/backup/postgres/full/full_<timestamp>/ /path/to/pgdata/
   ```
3. Create a `recovery.signal` file in the data directory
4. Configure `restore_command` in `postgresql.conf`:
   ```
   restore_command = 'cp /data/backup/postgres/wal/%f %p'
   ```
5. Optionally set `recovery_target_time` for a specific point in time:
   ```
   recovery_target_time = '2026-03-06 14:30:00'
   ```
6. Start PostgreSQL — it will replay WAL files and recover

## Monitoring

```bash
# Check backup service logs
docker compose logs postgres-backup

# Check WAL archiving activity
docker compose logs postgres-db | grep archive

# List full backups
ls /data/backup/postgres/full/

# List archived WAL files
ls /data/backup/postgres/wal/
```

## Volumes

| Volume/Mount | Purpose |
|-------------|---------|
| `postgres_data` (Docker volume) | PostgreSQL data directory |
| `/data/backup/postgres/wal` | Archived WAL segments |
| `/data/backup/postgres` | All backups (full + WAL) |