# 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) ``` **What it does:** 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 6. Prints replica identity warnings for tables without primary keys **Example:** ```bash pg_scribe --init -d mydb -f /backups/mydb -S mydb_backup ``` **Output:** ``` Creating wal2sql extension... Extension 'wal2sql' created (includes DDL event trigger). Creating replication slot 'mydb_backup'... Snapshot exported: 00000003-00000001-1 Taking base backup... Base backup saved to: /backups/mydb/base-2024-01-15.sql Capturing globals... Globals saved to: /backups/mydb/globals-2024-01-15.sql Writing metadata... Initialization complete! Warnings: Table 'public.logs' has no replica identity - UPDATE/DELETE will fail Run: ALTER TABLE public.logs REPLICA IDENTITY FULL; ``` **Output when re-running (idempotent):** ``` Checking wal2sql extension... Extension 'wal2sql' already exists. Checking replication slot 'mydb_backup'... Slot 'mydb_backup' already exists, skipping creation. Taking base backup... Base backup saved to: /backups/mydb/base-2024-01-15.sql Capturing globals... Globals saved to: /backups/mydb/globals-2024-01-15.sql Writing metadata... Initialization complete! ``` --- ### `--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) -n, --no-loop Don't retry on connection failure ``` **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 script example:** ```bash #!/bin/bash # Rotate incremental backup file BACKUP_FILE="/backups/mydb/incremental.sql" mv "$BACKUP_FILE" "$BACKUP_FILE.$(date +%Y%m%d)" killall -SIGHUP pg_scribe ``` **Output (to stderr):** Progress and status messages are written to stderr (following PostgreSQL tool conventions). When running as a background daemon, redirect stderr to a log file using `2>`. ``` Connecting to replication slot 'mydb_backup'... Streaming to: /backups/mydb/incremental.sql LSN: 0/1A2B3C4D Lag: 128 bytes LSN: 0/1A2B3C8F Lag: 0 bytes File rotated (SIGHUP received) Streaming to: /backups/mydb/incremental.sql ... ``` --- ### `--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 # With specific compression pg_scribe --full-backup -d mydb -f /backups/mydb --compress=zstd:9 ``` **Output:** ``` Taking full backup... Full backup saved to: /backups/mydb/base-2024-01-15.sql.zst Capturing globals... Globals saved to: /backups/mydb/globals-2024-01-15.sql Writing metadata... Full backup complete! Size: 1.2 GB (compressed from 5.4 GB) ``` --- ### `--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 -c, --clean Drop target database before creating (requires -C) --base-backup=FILENAME Specific base backup file (default: latest) --recovery-target-time=TIME Restore until timestamp (format: YYYY-MM-DD HH:MM:SS) --recovery-target-name=NAME Restore until specific incremental file -j, --jobs=NJOBS Parallel restore jobs (default: 1) --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 to specific point in time pg_scribe --restore -f /backups/mydb -d mydb_restored \ --recovery-target-time="2024-01-15 14:30:00" # Restore specific base backup pg_scribe --restore -f /backups/mydb -d mydb_restored \ --base-backup=/backups/mydb/base-2024-01-10.sql ``` **Output:** ``` Located base backup: base-2024-01-10.sql (5.4 GB) Found 5 incremental backups to apply Creating database 'mydb_restored'... Restoring globals... Restoring base backup... (5 minutes) Applying incremental backup 1/5: incremental-2024-01-10.sql Applying incremental backup 2/5: incremental-2024-01-11.sql Applying incremental backup 3/5: incremental-2024-01-12.sql Applying incremental backup 4/5: incremental-2024-01-13.sql Applying incremental backup 5/5: incremental-2024-01-14.sql Synchronizing sequences... users_id_seq: set to 12845 orders_id_seq: set to 98234 products_id_seq: set to 456 Restore complete! Duration: 8 minutes Rows restored: 1,284,567 ``` --- ### `--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) --format=FORMAT Output format: text or json (default: text) ``` **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:** ``` Replication Slot: mydb_backup Status: active Database: mydb Plugin: wal2sql Restart LSN: 0/1A2B3C4D Confirmed LSN: 0/1A2B3C8F Replication lag: 128 bytes WAL retention: 2.1 GB Slot age: 2 days 14 hours Backup Directory: /backups/mydb Base backups: 5 (oldest: 2024-01-01, newest: 2024-01-15) Incremental: 45 files (12.4 GB total) Last backup: 2024-01-15 14:23:01 (2 minutes ago) Coverage: Continuous since 2024-01-01 Status: OK ``` **Warning example:** ``` Status: WARNING Warnings: Replication lag is 1.2 GB (threshold: 1.0 GB) Slot has been inactive for 6 hours Last incremental backup is 2 hours old ``` --- ## Size and Time Specifications Size values accept suffixes: - `K` or `KB` - kilobytes - `M` or `MB` - megabytes - `G` or `GB` - gigabytes Time intervals accept suffixes: - `s` - seconds - `m` - minutes - `h` - hours - `d` - days **Examples:** - `--compress=zstd:9` (zstd compression, level 9) - `--fsync-interval=10` (10 seconds) --- ## Exit Status ``` 0 Success 1 General error 2 Database connection error 3 Replication slot error 4 Backup/restore error 5 Invalid arguments 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 <