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 **Key Requirements**:
  19 1. **DDL tracking** - Event triggers emit DDL via `pg_logical_emit_message()`; `pg_dumpall --globals-only` handles shared objects
  20 2. **Replica identity configuration** - All tables need proper configuration for UPDATE/DELETE
  21 3. **Aggressive monitoring** - Replication slots must be monitored to prevent operational issues
  22 4. **wal2sql extension** - ✅ **COMPLETE** - Logical decoding plugin with full DML, DDL, and TRUNCATE support
  23
  24 ## Architecture Overview
  25
  26 ### High-Level Design
  27
  28 ```
  29 ┌─────────────────────────────────────────────────────────────┐
  30 │                    PostgreSQL Database                       │
  31 │                                                              │
  32 │  ┌────────────────┐         ┌──────────────────┐           │
  33 │  │  Regular Tables│────────▶│  WAL (Write-Ahead│           │
  34 │  │  (DML Changes) │         │       Log)       │           │
  35 │  └────────────────┘         └──────────────────┘           │
  36 │                                      │                       │
  37 │                                      ▼                       │
  38 │                         ┌─────────────────────────┐         │
  39 │                         │ Logical Decoding Process│         │
  40 │                         │    (wal2sql plugin)     │         │
  41 │                         └─────────────────────────┘         │
  42 │                                      │                       │
  43 └──────────────────────────────────────┼───────────────────────┘
  44                                        │
  45                                        ▼
  46                         ┌─────────────────────────────┐
  47                         │  Replication Slot           │
  48                         │  (Tracks position, durable) │
  49                         └─────────────────────────────┘
  50                                        │
  51                                        ▼
  52                         ┌─────────────────────────────┐
  53                         │    pg_recvlogical Tool     │
  54                         │ (Built-in PostgreSQL util)  │
  55                         └─────────────────────────────┘
  56                                        │
  57                         ┌──────────────┴──────────────┐
  58                         ▼                             ▼
  59           ┌─────────────────────┐      ┌─────────────────────┐
  60           │  Incremental Files  │      │  Full pg_dump       │
  61           │  (SQL Changes)      │      │  (Periodic)         │
  62           │  - 2024-01-01.sql   │      │  - base-2024-01.sql │
  63           │  - 2024-01-02.sql   │      │  - base-2024-02.sql │
  64           │  - ...              │      │  - ...              │
  65           └─────────────────────┘      └─────────────────────┘
  66 ```
  67
  68 ### Core Components
  69
  70 1. **Logical Replication Slot**: Durable position tracker in PostgreSQL
  71 2. **wal2sql Plugin**: ✅ **IMPLEMENTED** - Transforms binary WAL to executable SQL
  72 3. **pg_recvlogical**: Built-in PostgreSQL tool that streams logical decoding output
  73 4. **Base Backup System**: ⏳ **PLANNED** - Regular full `pg_dump` backups with `--snapshot` for consistency
  74 5. **Schema Tracking System**: ⏳ **PLANNED** - Event triggers + `pg_dumpall --globals-only` for DDL changes
  75
  76 ## How It Works
  77
  78 ### DML Capture via Logical Replication
  79
  80 PostgreSQL's logical replication decodes the Write-Ahead Log (WAL) into logical changes. The `wal2sql` plugin outputs these directly as executable SQL:
  81
  82 ```sql
  83 BEGIN;
  84 INSERT INTO public.users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');
  85 UPDATE public.users SET name = 'Alice Smith' WHERE id = 1;
  86 DELETE FROM public.orders WHERE id = 42;
  87 COMMIT;
  88 ```
  89
  90 **Key Properties**:
  91 - **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)
  92 - **Consistent**: Transaction boundaries are preserved
  93 - **Online**: Runs without blocking database operations
  94 - **Idempotent positioning**: Can restart from last known position (clients responsible for handling duplicate messages)
  95
  96 ### DDL Capture via Event Triggers and Logical Messages
  97
  98 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:
  99
 100 ```sql
 101 -- Create event trigger function that emits DDL into replication stream
 102 CREATE OR REPLACE FUNCTION emit_ddl_to_stream()
 103 RETURNS event_trigger AS $$
 104 BEGIN
 105     -- Emit DDL command directly into logical replication stream
 106     -- The 'true' parameter makes this transactional (part of current transaction)
 107     -- The 'ddl' prefix allows the output plugin to identify these messages
 108     PERFORM pg_logical_emit_message(
 109         true,                    -- transactional
 110         'ddl',                   -- prefix for identification
 111         current_query()::text    -- the actual DDL SQL command
 112     );
 113 END;
 114 $$ LANGUAGE plpgsql;
 115
 116 -- Register the event trigger
 117 CREATE EVENT TRIGGER emit_ddl_to_stream ON ddl_command_end
 118     EXECUTE FUNCTION emit_ddl_to_stream();
 119 ```
 120
 121 **How it works**:
 122 1. Event trigger fires on all DDL commands
 123 2. `pg_logical_emit_message()` writes DDL into WAL as a logical decoding message
 124 3. Message appears in replication stream at exact chronological position relative to DML
 125 4. Output plugin's `message_cb` callback outputs DDL as executable SQL
 126 5. Restore is simple: just execute the incremental backup file sequentially
 127
 128 **Example incremental backup output**:
 129 ```sql
 130 BEGIN;
 131 INSERT INTO public.users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');
 132 COMMIT;
 133 BEGIN;
 134 -- DDL message appears at exact time of execution
 135 ALTER TABLE public.users DROP COLUMN email;
 136 COMMIT;
 137 BEGIN;
 138 -- Subsequent DML only references remaining columns
 139 INSERT INTO public.users (id, name) VALUES (2, 'Bob');
 140 COMMIT;
 141 ```
 142
 143 **Key Properties**:
 144 - ✅ **Perfect chronological ordering**: DDL appears exactly when it was executed
 145 - ✅ **Transactional integrity**: DDL message commits with its transaction
 146 - ✅ **Simple restore**: Execute backup file sequentially with `psql -f`
 147 - ✅ **PostgreSQL built-in**: `pg_logical_emit_message()` available since PostgreSQL 9.6
 148
 149 **Limitations**:
 150 - Event triggers don't fire for shared objects: databases, roles, tablespaces, parameter privileges, and ALTER SYSTEM commands
 151 - Solution: Use periodic `pg_dumpall --globals-only` to capture shared objects
 152
 153 ## Implementation Components
 154
 155 ### 1. Initial Setup Script
 156
 157 **Purpose**: Bootstrap the backup system
 158 **Status**: ⏳ **PLANNED** - Not yet implemented
 159
 160 **Tasks**:
 161 - Install wal2sql plugin (✅ complete - compile from source)
 162 - Create logical replication slot with snapshot export (see detailed procedure below)
 163 - Capture the exported snapshot identifier
 164 - Take initial base backup using the exported snapshot
 165 - Set up event triggers for DDL capture
 166 - Create initial `pg_dumpall --globals-only` backup
 167 - Configure `REPLICA IDENTITY` on tables without primary keys
 168 - Document PostgreSQL version and installed extensions
 169
 170 **Critical Detail - Initial Snapshot Consistency**:
 171
 172 From PostgreSQL documentation (Section 47.2.5):
 173 > 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.
 174
 175 This ensures the base backup and incremental stream are perfectly aligned with no gaps or overlaps.
 176
 177 **Step-by-Step Setup Procedure**:
 178
 179 1. **Create replication slot with snapshot export**:
 180    - Use `pg_recvlogical --create-slot` with `--if-not-exists` flag for idempotency
 181    - Command outputs snapshot identifier in format: `snapshot: 00000003-00000001-1`
 182    - Capture this identifier from stdout/stderr (appears on stderr with "snapshot:" prefix)
 183
 184 2. **Extract and validate snapshot identifier**:
 185    - Parse the snapshot identifier from slot creation output
 186    - Verify identifier was successfully captured before proceeding
 187    - Snapshot is only valid during the creating session/connection
 188
 189 3. **Take synchronized base backup**:
 190    - Use `pg_dump --snapshot=<identifier> --file=backup.sql` with captured snapshot
 191    - This guarantees perfect alignment between base backup and incremental stream
 192    - Use `--compress=zstd:9` for large databases to reduce storage requirements
 193
 194 4. **Capture globals and metadata**:
 195    - Run `pg_dumpall --globals-only` for roles, tablespaces
 196    - Document PostgreSQL version, extensions, encoding in metadata file
 197
 198 **Critical Considerations**:
 199 - **Snapshot validity**: Exported snapshot only valid until the session that created it disconnects; must use immediately or maintain connection
 200 - **Idempotency**: The `--if-not-exists` flag allows safe re-execution of setup scripts
 201 - **Timing**: Entire sequence (create slot → capture snapshot → run pg_dump) must complete while snapshot remains valid
 202
 203 ### 2. Incremental Backup Collection
 204
 205 **Status**: ⏳ **PLANNED** - Wrapper script not yet implemented (pg_recvlogical ready to use)
 206 **Tool**: Built-in `pg_recvlogical` utility with `wal2sql` plugin
 207
 208 **Key Configuration**:
 209
 210 Run `pg_recvlogical --start` with the following important parameters:
 211
 212 - **Status interval** (`--status-interval`): Controls how often client reports position back to server (default: 10 seconds)
 213   - Lower values allow server to advance slot position and free WAL more quickly
 214   - Balance between slot health and network overhead
 215
 216 - **Fsync interval** (`--fsync-interval`): Controls disk write safety (recommended: 10 seconds)
 217   - Frequency of forced disk synchronization for crash safety on backup client
 218   - 0 disables fsync (faster but risks data loss if client crashes)
 219   - Higher values improve performance but increase window of potential loss
 220
 221 - **Plugin options** (`--option`): Pass `include_transaction=on` to wal2sql
 222   - Includes BEGIN/COMMIT statements in output
 223   - Essential for maintaining transaction boundaries during restore
 224
 225 **What pg_recvlogical provides**:
 226 - Streams decoded changes continuously from the replication slot
 227 - Handles connection failures and automatic reconnection
 228 - Tracks LSN positions with status updates to the server
 229 - Supports file rotation via SIGHUP signal (for log rotation without stopping stream)
 230
 231 **What wal2sql provides** (✅ **ALL IMPLEMENTED**):
 232 - ✅ Schema-qualified table names: `INSERT INTO public.users (...)`
 233 - ✅ Proper column name quoting with `quote_identifier()`
 234 - ✅ Transaction boundaries via `include_transaction=on` option (BEGIN/COMMIT)
 235 - ✅ Intelligent replica identity handling (DEFAULT, INDEX, FULL, NOTHING)
 236 - ✅ Comprehensive data type support (booleans, NaN, Infinity, NULL, strings, bit strings)
 237 - ✅ TOAST optimization (skips unchanged TOAST columns in UPDATEs)
 238 - ✅ Production-quality memory management
 239 - ✅ **`message_cb` callback** to handle DDL messages from `pg_logical_emit_message()`
 240 - ✅ **`truncate_cb` callback** to handle TRUNCATE operations
 241
 242 **Custom wrapper script tasks** (⏳ **PLANNED**):
 243 - File rotation and timestamping
 244 - Coordinate with monitoring system
 245 - Metadata file generation (PostgreSQL version, extensions, encoding, collation)
 246
 247 ### 3. Periodic Full Backup Script
 248
 249 **Status**: ⏳ **PLANNED** - Not yet implemented
 250 **Purpose**: Take regular full backups as restore points
 251
 252 **Tasks**:
 253 - Execute `pg_dump --file=backup.sql` to create full database backup in plain SQL format
 254 - Execute `pg_dumpall --globals-only` to capture shared objects (databases, roles, tablespaces)
 255 - Compress backups to save space
 256 - Implement retention policy (delete old backups)
 257
 258 **Backup Approach**:
 259 - Use `pg_dump --file=backup.sql` for plain SQL output
 260 - Maintains human-readable format consistent with design goals
 261 - Single connection to database
 262 - Universal compatibility across PostgreSQL versions
 263
 264 **Compression**:
 265 - Use `pg_dump --compress=<method>` for built-in compression (e.g., `--compress=zstd:9`)
 266 - Or compress after creation with external tools (gzip, zstd)
 267 - Compression significantly reduces storage requirements while maintaining recoverability
 268
 269 ### 4. Restore Script
 270
 271 **Status**: ⏳ **PLANNED** - Not yet implemented
 272 **Purpose**: Restore database to latest captured state from base + incremental backups
 273
 274 **Restore Process**:
 275 1. Locate most recent full backup
 276 2. Find all incremental backups since that full backup
 277 3. Create new target database
 278 4. Restore `pg_dumpall --globals-only` (shared objects: roles, tablespaces)
 279 5. Restore full `pg_dump` backup using `psql -f backup.sql`
 280 6. Apply all incremental SQL backups in chronological order using `psql -f incremental-*.sql`
 281    - DDL and DML are already interleaved in correct chronological order
 282    - No separate DDL extraction or ordering step needed
 283 7. Synchronize sequence values using `setval()` with max values from tables
 284 8. Verify data integrity (row counts, application smoke tests)
 285
 286 **Sequence Synchronization**:
 287 - After applying all changes, sequences may be behind actual max values
 288 - Query `information_schema.sequences` to find all sequences
 289 - For each sequence: `SELECT setval('sequence_name', COALESCE(MAX(id_column), 1)) FROM table`
 290 - Can be automated by generating setval queries from schema metadata
 291
 292 **Handling Duplicate Transactions**:
 293
 294 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:
 295
 296 - Most SQL operations are idempotent or fail safely:
 297   - INSERT fails on duplicate primary key (acceptable during restore)
 298   - UPDATE reapplies same values (idempotent)
 299   - DELETE succeeds or reports row not found (acceptable)
 300 - Transaction boundaries (BEGIN/COMMIT from `include_transaction=on`) preserve consistency
 301 - Simply apply all incremental files in order; duplicates will be handled correctly
 302 - No additional LSN tracking infrastructure required
 303
 304 ### 5. Monitoring and Health Check Script
 305
 306 **Status**: ⏳ **PLANNED** - Not yet implemented
 307 **Purpose**: Prevent operational issues from inactive replication slots
 308
 309 **Critical Metrics**:
 310 ```sql
 311 -- Check replication slot health
 312 SELECT
 313     slot_name,
 314     slot_type,
 315     database,
 316     active,
 317     pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as replication_lag,
 318     pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) as confirmed_lag
 319 FROM pg_replication_slots
 320 WHERE slot_type = 'logical';
 321 ```
 322
 323 **Alerting**:
 324 - **Critical alert** when `restart_lsn` falls more than 1GB behind
 325 - **Emergency alert** when slot lag exceeds 10GB or age exceeds 24 hours
 326 - **Emergency procedure** documented to drop slot if it threatens database availability
 327
 328 **Available Tools**:
 329 - **Prometheus + postgres_exporter + Grafana**: Open-source monitoring stack
 330 - **pgDash** (https://pgdash.io/): Commercial PostgreSQL monitoring
 331 - **check_postgres**: Nagios/Icinga/Zabbix integration
 332 - **Built-in views**: `pg_replication_slots`, `pg_stat_replication_slots`
 333
 334 ## wal2sql Plugin Details
 335
 336 **Status**: ✅ **COMPLETE** - All required functionality implemented and tested
 337 **Source**: Forked from decoder_raw (https://github.com/michaelpq/pg_plugins/tree/main/decoder_raw)
 338 **Location**: `wal2sql/` directory
 339 **Compatibility**: PostgreSQL 18.0+
 340
 341 **Installation**:
 342 ```bash
 343 cd wal2sql
 344 make && make install
 345 PGUSER=postgres make installcheck  # Run tests
 346 ```
 347
 348 **Implemented Features**:
 349 - ✅ INSERT/UPDATE/DELETE statements with proper escaping
 350 - ✅ Transaction boundaries (BEGIN/COMMIT)
 351 - ✅ Replica identity handling (DEFAULT, INDEX, FULL, NOTHING)
 352 - ✅ **`message_cb`** - DDL capture via `pg_logical_emit_message()`
 353 - ✅ **`truncate_cb`** - TRUNCATE statement capture
 354 - ✅ Comprehensive data type support (booleans, NaN, Infinity, NULL, strings)
 355 - ✅ TOAST optimization
 356 - ✅ Full test coverage
 357
 358 ## Key Challenges and Solutions
 359
 360 ### 1. Replica Identity Required for UPDATE/DELETE
 361
 362 **Problem**: Tables need replica identity for UPDATE/DELETE operations.
 363
 364 From PostgreSQL documentation (Section 29.1.1):
 365 > 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.**
 366
 367 This means UPDATE/DELETE will **fail on the source database**, not just during restore!
 368
 369 **Solution**: Ensure all tables have one of:
 370 - A primary key (automatic replica identity)
 371 - A unique index configured via `REPLICA IDENTITY USING INDEX index_name`
 372 - Explicit `REPLICA IDENTITY FULL` setting (inefficient, last resort)
 373
 374 **Example**:
 375 ```sql
 376 -- Table without primary key will error on UPDATE/DELETE
 377 CREATE TABLE logs (timestamp TIMESTAMPTZ, message TEXT);
 378
 379 -- Fix: Set replica identity to FULL
 380 ALTER TABLE logs REPLICA IDENTITY FULL;
 381 ```
 382
 383 ### 2. Replication Slots Prevent WAL Cleanup
 384
 385 **Problem**: Inactive replication slots prevent WAL cleanup, leading to:
 386 1. Disk fills up (WAL files not cleaned)
 387 2. Table bloat (VACUUM cannot clean old row versions)
 388 3. **Database shutdown** (transaction ID wraparound)
 389
 390 From PostgreSQL documentation (Section 47.2.2):
 391 > In extreme cases this could cause the database to shut down to prevent transaction ID wraparound.
 392
 393 **Solution**:
 394 - **Monitor slot lag aggressively** (see monitoring section)
 395 - Set `max_slot_wal_keep_size` parameter (PostgreSQL 13+) to limit WAL retention
 396 - Have documented emergency procedure to drop slot if needed
 397 - Consider `pg_replication_slot_advance()` to skip ahead (loses backup coverage)
 398
 399 ### 3. Sequences Are Not Replicated
 400
 401 **Problem**: Sequence values are not captured in logical replication.
 402
 403 **Solution**:
 404 - Use `pg_dump --sequence-data` (enabled by default) in periodic full dumps
 405 - After restore, synchronize sequences:
 406   ```sql
 407   SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));
 408   ```
 409
 410 ### 4. Large Objects Are Not Replicated
 411
 412 **Problem**: PostgreSQL large objects are not captured in logical replication.
 413
 414 **Solution**:
 415 - **Preferred**: Use `BYTEA` columns instead (these ARE replicated)
 416 - **Alternative**: Use `pg_dump --large-objects` in periodic full backups
 417   - Note: Incremental changes to large objects NOT captured between full backups
 418
 419 ### 5. Crash Recovery and Duplicate Handling
 420
 421 **Problem**: After database crash, slot position may roll back, causing duplicate changes.
 422
 423 From PostgreSQL documentation (Section 47.2.2):
 424 > 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.
 425
 426 **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."
 427
 428 **Implementation**:
 429 - Most SQL operations in backup files are naturally idempotent:
 430   - INSERT will fail on duplicate primary key (acceptable during restore)
 431   - UPDATE will reapply same values (idempotent)
 432   - DELETE will succeed or report row not found (acceptable)
 433 - Transaction boundaries (BEGIN/COMMIT from `include_transaction=on`) ensure consistency
 434 - Simply apply all incremental files in chronological order
 435 - No additional LSN tracking infrastructure required
 436 - See Restore Script section (Section 4) for implementation details
 437
 438 **Testing**:
 439 - Test crash scenarios with `pg_ctl stop -m immediate` to verify duplicate handling
 440 - Monitor `confirmed_flush_lsn` lag during normal operations (see Monitoring section)
 441
 442 ### 6. Long-Term Readability
 443
 444 **Challenges**:
 445 - PostgreSQL syntax may change between major versions (rare)
 446 - Extension dependencies may not exist in future systems
 447 - Encoding/collation definitions may change
 448
 449 **Solution**: Include metadata file with each backup:
 450 - PostgreSQL version (full version string)
 451 - All installed extension names and versions
 452 - Database encoding
 453 - Locale and collation settings
 454 - Custom data types and enums
 455
 456 Periodically test restoring old backups on current PostgreSQL versions.
 457
 458 ## Prerequisites and Configuration
 459
 460 ### PostgreSQL Configuration
 461
 462 ```ini
 463 # postgresql.conf
 464
 465 # Required: Set WAL level to logical
 466 wal_level = logical
 467
 468 # Required: Allow at least one replication slot
 469 max_replication_slots = 10
 470
 471 # Recommended: Allow replication connections
 472 max_wal_senders = 10
 473
 474 # Recommended: Keep more WAL for safety
 475 wal_keep_size = 1GB
 476
 477 # Recommended: Limit WAL retention for safety (PostgreSQL 13+)
 478 max_slot_wal_keep_size = 10GB
 479
 480 # Optional: Tune checkpoint frequency to persist slot positions more often
 481 checkpoint_timeout = 5min
 482 ```
 483
 484 ### Client Requirements
 485
 486 - PostgreSQL client utilities installed (`pg_recvlogical`, `pg_dump`, `pg_dumpall`)
 487 - Superuser or role with `REPLICATION` privilege
 488 - Permission to create replication slots
 489 - decoder_raw plugin compiled and installed
 490
 491 ## Operational Procedures
 492
 493 ### Backup Schedule
 494
 495 **Recommended**:
 496 - **Incremental**: Continuously streaming via `pg_recvlogical`
 497 - **Full backup**: Daily at 2 AM
 498 - **Globals backup**: Daily (`pg_dumpall --globals-only`)
 499 - **Metadata export**: Daily (PostgreSQL version, extensions, encoding)
 500
 501 ### Retention Policy
 502
 503 - **Incremental backups**: Keep 7 days
 504 - **Full backups**: Keep 30 days, then one per month for 1 year
 505 - **Monitor disk space**: Alert if backup directory exceeds 80% capacity
 506
 507 ### Disaster Recovery Runbook
 508
 509 1. **Stop application** to prevent new writes during restore
 510 2. **Create new database** (don't overwrite production)
 511 3. **Restore shared objects**: `psql -f globals-YYYY-MM-DD.sql`
 512 4. **Restore full backup**: `psql dbname < base-YYYY-MM-DD.sql`
 513 5. **Apply all incremental backups**: `for f in incremental-*.sql; do psql dbname < "$f"; done`
 514    - DDL and DML are already interleaved in correct chronological order
 515 6. **Sync sequences**: Run `setval()` for all sequences to match table max values
 516 7. **Verify data integrity**: Check row counts, run application smoke tests
 517 8. **Test application** against restored database
 518 9. **Switch over** application to restored database
 519
 520 **See Section 4 (Restore Script)** for detailed procedures including sequence synchronization and duplicate transaction handling.
 521
 522 ## Testing Strategy
 523
 524 ### 1. Basic Functionality Test
 525
 526 ```sql
 527 -- Create test database and setup
 528 CREATE DATABASE backup_test;
 529 \c backup_test
 530
 531 -- Create test table
 532 CREATE TABLE test_users (id SERIAL PRIMARY KEY, name TEXT, created_at TIMESTAMP DEFAULT now());
 533
 534 -- Generate data and schema changes to test DDL/DML ordering
 535 INSERT INTO test_users (name) VALUES ('Alice'), ('Bob'), ('Charlie');
 536 UPDATE test_users SET name = 'Alice Smith' WHERE id = 1;
 537 DELETE FROM test_users WHERE id = 3;
 538
 539 -- Add column - DDL message should appear in stream here
 540 ALTER TABLE test_users ADD COLUMN email TEXT;
 541
 542 -- Use the new column - should work because DDL already executed
 543 UPDATE test_users SET email = 'alice@example.com' WHERE id = 1;
 544
 545 -- Drop column - DDL message should appear in stream here
 546 ALTER TABLE test_users DROP COLUMN created_at;
 547
 548 -- Subsequent inserts should work without the dropped column
 549 INSERT INTO test_users (name, email) VALUES ('David', 'david@example.com');
 550
 551 -- Restore and verify:
 552 -- 1. All operations should replay successfully
 553 -- 2. DML before column add should not reference email column
 554 -- 3. DML after column add should reference email column
 555 -- 4. DML after column drop should not reference created_at column
 556 ```
 557
 558 ### 2. Crash Recovery Test
 559
 560 ```bash
 561 # Start collecting incrementals
 562 # Generate load with pgbench
 563 # Simulate crash: pg_ctl stop -m immediate
 564 # Restart PostgreSQL
 565 # Verify no data loss and duplicates handled correctly
 566 # Restore and verify
 567 ```
 568
 569 ### 3. Long-Term Storage Test
 570
 571 ```bash
 572 # Create backup
 573 # Store backup files
 574 # Wait (or simulate) years passing
 575 # Restore on modern PostgreSQL version
 576 # Verify SQL is still readable and executable
 577 ```
 578
 579 ### 4. Replica Identity Test
 580
 581 ```sql
 582 -- Create table without primary key
 583 CREATE TABLE test_no_pk (col1 TEXT, col2 INT);
 584
 585 -- Attempt UPDATE (should fail with replica identity error)
 586 UPDATE test_no_pk SET col2 = 5 WHERE col1 = 'test';
 587
 588 -- Fix with REPLICA IDENTITY FULL
 589 ALTER TABLE test_no_pk REPLICA IDENTITY FULL;
 590
 591 -- Retry UPDATE (should succeed)
 592 ```
 593
 594 ### 5. TRUNCATE Handling Test
 595
 596 ```sql
 597 -- Create test table
 598 CREATE TABLE test_truncate (id INT);
 599 INSERT INTO test_truncate VALUES (1), (2), (3);
 600
 601 -- Perform TRUNCATE
 602 TRUNCATE test_truncate;
 603
 604 -- Verify: Check if wal2sql incremental backup captured TRUNCATE
 605 -- Expected: ✅ CAPTURED by wal2sql with truncate_cb
 606 -- Look for: TRUNCATE TABLE public.test_truncate;
 607 -- Note: Event triggers do NOT capture TRUNCATE (it's DML, not DDL)
 608
 609 -- Test TRUNCATE with multiple tables (foreign key cascade)
 610 CREATE TABLE parent_table (id INT PRIMARY KEY);
 611 CREATE TABLE child_table (parent_id INT REFERENCES parent_table(id));
 612 INSERT INTO parent_table VALUES (1), (2);
 613 INSERT INTO child_table VALUES (1), (2);
 614
 615 -- TRUNCATE CASCADE should capture both tables
 616 TRUNCATE parent_table, child_table;
 617 -- Expected output: TRUNCATE TABLE public.parent_table, public.child_table;
 618 ```
 619
 620 ## Performance Considerations
 621
 622 **Write Amplification**:
 623 - WAL must be written (normal)
 624 - WAL must be decoded into logical format (additional CPU)
 625 - Event triggers fire on every DDL operation (minimal overhead)
 626
 627 **Disk I/O**:
 628 - Additional WAL volume retained by replication slots
 629 - More frequent checkpoint I/O if checkpoint_timeout is tuned
 630
 631 **Recommendations**:
 632 - Benchmark overhead on test system with production-like workload
 633 - Monitor CPU usage of WAL sender processes
 634 - Monitor disk usage for WAL and backup directories
 635
 636 ## Implementation Status & Next Steps
 637
 638 ### ✅ Completed
 639
 640 1. **wal2sql Plugin** - Fully implemented and tested
 641    - ✅ Core DML operations (INSERT/UPDATE/DELETE)
 642    - ✅ `message_cb` for DDL capture
 643    - ✅ `truncate_cb` for TRUNCATE support
 644    - ✅ Comprehensive test suite passing
 645
 646 ### ⏳ Next Steps
 647
 648 1. **Setup Script** - Create initial backup environment
 649    - Create replication slot with `wal2sql`
 650    - Set up event triggers for DDL capture using `pg_logical_emit_message()`
 651    - Take synchronized base backup with `pg_dump --snapshot`
 652    - Configure `REPLICA IDENTITY` on tables
 653
 654 2. **Backup Collection Script** - Wrapper around `pg_recvlogical`
 655    - File rotation and timestamping
 656    - Metadata generation (PostgreSQL version, extensions, encoding)
 657    - Error handling and restart logic
 658
 659 3. **Restore Script** - Apply base + incremental backups
 660    - Locate and order backup files
 661    - Apply in sequence with error handling
 662    - Sequence synchronization with `setval()`
 663
 664 4. **Monitoring Script** - Replication slot health checks
 665    - Track slot lag and WAL retention
 666    - Alert on dangerous conditions
 667    - Emergency procedures documentation
 668
 669 5. **Integration Testing**
 670    - End-to-end backup and restore
 671    - DDL/DML interleaving scenarios
 672    - Crash recovery with `pg_ctl stop -m immediate`
 673    - Performance benchmarking
 674
 675 ## References
 676
 677 ### PostgreSQL Documentation
 678 - PostgreSQL Documentation: Chapter 25 - Backup and Restore
 679 - PostgreSQL Documentation: Chapter 29 - Logical Replication
 680 - PostgreSQL Documentation: Chapter 47 - Logical Decoding
 681 - PostgreSQL Documentation: Section 29.8 - Logical Replication Restrictions
 682 - PostgreSQL Documentation: Section 47.6.4.8 - Generic Message Callback (message_cb)
 683 - PostgreSQL Documentation: Section 9.28.6 - `pg_logical_emit_message()` function
 684 - PostgreSQL Documentation: `pg_recvlogical` man page
 685 - PostgreSQL Documentation: `pg_dump` man page
 686 - PostgreSQL Documentation: `pg_dumpall` man page
 687
 688 ### Essential Tools
 689 - **wal2sql**: ✅ **IMPLEMENTED** - SQL output plugin for logical decoding
 690   - Location: `wal2sql/` directory in this repository
 691   - Forked from: https://github.com/michaelpq/pg_plugins/tree/main/decoder_raw
 692   - License: PostgreSQL License (production-ready)
 693   - Compatibility: PostgreSQL 18.0+
 694
 695 ### Monitoring Tools
 696 - **Prometheus + postgres_exporter + Grafana**: Open-source monitoring stack
 697 - **pgDash**: PostgreSQL monitoring - https://pgdash.io/
 698 - **check_postgres**: Nagios/Icinga/Zabbix integration
 699 - **pg_stat_replication_slots**: Built-in PostgreSQL monitoring view