Fact check against pg docs

[pg_scribe] / logical-replication-backup-design.md

# Incremental SQL Backup System Using PostgreSQL Logical Replication

**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.

## Executive Summary

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.

**Design Decision**: Use `pg_recvlogical` with the `decoder_raw` plugin for DML capture, combined with event triggers for DDL tracking and periodic `pg_dumpall --globals-only` for shared objects.

**Why This Works**:
- **Built-in tooling handles complexity**: `pg_recvlogical` provides streaming infrastructure, crash recovery, and position tracking
- **No transformation layer needed**: `decoder_raw` produces production-ready SQL directly
- **Complete coverage**: Event triggers + `pg_dumpall --globals-only` captures all DDL
- **Long-term readability**: Plain SQL format that can be executed years later

**Key Requirements**:
1. **DDL tracking** - Event triggers capture schema changes; `pg_dumpall --globals-only` handles shared objects
2. **Replica identity configuration** - All tables need proper configuration for UPDATE/DELETE
3. **Aggressive monitoring** - Replication slots must be monitored to prevent operational issues
4. **decoder_raw installation** - Third-party plugin must be compiled and installed

## Architecture Overview

### High-Level Design

```
┌─────────────────────────────────────────────────────────────┐
│                    PostgreSQL Database                       │
│                                                              │
│  ┌────────────────┐         ┌──────────────────┐           │
│  │  Regular Tables│────────▶│  WAL (Write-Ahead│           │
│  │  (DML Changes) │         │       Log)       │           │
│  └────────────────┘         └──────────────────┘           │
│                                      │                       │
│                                      ▼                       │
│                         ┌─────────────────────────┐         │
│                         │ Logical Decoding Process│         │
│                         │  (decoder_raw plugin)   │         │
│                         └─────────────────────────┘         │
│                                      │                       │
└──────────────────────────────────────┼───────────────────────┘
                                       │
                                       ▼
                        ┌─────────────────────────────┐
                        │  Replication Slot           │
                        │  (Tracks position, durable) │
                        └─────────────────────────────┘
                                       │
                                       ▼
                        ┌─────────────────────────────┐
                        │    pg_recvlogical Tool     │
                        │ (Built-in PostgreSQL util)  │
                        └─────────────────────────────┘
                                       │
                        ┌──────────────┴──────────────┐
                        ▼                             ▼
          ┌─────────────────────┐      ┌─────────────────────┐
          │  Incremental Files  │      │  Full pg_dump       │
          │  (SQL Changes)      │      │  (Periodic)         │
          │  - 2024-01-01.sql   │      │  - base-2024-01.sql │
          │  - 2024-01-02.sql   │      │  - base-2024-02.sql │
          │  - ...              │      │  - ...              │
          └─────────────────────┘      └─────────────────────┘
```

### Core Components

1. **Logical Replication Slot**: Durable position tracker in PostgreSQL
2. **decoder_raw Plugin**: Transforms binary WAL to executable SQL
3. **pg_recvlogical**: Built-in PostgreSQL tool that streams logical decoding output
4. **Base Backup System**: Regular full `pg_dump` backups with `--snapshot` for consistency
5. **Schema Tracking System**: Event triggers + `pg_dumpall --globals-only` for DDL changes

## How It Works

### DML Capture via Logical Replication

PostgreSQL's logical replication decodes the Write-Ahead Log (WAL) into logical changes. The `decoder_raw` plugin outputs these directly as executable SQL:

```sql
BEGIN;
INSERT INTO public.users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');
UPDATE public.users SET name = 'Alice Smith' WHERE id = 1;
DELETE FROM public.orders WHERE id = 42;
COMMIT;
```

**Key Properties**:
- **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)
- **Consistent**: Transaction boundaries are preserved
- **Online**: Runs without blocking database operations
- **Idempotent positioning**: Can restart from last known position (clients responsible for handling duplicate messages)

### DDL Capture via Event Triggers

Logical replication does **not** capture DDL (CREATE TABLE, ALTER TABLE, etc.). We solve this with event triggers:

```sql
-- Create a table to log DDL changes
CREATE TABLE ddl_history (
    id SERIAL PRIMARY KEY,
    executed_at TIMESTAMP DEFAULT now(),
    ddl_command TEXT,
    object_type TEXT,
    object_identity TEXT,
    query TEXT
);

-- Create event trigger function
CREATE OR REPLACE FUNCTION log_ddl_changes()
RETURNS event_trigger AS $$
DECLARE
    obj RECORD;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        INSERT INTO ddl_history (ddl_command, object_type, object_identity, query)
        VALUES (obj.command_tag, obj.object_type, obj.object_identity, current_query());
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Register the event trigger
CREATE EVENT TRIGGER capture_ddl ON ddl_command_end
    EXECUTE FUNCTION log_ddl_changes();
```

**How it works**:
1. Event trigger captures all DDL commands
2. Stores actual query text in `ddl_history` table with timestamps
3. The `ddl_history` table is replicated via logical replication
4. During restore, apply DDL changes in chronological order

**Limitations**:
- Event triggers don't fire for shared objects: databases, roles (role definitions and role memberships), tablespaces, parameter privileges, and ALTER SYSTEM commands
- Solution: Use periodic `pg_dumpall --globals-only` to capture shared objects

## Implementation Components

### 1. Initial Setup Script

**Purpose**: Bootstrap the backup system

**Tasks**:
- Install decoder_raw plugin (compile from source)
- Create logical replication slot: `pg_recvlogical --create-slot --plugin=decoder_raw`
- Export the snapshot from the slot for consistency
- Take initial base backup: `pg_dump --snapshot=<exported_snapshot>`
- Set up event triggers for DDL capture
- Create initial `pg_dumpall --globals-only` backup
- Configure `REPLICA IDENTITY` on tables without primary keys
- Document PostgreSQL version and installed extensions

**Critical Detail - Initial Snapshot Consistency**:

From PostgreSQL documentation (Section 47.2.5):
> 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.

This ensures the base backup and incremental stream are perfectly aligned with no gaps or overlaps.

### 2. Incremental Backup Collection

**Tool**: Built-in `pg_recvlogical` utility with `decoder_raw` plugin

**Example Usage**:
```bash
pg_recvlogical \
    --dbname=mydb \
    --slot=backup_slot \
    --plugin=decoder_raw \
    --start \
    --file=/backups/incremental/changes.sql \
    --option=include_transaction=on \
    --fsync-interval=10
```

**What pg_recvlogical provides**:
- Streams decoded changes continuously from the replication slot
- Handles connection failures and automatic reconnection
- Tracks LSN positions with status updates to the server
- Supports file rotation via SIGHUP signal
- Configurable fsync intervals for crash safety

**What decoder_raw provides**:
- ✅ Schema-qualified table names: `INSERT INTO public.users (...)`
- ✅ Proper column name quoting with `quote_identifier()`
- ✅ Transaction boundaries via `include_transaction=on` option (BEGIN/COMMIT)
- ✅ Intelligent replica identity handling (DEFAULT, INDEX, FULL, NOTHING)
- ✅ Comprehensive data type support (booleans, NaN, Infinity, NULL, strings, bit strings)
- ✅ TOAST optimization (skips unchanged TOAST columns in UPDATEs)
- ✅ Production-quality memory management

**Custom wrapper script tasks**:
- File rotation and timestamping
- Coordinate with monitoring system
- Metadata file generation (PostgreSQL version, extensions, encoding, collation)

### 3. Periodic Full Backup Script

**Purpose**: Take regular full backups as restore points

**Tasks**:
- Execute `pg_dump` to create full database backup
- Execute `pg_dumpall --globals-only` to capture shared objects (databases, roles, tablespaces)
- Compress old backups to save space
- Implement retention policy (delete old backups)

### 4. Restore Script

**Purpose**: Restore database from base + incremental backups

**Tasks**:
1. Locate most recent full backup
2. Find all incremental backups since that full backup
3. Recreate target database
4. Restore `pg_dumpall --globals-only` (shared objects)
5. Restore full `pg_dump` backup
6. Apply DDL changes from `ddl_history` in chronological order
7. Apply incremental SQL backups in chronological order
8. Synchronize sequence values: `SELECT setval('seq_name', (SELECT MAX(id) FROM table))`
9. Verify data integrity (checksums, row counts)

### 5. Monitoring and Health Check Script

**Purpose**: Prevent operational issues from inactive replication slots

**Critical Metrics**:
```sql
-- Check replication slot health
SELECT
    slot_name,
    slot_type,
    database,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as replication_lag,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) as confirmed_lag
FROM pg_replication_slots
WHERE slot_type = 'logical';
```

**Alerting**:
- **Critical alert** when `restart_lsn` falls more than 1GB behind
- **Emergency alert** when slot lag exceeds 10GB or age exceeds 24 hours
- **Emergency procedure** documented to drop slot if it threatens database availability

**Available Tools**:
- **Prometheus + postgres_exporter + Grafana**: Open-source monitoring stack
- **pgDash** (https://pgdash.io/): Commercial PostgreSQL monitoring
- **check_postgres**: Nagios/Icinga/Zabbix integration
- **Built-in views**: `pg_replication_slots`, `pg_stat_replication_slots`

## decoder_raw Plugin Details

**Source**: https://github.com/michaelpq/pg_plugins/tree/main/decoder_raw
**License**: PostgreSQL License (permissive, production-ready)
**Compatibility**: PostgreSQL 9.4+

**Installation**:
```bash
# Install PostgreSQL development headers
apt-get install postgresql-server-dev-XX  # Debian/Ubuntu
yum install postgresql-devel              # RHEL/CentOS

# Clone and compile
git clone https://github.com/michaelpq/pg_plugins.git
cd pg_plugins/decoder_raw
make
sudo make install

# Verify installation
ls $(pg_config --pkglibdir)/decoder_raw.so
```

**Why decoder_raw is essential**:
- Eliminates the entire SQL transformation layer
- Handles all data type escaping correctly (strings, NULL, NaN, Infinity, booleans)
- Produces production-ready SQL that can be executed with `psql -f changes.sql`
- Mature codebase with comprehensive test suite

**Known Limitation of decoder_raw Plugin**:
- ⚠️ **TRUNCATE not captured by decoder_raw**: While PostgreSQL logical replication supports TRUNCATE replication (documented in Section 29.8), the decoder_raw plugin does not implement the `truncate_cb` callback to output TRUNCATE statements
- **Workarounds** (all viable):
  1. Event triggers CAN and WILL capture TRUNCATE as DDL (stored in `ddl_history` table) - **recommended approach**
  2. Use `DELETE FROM table` instead of TRUNCATE (slower but captured by decoder_raw)
  3. Contribute `truncate_cb` callback implementation to the decoder_raw project upstream
- **Important**: TRUNCATE operations will be captured and replicated via the event trigger mechanism, so this limitation has a complete workaround within this design

## Key Challenges and Solutions

### 1. Replica Identity Required for UPDATE/DELETE

**Problem**: Tables need replica identity for UPDATE/DELETE operations.

From PostgreSQL documentation (Section 29.1.1):
> 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.**

This means UPDATE/DELETE will **fail on the source database**, not just during restore!

**Solution**: Ensure all tables have one of:
- A primary key (automatic replica identity)
- A unique index configured via `REPLICA IDENTITY USING INDEX index_name`
- Explicit `REPLICA IDENTITY FULL` setting (inefficient, last resort)

**Example**:
```sql
-- Table without primary key will error on UPDATE/DELETE
CREATE TABLE logs (timestamp TIMESTAMPTZ, message TEXT);

-- Fix: Set replica identity to FULL
ALTER TABLE logs REPLICA IDENTITY FULL;
```

### 2. Replication Slots Prevent WAL Cleanup

**Problem**: Inactive replication slots prevent WAL cleanup, leading to:
1. Disk fills up (WAL files not cleaned)
2. Table bloat (VACUUM cannot clean old row versions)
3. **Database shutdown** (transaction ID wraparound)

From PostgreSQL documentation (Section 47.2.2):
> In extreme cases this could cause the database to shut down to prevent transaction ID wraparound.

**Solution**:
- **Monitor slot lag aggressively** (see monitoring section)
- Set `max_slot_wal_keep_size` parameter (PostgreSQL 13+) to limit WAL retention
- Have documented emergency procedure to drop slot if needed
- Consider `pg_replication_slot_advance()` to skip ahead (loses backup coverage)

### 3. Sequences Are Not Replicated

**Problem**: Sequence values are not captured in logical replication.

**Solution**:
- Use `pg_dump --sequence-data` (enabled by default) in periodic full dumps
- After restore, synchronize sequences:
  ```sql
  SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));
  ```

### 4. Large Objects Are Not Replicated

**Problem**: PostgreSQL large objects are not captured in logical replication.

**Solution**:
- **Preferred**: Use `BYTEA` columns instead (these ARE replicated)
- **Alternative**: Use `pg_dump --large-objects` in periodic full backups
  - Note: Incremental changes to large objects NOT captured between full backups

### 5. Crash Recovery and Duplicate Handling

**Problem**: After database crash, slot position may roll back, causing duplicate changes.

From PostgreSQL documentation (Section 47.2.2):
> 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.

**Solution**: `pg_recvlogical` handles position tracking through status reporting and `--startpos` parameter. However, per PostgreSQL documentation (Section 47.2.2): "Logical decoding clients are responsible for avoiding ill effects from handling the same message more than once."

**Recommendations**:
- The restore process should be idempotent (safe to replay duplicate transactions)
- For exact-once semantics, consider tracking last applied LSN in metadata and using `--startpos` during restore
- Test crash scenarios to verify duplicate handling behaves acceptably

### 6. Long-Term Readability

**Challenges**:
- PostgreSQL syntax may change between major versions (rare)
- Extension dependencies may not exist in future systems
- Encoding/collation definitions may change

**Solution**: Include metadata file with each backup:
- PostgreSQL version (full version string)
- All installed extension names and versions
- Database encoding
- Locale and collation settings
- Custom data types and enums

Periodically test restoring old backups on current PostgreSQL versions.

## Prerequisites and Configuration

### PostgreSQL Configuration

```ini
# postgresql.conf

# Required: Set WAL level to logical
wal_level = logical

# Required: Allow at least one replication slot
max_replication_slots = 10

# Recommended: Allow replication connections
max_wal_senders = 10

# Recommended: Keep more WAL for safety
wal_keep_size = 1GB

# Recommended: Limit WAL retention for safety (PostgreSQL 13+)
max_slot_wal_keep_size = 10GB

# Optional: Tune checkpoint frequency to persist slot positions more often
checkpoint_timeout = 5min
```

### Client Requirements

- PostgreSQL client utilities installed (`pg_recvlogical`, `pg_dump`, `pg_dumpall`)
- Superuser or role with `REPLICATION` privilege
- Permission to create replication slots
- decoder_raw plugin compiled and installed

## Operational Procedures

### Backup Schedule

**Recommended**:
- **Incremental**: Continuously streaming via `pg_recvlogical`
- **Full backup**: Daily at 2 AM
- **Globals backup**: Daily (`pg_dumpall --globals-only`)
- **Metadata export**: Daily (PostgreSQL version, extensions, encoding)

### Retention Policy

- **Incremental backups**: Keep 7 days
- **Full backups**: Keep 30 days, then one per month for 1 year
- **Monitor disk space**: Alert if backup directory exceeds 80% capacity

### Disaster Recovery Runbook

1. **Stop application** to prevent new writes during restore
2. **Create new database** (don't overwrite production)
3. **Restore shared objects**: `psql -f globals-YYYY-MM-DD.sql`
4. **Restore full backup**: `psql dbname < base-YYYY-MM-DD.sql`
5. **Apply DDL changes**: Extract from `ddl_history` table, apply in chronological order
6. **Apply incremental backups**: `psql dbname < incremental-YYYY-MM-DD.sql` in order
7. **Sync sequences**: Run `setval()` for all sequences
8. **Verify data integrity**: Check row counts, checksums
9. **Test application** against restored database
10. **Switch over** application to restored database

## Testing Strategy

### 1. Basic Functionality Test

```sql
-- Create test database and setup
CREATE DATABASE backup_test;
\c backup_test

-- Create test table
CREATE TABLE test_users (id SERIAL PRIMARY KEY, name TEXT, created_at TIMESTAMP DEFAULT now());

-- Generate data and schema changes
INSERT INTO test_users (name) VALUES ('Alice'), ('Bob'), ('Charlie');
UPDATE test_users SET name = 'Alice Smith' WHERE id = 1;
DELETE FROM test_users WHERE id = 3;
ALTER TABLE test_users ADD COLUMN email TEXT;
UPDATE test_users SET email = 'alice@example.com' WHERE id = 1;

-- Restore and verify
```

### 2. Crash Recovery Test

```bash
# Start collecting incrementals
# Generate load with pgbench
# Simulate crash: pg_ctl stop -m immediate
# Restart PostgreSQL
# Verify no data loss and duplicates handled correctly
# Restore and verify
```

### 3. Long-Term Storage Test

```bash
# Create backup
# Store backup files
# Wait (or simulate) years passing
# Restore on modern PostgreSQL version
# Verify SQL is still readable and executable
```

### 4. Replica Identity Test

```sql
-- Create table without primary key
CREATE TABLE test_no_pk (col1 TEXT, col2 INT);

-- Attempt UPDATE (should fail with replica identity error)
UPDATE test_no_pk SET col2 = 5 WHERE col1 = 'test';

-- Fix with REPLICA IDENTITY FULL
ALTER TABLE test_no_pk REPLICA IDENTITY FULL;

-- Retry UPDATE (should succeed)
```

### 5. TRUNCATE Handling Test

```sql
-- Create test table
CREATE TABLE test_truncate (id INT);
INSERT INTO test_truncate VALUES (1), (2), (3);

-- Perform TRUNCATE
TRUNCATE test_truncate;

-- Verify: Check if decoder_raw incremental backup captured TRUNCATE
-- Expected: NOT captured by decoder_raw (known plugin limitation)
-- Verify: Check if event trigger captured TRUNCATE as DDL in ddl_history table
-- Expected: SHOULD be captured by event trigger (this is the workaround)
SELECT * FROM ddl_history WHERE ddl_command = 'TRUNCATE TABLE' ORDER BY executed_at DESC LIMIT 1;
```

## Performance Considerations

**Write Amplification**:
- WAL must be written (normal)
- WAL must be decoded into logical format (additional CPU)
- Event triggers fire on every DDL operation (minimal overhead)

**Disk I/O**:
- Additional WAL volume retained by replication slots
- More frequent checkpoint I/O if checkpoint_timeout is tuned

**Recommendations**:
- Benchmark overhead on test system with production-like workload
- Monitor CPU usage of WAL sender processes
- Monitor disk usage for WAL and backup directories

## Next Steps for Proof of Concept

1. **Install decoder_raw**
   - Clone pg_plugins repository
   - Install PostgreSQL development headers
   - Compile and install decoder_raw
   - Verify installation

2. **Initial Setup**
   - Create replication slot with decoder_raw
   - Set up event triggers for DDL capture
   - Take initial synchronized base backup

3. **Streaming Collection**
   - Test `pg_recvlogical` with decoder_raw
   - Verify output is immediately executable SQL
   - Test with various data types and operations

4. **DDL Handling**
   - Test event trigger captures DDL correctly
   - Test `pg_dumpall --globals-only` captures shared objects
   - Verify coordination during restore

5. **Monitoring Setup**
   - Configure replication slot monitoring
   - Set up critical alerting
   - Document emergency procedures

6. **Restore Process**
   - Build restore scripts
   - Test point-in-time recovery
   - Verify sequence synchronization

7. **Crash Recovery**
   - Test duplicate handling
   - Verify LSN tracking

8. **Performance Testing**
   - Measure storage overhead
   - Measure CPU overhead
   - Benchmark restore time

## References

### PostgreSQL Documentation
- PostgreSQL Documentation: Chapter 25 - Backup and Restore
- PostgreSQL Documentation: Chapter 29 - Logical Replication
- PostgreSQL Documentation: Chapter 47 - Logical Decoding
- PostgreSQL Documentation: Section 29.8 - Logical Replication Restrictions
- PostgreSQL Documentation: `pg_recvlogical` man page
- PostgreSQL Documentation: `pg_dump` man page
- PostgreSQL Documentation: `pg_dumpall` man page

### Essential Tools
- **decoder_raw**: SQL output plugin for logical decoding
  - Source: https://github.com/michaelpq/pg_plugins/tree/main/decoder_raw
  - **CRITICAL COMPONENT**: Eliminates output transformation layer
  - License: PostgreSQL License (production-ready)
  - Compatibility: PostgreSQL 9.4+

### Monitoring Tools
- **Prometheus + postgres_exporter + Grafana**: Open-source monitoring stack
- **pgDash**: PostgreSQL monitoring - https://pgdash.io/
- **check_postgres**: Nagios/Icinga/Zabbix integration
- **pg_stat_replication_slots**: Built-in PostgreSQL monitoring view