begriffs open source - pg_scribe/blob - cli-design.md

   1 # pg_fossil CLI Design Specification
   2
   3 **Version**: 1.0
   4 **Date**: 2025-10-13
   5
   6 ## Overview
   7
   8 `pg_fossil` is a unified command-line interface that orchestrates the underlying PostgreSQL tools (`pg_recvlogical`, `pg_dump`, `pg_dumpall`, event triggers) into a cohesive backup system. It follows Unix principles: simple subcommands, composable operations, clear stdout/stderr separation, and scriptable workflows.
   9
  10 ## Design Philosophy
  11
  12 **Unix Principles Applied:**
  13 - **Composability**: Each subcommand can be used independently or in pipelines
  14 - **Minimalism**: One subcommand per logical operation
  15 - **Discoverability**: Clear help output, sensible defaults, interactive prompts when needed
  16 - **Text-based**: All configuration and metadata in plain text files
  17 - **Non-interactive by default**: Suitable for cron jobs and scripts
  18 - **Exit codes**: 0 for success, non-zero for failure (scriptable)
  19
  20 ## Command Structure
  21
  22 ```
  23 pg_fossil <subcommand> [options]
  24
  25 Subcommands:
  26   init        Initialize backup system for a database
  27   collect     Collect incremental changes (continuous or one-shot)
  28   snapshot    Take a full backup snapshot
  29   restore     Restore database from backups
  30   verify      Verify backup integrity
  31   status      Show replication slot and backup status
  32   prune       Remove old backups according to retention policy
  33   emergency   Emergency procedures (drop slot, etc.)
  34 ```
  35
  36 ## Subcommand Specifications
  37
  38 ### `pg_fossil init`
  39
  40 **Purpose**: Bootstrap the backup system for a database.
  41
  42 **Usage**:
  43 ```bash
  44 pg_fossil init [options]
  45
  46 Options:
  47   -d, --dbname=NAME          Database name (required)
  48   -h, --host=HOST            Database host (default: localhost)
  49   -p, --port=PORT            Database port (default: 5432)
  50   -U, --username=USER        Database user (default: current user)
  51   -s, --slot=NAME            Replication slot name (default: pg_fossil_<dbname>)
  52   -D, --backup-dir=PATH      Backup directory (default: ./pg_fossil_backups)
  53       --plugin-path=PATH     Path to decoder_raw.so (auto-detected by default)
  54       --skip-full-backup     Skip initial full backup (for testing)
  55       --skip-event-triggers  Skip event trigger setup (not recommended)
  56   -v, --verbose              Verbose output
  57   -q, --quiet                Minimal output
  58 ```
  59
  60 **Example**:
  61 ```bash
  62 pg_fossil init --dbname=mydb --backup-dir=/var/backups/mydb
  63 ```
  64
  65 **What it does**:
  66 1. Checks PostgreSQL configuration (`wal_level = logical`, etc.)
  67 2. Verifies decoder_raw plugin is installed
  68 3. Creates logical replication slot with decoder_raw
  69 4. Exports snapshot from slot
  70 5. Takes initial base backup using exported snapshot
  71 6. Creates `pg_dumpall --globals-only` backup
  72 7. Sets up event triggers for DDL capture
  73 8. Configures `REPLICA IDENTITY` on tables without primary keys (with warnings)
  74 9. Creates backup directory structure
  75 10. Writes configuration file: `$BACKUP_DIR/pg_fossil.conf`
  76 11. Writes metadata file: `$BACKUP_DIR/metadata.json`
  77
  78 **Output** (stdout):
  79 ```
  80 pg_fossil: Checking PostgreSQL configuration... OK
  81 pg_fossil: Verifying decoder_raw plugin... OK
  82 pg_fossil: Creating replication slot 'pg_fossil_mydb'... OK
  83 pg_fossil:   Exported snapshot: 00000003-00000001-1
  84 pg_fossil: Taking initial base backup... OK (2.3 GB)
  85 pg_fossil: Backing up global objects... OK
  86 pg_fossil: Setting up event triggers... OK
  87 pg_fossil: Checking replica identity...
  88 pg_fossil:   WARNING: Table 'public.logs' has no replica identity
  89 pg_fossil:   Set to REPLICA IDENTITY FULL
  90 pg_fossil: Creating backup directory: /var/backups/mydb
  91 pg_fossil: Writing configuration... OK
  92 pg_fossil:
  93 pg_fossil: Initialization complete!
  94 pg_fossil:
  95 pg_fossil: Next steps:
  96 pg_fossil:   1. Start collection: pg_fossil collect -d mydb
  97 pg_fossil:   2. Monitor status:   pg_fossil status -d mydb
  98 pg_fossil:   3. Schedule snapshots (cron): 0 2 * * * pg_fossil snapshot -d mydb
  99 ```
 100
 101 **Exit codes**:
 102 - 0: Success
 103 - 1: PostgreSQL configuration error (wal_level not logical, etc.)
 104 - 2: decoder_raw plugin not found
 105 - 3: Permission denied
 106 - 4: Slot already exists (use --force to recreate)
 107 - 5: Disk space insufficient
 108
 109 **Files created**:
 110 ```
 111 /var/backups/mydb/
 112 ├── pg_fossil.conf          # Configuration (plain text)
 113 ├── metadata.json           # PostgreSQL version, extensions, encoding
 114 ├── base/
 115 │   └── 2024-01-15_base.sql.gz
 116 ├── globals/
 117 │   └── 2024-01-15_globals.sql
 118 ├── incremental/
 119 │   └── (empty initially)
 120 └── logs/
 121     └── pg_fossil.log
 122 ```
 123
 124 ### `pg_fossil collect`
 125
 126 **Purpose**: Collect incremental changes from logical replication slot.
 127
 128 **Usage**:
 129 ```bash
 130 pg_fossil collect [options]
 131
 132 Options:
 133   -d, --dbname=NAME          Database name (required, or read from config)
 134   -D, --backup-dir=PATH      Backup directory (default: read from config)
 135   -f, --foreground           Run in foreground (default: daemon mode)
 136       --stdout               Write changes to stdout instead of files
 137       --rotate-interval=DURATION  Rotate files every DURATION (default: 1h)
 138       --rotate-size=SIZE     Rotate when file exceeds SIZE (default: 100MB)
 139       --fsync-interval=SEC   Fsync interval in seconds (default: 10)
 140       --start-lsn=LSN        Start from specific LSN (for recovery)
 141   -v, --verbose              Verbose output
 142       --no-daemon            Synonym for --foreground
 143 ```
 144
 145 **Example (daemon mode)**:
 146 ```bash
 147 pg_fossil collect --dbname=mydb
 148 # Runs in background, writes PID to backup-dir/pg_fossil_collect.pid
 149 ```
 150
 151 **Example (foreground for testing)**:
 152 ```bash
 153 pg_fossil collect --dbname=mydb --foreground --verbose
 154 ```
 155
 156 **Example (stdout for piping)**:
 157 ```bash
 158 pg_fossil collect --dbname=mydb --stdout | tee incremental.sql | grep "DELETE FROM users"
 159 ```
 160
 161 **What it does**:
 162 1. Reads configuration from `pg_fossil.conf`
 163 2. Starts `pg_recvlogical` with decoder_raw plugin
 164 3. Manages file rotation (time-based or size-based)
 165 4. Handles SIGHUP (rotate), SIGTERM (shutdown), SIGINT (shutdown)
 166 5. Writes to timestamped files: `incremental/YYYY-MM-DD_HH-MM-SS.sql`
 167 6. Updates position tracking metadata
 168 7. Monitors replication lag and logs warnings
 169
 170 **Output** (stdout in foreground mode):
 171 ```
 172 pg_fossil: Starting collection for database 'mydb'
 173 pg_fossil: Replication slot: pg_fossil_mydb
 174 pg_fossil: Output directory: /var/backups/mydb/incremental
 175 pg_fossil: Rotation: every 1h or 100MB
 176 pg_fossil: Connected to PostgreSQL, streaming changes...
 177 pg_fossil: [2024-01-15 10:30:15] Wrote 1,234 transactions to 2024-01-15_10-00-00.sql
 178 pg_fossil: [2024-01-15 11:00:00] Rotating file (time interval reached)
 179 pg_fossil: [2024-01-15 11:00:01] New file: 2024-01-15_11-00-00.sql
 180 ...
 181 ```
 182
 183 **Output** (daemon mode):
 184 ```
 185 pg_fossil: Collection started in background (PID: 12345)
 186 pg_fossil: Logs: /var/backups/mydb/logs/pg_fossil_collect.log
 187 pg_fossil: Stop with: kill -TERM 12345
 188 ```
 189
 190 **Signal handling**:
 191 - **SIGHUP**: Rotate current incremental file
 192 - **SIGTERM**: Graceful shutdown (flush current file, update position)
 193 - **SIGINT**: Same as SIGTERM
 194
 195 **Exit codes**:
 196 - 0: Success (foreground: clean shutdown; daemon: successfully started)
 197 - 1: Configuration error
 198 - 2: Cannot connect to database
 199 - 3: Replication slot not found (run `pg_fossil init` first)
 200 - 4: Already running (PID file exists)
 201
 202 ### `pg_fossil snapshot`
 203
 204 **Purpose**: Take a full backup snapshot (base + globals).
 205
 206 **Usage**:
 207 ```bash
 208 pg_fossil snapshot [options]
 209
 210 Options:
 211   -d, --dbname=NAME          Database name (required, or read from config)
 212   -D, --backup-dir=PATH      Backup directory (default: read from config)
 213       --compress=METHOD      Compression method: gzip, zstd, none (default: gzip)
 214       --parallel=N           Parallel dump jobs (pg_dump -j, default: 1)
 215       --exclude-schema=PATTERN  Exclude schemas matching pattern
 216       --exclude-table=PATTERN   Exclude tables matching pattern
 217   -v, --verbose              Verbose output
 218 ```
 219
 220 **Example**:
 221 ```bash
 222 pg_fossil snapshot --dbname=mydb --compress=zstd --parallel=4
 223 ```
 224
 225 **What it does**:
 226 1. Runs `pg_dump` with specified options
 227 2. Runs `pg_dumpall --globals-only`
 228 3. Compresses output files
 229 4. Updates metadata with snapshot timestamp and LSN
 230 5. Calculates and stores SHA256 checksums
 231
 232 **Output** (stdout):
 233 ```
 234 pg_fossil: Taking snapshot of database 'mydb'
 235 pg_fossil: Starting pg_dump (parallel: 4, compression: zstd)...
 236 pg_fossil:   Progress: [=========>            ] 45% (2.1 GB / 4.7 GB)
 237 pg_fossil: Snapshot complete: base/2024-01-15_base.sql.zst (1.8 GB compressed)
 238 pg_fossil: Backing up global objects...
 239 pg_fossil: Globals saved: globals/2024-01-15_globals.sql
 240 pg_fossil: Calculating checksums...
 241 pg_fossil:   SHA256: a3f2e9d8c4b1... (base)
 242 pg_fossil:   SHA256: 7b4c1e9a2f3d... (globals)
 243 pg_fossil: Snapshot complete!
 244 pg_fossil:   LSN: 0/1A2B3C4D
 245 pg_fossil:   Size: 1.8 GB (4.7 GB uncompressed)
 246 pg_fossil:   Duration: 3m 42s
 247 ```
 248
 249 **Exit codes**:
 250 - 0: Success
 251 - 1: Configuration error
 252 - 2: pg_dump failed
 253 - 3: Disk space insufficient
 254 - 4: Checksum calculation failed
 255
 256 ### `pg_fossil restore`
 257
 258 **Purpose**: Restore database from backups.
 259
 260 **Usage**:
 261 ```bash
 262 pg_fossil restore [options]
 263
 264 Options:
 265   -d, --dbname=NAME          Target database name (required)
 266   -D, --backup-dir=PATH      Backup directory (required)
 267       --snapshot=TIMESTAMP   Restore specific snapshot (default: latest)
 268       --until=TIMESTAMP      Point-in-time recovery: apply incrementals until this time
 269       --target-lsn=LSN       Stop at specific LSN (alternative to --until)
 270       --no-globals           Skip restoring global objects
 271       --no-ddl               Skip applying DDL changes from event triggers
 272       --no-incremental       Restore only snapshot (no incremental changes)
 273       --target-db=NAME       Database name on target (if different from backup)
 274       --dry-run              Show restore plan without executing
 275       --verify               Verify checksums before restore
 276   -v, --verbose              Verbose output
 277   -y, --yes                  Assume yes to prompts (for scripts)
 278 ```
 279
 280 **Example (full restore)**:
 281 ```bash
 282 pg_fossil restore --dbname=mydb_restored --backup-dir=/var/backups/mydb --verify
 283 ```
 284
 285 **Example (point-in-time recovery)**:
 286 ```bash
 287 pg_fossil restore --dbname=mydb_restored --backup-dir=/var/backups/mydb \
 288   --until="2024-01-15 14:30:00"
 289 ```
 290
 291 **Example (dry run)**:
 292 ```bash
 293 pg_fossil restore --dbname=mydb_restored --backup-dir=/var/backups/mydb --dry-run
 294 ```
 295
 296 **What it does**:
 297 1. Finds latest snapshot (or specified snapshot)
 298 2. Identifies all incremental files since snapshot
 299 3. If `--until` specified, filters incrementals by timestamp
 300 4. Verifies checksums if `--verify` enabled
 301 5. Prompts for confirmation (unless `--yes`)
 302 6. Creates target database if it doesn't exist
 303 7. Restores global objects (unless `--no-globals`)
 304 8. Restores base snapshot
 305 9. Applies DDL changes from `ddl_history` in chronological order (unless `--no-ddl`)
 306 10. Applies incremental SQL files in order (unless `--no-incremental`)
 307 11. Synchronizes sequences using `setval()`
 308 12. Reports success and statistics
 309
 310 **Output** (stdout):
 311 ```
 312 pg_fossil: Restore plan for database 'mydb'
 313 pg_fossil:   Source: /var/backups/mydb
 314 pg_fossil:   Target: mydb_restored
 315 pg_fossil:
 316 pg_fossil: Restore components:
 317 pg_fossil:   1. Snapshot: 2024-01-15_base.sql.zst (4.7 GB uncompressed)
 318 pg_fossil:   2. Globals:  2024-01-15_globals.sql
 319 pg_fossil:   3. Incremental files: 24 files (2024-01-15 to 2024-01-16)
 320 pg_fossil:      Total transactions: ~45,230
 321 pg_fossil:   4. Point-in-time: 2024-01-15 14:30:00
 322 pg_fossil:
 323 pg_fossil: WARNING: This will create/overwrite database 'mydb_restored'
 324 pg_fossil: Continue? [y/N]: y
 325 pg_fossil:
 326 pg_fossil: [1/6] Verifying checksums... OK
 327 pg_fossil: [2/6] Creating database 'mydb_restored'... OK
 328 pg_fossil: [3/6] Restoring global objects... OK
 329 pg_fossil: [4/6] Restoring base snapshot...
 330 pg_fossil:   Progress: [=================>    ] 85%
 331 pg_fossil:   OK (3m 15s)
 332 pg_fossil: [5/6] Applying DDL changes... 12 schema changes applied
 333 pg_fossil: [6/6] Applying incremental changes...
 334 pg_fossil:   Processing: 2024-01-15_10-00-00.sql (3,451 transactions)
 335 pg_fossil:   Processing: 2024-01-15_11-00-00.sql (2,890 transactions)
 336 pg_fossil:   ...
 337 pg_fossil:   Stopped at: 2024-01-15 14:30:00 (--until reached)
 338 pg_fossil:   Applied: 18 of 24 files, 38,442 transactions
 339 pg_fossil: Synchronizing sequences... 15 sequences updated
 340 pg_fossil:
 341 pg_fossil: Restore complete!
 342 pg_fossil:   Database: mydb_restored
 343 pg_fossil:   Final LSN: 0/1A2B3C4D
 344 pg_fossil:   Total duration: 8m 32s
 345 pg_fossil:
 346 pg_fossil: Next steps:
 347 pg_fossil:   1. Verify data: psql mydb_restored -c "SELECT count(*) FROM users;"
 348 pg_fossil:   2. Test application against restored database
 349 pg_fossil:   3. When ready, switch application to mydb_restored
 350 ```
 351
 352 **Exit codes**:
 353 - 0: Success
 354 - 1: Configuration error
 355 - 2: Snapshot not found
 356 - 3: Restore failed (SQL error during apply)
 357 - 4: Checksum verification failed
 358 - 5: User cancelled (answered 'no' to prompt)
 359
 360 ### `pg_fossil status`
 361
 362 **Purpose**: Show replication slot health and backup status.
 363
 364 **Usage**:
 365 ```bash
 366 pg_fossil status [options]
 367
 368 Options:
 369   -d, --dbname=NAME          Database name (required, or read from config)
 370   -D, --backup-dir=PATH      Backup directory (default: read from config)
 371       --json                 Output in JSON format (for monitoring)
 372       --check                Exit with error if slot lag is critical
 373       --warn-lag=SIZE        Warning threshold (default: 1GB)
 374       --critical-lag=SIZE    Critical threshold (default: 10GB)
 375   -v, --verbose              Show detailed statistics
 376 ```
 377
 378 **Example**:
 379 ```bash
 380 pg_fossil status --dbname=mydb
 381 ```
 382
 383 **Example (monitoring script)**:
 384 ```bash
 385 pg_fossil status --dbname=mydb --check --json | jq '.slot.lag_bytes'
 386 ```
 387
 388 **What it does**:
 389 1. Queries `pg_replication_slots` for slot status
 390 2. Calculates replication lag in bytes and estimated time
 391 3. Lists recent snapshots and incremental files
 392 4. Shows disk usage statistics
 393 5. If `--check` enabled, exits with error code if lag exceeds critical threshold
 394
 395 **Output** (stdout, human-readable):
 396 ```
 397 pg_fossil: Status for database 'mydb'
 398
 399 Replication Slot:
 400   Name:               pg_fossil_mydb
 401   Status:             active
 402   Plugin:             decoder_raw
 403   Current LSN:        0/1A2B3C4D
 404   Restart LSN:        0/1A2B3000
 405   Confirmed Flush:    0/1A2B3C40
 406   Lag:                13 MB (estimated 2 minutes behind)
 407   WAL Retained:       156 MB
 408   Last Activity:      2024-01-15 15:42:33 (2 seconds ago)
 409
 410 Backup Directory: /var/backups/mydb
 411   Latest Snapshot:    2024-01-15_base.sql.zst (1.8 GB)
 412                       Age: 13h 42m
 413   Incremental Files:  24 files
 414                       Range: 2024-01-15 02:00 to 2024-01-15 15:42
 415                       Total size: 245 MB
 416
 417 Disk Usage:
 418   Total backups:      2.3 GB
 419   Available space:    847 GB (99% free)
 420
 421 Collection Process:
 422   Status:             running (PID: 12345)
 423   Uptime:             13h 42m
 424   Current file:       incremental/2024-01-15_15-00-00.sql (23 MB)
 425
 426 Health: OK
 427 ```
 428
 429 **Output** (JSON format):
 430 ```json
 431 {
 432   "database": "mydb",
 433   "timestamp": "2024-01-15T15:42:35Z",
 434   "slot": {
 435     "name": "pg_fossil_mydb",
 436     "active": true,
 437     "plugin": "decoder_raw",
 438     "current_lsn": "0/1A2B3C4D",
 439     "restart_lsn": "0/1A2B3000",
 440     "confirmed_flush_lsn": "0/1A2B3C40",
 441     "lag_bytes": 13631488,
 442     "lag_estimated_seconds": 120,
 443     "wal_retained_bytes": 163577856,
 444     "last_activity": "2024-01-15T15:42:33Z"
 445   },
 446   "backups": {
 447     "directory": "/var/backups/mydb",
 448     "latest_snapshot": {
 449       "file": "2024-01-15_base.sql.zst",
 450       "size_bytes": 1932735283,
 451       "timestamp": "2024-01-15T02:00:00Z",
 452       "age_seconds": 49353
 453     },
 454     "incremental_count": 24,
 455     "incremental_size_bytes": 256901120,
 456     "total_size_bytes": 2402338816
 457   },
 458   "disk": {
 459     "available_bytes": 909543923712,
 460     "percent_free": 99
 461   },
 462   "collection": {
 463     "running": true,
 464     "pid": 12345,
 465     "uptime_seconds": 49320
 466   },
 467   "health": "ok"
 468 }
 469 ```
 470
 471 **Exit codes**:
 472 - 0: Health OK (or not using `--check`)
 473 - 1: Warning threshold exceeded
 474 - 2: Critical threshold exceeded
 475 - 3: Slot inactive
 476 - 4: Collection process not running
 477
 478 ### `pg_fossil verify`
 479
 480 **Purpose**: Verify backup integrity and executability.
 481
 482 **Usage**:
 483 ```bash
 484 pg_fossil verify [options]
 485
 486 Options:
 487   -D, --backup-dir=PATH      Backup directory (required)
 488       --snapshot=TIMESTAMP   Verify specific snapshot (default: all)
 489       --incremental          Verify incremental files
 490       --checksums            Verify SHA256 checksums
 491       --syntax               Verify SQL syntax (parse only, no execution)
 492       --deep                 Deep verification (try restoring to temp database)
 493       --parallel=N           Parallel verification jobs (default: 1)
 494   -v, --verbose              Verbose output
 495 ```
 496
 497 **Example**:
 498 ```bash
 499 pg_fossil verify --backup-dir=/var/backups/mydb --checksums --syntax
 500 ```
 501
 502 **Example (deep verification)**:
 503 ```bash
 504 pg_fossil verify --backup-dir=/var/backups/mydb --deep
 505 ```
 506
 507 **What it does**:
 508 1. Verifies files exist and are readable
 509 2. If `--checksums`: Validates SHA256 checksums
 510 3. If `--syntax`: Parses SQL files for syntax errors (using `psql --no-psqlrc --single-transaction < file`)
 511 4. If `--deep`: Creates temporary database, performs full restore, verifies row counts
 512 5. Reports any corruption or issues
 513
 514 **Output** (stdout):
 515 ```
 516 pg_fossil: Verifying backups in /var/backups/mydb
 517
 518 Snapshots:
 519   [OK] 2024-01-15_base.sql.zst (checksum valid, syntax valid)
 520   [OK] 2024-01-14_base.sql.zst (checksum valid, syntax valid)
 521   [OK] 2024-01-13_base.sql.zst (checksum valid, syntax valid)
 522
 523 Globals:
 524   [OK] 2024-01-15_globals.sql (checksum valid, syntax valid)
 525   [OK] 2024-01-14_globals.sql (checksum valid, syntax valid)
 526
 527 Incremental files (24 files):
 528   [OK] 2024-01-15_10-00-00.sql (syntax valid)
 529   [OK] 2024-01-15_11-00-00.sql (syntax valid)
 530   ...
 531   [WARN] 2024-01-15_14-00-00.sql (file is empty, may be rotation artifact)
 532
 533 Summary:
 534   Total files checked: 29
 535   Passed: 28
 536   Warnings: 1
 537   Errors: 0
 538
 539 Verification: PASSED
 540 ```
 541
 542 **Exit codes**:
 543 - 0: All checks passed
 544 - 1: Warnings found
 545 - 2: Errors found (corruption detected)
 546 - 3: Syntax errors in SQL files
 547
 548 ### `pg_fossil prune`
 549
 550 **Purpose**: Remove old backups according to retention policy.
 551
 552 **Usage**:
 553 ```bash
 554 pg_fossil prune [options]
 555
 556 Options:
 557   -D, --backup-dir=PATH      Backup directory (required)
 558       --keep-days=N          Keep incremental backups for N days (default: 7)
 559       --keep-snapshots=N     Keep N most recent snapshots (default: 30)
 560       --keep-monthly=N       Keep one snapshot per month for N months (default: 12)
 561       --dry-run              Show what would be deleted without deleting
 562       --force                Skip confirmation prompt
 563   -v, --verbose              Verbose output
 564 ```
 565
 566 **Example**:
 567 ```bash
 568 pg_fossil prune --backup-dir=/var/backups/mydb --keep-days=7 --keep-snapshots=30
 569 ```
 570
 571 **Example (dry run)**:
 572 ```bash
 573 pg_fossil prune --backup-dir=/var/backups/mydb --dry-run
 574 ```
 575
 576 **What it does**:
 577 1. Identifies backups outside retention policy
 578 2. Shows deletion plan
 579 3. Prompts for confirmation (unless `--force` or `--dry-run`)
 580 4. Deletes old backups
 581 5. Updates metadata
 582
 583 **Output** (stdout):
 584 ```
 585 pg_fossil: Pruning backups in /var/backups/mydb
 586
 587 Retention policy:
 588   Incremental: 7 days
 589   Snapshots:   30 most recent
 590   Monthly:     12 months
 591
 592 Files to delete:
 593   Snapshots (3 files, 5.4 GB):
 594     2023-11-15_base.sql.zst (1.8 GB)
 595     2023-10-15_base.sql.zst (1.8 GB)
 596     2023-09-15_base.sql.zst (1.8 GB)
 597
 598   Incremental (156 files, 2.1 GB):
 599     2024-01-01_*.sql through 2024-01-07_*.sql
 600
 601   Total: 159 files, 7.5 GB
 602
 603 Continue? [y/N]: y
 604
 605 Deleting files...
 606   Progress: [==================>] 100% (159/159)
 607
 608 Pruning complete!
 609   Deleted: 159 files (7.5 GB)
 610   Remaining: 87 files (12.3 GB)
 611 ```
 612
 613 **Exit codes**:
 614 - 0: Success
 615 - 1: Configuration error
 616 - 2: User cancelled
 617 - 3: Deletion failed (partial deletion)
 618
 619 ### `pg_fossil emergency`
 620
 621 **Purpose**: Emergency procedures for critical situations.
 622
 623 **Usage**:
 624 ```bash
 625 pg_fossil emergency <operation> [options]
 626
 627 Operations:
 628   drop-slot              Drop replication slot (stops WAL retention)
 629   advance-slot           Advance slot to current LSN (loses backup coverage)
 630   reset                  Full reset (drop slot, delete backups, start fresh)
 631
 632 Options:
 633   -d, --dbname=NAME          Database name (required)
 634   -s, --slot=NAME            Replication slot name (default: from config)
 635   -D, --backup-dir=PATH      Backup directory (for reset operation)
 636       --force                Skip all confirmations (DANGEROUS)
 637   -v, --verbose              Verbose output
 638 ```
 639
 640 **Example (drop slot)**:
 641 ```bash
 642 pg_fossil emergency drop-slot --dbname=mydb
 643 ```
 644
 645 **What it does**:
 646 1. Shows impact assessment (WAL will be cleaned, backups may be incomplete)
 647 2. Requires typing "YES" to confirm (unless `--force`)
 648 3. Executes emergency operation
 649 4. Logs action to audit log
 650
 651 **Output** (stdout):
 652 ```
 653 pg_fossil: EMERGENCY OPERATION: drop-slot
 654
 655 WARNING: This will drop the replication slot 'pg_fossil_mydb'
 656
 657 Impact:
 658   - PostgreSQL will immediately start cleaning old WAL files
 659   - Disk space will be freed
 660   - Incremental backup collection will STOP
 661   - Existing backups will remain but may be incomplete
 662   - You will need to run 'pg_fossil init' to restart backups
 663
 664 Current slot status:
 665   WAL retained: 15.3 GB
 666   Lag: 12.5 GB (estimated 3 hours behind)
 667   Last activity: 3 hours ago
 668
 669 This operation CANNOT be undone.
 670
 671 Type 'YES' to confirm: YES
 672
 673 Dropping replication slot 'pg_fossil_mydb'... OK
 674
 675 Emergency operation complete.
 676 Replication slot has been dropped.
 677
 678 Next steps:
 679   1. Verify disk space freed: df -h
 680   2. To resume backups: pg_fossil init --dbname=mydb
 681   3. Review incident and prevent recurrence
 682 ```
 683
 684 **Exit codes**:
 685 - 0: Success
 686 - 1: User cancelled
 687 - 2: Operation failed
 688 - 3: Slot not found
 689
 690 ## Configuration File Format
 691
 692 **Location**: `$BACKUP_DIR/pg_fossil.conf`
 693
 694 **Format**: Plain text, INI-style (compatible with PostgreSQL config format)
 695
 696 **Example**:
 697 ```ini
 698 # pg_fossil configuration
 699 # Generated: 2024-01-15 02:00:00
 700
 701 [database]
 702 name = mydb
 703 host = localhost
 704 port = 5432
 705 user = postgres
 706
 707 [replication]
 708 slot_name = pg_fossil_mydb
 709 plugin = decoder_raw
 710
 711 [backup]
 712 backup_dir = /var/backups/mydb
 713 compress = gzip
 714 parallel_jobs = 4
 715
 716 [incremental]
 717 rotate_interval = 1h
 718 rotate_size = 100MB
 719 fsync_interval = 10
 720
 721 [retention]
 722 incremental_days = 7
 723 snapshot_count = 30
 724 monthly_count = 12
 725
 726 [monitoring]
 727 warn_lag = 1GB
 728 critical_lag = 10GB
 729 ```
 730
 731 ## Metadata File Format
 732
 733 **Location**: `$BACKUP_DIR/metadata.json`
 734
 735 **Format**: JSON (machine-readable)
 736
 737 **Example**:
 738 ```json
 739 {
 740   "version": "1.0",
 741   "database": {
 742     "name": "mydb",
 743     "version": "PostgreSQL 18.0 (Debian 18.0-1.pgdg120+1)",
 744     "encoding": "UTF8",
 745     "collation": "en_US.UTF-8",
 746     "ctype": "en_US.UTF-8"
 747   },
 748   "extensions": [
 749     {"name": "plpgsql", "version": "1.0"},
 750     {"name": "pg_stat_statements", "version": "1.10"}
 751   ],
 752   "replication": {
 753     "slot_name": "pg_fossil_mydb",
 754     "plugin": "decoder_raw",
 755     "plugin_path": "/usr/lib/postgresql/18/lib/decoder_raw.so"
 756   },
 757   "initialization": {
 758     "timestamp": "2024-01-15T02:00:00Z",
 759     "snapshot": "00000003-00000001-1",
 760     "lsn": "0/1A000000"
 761   },
 762   "snapshots": [
 763     {
 764       "timestamp": "2024-01-15T02:00:00Z",
 765       "file": "base/2024-01-15_base.sql.zst",
 766       "size_bytes": 1932735283,
 767       "uncompressed_bytes": 4932735283,
 768       "lsn": "0/1A2B3C4D",
 769       "sha256": "a3f2e9d8c4b1..."
 770     }
 771   ],
 772   "last_updated": "2024-01-15T15:42:35Z"
 773 }
 774 ```
 775
 776 ## Exit Code Convention
 777
 778 All `pg_fossil` commands follow consistent exit code conventions:
 779
 780 ```
 781 0   = Success
 782 1   = Configuration/usage error
 783 2   = Database connection error
 784 3   = Operation failed (specific to command)
 785 4   = Resource error (disk space, permissions, etc.)
 786 5   = User cancelled
 787 10+ = Command-specific error codes
 788 ```
 789
 790 ## Standard Output Conventions
 791
 792 **Stdout**: Progress, results, structured data (e.g., JSON with `--json`)
 793 **Stderr**: Warnings, errors, diagnostics
 794
 795 **Prefix convention**:
 796 - `pg_fossil:` for normal messages
 797 - `pg_fossil: WARNING:` for warnings
 798 - `pg_fossil: ERROR:` for errors
 799
 800 **Enables filtering**:
 801 ```bash
 802 # Show only errors
 803 pg_fossil status 2>&1 | grep ERROR
 804
 805 # Get JSON output without messages
 806 pg_fossil status --json 2>/dev/null | jq '.slot.lag_bytes'
 807 ```
 808
 809 ## Composability Examples
 810
 811 ### Example 1: Monitor lag and alert
 812 ```bash
 813 #!/bin/bash
 814 lag=$(pg_fossil status --dbname=mydb --json | jq '.slot.lag_bytes')
 815 if [ "$lag" -gt 1073741824 ]; then  # 1GB
 816   echo "CRITICAL: Replication lag is $(($lag / 1024 / 1024)) MB" | mail -s "Backup Alert" admin@example.com
 817 fi
 818 ```
 819
 820 ### Example 2: Stream changes to monitoring system
 821 ```bash
 822 pg_fossil collect --dbname=mydb --stdout | \
 823   grep "DELETE FROM" | \
 824   while read line; do
 825     echo "$line" | send-to-monitoring-system
 826   done
 827 ```
 828
 829 ### Example 3: Automated restore testing
 830 ```bash
 831 #!/bin/bash
 832 # Test restore every night to verify backups work
 833 pg_fossil restore --dbname=test_restore --backup-dir=/var/backups/mydb \
 834   --yes --no-globals 2>&1 | tee /var/log/restore-test.log
 835
 836 if [ $? -eq 0 ]; then
 837   echo "Restore test PASSED" | mail -s "Backup Verification" admin@example.com
 838   psql test_restore -c "DROP DATABASE test_restore;"
 839 else
 840   echo "Restore test FAILED" | mail -s "URGENT: Backup Failure" admin@example.com
 841 fi
 842 ```
 843
 844 ### Example 4: Backup rotation in cron
 845 ```cron
 846 # Daily full snapshot at 2 AM
 847 0 2 * * * /usr/local/bin/pg_fossil snapshot --dbname=mydb --quiet
 848
 849 # Weekly pruning on Sundays at 3 AM
 850 0 3 * * 0 /usr/local/bin/pg_fossil prune --backup-dir=/var/backups/mydb --force --quiet
 851
 852 # Hourly monitoring
 853 0 * * * * /usr/local/bin/pg_fossil status --dbname=mydb --check || echo "Replication lag critical" | mail -s "Alert" admin@example.com
 854 ```
 855
 856 ### Example 5: Point-in-time recovery workflow
 857 ```bash
 858 # User reports: "I accidentally deleted customer records at 2:30 PM today"
 859
 860 # 1. Find exact timestamp from application logs
 861 # 2. Restore to 5 minutes before the incident
 862 pg_fossil restore --dbname=mydb_recovery \
 863   --backup-dir=/var/backups/mydb \
 864   --until="2024-01-15 14:25:00" \
 865   --verify
 866
 867 # 3. Extract the deleted records
 868 psql mydb_recovery -c "COPY (SELECT * FROM customers WHERE deleted_at IS NULL) TO '/tmp/recovered_customers.csv' CSV HEADER;"
 869
 870 # 4. Re-insert into production (or compare and merge manually)
 871 ```
 872
 873 ## Implementation Notes
 874
 875 ### Language and Dependencies
 876
 877 **Language**: Python 3.8+ recommended
 878 - Good PostgreSQL library support (`psycopg2` or `psycopg3`)
 879 - Easy subprocess management for `pg_recvlogical`, `pg_dump`
 880 - Rich CLI library ecosystem (`argparse` or `click`)
 881 - JSON handling built-in
 882 - Cross-platform compatibility
 883
 884 **Dependencies**:
 885 - PostgreSQL client utilities (pg_recvlogical, pg_dump, pg_dumpall, psql)
 886 - Python 3.8+
 887 - psycopg2 or psycopg3
 888 - Standard library only (no exotic dependencies)
 889
 890 ### Installation
 891
 892 ```bash
 893 pip install pg-fossil
 894 # or
 895 apt install pg-fossil  # Future package
 896 ```
 897
 898 ### Testing Strategy
 899
 900 - Unit tests for each subcommand
 901 - Integration tests with PostgreSQL in Docker
 902 - End-to-end restore tests
 903 - Crash recovery tests
 904 - Signal handling tests
 905
 906 ## Quick Start Example
 907
 908 For users who want to try the system immediately:
 909
 910 ```bash
 911 # 1. Setup (one time)
 912 pg_fossil init --dbname=mydb --backup-dir=/var/backups/mydb
 913
 914 # 2. Start collecting changes (run once, continues in background)
 915 pg_fossil collect --dbname=mydb
 916
 917 # 3. Take daily snapshots (add to cron)
 918 pg_fossil snapshot --dbname=mydb
 919
 920 # 4. Monitor health
 921 pg_fossil status --dbname=mydb
 922
 923 # 5. Restore when needed
 924 pg_fossil restore --dbname=mydb_restored --backup-dir=/var/backups/mydb
 925 ```
 926
 927 ## Unix Philosophy Alignment
 928
 929 This CLI design adheres to the Unix philosophy:
 930
 931 1. **Do one thing well**: Each subcommand has a focused purpose
 932 2. **Text-based**: All input/output in plain text (SQL, JSON, INI)
 933 3. **Composable**: Commands can be piped and scripted
 934 4. **Non-interactive by default**: Suitable for automation
 935 5. **Clear separation**: stdout for data, stderr for diagnostics
 936 6. **Orthogonal**: Subcommands don't overlap in functionality
 937 7. **Discoverable**: Clear help, sensible defaults, progress messages
 938 8. **Scriptable**: Exit codes, JSON output, quiet modes
 939
 940 ## Design Rationale
 941
 942 ### Why a unified CLI?
 943
 944 The underlying PostgreSQL tools (`pg_recvlogical`, `pg_dump`, etc.) are powerful but require careful orchestration. A unified CLI:
 945
 946 1. **Reduces complexity**: Users learn one tool instead of five
 947 2. **Ensures correctness**: Orchestration logic is tested and reliable
 948 3. **Improves discoverability**: `pg_fossil --help` shows all capabilities
 949 4. **Enables best practices**: Built-in monitoring, verification, rotation
 950 5. **Maintains composability**: Each subcommand still usable independently
 951
 952 ### Why text-based config?
 953
 954 - Human-readable and editable
 955 - Version control friendly
 956 - Compatible with PostgreSQL conventions
 957 - No binary parsing dependencies
 958
 959 ### Why both INI and JSON?
 960
 961 - **INI** (`pg_fossil.conf`): Human-editable configuration
 962 - **JSON** (`metadata.json`): Machine-readable metadata for tooling
 963
 964 ## Future Extensions
 965
 966 Potential future enhancements (not in v1.0):
 967
 968 1. **Remote backups**: `pg_fossil collect --remote=s3://bucket/path`
 969 2. **Encryption**: `pg_fossil snapshot --encrypt=gpg-key-id`
 970 3. **Compression streaming**: `pg_fossil collect --compress=zstd`
 971 4. **Web UI**: `pg_fossil serve --port=8080` (view status, trigger restores)
 972 5. **Plugin system**: Allow custom hooks for monitoring, alerting
 973 6. **Multi-database**: `pg_fossil init --all-databases`
 974 7. **Differential snapshots**: Faster snapshots between full dumps
 975
 976 ## References
 977
 978 - [The Art of Unix Programming](http://www.catb.org/~esr/writings/taoup/) by Eric S. Raymond
 979 - [PostgreSQL Logical Replication Documentation](https://www.postgresql.org/docs/current/logical-replication.html)
 980 - [decoder_raw plugin](https://github.com/michaelpq/pg_plugins/tree/main/decoder_raw)
 981 - [12 Factor CLI Apps](https://medium.com/@jdxcode/12-factor-cli-apps-dd3c227a0e46)