begriffs open source - pg_scribe/blob - logical-replication-backup-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 `decoder_raw` 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**: `decoder_raw` 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. **decoder_raw extension** - Third-party plugin must be extended with TRUNCATE support and `message_cb` callback
  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 │                         │  (decoder_raw 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. **decoder_raw Plugin**: Transforms binary WAL to executable SQL
  72 3. **pg_recvlogical**: Built-in PostgreSQL tool that streams logical decoding output
  73 4. **Base Backup System**: Regular full `pg_dump` backups with `--snapshot` for consistency
  74 5. **Schema Tracking System**: 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 `decoder_raw` 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
 159 **Tasks**:
 160 - Install decoder_raw plugin (compile from source)
 161 - Create logical replication slot with snapshot export (see detailed procedure below)
 162 - Capture the exported snapshot identifier
 163 - Take initial base backup using the exported snapshot
 164 - Set up event triggers for DDL capture
 165 - Create initial `pg_dumpall --globals-only` backup
 166 - Configure `REPLICA IDENTITY` on tables without primary keys
 167 - Document PostgreSQL version and installed extensions
 168
 169 **Critical Detail - Initial Snapshot Consistency**:
 170
 171 From PostgreSQL documentation (Section 47.2.5):
 172 > 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.
 173
 174 This ensures the base backup and incremental stream are perfectly aligned with no gaps or overlaps.
 175
 176 **Step-by-Step Setup Procedure**:
 177
 178 1. **Create replication slot with snapshot export**:
 179    - Use `pg_recvlogical --create-slot` with `--if-not-exists` flag for idempotency
 180    - Command outputs snapshot identifier in format: `snapshot: 00000003-00000001-1`
 181    - Capture this identifier from stdout/stderr (appears on stderr with "snapshot:" prefix)
 182
 183 2. **Extract and validate snapshot identifier**:
 184    - Parse the snapshot identifier from slot creation output
 185    - Verify identifier was successfully captured before proceeding
 186    - Snapshot is only valid during the creating session/connection
 187
 188 3. **Take synchronized base backup**:
 189    - Use `pg_dump --snapshot=<identifier> --file=backup.sql` with captured snapshot
 190    - This guarantees perfect alignment between base backup and incremental stream
 191    - Use `--compress=zstd:9` for large databases to reduce storage requirements
 192
 193 4. **Capture globals and metadata**:
 194    - Run `pg_dumpall --globals-only` for roles, tablespaces
 195    - Document PostgreSQL version, extensions, encoding in metadata file
 196
 197 **Critical Considerations**:
 198 - **Snapshot validity**: Exported snapshot only valid until the session that created it disconnects; must use immediately or maintain connection
 199 - **Idempotency**: The `--if-not-exists` flag allows safe re-execution of setup scripts
 200 - **Timing**: Entire sequence (create slot → capture snapshot → run pg_dump) must complete while snapshot remains valid
 201
 202 ### 2. Incremental Backup Collection
 203
 204 **Tool**: Built-in `pg_recvlogical` utility with `decoder_raw` plugin
 205
 206 **Key Configuration**:
 207
 208 Run `pg_recvlogical --start` with the following important parameters:
 209
 210 - **Status interval** (`--status-interval`): Controls how often client reports position back to server (recommended: 10 seconds)
 211   - Lower values (5-10s) allow server to advance slot position and free WAL more quickly
 212   - Too high risks slot lag and WAL accumulation
 213   - Balance between slot health and network overhead
 214
 215 - **Fsync interval** (`--fsync-interval`): Controls disk write safety (recommended: 10 seconds)
 216   - Frequency of forced disk synchronization for crash safety on backup client
 217   - 0 disables fsync (faster but risks data loss if client crashes)
 218   - Higher values improve performance but increase window of potential loss
 219
 220 - **Plugin options** (`--option`): Pass `include_transaction=on` to decoder_raw
 221   - Includes BEGIN/COMMIT statements in output
 222   - Essential for maintaining transaction boundaries during restore
 223
 224 **What pg_recvlogical provides**:
 225 - Streams decoded changes continuously from the replication slot
 226 - Handles connection failures and automatic reconnection
 227 - Tracks LSN positions with status updates to the server
 228 - Supports file rotation via SIGHUP signal (for log rotation without stopping stream)
 229
 230 **What decoder_raw provides**:
 231 - ✅ Schema-qualified table names: `INSERT INTO public.users (...)`
 232 - ✅ Proper column name quoting with `quote_identifier()`
 233 - ✅ Transaction boundaries via `include_transaction=on` option (BEGIN/COMMIT)
 234 - ✅ Intelligent replica identity handling (DEFAULT, INDEX, FULL, NOTHING)
 235 - ✅ Comprehensive data type support (booleans, NaN, Infinity, NULL, strings, bit strings)
 236 - ✅ TOAST optimization (skips unchanged TOAST columns in UPDATEs)
 237 - ✅ Production-quality memory management
 238
 239 **What decoder_raw needs (via extension)**:
 240 - ⚠️ **`message_cb` callback** to handle DDL messages from `pg_logical_emit_message()`
 241 - ⚠️ **`truncate_cb` callback** to handle TRUNCATE operations
 242
 243 **Custom wrapper script tasks**:
 244 - File rotation and timestamping
 245 - Coordinate with monitoring system
 246 - Metadata file generation (PostgreSQL version, extensions, encoding, collation)
 247
 248 ### 3. Periodic Full Backup Script
 249
 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 **Purpose**: Restore database to latest captured state from base + incremental backups
 272
 273 **Restore Process**:
 274 1. Locate most recent full backup
 275 2. Find all incremental backups since that full backup
 276 3. Create new target database
 277 4. Restore `pg_dumpall --globals-only` (shared objects: roles, tablespaces)
 278 5. Restore full `pg_dump` backup using `psql -f backup.sql`
 279 6. Apply all incremental SQL backups in chronological order using `psql -f incremental-*.sql`
 280    - DDL and DML are already interleaved in correct chronological order
 281    - No separate DDL extraction or ordering step needed
 282 7. Synchronize sequence values using `setval()` with max values from tables
 283 8. Verify data integrity (row counts, application smoke tests)
 284
 285 **Sequence Synchronization**:
 286 - After applying all changes, sequences may be behind actual max values
 287 - Query `information_schema.sequences` to find all sequences
 288 - For each sequence: `SELECT setval('sequence_name', COALESCE(MAX(id_column), 1)) FROM table`
 289 - Can be automated by generating setval queries from schema metadata
 290
 291 **Handling Duplicate Transactions**:
 292
 293 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:
 294
 295 - Most SQL operations are idempotent or fail safely:
 296   - INSERT fails on duplicate primary key (acceptable during restore)
 297   - UPDATE reapplies same values (idempotent)
 298   - DELETE succeeds or reports row not found (acceptable)
 299 - Transaction boundaries (BEGIN/COMMIT from `include_transaction=on`) preserve consistency
 300 - Simply apply all incremental files in order; duplicates will be handled correctly
 301 - No additional LSN tracking infrastructure required
 302
 303 ### 5. Monitoring and Health Check Script
 304
 305 **Purpose**: Prevent operational issues from inactive replication slots
 306
 307 **Critical Metrics**:
 308 ```sql
 309 -- Check replication slot health
 310 SELECT
 311     slot_name,
 312     slot_type,
 313     database,
 314     active,
 315     pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as replication_lag,
 316     pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) as confirmed_lag
 317 FROM pg_replication_slots
 318 WHERE slot_type = 'logical';
 319 ```
 320
 321 **Alerting**:
 322 - **Critical alert** when `restart_lsn` falls more than 1GB behind
 323 - **Emergency alert** when slot lag exceeds 10GB or age exceeds 24 hours
 324 - **Emergency procedure** documented to drop slot if it threatens database availability
 325
 326 **Available Tools**:
 327 - **Prometheus + postgres_exporter + Grafana**: Open-source monitoring stack
 328 - **pgDash** (https://pgdash.io/): Commercial PostgreSQL monitoring
 329 - **check_postgres**: Nagios/Icinga/Zabbix integration
 330 - **Built-in views**: `pg_replication_slots`, `pg_stat_replication_slots`
 331
 332 ## decoder_raw Plugin Details
 333
 334 **Source**: https://github.com/michaelpq/pg_plugins/tree/main/decoder_raw
 335 **License**: PostgreSQL License (permissive, production-ready)
 336 **Compatibility**: PostgreSQL 9.4+
 337
 338 **Installation**:
 339 ```bash
 340 # Install PostgreSQL development headers
 341 apt-get install postgresql-server-dev-XX  # Debian/Ubuntu
 342 yum install postgresql-devel              # RHEL/CentOS
 343
 344 # Clone and compile
 345 git clone https://github.com/michaelpq/pg_plugins.git
 346 cd pg_plugins/decoder_raw
 347 make
 348 sudo make install
 349
 350 # Verify installation
 351 ls $(pg_config --pkglibdir)/decoder_raw.so
 352 ```
 353
 354 **Why decoder_raw is essential**:
 355 - Eliminates the entire SQL transformation layer
 356 - Handles all data type escaping correctly (strings, NULL, NaN, Infinity, booleans)
 357 - Produces production-ready SQL that can be executed with `psql -f changes.sql`
 358 - Mature codebase with comprehensive test suite
 359 - Clean code structure makes it straightforward to extend with additional callbacks
 360
 361 **Required Extensions to decoder_raw**:
 362
 363 The stock `decoder_raw` plugin does not implement two optional callbacks required for this design:
 364
 365 **1. `message_cb` Callback for DDL Capture**:
 366 - Required to handle messages from `pg_logical_emit_message()` (documented in Section 47.6.4.8)
 367 - Filter messages by prefix: only output messages with prefix `'ddl'`
 368 - Output the message content as executable SQL (already valid DDL)
 369 - Example output: `ALTER TABLE public.users DROP COLUMN email;`
 370 - Straightforward implementation (~30-50 lines)
 371
 372 **2. `truncate_cb` Callback for TRUNCATE Operations**:
 373 - The stock plugin silently ignores TRUNCATE operations during logical decoding
 374 - Event triggers for `ddl_command_end` only fire for DDL commands (CREATE, ALTER, DROP, etc.)
 375 - TRUNCATE is a DML operation, not DDL, and does NOT trigger event triggers
 376 - Must be captured via logical replication using `truncate_cb` (documented in Section 47.6.4.6)
 377 - The callback receives an array of relations since TRUNCATE can affect multiple tables via foreign keys
 378 - Should output: `TRUNCATE TABLE schema.table1, schema.table2;`
 379 - Straightforward implementation following existing patterns in decoder_raw.c (~50-100 lines)
 380 - Alternative: Enforce organizational policy against using TRUNCATE
 381
 382 ## Key Challenges and Solutions
 383
 384 ### 1. Replica Identity Required for UPDATE/DELETE
 385
 386 **Problem**: Tables need replica identity for UPDATE/DELETE operations.
 387
 388 From PostgreSQL documentation (Section 29.1.1):
 389 > 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.**
 390
 391 This means UPDATE/DELETE will **fail on the source database**, not just during restore!
 392
 393 **Solution**: Ensure all tables have one of:
 394 - A primary key (automatic replica identity)
 395 - A unique index configured via `REPLICA IDENTITY USING INDEX index_name`
 396 - Explicit `REPLICA IDENTITY FULL` setting (inefficient, last resort)
 397
 398 **Example**:
 399 ```sql
 400 -- Table without primary key will error on UPDATE/DELETE
 401 CREATE TABLE logs (timestamp TIMESTAMPTZ, message TEXT);
 402
 403 -- Fix: Set replica identity to FULL
 404 ALTER TABLE logs REPLICA IDENTITY FULL;
 405 ```
 406
 407 ### 2. Replication Slots Prevent WAL Cleanup
 408
 409 **Problem**: Inactive replication slots prevent WAL cleanup, leading to:
 410 1. Disk fills up (WAL files not cleaned)
 411 2. Table bloat (VACUUM cannot clean old row versions)
 412 3. **Database shutdown** (transaction ID wraparound)
 413
 414 From PostgreSQL documentation (Section 47.2.2):
 415 > In extreme cases this could cause the database to shut down to prevent transaction ID wraparound.
 416
 417 **Solution**:
 418 - **Monitor slot lag aggressively** (see monitoring section)
 419 - Set `max_slot_wal_keep_size` parameter (PostgreSQL 13+) to limit WAL retention
 420 - Have documented emergency procedure to drop slot if needed
 421 - Consider `pg_replication_slot_advance()` to skip ahead (loses backup coverage)
 422
 423 ### 3. Sequences Are Not Replicated
 424
 425 **Problem**: Sequence values are not captured in logical replication.
 426
 427 **Solution**:
 428 - Use `pg_dump --sequence-data` (enabled by default) in periodic full dumps
 429 - After restore, synchronize sequences:
 430   ```sql
 431   SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));
 432   ```
 433
 434 ### 4. Large Objects Are Not Replicated
 435
 436 **Problem**: PostgreSQL large objects are not captured in logical replication.
 437
 438 **Solution**:
 439 - **Preferred**: Use `BYTEA` columns instead (these ARE replicated)
 440 - **Alternative**: Use `pg_dump --large-objects` in periodic full backups
 441   - Note: Incremental changes to large objects NOT captured between full backups
 442
 443 ### 5. Crash Recovery and Duplicate Handling
 444
 445 **Problem**: After database crash, slot position may roll back, causing duplicate changes.
 446
 447 From PostgreSQL documentation (Section 47.2.2):
 448 > 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.
 449
 450 **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."
 451
 452 **Implementation**:
 453 - Most SQL operations in backup files are naturally idempotent:
 454   - INSERT will fail on duplicate primary key (acceptable during restore)
 455   - UPDATE will reapply same values (idempotent)
 456   - DELETE will succeed or report row not found (acceptable)
 457 - Transaction boundaries (BEGIN/COMMIT from `include_transaction=on`) ensure consistency
 458 - Simply apply all incremental files in chronological order
 459 - No additional LSN tracking infrastructure required
 460 - See Restore Script section (Section 4) for implementation details
 461
 462 **Testing**:
 463 - Test crash scenarios with `pg_ctl stop -m immediate` to verify duplicate handling
 464 - Monitor `confirmed_flush_lsn` lag during normal operations (see Monitoring section)
 465
 466 ### 6. Long-Term Readability
 467
 468 **Challenges**:
 469 - PostgreSQL syntax may change between major versions (rare)
 470 - Extension dependencies may not exist in future systems
 471 - Encoding/collation definitions may change
 472
 473 **Solution**: Include metadata file with each backup:
 474 - PostgreSQL version (full version string)
 475 - All installed extension names and versions
 476 - Database encoding
 477 - Locale and collation settings
 478 - Custom data types and enums
 479
 480 Periodically test restoring old backups on current PostgreSQL versions.
 481
 482 ## Prerequisites and Configuration
 483
 484 ### PostgreSQL Configuration
 485
 486 ```ini
 487 # postgresql.conf
 488
 489 # Required: Set WAL level to logical
 490 wal_level = logical
 491
 492 # Required: Allow at least one replication slot
 493 max_replication_slots = 10
 494
 495 # Recommended: Allow replication connections
 496 max_wal_senders = 10
 497
 498 # Recommended: Keep more WAL for safety
 499 wal_keep_size = 1GB
 500
 501 # Recommended: Limit WAL retention for safety (PostgreSQL 13+)
 502 max_slot_wal_keep_size = 10GB
 503
 504 # Optional: Tune checkpoint frequency to persist slot positions more often
 505 checkpoint_timeout = 5min
 506 ```
 507
 508 ### Client Requirements
 509
 510 - PostgreSQL client utilities installed (`pg_recvlogical`, `pg_dump`, `pg_dumpall`)
 511 - Superuser or role with `REPLICATION` privilege
 512 - Permission to create replication slots
 513 - decoder_raw plugin compiled and installed
 514
 515 ## Operational Procedures
 516
 517 ### Backup Schedule
 518
 519 **Recommended**:
 520 - **Incremental**: Continuously streaming via `pg_recvlogical`
 521 - **Full backup**: Daily at 2 AM
 522 - **Globals backup**: Daily (`pg_dumpall --globals-only`)
 523 - **Metadata export**: Daily (PostgreSQL version, extensions, encoding)
 524
 525 ### Retention Policy
 526
 527 - **Incremental backups**: Keep 7 days
 528 - **Full backups**: Keep 30 days, then one per month for 1 year
 529 - **Monitor disk space**: Alert if backup directory exceeds 80% capacity
 530
 531 ### Disaster Recovery Runbook
 532
 533 1. **Stop application** to prevent new writes during restore
 534 2. **Create new database** (don't overwrite production)
 535 3. **Restore shared objects**: `psql -f globals-YYYY-MM-DD.sql`
 536 4. **Restore full backup**: `psql dbname < base-YYYY-MM-DD.sql`
 537 5. **Apply all incremental backups**: `for f in incremental-*.sql; do psql dbname < "$f"; done`
 538    - DDL and DML are already interleaved in correct chronological order
 539 6. **Sync sequences**: Run `setval()` for all sequences to match table max values
 540 7. **Verify data integrity**: Check row counts, run application smoke tests
 541 8. **Test application** against restored database
 542 9. **Switch over** application to restored database
 543
 544 **See Section 4 (Restore Script)** for detailed procedures including sequence synchronization and duplicate transaction handling.
 545
 546 ## Testing Strategy
 547
 548 ### 1. Basic Functionality Test
 549
 550 ```sql
 551 -- Create test database and setup
 552 CREATE DATABASE backup_test;
 553 \c backup_test
 554
 555 -- Create test table
 556 CREATE TABLE test_users (id SERIAL PRIMARY KEY, name TEXT, created_at TIMESTAMP DEFAULT now());
 557
 558 -- Generate data and schema changes to test DDL/DML ordering
 559 INSERT INTO test_users (name) VALUES ('Alice'), ('Bob'), ('Charlie');
 560 UPDATE test_users SET name = 'Alice Smith' WHERE id = 1;
 561 DELETE FROM test_users WHERE id = 3;
 562
 563 -- Add column - DDL message should appear in stream here
 564 ALTER TABLE test_users ADD COLUMN email TEXT;
 565
 566 -- Use the new column - should work because DDL already executed
 567 UPDATE test_users SET email = 'alice@example.com' WHERE id = 1;
 568
 569 -- Drop column - DDL message should appear in stream here
 570 ALTER TABLE test_users DROP COLUMN created_at;
 571
 572 -- Subsequent inserts should work without the dropped column
 573 INSERT INTO test_users (name, email) VALUES ('David', 'david@example.com');
 574
 575 -- Restore and verify:
 576 -- 1. All operations should replay successfully
 577 -- 2. DML before column add should not reference email column
 578 -- 3. DML after column add should reference email column
 579 -- 4. DML after column drop should not reference created_at column
 580 ```
 581
 582 ### 2. Crash Recovery Test
 583
 584 ```bash
 585 # Start collecting incrementals
 586 # Generate load with pgbench
 587 # Simulate crash: pg_ctl stop -m immediate
 588 # Restart PostgreSQL
 589 # Verify no data loss and duplicates handled correctly
 590 # Restore and verify
 591 ```
 592
 593 ### 3. Long-Term Storage Test
 594
 595 ```bash
 596 # Create backup
 597 # Store backup files
 598 # Wait (or simulate) years passing
 599 # Restore on modern PostgreSQL version
 600 # Verify SQL is still readable and executable
 601 ```
 602
 603 ### 4. Replica Identity Test
 604
 605 ```sql
 606 -- Create table without primary key
 607 CREATE TABLE test_no_pk (col1 TEXT, col2 INT);
 608
 609 -- Attempt UPDATE (should fail with replica identity error)
 610 UPDATE test_no_pk SET col2 = 5 WHERE col1 = 'test';
 611
 612 -- Fix with REPLICA IDENTITY FULL
 613 ALTER TABLE test_no_pk REPLICA IDENTITY FULL;
 614
 615 -- Retry UPDATE (should succeed)
 616 ```
 617
 618 ### 5. TRUNCATE Handling Test
 619
 620 ```sql
 621 -- Create test table
 622 CREATE TABLE test_truncate (id INT);
 623 INSERT INTO test_truncate VALUES (1), (2), (3);
 624
 625 -- Perform TRUNCATE
 626 TRUNCATE test_truncate;
 627
 628 -- Verify: Check if decoder_raw incremental backup captured TRUNCATE
 629 -- Expected: SHOULD be captured by extended decoder_raw with truncate_cb
 630 -- Look for: TRUNCATE TABLE public.test_truncate;
 631 -- Note: Event triggers do NOT capture TRUNCATE (it's DML, not DDL)
 632
 633 -- Test TRUNCATE with multiple tables (foreign key cascade)
 634 CREATE TABLE parent_table (id INT PRIMARY KEY);
 635 CREATE TABLE child_table (parent_id INT REFERENCES parent_table(id));
 636 INSERT INTO parent_table VALUES (1), (2);
 637 INSERT INTO child_table VALUES (1), (2);
 638
 639 -- TRUNCATE CASCADE should capture both tables
 640 TRUNCATE parent_table, child_table;
 641 -- Expected output: TRUNCATE TABLE public.parent_table, public.child_table;
 642 ```
 643
 644 ## Performance Considerations
 645
 646 **Write Amplification**:
 647 - WAL must be written (normal)
 648 - WAL must be decoded into logical format (additional CPU)
 649 - Event triggers fire on every DDL operation (minimal overhead)
 650
 651 **Disk I/O**:
 652 - Additional WAL volume retained by replication slots
 653 - More frequent checkpoint I/O if checkpoint_timeout is tuned
 654
 655 **Recommendations**:
 656 - Benchmark overhead on test system with production-like workload
 657 - Monitor CPU usage of WAL sender processes
 658 - Monitor disk usage for WAL and backup directories
 659
 660 ## Next Steps for Proof of Concept
 661
 662 1. **Extend and install decoder_raw**
 663    - Clone pg_plugins repository
 664    - Install PostgreSQL development headers
 665    - Add `message_cb` callback to decoder_raw.c for DDL messages
 666    - Add `truncate_cb` callback to decoder_raw.c for TRUNCATE operations
 667    - Compile and install modified decoder_raw
 668    - Test both DDL message handling and TRUNCATE support
 669
 670 2. **Initial Setup**
 671    - Create replication slot with extended decoder_raw
 672    - Set up event triggers using `pg_logical_emit_message()` for DDL capture
 673    - Take initial synchronized base backup
 674
 675 3. **Streaming Collection**
 676    - Test `pg_recvlogical` with extended decoder_raw
 677    - Verify output is immediately executable SQL
 678    - Test with various data types and operations
 679
 680 4. **DDL Handling**
 681    - Test event trigger emits DDL messages correctly via `pg_logical_emit_message()`
 682    - Verify DDL appears in incremental backup stream at correct chronological position
 683    - Test DDL/DML interleaving (e.g., add column, insert with new column, drop column, insert without column)
 684    - Test `pg_dumpall --globals-only` captures shared objects
 685    - Verify simple sequential restore works correctly
 686
 687 5. **Monitoring Setup**
 688    - Configure replication slot monitoring
 689    - Set up critical alerting
 690    - Document emergency procedures
 691
 692 6. **Restore Process**
 693    - Build restore scripts
 694    - Test point-in-time recovery
 695    - Verify sequence synchronization
 696
 697 7. **Crash Recovery**
 698    - Test duplicate handling with `pg_ctl stop -m immediate`
 699    - Verify idempotent restore behavior
 700
 701 8. **Performance Testing**
 702    - Measure storage overhead
 703    - Measure CPU overhead
 704    - Benchmark restore time
 705
 706 ## References
 707
 708 ### PostgreSQL Documentation
 709 - PostgreSQL Documentation: Chapter 25 - Backup and Restore
 710 - PostgreSQL Documentation: Chapter 29 - Logical Replication
 711 - PostgreSQL Documentation: Chapter 47 - Logical Decoding
 712 - PostgreSQL Documentation: Section 29.8 - Logical Replication Restrictions
 713 - PostgreSQL Documentation: Section 47.6.4.8 - Generic Message Callback (message_cb)
 714 - PostgreSQL Documentation: Section 9.28.6 - `pg_logical_emit_message()` function
 715 - PostgreSQL Documentation: `pg_recvlogical` man page
 716 - PostgreSQL Documentation: `pg_dump` man page
 717 - PostgreSQL Documentation: `pg_dumpall` man page
 718
 719 ### Essential Tools
 720 - **decoder_raw**: SQL output plugin for logical decoding
 721   - Source: https://github.com/michaelpq/pg_plugins/tree/main/decoder_raw
 722   - **CRITICAL COMPONENT**: Eliminates output transformation layer
 723   - License: PostgreSQL License (production-ready)
 724   - Compatibility: PostgreSQL 9.4+
 725
 726 ### Monitoring Tools
 727 - **Prometheus + postgres_exporter + Grafana**: Open-source monitoring stack
 728 - **pgDash**: PostgreSQL monitoring - https://pgdash.io/
 729 - **check_postgres**: Nagios/Icinga/Zabbix integration
 730 - **pg_stat_replication_slots**: Built-in PostgreSQL monitoring view