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

   1 # Incremental SQL Backup System Using PostgreSQL Logical Replication
   2
   3 **PostgreSQL Version**: This design is based on PostgreSQL 18.0 documentation. While most features (logical replication, event triggers, pg_recvlogical) are available in earlier versions (PostgreSQL 10+), verify specific parameter availability (e.g., `max_slot_wal_keep_size` requires PostgreSQL 13+) for your target version.
   4
   5 ## Executive Summary
   6
   7 This document details the design for a PostgreSQL backup system that produces human-readable, plain SQL incremental backups using logical replication. The system creates backups that remain readable and restorable for 10+ years while supporting online operation and crash safety.
   8
   9 **Design Decision**: Use `pg_recvlogical` with the `wal2sql` plugin for DML capture, combined with event triggers using `pg_logical_emit_message()` for DDL tracking and periodic `pg_dumpall --globals-only` for shared objects.
  10
  11 **Why This Works**:
  12 - **Built-in tooling handles complexity**: `pg_recvlogical` provides streaming infrastructure, crash recovery, and position tracking
  13 - **No transformation layer needed**: `wal2sql` produces production-ready SQL directly
  14 - **Complete coverage**: Event triggers + `pg_logical_emit_message()` + `pg_dumpall --globals-only` captures all DDL at correct chronological positions
  15 - **Long-term readability**: Plain SQL format that can be executed years later
  16 - **Correct DDL/DML ordering**: DDL messages appear in replication stream at exact time of execution
  17
  18 **Note on Backup Strategy**:
  19
  20 This system implements **differential backups** using logical replication. Each incremental backup file contains all changes since the base backup, allowing simple restore: base + most recent differential. This differs from PostgreSQL 17+'s native incremental backups (`pg_basebackup --incremental`), which are true incremental backups where each backup depends on the previous backup, requiring all incremental backups in sequence to restore. See file-handling.md for detailed terminology and filesystem organization.
  21
  22 **Key Requirements**:
  23 1. **DDL tracking** - Event triggers emit DDL via `pg_logical_emit_message()`; `pg_dumpall --globals-only` handles shared objects
  24 2. **Replica identity configuration** - All tables need proper configuration for UPDATE/DELETE
  25 3. **Aggressive monitoring** - Replication slots must be monitored to prevent operational issues
  26 4. **wal2sql extension** - ✅ **COMPLETE** - Logical decoding plugin with full DML, DDL, and TRUNCATE support
  27
  28 ## Architecture Overview
  29
  30 ### High-Level Design
  31
  32 ```
  33 ┌─────────────────────────────────────────────────────────────┐
  34 │                    PostgreSQL Database                       │
  35 │                                                              │
  36 │  ┌────────────────┐         ┌──────────────────┐           │
  37 │  │  Regular Tables│────────▶│  WAL (Write-Ahead│           │
  38 │  │  (DML Changes) │         │       Log)       │           │
  39 │  └────────────────┘         └──────────────────┘           │
  40 │                                      │                       │
  41 │                                      ▼                       │
  42 │                         ┌─────────────────────────┐         │
  43 │                         │ Logical Decoding Process│         │
  44 │                         │    (wal2sql plugin)     │         │
  45 │                         └─────────────────────────┘         │
  46 │                                      │                       │
  47 └──────────────────────────────────────┼───────────────────────┘
  48                                        │
  49                                        ▼
  50                         ┌─────────────────────────────┐
  51                         │  Replication Slot           │
  52                         │  (Tracks position, durable) │
  53                         └─────────────────────────────┘
  54                                        │
  55                                        ▼
  56                         ┌─────────────────────────────┐
  57                         │    pg_recvlogical Tool     │
  58                         │ (Built-in PostgreSQL util)  │
  59                         └─────────────────────────────┘
  60                                        │
  61                         ┌──────────────┴──────────────┐
  62                         ▼                             ▼
  63           ┌─────────────────────┐      ┌─────────────────────┐
  64           │  Differential Files │      │  New Chain Creation │
  65           │  (active.sql,       │      │  (Base Backup)      │
  66           │   diff-*.sql)       │      │  via --new-chain    │
  67           └─────────────────────┘      └─────────────────────┘
  68                    │                              │
  69                    └──────────────┬───────────────┘
  70                                   ▼
  71                     ┌──────────────────────────┐
  72                     │  Chain-Based Storage     │
  73                     │  chain-TIMESTAMP/        │
  74                     │    base.sql              │
  75                     │    globals.sql           │
  76                     │    active.sql            │
  77                     │    diff-*.sql            │
  78                     └──────────────────────────┘
  79 ```
  80
  81 ### Core Components
  82
  83 1. **Logical Replication Slot**: Durable position tracker in PostgreSQL
  84 2. **wal2sql Plugin**: ✅ **IMPLEMENTED** - Transforms binary WAL to executable SQL
  85 3. **pg_recvlogical**: Built-in PostgreSQL tool that streams logical decoding output
  86 4. **Base Backup System**: ⏳ **PLANNED** - Regular full `pg_dump` backups with `--snapshot` for consistency
  87 5. **Schema Tracking System**: ⏳ **PLANNED** - Event triggers + `pg_dumpall --globals-only` for DDL changes
  88
  89 ## How It Works
  90
  91 ### DML Capture via Logical Replication
  92
  93 PostgreSQL's logical replication decodes the Write-Ahead Log (WAL) into logical changes. The `wal2sql` plugin outputs these directly as executable SQL:
  94
  95 ```sql
  96 BEGIN;
  97 INSERT INTO public.users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');
  98 UPDATE public.users SET name = 'Alice Smith' WHERE id = 1;
  99 DELETE FROM public.orders WHERE id = 42;
 100 COMMIT;
 101 ```
 102
 103 **Key Properties**:
 104 - **Crash-safe**: Replication slots persist position across crashes (positions persisted at checkpoint intervals; after crash, slot may return to earlier LSN causing recent changes to be replayed)
 105 - **Consistent**: Transaction boundaries are preserved
 106 - **Online**: Runs without blocking database operations
 107 - **Idempotent positioning**: Can restart from last known position (clients responsible for handling duplicate messages)
 108
 109 ### DDL Capture via Event Triggers and Logical Messages
 110
 111 Logical replication does **not** capture DDL (CREATE TABLE, ALTER TABLE, etc.). We solve this by emitting DDL commands directly into the logical replication stream using PostgreSQL's `pg_logical_emit_message()` function.
 112
 113 The wal2sql extension (installed via `CREATE EXTENSION wal2sql`) provides an event trigger that automatically captures DDL and emits it into the replication stream with the 'ddl' prefix. See `wal2sql/wal2sql--0.1.sql` for the implementation.
 114
 115 **How it works**:
 116 1. Event trigger fires on all DDL commands
 117 2. `pg_logical_emit_message()` writes DDL into WAL as a logical decoding message
 118 3. Message appears in replication stream at exact chronological position relative to DML
 119 4. Output plugin's `message_cb` callback outputs DDL as executable SQL
 120 5. Restore is simple: just execute the incremental backup file sequentially
 121
 122 **Example incremental backup output**:
 123 ```sql
 124 BEGIN;
 125 INSERT INTO public.users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');
 126 COMMIT;
 127 BEGIN;
 128 -- DDL message appears at exact time of execution
 129 ALTER TABLE public.users DROP COLUMN email;
 130 COMMIT;
 131 BEGIN;
 132 -- Subsequent DML only references remaining columns
 133 INSERT INTO public.users (id, name) VALUES (2, 'Bob');
 134 COMMIT;
 135 ```
 136
 137 **Key Properties**:
 138 - ✅ **Perfect chronological ordering**: DDL appears exactly when it was executed
 139 - ✅ **Transactional integrity**: DDL message commits with its transaction
 140 - ✅ **Simple restore**: Execute backup file sequentially with `psql -f`
 141 - ✅ **PostgreSQL built-in**: `pg_logical_emit_message()` available since PostgreSQL 9.6
 142
 143 **Limitations**:
 144 - Event triggers don't fire for shared objects: databases, roles, tablespaces, parameter privileges, and ALTER SYSTEM commands
 145 - Solution: Use periodic `pg_dumpall --globals-only` to capture shared objects
 146
 147 ## Database Schema Prerequisites
 148
 149 This section summarizes the **mandatory database requirements** for this backup system. The setup script MUST validate these and fail with clear errors if not met.
 150
 151 ### Critical Requirements
 152
 153 **1. Replica Identity** (see "Key Challenges" section #1 for details):
 154 - Every table MUST have: PRIMARY KEY, or `REPLICA IDENTITY USING INDEX`, or `REPLICA IDENTITY FULL`
 155 - Without this: UPDATE/DELETE operations **fail on the source database**
 156 - Validation: `SELECT n.nspname, c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND c.relreplident = 'd' AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid = c.oid AND i.indisprimary) AND n.nspname NOT IN ('pg_catalog', 'information_schema');`
 157
 158 **2. PostgreSQL Configuration**:
 159 - `wal_level = logical` (required)
 160 - `max_replication_slots >= 1` (required)
 161 - `max_wal_senders >= 1` (required)
 162
 163 **3. Event Trigger Installation**:
 164 - Run `CREATE EXTENSION wal2sql;` in every database being backed up
 165 - Without this: DDL changes NOT captured
 166
 167 ### Objects NOT Captured (plan alternatives)
 168
 169 - **Unlogged/temporary tables**: Silently excluded from logical replication
 170 - **Sequence state**: Not replicated incrementally (restore script automatically synchronizes via `setval()`)
 171 - **Large objects**: Use `BYTEA` instead or accept loss (see "Key Challenges" #4)
 172 - **Shared objects**: Captured via `pg_dumpall --globals-only`
 173 - **System columns**: `ctid`, `xmin`, `xmax` will differ after restore
 174
 175 ### Additional Constraints
 176
 177 - **REPLICA IDENTITY FULL** + geometric types (`point`, `box`, `circle`, etc.): Use PRIMARY KEY instead
 178 - **Foreign keys**: TRUNCATE CASCADE fails if related tables split across different publications
 179 - **Row-level security**: Disable RLS on target tables during restore
 180 - **Triggers**: Disable triggers during restore to avoid duplicate side effects
 181 - **Extensions**: Must be available on restore target (document in metadata)
 182 - **Collation/encoding**: Must be compatible between source and target
 183
 184 ### Pre-Deployment Validation
 185
 186 ```sql
 187 -- CRITICAL: Find tables without adequate replica identity
 188 SELECT n.nspname, c.relname, c.relreplident
 189 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
 190 WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema')
 191   AND c.relreplident IN ('d', 'n')  -- DEFAULT or NOTHING
 192   AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid = c.oid AND i.indisprimary);
 193 -- Must return 0 rows
 194
 195 -- CRITICAL: Verify configuration
 196 SHOW wal_level;  -- Must be 'logical'
 197 SELECT count(*) FROM pg_event_trigger WHERE evtname = 'wal2sql_ddl_trigger';  -- Must be 1
 198
 199 -- WARNING: Check for unlogged tables (won't be backed up)
 200 SELECT n.nspname, c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
 201 WHERE c.relkind = 'r' AND c.relpersistence = 'u'
 202   AND n.nspname NOT IN ('pg_catalog', 'information_schema');
 203
 204 -- WARNING: Check for large objects (not incrementally backed up)
 205 SELECT count(*) FROM pg_largeobject_metadata;
 206 ```
 207
 208 See "Key Challenges and Solutions" section below for detailed explanations and solutions.
 209
 210 ## Implementation Components
 211
 212 ### 1. Initial Setup Script
 213
 214 **Purpose**: Bootstrap the backup system
 215 **Status**: ⏳ **PLANNED** - Not yet implemented
 216
 217 **Philosophy**: The setup script validates prerequisites and **fails fast** with clear error messages. It does NOT automatically fix schema issues - human oversight required for replica identity decisions due to performance tradeoffs.
 218
 219 **Tasks**:
 220 1. **Validate Prerequisites** (print report to stdout):
 221    - **CRITICAL** (must fix before proceeding):
 222      - Verify `wal_level = logical`, `max_replication_slots >= 1`, `max_wal_senders >= 1`
 223      - Check all tables have adequate replica identity (PRIMARY KEY, USING INDEX, or FULL)
 224    - **Coverage Warnings**:
 225      - List unlogged tables (will not be backed up)
 226      - Check for large objects (not incrementally backed up)
 227    - **If validation fails**: Print detailed report and EXIT with error
 228    - **Human must fix CRITICAL issues** before re-running setup
 229
 230 2. **Setup Replication Infrastructure** (after validation passes):
 231    - Create logical replication slot with snapshot export
 232    - Capture the exported snapshot identifier
 233    - Set up event triggers via `CREATE EXTENSION wal2sql`
 234
 235 3. **Initial Backup**:
 236    - Take synchronized base backup using exported snapshot (`pg_dump`)
 237    - Create initial `pg_dumpall --globals-only` backup
 238    - Write `pg_scribe_version.txt` to backup directory (PostgreSQL version, pg_scribe version, setup timestamp)
 239
 240 **Note**: Most metadata (sequences, extensions, replica identity, encoding) is already captured in the `pg_dump` and `pg_dumpall` files. The restore script queries this information directly from the backup files or restored database as needed.
 241
 242 **Critical Detail - Initial Snapshot Consistency**:
 243
 244 From PostgreSQL documentation (Section 47.2.5):
 245 > When a new replication slot is created using the streaming replication interface, a snapshot is exported which will show exactly the state of the database after which all changes will be included in the change stream.
 246
 247 This ensures the base backup and incremental stream are perfectly aligned with no gaps or overlaps.
 248
 249 **Step-by-Step Setup Procedure**:
 250
 251 1. **Verify prerequisites**:
 252    - Check backup directory is empty (or create if doesn't exist)
 253    - Verify replication slot doesn't already exist (fail if it does)
 254    - Validate PostgreSQL configuration and table replica identity
 255
 256 2. **Create replication slot**:
 257    - Use `pg_create_logical_replication_slot()` SQL function
 258    - Capture returned LSN position for base backup coordination
 259    - If creation fails, exit with error (no cleanup needed yet)
 260
 261 3. **Take synchronized base backup**:
 262    - Use `pg_dump --file=backup.sql` immediately after slot creation
 263    - The slot preserves WAL from its creation LSN forward, ensuring no changes are lost
 264    - Track created files for cleanup if subsequent steps fail
 265
 266 4. **Capture globals and metadata**:
 267    - Run `pg_dumpall --globals-only` for roles, tablespaces
 268    - Document PostgreSQL version, extensions, encoding in metadata file
 269    - Track all created files for cleanup if any step fails
 270
 271 **Critical Considerations**:
 272 - **Cleanup on failure**: If any step fails, automatically remove replication slot and partial backup files
 273 - **Non-idempotent**: Following PostgreSQL conventions (`initdb`, `pg_basebackup`), the operation requires a clean state and does not support re-running
 274 - **Empty directory required**: Like `pg_basebackup`, the backup directory must be empty to prevent accidental overwrites
 275
 276 ### 2. Incremental Backup Collection
 277
 278 **Status**: ✅ **IMPLEMENTED** - `pg_scribe --start` command available
 279 **Tool**: Built-in `pg_recvlogical` utility with `wal2sql` plugin
 280
 281 **Implementation Approach**:
 282
 283 The `pg_scribe --start` command is a thin wrapper that:
 284 1. Validates the database connection and replication slot
 285 2. Displays configuration and user information
 286 3. Uses `exec` to **replace itself** with `pg_recvlogical`
 287
 288 This design has several advantages over traditional parent-child process management:
 289 - **No orphaned processes**: The pg_scribe process becomes pg_recvlogical (same PID)
 290 - **Direct signal handling**: Signals (SIGTERM, SIGINT, SIGHUP) go directly to pg_recvlogical
 291 - **Simpler implementation**: No need for signal forwarding or child process tracking
 292 - **Reliable cleanup**: Test suites and process management tools interact with a single process
 293
 294 **Key Configuration**:
 295
 296 The wrapper invokes `pg_recvlogical --start` with the following important parameters:
 297
 298 - **Status interval** (`--status-interval`): Controls how often client reports position back to server (default: 10 seconds)
 299   - Lower values allow server to advance slot position and free WAL more quickly
 300   - Balance between slot health and network overhead
 301
 302 - **Fsync interval** (`--fsync-interval`): Controls disk write safety (default: 10 seconds)
 303   - Frequency of forced disk synchronization for crash safety on backup client
 304   - 0 disables fsync (faster but risks data loss if client crashes)
 305   - Higher values improve performance but increase window of potential loss
 306
 307 - **Plugin options** (`--option`): Pass `include_transaction=on` to wal2sql
 308   - Includes BEGIN/COMMIT statements in output
 309   - Essential for maintaining transaction boundaries during restore
 310
 311 **What pg_recvlogical provides**:
 312 - Streams decoded changes continuously from the replication slot
 313 - Handles connection failures and automatic reconnection
 314 - Tracks LSN positions with status updates to the server
 315 - Supports file rotation via SIGHUP signal (for log rotation without stopping stream)
 316
 317 **What wal2sql provides** (✅ **ALL IMPLEMENTED**):
 318 - ✅ Schema-qualified table names: `INSERT INTO public.users (...)`
 319 - ✅ Proper column name quoting with `quote_identifier()`
 320 - ✅ Transaction boundaries via `include_transaction=on` option (BEGIN/COMMIT)
 321 - ✅ Intelligent replica identity handling (DEFAULT, INDEX, FULL, NOTHING)
 322 - ✅ Comprehensive data type support (booleans, NaN, Infinity, NULL, strings, bit strings)
 323 - ✅ TOAST optimization (skips unchanged TOAST columns in UPDATEs)
 324 - ✅ Production-quality memory management
 325 - ✅ **`message_cb` callback** to handle DDL messages from `pg_logical_emit_message()`
 326 - ✅ **`truncate_cb` callback** to handle TRUNCATE operations
 327
 328 ### 3. Periodic Full Backup Script
 329
 330 **Status**: ⏳ **PLANNED** - Not yet implemented
 331 **Purpose**: Take regular full backups as restore points
 332
 333 **Tasks**:
 334 - Execute `pg_dump --file=backup.sql` to create full database backup in plain SQL format
 335 - Execute `pg_dumpall --globals-only` to capture shared objects (databases, roles, tablespaces)
 336 - Compress backups to save space
 337 - Implement retention policy (delete old backups)
 338
 339 **Backup Approach**:
 340 - Use `pg_dump --file=backup.sql` for plain SQL output
 341 - Maintains human-readable format consistent with design goals
 342 - Single connection to database
 343 - Universal compatibility across PostgreSQL versions
 344
 345 **Compression**:
 346 - Use `pg_dump --compress=<method>` for built-in compression (e.g., `--compress=zstd:9`)
 347 - Or compress after creation with external tools (gzip, zstd)
 348 - Compression significantly reduces storage requirements while maintaining recoverability
 349
 350 ### 4. Restore Script
 351
 352 **Status**: ⏳ **PLANNED** - Not yet implemented
 353 **Purpose**: Restore database to latest captured state from base + incremental backups
 354
 355 **Restore Process**:
 356 1. Locate most recent full backup and all incremental backups since then
 357 2. Create new target database
 358 3. Restore `pg_dumpall --globals-only` (shared objects: roles, tablespaces)
 359 4. Restore full `pg_dump` backup using `psql -f backup.sql`
 360 5. Disable triggers to prevent duplicate side effects during replay
 361 6. Apply all incremental SQL backups in chronological order using `psql -f incremental-*.sql`
 362    - DDL and DML are already interleaved in correct chronological order
 363 7. Automatically synchronize all sequence values to match table max values
 364 8. Re-enable triggers
 365 9. Verify data integrity (row counts, application smoke tests)
 366
 367 **Key Automation**:
 368 - **Sequences**: Script automatically runs `setval()` for all sequences after restore
 369 - **Triggers**: Script automatically disables/re-enables to prevent side effects
 370 - **RLS**: Script handles row-level security if present (disable during restore or use superuser)
 371
 372 **Handling Duplicate Transactions**:
 373
 374 After PostgreSQL crash, replication slot may return to earlier LSN, causing some transactions to be streamed again. The restore process handles this naturally through idempotent operations:
 375
 376 - Most SQL operations are idempotent or fail safely:
 377   - INSERT fails on duplicate primary key (acceptable during restore)
 378   - UPDATE reapplies same values (idempotent)
 379   - DELETE succeeds or reports row not found (acceptable)
 380 - Transaction boundaries (BEGIN/COMMIT from `include_transaction=on`) preserve consistency
 381 - Simply apply all incremental files in order; duplicates will be handled correctly
 382 - No additional LSN tracking infrastructure required
 383
 384 ### 5. Monitoring and Health Check Script
 385
 386 **Status**: ⏳ **PLANNED** - Not yet implemented
 387 **Purpose**: Prevent operational issues from inactive replication slots
 388
 389 **Critical Metrics**:
 390 ```sql
 391 -- Check replication slot health
 392 SELECT
 393     slot_name,
 394     slot_type,
 395     database,
 396     active,
 397     pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as replication_lag,
 398     pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) as confirmed_lag
 399 FROM pg_replication_slots
 400 WHERE slot_type = 'logical';
 401 ```
 402
 403 **Alerting**:
 404 - **Critical alert** when `restart_lsn` falls more than 1GB behind
 405 - **Emergency alert** when slot lag exceeds 10GB or age exceeds 24 hours
 406 - **Emergency procedure** documented to drop slot if it threatens database availability
 407
 408 **Available Tools**:
 409 - **Prometheus + postgres_exporter + Grafana**: Open-source monitoring stack
 410 - **pgDash** (https://pgdash.io/): Commercial PostgreSQL monitoring
 411 - **check_postgres**: Nagios/Icinga/Zabbix integration
 412 - **Built-in views**: `pg_replication_slots`, `pg_stat_replication_slots`
 413
 414 ## wal2sql Plugin Details
 415
 416 **Status**: ✅ **COMPLETE** - All required functionality implemented and tested
 417 **Source**: Forked from decoder_raw (https://github.com/michaelpq/pg_plugins/tree/main/decoder_raw)
 418 **Location**: `wal2sql/` directory
 419 **Compatibility**: PostgreSQL 18.0+
 420
 421 **Installation**:
 422 ```bash
 423 cd wal2sql
 424 make && make install
 425 PGUSER=postgres make installcheck  # Run tests
 426 ```
 427
 428 **Implemented Features**:
 429 - ✅ INSERT/UPDATE/DELETE statements with proper escaping
 430 - ✅ Transaction boundaries (BEGIN/COMMIT)
 431 - ✅ Replica identity handling (DEFAULT, INDEX, FULL, NOTHING)
 432 - ✅ **`message_cb`** - DDL capture via `pg_logical_emit_message()`
 433 - ✅ **`truncate_cb`** - TRUNCATE statement capture
 434 - ✅ Comprehensive data type support (booleans, NaN, Infinity, NULL, strings)
 435 - ✅ TOAST optimization
 436 - ✅ Full test coverage
 437
 438 ## Key Challenges and Solutions
 439
 440 ### 1. Replica Identity Required for UPDATE/DELETE
 441
 442 **Problem**: Tables need replica identity for UPDATE/DELETE operations.
 443
 444 From PostgreSQL documentation (Section 29.1.1):
 445 > Tables with a replica identity defined as `NOTHING`, `DEFAULT` without a primary key, or `USING INDEX` with a dropped index, **cannot support UPDATE or DELETE operations**. **Attempting such operations will result in an error on the publisher.**
 446
 447 This means UPDATE/DELETE will **fail on the source database**, not just during restore!
 448
 449 **Solution**: Ensure all tables have one of:
 450 - A primary key (automatic replica identity)
 451 - A unique index configured via `REPLICA IDENTITY USING INDEX index_name`
 452 - Explicit `REPLICA IDENTITY FULL` setting (inefficient, last resort)
 453
 454 **Example**:
 455 ```sql
 456 -- Table without primary key will error on UPDATE/DELETE
 457 CREATE TABLE logs (timestamp TIMESTAMPTZ, message TEXT);
 458
 459 -- Fix: Set replica identity to FULL
 460 ALTER TABLE logs REPLICA IDENTITY FULL;
 461 ```
 462
 463 ### 2. Replication Slots Prevent WAL Cleanup
 464
 465 **Problem**: Inactive replication slots prevent WAL cleanup, leading to:
 466 1. Disk fills up (WAL files not cleaned)
 467 2. Table bloat (VACUUM cannot clean old row versions)
 468 3. **Database shutdown** (transaction ID wraparound)
 469
 470 From PostgreSQL documentation (Section 47.2.2):
 471 > In extreme cases this could cause the database to shut down to prevent transaction ID wraparound.
 472
 473 **Solution**:
 474 - **Monitor slot lag aggressively** (see monitoring section)
 475 - Set `max_slot_wal_keep_size` parameter (PostgreSQL 13+) to limit WAL retention
 476 - Have documented emergency procedure to drop slot if needed
 477 - Consider `pg_replication_slot_advance()` to skip ahead (loses backup coverage)
 478
 479 ### 3. Sequences Are Not Replicated
 480
 481 **Problem**: Sequence values are not captured in logical replication.
 482
 483 **Solution**:
 484 - Use `pg_dump --sequence-data` (enabled by default) in periodic full dumps
 485 - After restore, synchronize sequences:
 486   ```sql
 487   SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));
 488   ```
 489
 490 ### 4. Large Objects Are Not Replicated
 491
 492 **Problem**: PostgreSQL large objects are not captured in logical replication.
 493
 494 **Solution**:
 495 - **Preferred**: Use `BYTEA` columns instead (these ARE replicated)
 496 - **Alternative**: Use `pg_dump --large-objects` in periodic full backups
 497   - Note: Incremental changes to large objects NOT captured between full backups
 498
 499 ### 5. Crash Recovery and Duplicate Handling
 500
 501 **Problem**: After database crash, slot position may roll back, causing duplicate changes.
 502
 503 From PostgreSQL documentation (Section 47.2.2):
 504 > The current position of each slot is persisted only at checkpoint, so in the case of a crash the slot might return to an earlier LSN, which will then cause recent changes to be sent again when the server restarts.
 505
 506 **Solution**: The restore process handles duplicates naturally through idempotent operations. Per PostgreSQL documentation (Section 47.2.2): "Logical decoding clients are responsible for avoiding ill effects from handling the same message more than once."
 507
 508 **Implementation**:
 509 - Most SQL operations in backup files are naturally idempotent:
 510   - INSERT will fail on duplicate primary key (acceptable during restore)
 511   - UPDATE will reapply same values (idempotent)
 512   - DELETE will succeed or report row not found (acceptable)
 513 - Transaction boundaries (BEGIN/COMMIT from `include_transaction=on`) ensure consistency
 514 - Simply apply all incremental files in chronological order
 515 - No additional LSN tracking infrastructure required
 516 - See Restore Script section (Section 4) for implementation details
 517
 518 **Testing**:
 519 - Test crash scenarios with `pg_ctl stop -m immediate` to verify duplicate handling
 520 - Monitor `confirmed_flush_lsn` lag during normal operations (see Monitoring section)
 521
 522 ### 6. Long-Term Readability
 523
 524 **Challenges**:
 525 - PostgreSQL syntax may change between major versions (rare)
 526 - Extension dependencies may not exist in future systems
 527 - Encoding/collation definitions may change
 528
 529 **Solution**: Include metadata file with each backup:
 530 - PostgreSQL version (full version string)
 531 - All installed extension names and versions
 532 - Database encoding
 533 - Locale and collation settings
 534 - Custom data types and enums
 535
 536 Periodically test restoring old backups on current PostgreSQL versions.
 537
 538 ## Prerequisites and Configuration
 539
 540 ### PostgreSQL Configuration
 541
 542 ```ini
 543 # postgresql.conf
 544
 545 # Required: Set WAL level to logical
 546 wal_level = logical
 547
 548 # Required: Allow at least one replication slot
 549 max_replication_slots = 10
 550
 551 # Recommended: Allow replication connections
 552 max_wal_senders = 10
 553
 554 # Recommended: Keep more WAL for safety
 555 wal_keep_size = 1GB
 556
 557 # Recommended: Limit WAL retention for safety (PostgreSQL 13+)
 558 max_slot_wal_keep_size = 10GB
 559
 560 # Optional: Tune checkpoint frequency to persist slot positions more often
 561 checkpoint_timeout = 5min
 562 ```
 563
 564 ### Client Requirements
 565
 566 - PostgreSQL client utilities installed (`pg_recvlogical`, `pg_dump`, `pg_dumpall`)
 567 - Superuser or role with `REPLICATION` privilege
 568 - Permission to create replication slots
 569 - wal2sql plugin compiled and installed
 570
 571 ## Operational Procedures
 572
 573 ### Backup Schedule
 574
 575 **Recommended**:
 576 - **Continuous streaming**: Run `pg_scribe --start` as a daemon (writes to `active.sql` in latest chain)
 577 - **Differential rotation**: Daily via `pg_scribe --rotate-diff` (seals daily differential)
 578 - **New chain creation**: Monthly via `pg_scribe --new-chain` (fresh base backup + globals + metadata)
 579 - **Monitoring**: Periodic slot health checks via `pg_scribe --status`
 580
 581 ### Retention Policy
 582
 583 - **Differential rotation**: Rotate differentials daily (via `--rotate-diff`)
 584 - **Chain rotation**: Create new chains monthly (via `--new-chain`)
 585 - **Chain retention**: Keep recent chains for 30 days, then one per month for 1 year
 586 - **Cleanup**: Manual removal of old chains using standard Unix tools (`rm -rf chain-{CHAIN_ID}`)
 587 - **Monitor disk space**: Alert if backup directory exceeds 80% capacity
 588
 589 ### Disaster Recovery Runbook
 590
 591 1. **Stop application** to prevent new writes during restore
 592 2. **Create new database** (don't overwrite production)
 593 3. **Use `--restore` command**: `pg_scribe --restore --backup-dir /backups/production -d restored_db --create`
 594    - Automatically restores globals (roles, tablespaces)
 595    - Applies base backup from selected chain
 596    - Applies all sealed differentials in chronological order
 597    - DDL and DML are already interleaved in correct chronological order
 598 4. **Sync sequences**: Automatically synchronized via `setval()` (unless `--no-sync-sequences` specified)
 599 5. **Verify data integrity**: Check row counts, run application smoke tests
 600 6. **Test application** against restored database
 601 7. **Switch over** application to restored database
 602
 603 **Manual restore (alternative)**:
 604 ```bash
 605 CHAIN_ID=20231215T120000Z  # Or use latest
 606 CHAIN_DIR=/backups/production/chain-$CHAIN_ID
 607
 608 # Restore globals and base
 609 psql -f $CHAIN_DIR/globals.sql
 610 psql dbname < $CHAIN_DIR/base.sql
 611
 612 # Apply all sealed differentials
 613 for f in $CHAIN_DIR/diff-*.sql; do
 614   psql dbname < "$f"
 615 done
 616
 617 # Sync sequences
 618 pg_scribe --restore --backup-dir /backups/production --chain-id $CHAIN_ID \
 619   -d dbname  # (handles sequence sync automatically)
 620 ```
 621
 622 **See Section 4 (Restore Script)** for detailed procedures including sequence synchronization and duplicate transaction handling. See `doc/cli.md` for `--restore` command options.
 623
 624 ## Testing Strategy
 625
 626 ### 1. Basic Functionality Test
 627
 628 ```sql
 629 -- Create test database and setup
 630 CREATE DATABASE backup_test;
 631 \c backup_test
 632
 633 -- Create test table
 634 CREATE TABLE test_users (id SERIAL PRIMARY KEY, name TEXT, created_at TIMESTAMP DEFAULT now());
 635
 636 -- Generate data and schema changes to test DDL/DML ordering
 637 INSERT INTO test_users (name) VALUES ('Alice'), ('Bob'), ('Charlie');
 638 UPDATE test_users SET name = 'Alice Smith' WHERE id = 1;
 639 DELETE FROM test_users WHERE id = 3;
 640
 641 -- Add column - DDL message should appear in stream here
 642 ALTER TABLE test_users ADD COLUMN email TEXT;
 643
 644 -- Use the new column - should work because DDL already executed
 645 UPDATE test_users SET email = 'alice@example.com' WHERE id = 1;
 646
 647 -- Drop column - DDL message should appear in stream here
 648 ALTER TABLE test_users DROP COLUMN created_at;
 649
 650 -- Subsequent inserts should work without the dropped column
 651 INSERT INTO test_users (name, email) VALUES ('David', 'david@example.com');
 652
 653 -- Restore and verify:
 654 -- 1. All operations should replay successfully
 655 -- 2. DML before column add should not reference email column
 656 -- 3. DML after column add should reference email column
 657 -- 4. DML after column drop should not reference created_at column
 658 ```
 659
 660 ### 2. Crash Recovery Test
 661
 662 ```bash
 663 # Start collecting incrementals
 664 # Generate load with pgbench
 665 # Simulate crash: pg_ctl stop -m immediate
 666 # Restart PostgreSQL
 667 # Verify no data loss and duplicates handled correctly
 668 # Restore and verify
 669 ```
 670
 671 ### 3. Long-Term Storage Test
 672
 673 ```bash
 674 # Create backup
 675 # Store backup files
 676 # Wait (or simulate) years passing
 677 # Restore on modern PostgreSQL version
 678 # Verify SQL is still readable and executable
 679 ```
 680
 681 ### 4. Replica Identity Test
 682
 683 ```sql
 684 -- Create table without primary key
 685 CREATE TABLE test_no_pk (col1 TEXT, col2 INT);
 686
 687 -- Attempt UPDATE (should fail with replica identity error)
 688 UPDATE test_no_pk SET col2 = 5 WHERE col1 = 'test';
 689
 690 -- Fix with REPLICA IDENTITY FULL
 691 ALTER TABLE test_no_pk REPLICA IDENTITY FULL;
 692
 693 -- Retry UPDATE (should succeed)
 694 ```
 695
 696 ### 5. TRUNCATE Handling Test
 697
 698 ```sql
 699 -- Create test table
 700 CREATE TABLE test_truncate (id INT);
 701 INSERT INTO test_truncate VALUES (1), (2), (3);
 702
 703 -- Perform TRUNCATE
 704 TRUNCATE test_truncate;
 705
 706 -- Verify: Check if wal2sql incremental backup captured TRUNCATE
 707 -- Expected: ✅ CAPTURED by wal2sql with truncate_cb
 708 -- Look for: TRUNCATE TABLE public.test_truncate;
 709 -- Note: Event triggers do NOT capture TRUNCATE (it's DML, not DDL)
 710
 711 -- Test TRUNCATE with multiple tables (foreign key cascade)
 712 CREATE TABLE parent_table (id INT PRIMARY KEY);
 713 CREATE TABLE child_table (parent_id INT REFERENCES parent_table(id));
 714 INSERT INTO parent_table VALUES (1), (2);
 715 INSERT INTO child_table VALUES (1), (2);
 716
 717 -- TRUNCATE CASCADE should capture both tables
 718 TRUNCATE parent_table, child_table;
 719 -- Expected output: TRUNCATE TABLE public.parent_table, public.child_table;
 720 ```
 721
 722 ## Performance Considerations
 723
 724 **Write Amplification**:
 725 - WAL must be written (normal)
 726 - WAL must be decoded into logical format (additional CPU)
 727 - Event triggers fire on every DDL operation (minimal overhead)
 728
 729 **Disk I/O**:
 730 - Additional WAL volume retained by replication slots
 731 - More frequent checkpoint I/O if checkpoint_timeout is tuned
 732
 733 **Recommendations**:
 734 - Benchmark overhead on test system with production-like workload
 735 - Monitor CPU usage of WAL sender processes
 736 - Monitor disk usage for WAL and backup directories
 737
 738 ## Implementation Status & Next Steps
 739
 740 ### ✅ Completed
 741
 742 1. **wal2sql Plugin** - Fully implemented and tested
 743    - ✅ Core DML operations (INSERT/UPDATE/DELETE)
 744    - ✅ `message_cb` for DDL capture
 745    - ✅ `truncate_cb` for TRUNCATE support
 746    - ✅ Comprehensive test suite passing
 747
 748 ### ⏳ Next Steps
 749
 750 1. **Setup Script** - Create initial backup environment
 751    - Create replication slot with `wal2sql`
 752    - Set up event triggers for DDL capture using `pg_logical_emit_message()`
 753    - Take synchronized base backup with `pg_dump --snapshot`
 754    - Configure `REPLICA IDENTITY` on tables
 755
 756 2. **Backup Collection Script** - Wrapper around `pg_recvlogical`
 757    - File rotation and timestamping
 758    - Metadata generation (PostgreSQL version, extensions, encoding)
 759    - Error handling and restart logic
 760
 761 3. **Restore Script** - Apply base + incremental backups
 762    - Locate and order backup files
 763    - Apply in sequence with error handling
 764    - Sequence synchronization with `setval()`
 765
 766 4. **Monitoring Script** - Replication slot health checks
 767    - Track slot lag and WAL retention
 768    - Alert on dangerous conditions
 769    - Emergency procedures documentation
 770
 771 5. **Integration Testing**
 772    - End-to-end backup and restore
 773    - DDL/DML interleaving scenarios
 774    - Crash recovery with `pg_ctl stop -m immediate`
 775    - Performance benchmarking
 776
 777 ## References
 778
 779 ### PostgreSQL Documentation
 780 - PostgreSQL Documentation: Chapter 25 - Backup and Restore
 781 - PostgreSQL Documentation: Chapter 29 - Logical Replication
 782 - PostgreSQL Documentation: Chapter 47 - Logical Decoding
 783 - PostgreSQL Documentation: Section 29.8 - Logical Replication Restrictions
 784 - PostgreSQL Documentation: Section 47.6.4.8 - Generic Message Callback (message_cb)
 785 - PostgreSQL Documentation: Section 9.28.6 - `pg_logical_emit_message()` function
 786 - PostgreSQL Documentation: `pg_recvlogical` man page
 787 - PostgreSQL Documentation: `pg_dump` man page
 788 - PostgreSQL Documentation: `pg_dumpall` man page
 789
 790 ### Essential Tools
 791 - **wal2sql**: ✅ **IMPLEMENTED** - SQL output plugin for logical decoding
 792   - Location: `wal2sql/` directory in this repository
 793   - Forked from: https://github.com/michaelpq/pg_plugins/tree/main/decoder_raw
 794   - License: PostgreSQL License (production-ready)
 795   - Compatibility: PostgreSQL 18.0+
 796
 797 ### Monitoring Tools
 798 - **Prometheus + postgres_exporter + Grafana**: Open-source monitoring stack
 799 - **pgDash**: PostgreSQL monitoring - https://pgdash.io/
 800 - **check_postgres**: Nagios/Icinga/Zabbix integration
 801 - **pg_stat_replication_slots**: Built-in PostgreSQL monitoring view