More about initial validation, less prescriptive output

[pg_scribe] / doc / cli.md

# pg_scribe Command Line Interface

This document describes the command line interface for **pg_scribe**, an incremental SQL backup system for PostgreSQL.

## Overview

pg_scribe uses action flags similar to other PostgreSQL tools like `pg_recvlogical`. The tool performs one primary action per invocation, specified by action flags.

## Synopsis

```bash
# Initialize backup system
pg_scribe --init [OPTIONS]

# Start streaming incremental backups
pg_scribe --start [OPTIONS]

# Take a full backup
pg_scribe --full-backup [OPTIONS]

# Restore from backups
pg_scribe --restore [OPTIONS]

# Check replication slot status
pg_scribe --status [OPTIONS]
```

## Common Options

These options apply to all actions:

### Connection Options

```
-d, --dbname=DBNAME          Database name (can be a connection string)
-h, --host=HOSTNAME          Database server host (default: localhost)
-p, --port=PORT              Database server port (default: 5432)
-U, --username=USERNAME      Database user (default: $PGUSER or $USER)
-w, --no-password            Never prompt for password
-W, --password               Force password prompt
```

### General Options

```
-v, --verbose                Enable verbose mode
-V, --version                Print version and exit
-?, --help                   Show help and exit
```

## Action Flags

Exactly one of the following action flags must be specified:

### `--init`

Initialize the backup system by creating a replication slot, setting up DDL capture via the wal2sql extension, and taking an initial base backup.

**This operation is idempotent** - it can be safely re-run if initialization fails partway through. Existing slots and extensions will not cause errors.

**Additional options:**

```
-f, --file=DIRECTORY         Backup output directory (required)
-S, --slot=SLOTNAME          Replication slot name (default: pg_scribe)
    --force                  Skip validation and force initialization (dangerous!)
```

**What it does:**

**Phase 1: Validation** (runs first, can fail)

1. **CRITICAL Checks** (must pass or initialization fails):
   - Verify `wal_level = logical`
   - Verify `max_replication_slots >= 1`
   - Verify `max_wal_senders >= 1`
   - Check all tables have adequate replica identity (PRIMARY KEY, USING INDEX, or FULL)

2. **Coverage Warnings** (non-blocking, informational):
   - List unlogged tables (will not be backed up)
   - Check for large objects (not incrementally backed up)

**Phase 2: Setup** (only runs if validation passes or `--force` used)

1. Creates the wal2sql extension if it doesn't exist (`CREATE EXTENSION IF NOT EXISTS wal2sql;`)
   - This automatically installs the DDL event trigger
2. Creates a logical replication slot with snapshot export (skips if slot exists)
3. Takes synchronized base backup using `pg_dump --snapshot`
4. Creates initial `pg_dumpall --globals-only` backup
5. Generates metadata file with PostgreSQL version, extensions, encoding

**Example:**

```bash
pg_scribe --init -d mydb -f /backups/mydb -S mydb_backup
```

**Output should convey:**

- **Validation results** with clear pass/fail status for:
  - PostgreSQL configuration (wal_level, max_replication_slots, max_wal_senders)
  - Replica identity for all tables
  - Coverage warnings (unlogged tables, large objects)
- **If validation fails**: List CRITICAL issues with specific fix commands, then exit with error code 5 (unless `--force` used)
- **If validation passes** (or `--force`): Progress through setup steps (extension creation, slot creation with snapshot ID, backup paths)
- **Final status**: Success message or warning if forced past validation failures

---

### `--start`

Start streaming incremental backups continuously from a replication slot.

**Additional options:**

```
-f, --file=FILENAME          Output file (use '-' for stdout, required)
-S, --slot=SLOTNAME          Replication slot name (default: pg_scribe)
-s, --status-interval=SECS   Status update interval in seconds (default: 10)
-F, --fsync-interval=SECS    Fsync interval in seconds (default: 10, 0 to disable)
```

**What it does:**

1. Wraps `pg_recvlogical` to stream from the specified replication slot
2. Streams decoded changes using the wal2sql plugin
3. Writes SQL to output file
4. Periodically fsyncs output file for crash safety
5. Reports LSN position and lag to stderr
6. Responds to SIGHUP by closing and reopening output file (for log rotation)

**Implementation:** `pg_scribe --start` is a thin wrapper around `pg_recvlogical --start` that:
- Invokes `pg_recvlogical` with the `wal2sql` plugin and appropriate options
- Forwards signals (SIGHUP, SIGTERM, SIGINT) to the child process
- Provides consistent interface with other pg_scribe commands

**Log rotation support:** File rotation is provided automatically by `pg_recvlogical`:

1. Rename the current output file
2. Send SIGHUP to pg_scribe process (which forwards it to pg_recvlogical)
3. pg_recvlogical closes the old file and opens a new one with the same name

**Example:**

```bash
# Stream to a file (foreground)
pg_scribe --start -d mydb -f /backups/mydb/incremental.sql -S mydb_backup

# Run as background daemon (redirect stderr to log file)
pg_scribe --start -d mydb -f /backups/mydb/incremental.sql -S mydb_backup \
  2>/var/log/pg_scribe.log &

# Stream to stdout (for processing with other tools)
pg_scribe --start -d mydb -f - -S mydb_backup > /backups/mydb/incremental.sql
```

**Log rotation:**

```bash
#!/bin/bash
BACKUP_FILE="/backups/mydb/incremental.sql"
mv "$BACKUP_FILE" "$BACKUP_FILE.$(date +%Y%m%d)"
killall -SIGHUP pg_scribe
```

**Output (to stderr):** Connection status, output file path, periodic LSN position and lag, file rotation events

---

### `--full-backup`

Take a full backup using `pg_dump` and `pg_dumpall --globals-only`.

**Additional options:**

```
-f, --file=DIRECTORY         Backup output directory (required)
-Z, --compress=METHOD        Compression method: gzip, lz4, zstd, or none
                             Can include level (e.g., zstd:9) (default: zstd:9)
```

**What it does:**

1. Takes full `pg_dump` backup
2. Takes `pg_dumpall --globals-only` backup
3. Generates metadata file
4. Compresses backups if enabled

**Example:**

```bash
pg_scribe --full-backup -d mydb -f /backups/mydb
pg_scribe --full-backup -d mydb -f /backups/mydb --compress=zstd:9
```

**Output should convey:** Progress for pg_dump and pg_dumpall steps, file paths, backup size (compressed and uncompressed if applicable)

---

### `--restore`

Restore database from base backup plus incremental backups.

**Additional options:**

```
-f, --file=DIRECTORY         Backup input directory (required)
-d, --dbname=DBNAME          Target database name (required)
-C, --create                 Create target database
--base-backup=FILENAME       Specific base backup file (default: latest)
--no-sync-sequences          Skip sequence synchronization
```

**What it does:**

1. Locates base backup (latest or specified)
2. Finds all incremental backups since base
3. Creates target database (if `--create` specified)
4. Restores globals (roles, tablespaces)
5. Restores base backup
6. Applies incremental backups in chronological order
7. Synchronizes sequences using `setval()` (unless `--no-sync-sequences`)
8. Reports basic statistics

**Example:**

```bash
# Restore latest to new database
pg_scribe --restore -f /backups/mydb -d mydb_restored --create

# Restore specific base backup
pg_scribe --restore -f /backups/mydb -d mydb_restored \
  --base-backup=/backups/mydb/base-2024-01-10.sql
```

**Output should convey:** Base backup identified, number of incremental backups to apply, progress through each restore phase (globals, base, incrementals with counts), sequence synchronization details, final statistics (duration, row counts)

---

### `--status`

Check replication slot health and backup system status.

**Additional options:**

```
-S, --slot=SLOTNAME          Replication slot name (default: pg_scribe)
-f, --file=DIRECTORY         Backup directory to analyze (optional)
```

**What it does:**

1. Queries `pg_replication_slots` for slot health
2. Shows replication lag and WAL retention
3. Analyzes backup directory if provided
4. Reports warnings about potential issues

**Example:**

```bash
pg_scribe --status -d mydb -S mydb_backup -f /backups/mydb
```

**Output should convey:** Replication slot details (active status, LSN positions, lag, WAL retention, age), backup directory analysis (base backup count and dates, incremental file count and size, last backup timestamp, coverage continuity), overall health status with warnings if applicable

---

## Exit Status

```
0   Success
1   General error
2   Database connection error
3   Replication slot error
4   Backup/restore error
5   Invalid arguments or validation failure (--init without --force)
10  Warning conditions (--status only)
```

---

## Environment Variables

Standard PostgreSQL environment variables are supported:

```
PGHOST              Database host
PGPORT              Database port
PGDATABASE          Database name
PGUSER              Database user
PGPASSWORD          Database password (not recommended, use .pgpass instead)
PG_COLOR            Use color in diagnostics: always, auto, or never
```

---

## Example Workflows

### Initial Setup and Daily Operation

```bash
# 1. Initialize backup system (idempotent - safe to re-run)
pg_scribe --init -d production -f /backups/production -S prod_backup

# 2. Start streaming backups (run as daemon/service)
pg_scribe --start -d production -f /backups/production/incremental.sql \
  -S prod_backup 2>/var/log/pg_scribe.log &

# 3. Set up log rotation (logrotate or custom script)
cat > /etc/logrotate.d/pg_scribe <<EOF
/backups/production/incremental.sql {
    daily
    rotate 7
    dateext
    postrotate
        killall -SIGHUP pg_scribe
    endscript
}
EOF

# 4. Schedule daily full backups (cron: 0 2 * * *)
pg_scribe --full-backup -d production -f /backups/production

# 5. Monitor slot health (cron: */15 * * * *)
pg_scribe --status -d production -S prod_backup
```

### Disaster Recovery

```bash
# 1. Check available backups
pg_scribe --status -f /backups/production

# 2. Restore to new database
pg_scribe --restore -f /backups/production \
  -d production_restored --create

# 3. Test restored database
psql -d production_restored -c "SELECT COUNT(*) FROM users;"

# 4. Switch application to restored database (manual step)
```

---

## Comparison with PostgreSQL Tools

pg_scribe follows the same CLI conventions as core PostgreSQL tools:

| Tool | Action Flags | Connection Options | File Options |
|------|-------------|-------------------|--------------|
| **pg_recvlogical** | `--create-slot`, `--drop-slot`, `--start` | `-d`, `-h`, `-p`, `-U` | `-f` (output file), `-S` (slot) |
| **pg_dump** | (positional dbname) | `-d`, `-h`, `-p`, `-U` | `-f` (output file) |
| **pg_basebackup** | (none) | `-d`, `-h`, `-p`, `-U` | `-D` (data directory!) |
| **pg_restore** | (none) | `-d`, `-h`, `-p`, `-U` | positional (archive file) |
| **pg_scribe** | `--init`, `--start`, `--full-backup`, `--restore`, `--status` | `-d`, `-h`, `-p`, `-U` | `-f` (file/directory), `-S` (slot) |

**Note:** `pg_basebackup` uses `-D/--pgdata` because it creates an actual PostgreSQL data directory cluster. `pg_scribe` uses `-f/--file` like `pg_dump` and `pg_recvlogical` because it creates backup files.

---

## Implementation Notes

### Design Principles

1. **Consistent with PostgreSQL**: Follow exact same conventions as pg_recvlogical, pg_dump, etc.
2. **Idempotent where sensible**: `--init` can be safely re-run
3. **Clear output**: Progress to stderr, data to stdout (when using `-f -`)
4. **Scriptable**: Clear text output format, proper exit codes

### Technology Choices (POC)

- **Language**: Bash or Python
  - Bash: Minimal dependencies, matches PostgreSQL tool style
  - Python: Better error handling, easier testing
- **Dependencies**: Only PostgreSQL client tools (pg_recvlogical, pg_dump, pg_dumpall, psql)

### Key Implementation Components

1. **Connection management**: Use libpq environment variables, .pgpass
2. **Error handling**: Validate prerequisites before starting operations
3. **File management**:
   - For `--start`: Invoke `pg_recvlogical` with file specified by `-f`
   - For `--full-backup`: Write to directory specified by `-f`
   - For `--restore`: Read from directory specified by `-f`
4. **Signal handling**:
   - SIGTERM/SIGINT for graceful shutdown (forward to child processes)
   - SIGHUP for file rotation (--start only, forwarded to pg_recvlogical)
5. **Output conventions**:
   - Progress and status messages → stderr
   - SQL output → file specified by `-f` (or stdout if `-f -`)
6. **Process management for --start**:
   - Spawn `pg_recvlogical` as child process
   - Forward signals to child process
   - Monitor child exit status
   - Wrap child output to provide consistent pg_scribe formatting

**Extensibility Note**: This POC design uses a modular, action-based CLI that can accommodate additional features and options in future versions without breaking compatibility.