# Incremental SQL Backup System Using PostgreSQL Logical Replication

## Executive Summary

This document details a proof-of-concept design for a PostgreSQL backup system that produces human-readable, plain SQL incremental backups using logical replication. The system aims to create backups that remain readable and restorable for 10+ years while supporting online operation and crash safety.

**Key Finding**: This approach is **feasible with significant caveats**. The primary limitation is that **DDL changes are NOT captured by logical replication**, requiring a separate mechanism for schema evolution tracking.

## Architecture Overview

### High-Level Design

```
┌─────────────────────────────────────────────────────────────┐
│                    PostgreSQL Database                       │
│                                                              │
│  ┌────────────────┐         ┌──────────────────┐           │
│  │  Regular Tables│────────▶│  WAL (Write-Ahead│           │
│  │  (DML Changes) │         │       Log)       │           │
│  └────────────────┘         └──────────────────┘           │
│                                      │                       │
│                                      ▼                       │
│                         ┌─────────────────────────┐         │
│                         │ Logical Decoding Process│         │
│                         │  (Decodes WAL to SQL)   │         │
│                         └─────────────────────────┘         │
│                                      │                       │
└──────────────────────────────────────┼───────────────────────┘
                                       │
                                       ▼
                        ┌─────────────────────────────┐
                        │  Replication Slot           │
                        │  (Tracks position, durable) │
                        └─────────────────────────────┘
                                       │
                                       ▼
                        ┌─────────────────────────────┐
                        │  Backup Collection Script   │
                        │  (Python/Bash)              │
                        └─────────────────────────────┘
                                       │
                        ┌──────────────┴──────────────┐
                        ▼                             ▼
          ┌─────────────────────┐      ┌─────────────────────┐
          │  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. **Output Plugin**: Transforms binary WAL to human-readable format (e.g., `test_decoding`)
3. **Collection Script**: Periodically fetches and stores decoded changes
4. **Base Backup System**: Regular full `pg_dump` backups
5. **Schema Tracking System**: Separate mechanism for DDL changes (**required**)

## How Logical Replication Works

### What Logical Replication Provides

PostgreSQL's logical replication decodes the Write-Ahead Log (WAL) into logical changes:

```sql
-- Example output from test_decoding plugin:
BEGIN 10298
table public.users: INSERT: id[integer]:1 name[text]:'Alice' email[text]:'alice@example.com'
table public.users: UPDATE: id[integer]:1 name[text]:'Alice Smith' email[text]:'alice@example.com'
table public.orders: DELETE: id[integer]:42
COMMIT 10298
```

This can be transformed into standard 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
- **Consistent**: Transaction boundaries are preserved
- **Online**: Runs without blocking database operations
- **Idempotent positioning**: Can restart from last known position

## Critical Challenge: DDL is NOT Replicated

### The Problem

From PostgreSQL documentation (Section 29.8):

> **The database schema and DDL commands are not replicated.** The initial schema can be copied by hand using `pg_dump --schema-only`. Subsequent schema changes would need to be kept in sync manually.

This means logical replication will **NOT** capture:

- `CREATE TABLE`
- `ALTER TABLE` (adding columns, changing types, etc.)
- `DROP TABLE`
- `CREATE INDEX`
- Any other schema modifications

### Example of the Issue

```sql
-- Day 1: Base backup taken
CREATE TABLE users (id INT PRIMARY KEY, name TEXT);

-- Day 2: Data changes (CAPTURED by logical replication)
INSERT INTO users VALUES (1, 'Alice');

-- Day 3: Schema change (NOT CAPTURED)
ALTER TABLE users ADD COLUMN email TEXT;

-- Day 4: More data changes (CAPTURED, but in new schema)
UPDATE users SET email = 'alice@example.com' WHERE id = 1;
```

**Problem**: The incremental backup for Day 4 will contain:
```sql
UPDATE users SET email = 'alice@example.com' WHERE id = 1;
```

But if you restore from Day 1 base backup, the `email` column doesn't exist yet, and the UPDATE will fail.

### Solutions for DDL Tracking

#### Option 1: Event Triggers (Recommended)

PostgreSQL supports **event triggers** that can capture DDL events:

```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
    -- Capture DDL command details
    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 for DDL commands
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 them in `ddl_history` table with timestamps
3. Your backup script exports this table periodically
4. During restore, apply DDL changes in chronological order before applying DML changes

**Benefits**:
- ✓ Captures all DDL automatically
- ✓ Timestamp-ordered for correct replay
- ✓ The `ddl_history` table itself gets replicated via logical replication

**Limitations**:
- Event triggers don't fire for system catalog operations
- Some DDL (like `CREATE DATABASE`) can't be captured this way

#### Option 2: Application-Level Tracking

Track all schema migrations in your application:

```python
# migration_001.sql
CREATE TABLE users (id INT PRIMARY KEY, name TEXT);

# migration_002.sql
ALTER TABLE users ADD COLUMN email TEXT;
```

Store these migrations alongside your backups and apply them in order during restore.

**Benefits**:
- ✓ Clean, version-controlled schema evolution
- ✓ Works well if you already use migration tools (Flyway, Liquibase, Alembic)

**Limitations**:
- ✗ Requires discipline - all DDL must go through migration system
- ✗ Ad-hoc DDL changes will be missed

#### Option 3: Periodic Schema Dumps

Take regular `pg_dump --schema-only` snapshots:

```bash
# Every hour or day
pg_dump --schema-only dbname > schema-2024-01-01-14:00.sql
```

During restore:
1. Restore base backup
2. Apply schema dumps in order
3. Apply incremental DML changes

**Benefits**:
- ✓ Simple to implement
- ✓ Catches all schema changes

**Limitations**:
- ✗ May capture schema at wrong moment (between related DDL+DML)
- ✗ Harder to determine exact order of schema vs. data changes

## Other Challenges and Limitations

### 1. Sequences Are Not Replicated

**Problem**: Sequence values (used by `SERIAL` and `IDENTITY` columns) are not replicated.

**From documentation (Section 29.8)**:
> Sequence data is not replicated. The data in serial or identity columns backed by sequences will of course be replicated as part of the table, but the sequence itself would still show the start value on the subscriber.

**Example**:
```sql
CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);

-- Insert 1000 rows
-- Sequence on source is now at 1001
-- Sequence on restored database is still at 1

-- Next insert will fail with duplicate key error
INSERT INTO users (name) VALUES ('Bob');  -- Tries to use id=1, but id=1 already exists
```

**Solution**:
Include sequence values in periodic full dumps, or have restore script run:

```sql
-- After restore, sync all sequences
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));
```

### 2. Large Objects Are Not Replicated

**From documentation (Section 29.8)**:
> Large objects (see Chapter 33) are not replicated. There is no workaround for that, other than storing data in normal tables.

**Solution**: Avoid using PostgreSQL large objects. Use `BYTEA` columns instead, which ARE replicated.

### 3. Replication Slots Prevent WAL Cleanup

**From documentation (Section 47.2.2)**:
> Replication slots persist across crashes and know nothing about the state of their consumer(s). They will prevent removal of required resources even when there is no connection using them. This consumes storage because neither required WAL nor required rows from the system catalogs can be removed by VACUUM as long as they are required by a replication slot.

**Problem**: If your backup script stops running, the replication slot will:
1. Prevent WAL files from being cleaned up (disk fills)
2. Prevent VACUUM from cleaning old row versions (bloat)
3. Eventually cause database shutdown to prevent transaction ID wraparound

**Solution**:
- Monitor slot lag: `SELECT * FROM pg_replication_slots;`
- Set up alerting when `restart_lsn` falls too far behind
- Have a maximum retention policy - drop slot if it gets too old
- Consider using `pg_replication_slot_advance()` to skip ahead if needed

### 4. Slot Position is Only Persisted at Checkpoint

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

**Problem**: After a crash, you might receive duplicate changes.

**Example**:
```
1. Script fetches changes, receives up to LSN 1000
2. Script writes changes to file
3. Database crashes before checkpoint
4. Slot position rolled back to LSN 900
5. Script fetches again, receives LSN 900-1000 again (duplicates!)
```

**Solution**:
- Track the highest LSN you've successfully written
- Pass that LSN to `pg_logical_slot_get_changes(slot_name, upto_lsn, ...)`
- Or filter duplicates based on transaction ID

### 5. TRUNCATE Replication Complexity

**From documentation (Section 29.8)**:
> Replication of TRUNCATE commands is supported, but some care must be taken when truncating groups of tables connected by foreign keys.

**Problem**: If you truncate multiple related tables, the incremental backup needs to capture all of them.

**Solution**: Logical replication handles this automatically, but be aware of the behavior.

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

For UPDATE and DELETE operations, PostgreSQL needs to identify which rows to modify. This requires a **replica identity**:

- **DEFAULT**: Uses primary key (if exists)
- **USING INDEX**: Uses a specific unique index
- **FULL**: Uses all columns (expensive, but works without primary key)
- **NOTHING**: Only INSERT is replicated (UPDATE/DELETE are ignored!)

**Example**:
```sql
-- Table without primary key
CREATE TABLE logs (timestamp TIMESTAMPTZ, message TEXT);

-- By default, replica identity is NOTHING for tables without PK
-- This means UPDATEs and DELETEs won't be captured!

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

**Best practice**: Ensure all tables have either:
- A primary key (automatic replica identity), OR
- Explicit `REPLICA IDENTITY FULL` setting

## 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

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

### Client Requirements

- PostgreSQL client libraries (`psycopg2` for Python)
- Superuser or role with `REPLICATION` privilege
- Permission to create replication slots

## Implementation Components

When implementing this system, the following programs/scripts will be needed:

### 1. Initial Setup Script

**Purpose**: Bootstrap the backup system
**Tasks**:
- Create logical replication slot with appropriate output plugin
- Set up DDL tracking infrastructure (event triggers and ddl_history table)
- Take initial base backup using `pg_dump`
- Export initial snapshot for consistency

### 2. Incremental Backup Collection Script

**Purpose**: Regularly collect changes from the replication slot
**Tasks**:
- Connect to PostgreSQL and fetch changes from the replication slot
- Track last processed LSN to handle duplicates after crashes
- Transform test_decoding output into clean SQL statements
- Write changes to timestamped SQL files
- Update state file with last processed LSN
- Handle errors and retry logic

### 3. Periodic Full Backup Script

**Purpose**: Take regular full backups as restore points
**Tasks**:
- Execute `pg_dump` to create full database backup
- Take schema-only dump separately for reference
- Compress old backups to save space
- Implement retention policy (delete old backups)
- Export DDL history table

### 4. Restore Script

**Purpose**: Restore database from base + incremental backups
**Tasks**:
- Locate most recent full backup
- Find all incremental backups since that full backup
- Recreate target database
- Restore full backup
- Apply incremental backups in chronological order
- Handle point-in-time recovery (stop at specific timestamp)
- Synchronize sequence values to current maximums
- Verify data integrity

### 5. Output Plugin / Format Transformer

**Purpose**: Convert test_decoding output to clean SQL
**Tasks**:
- Parse test_decoding text format
- Generate proper INSERT/UPDATE/DELETE statements
- Handle data type conversions and escaping
- Preserve transaction boundaries
- Format output for human readability

### 6. Monitoring and Health Check Script

**Purpose**: Ensure backup system is functioning correctly
**Tasks**:
- Check replication slot health and lag
- Alert if slot is not advancing
- Monitor disk space in backup directories
- Verify backup files are being created
- Test restore procedures periodically

## Testing Strategy

### 1. Basic Functionality Test

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

-- Run setup script
-- Then create test data
CREATE TABLE test_users (id SERIAL PRIMARY KEY, name TEXT, created_at TIMESTAMP DEFAULT now());

INSERT INTO test_users (name) VALUES ('Alice'), ('Bob'), ('Charlie');

-- Wait for incremental backup to run
-- Add more data
UPDATE test_users SET name = 'Alice Smith' WHERE id = 1;
DELETE FROM test_users WHERE id = 3;

-- Take another incremental
-- Add schema change
ALTER TABLE test_users ADD COLUMN email TEXT;
UPDATE test_users SET email = 'alice@example.com' WHERE id = 1;

-- Now restore and verify all data is present and correct
```

### 2. Crash Recovery Test

```bash
# Start collecting incrementals
# Generate load with pgbench
# Simulate crash with pg_ctl stop -m immediate
# Restart PostgreSQL
# Collect incrementals again - verify no data loss and duplicates are handled
# Restore and verify data matches
```

### 3. Long-Term Storage Test

```bash
# Create backup
# Store backup in time capsule (simulate 10 years)
# On modern system, verify SQL is still readable and executable
```

## Operational Procedures

### Monitoring

Monitor these 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';

-- Alert if lag > 1GB or slot is inactive for > 1 hour
```

### Backup Schedule

**Recommended**:
- **Incremental**: Every 15 minutes (or continuously streaming)
- **Full backup**: Daily at 2 AM
- **Schema-only dump**: Every hour
- **DDL history export**: Every 15 minutes (part of incremental)

### 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 latest full backup**
4. **Apply DDL changes** from `ddl_history` in chronological order
5. **Apply incremental backups** in chronological order
6. **Sync sequences** to latest values
7. **Verify data integrity** with checksums or row counts
8. **Test application** against restored database
9. **Switch over** application to restored database

## Comparison with Alternatives

| Feature | Logical Replication | pg_dump Only | WAL Archiving |
|---------|-------------------|--------------|---------------|
| Incremental | ✓ Yes | ✗ No | ✓ Yes |
| Human-readable | ✓ SQL-like | ✓ SQL | ✗ Binary |
| Online operation | ✓ Yes | ✓ Yes | ✓ Yes |
| Crash-safe position | ✓ Yes | N/A | ✓ Yes |
| Captures DDL | ✗ **No** | ✓ Yes | ✓ Yes |
| Long-term readable | ✓ Yes | ✓ Yes | ✗ No |
| Point-in-time recovery | ✓ Yes | ✗ No | ✓ Yes |
| Storage efficiency | ✓ Good | ✗ Poor | ✓ Excellent |

## Conclusion

### Feasibility: YES, with Important Caveats

The logical replication approach **is feasible** for incremental SQL backups, but requires:

1. **Separate DDL tracking** using event triggers or schema dumps
2. **Careful monitoring** of replication slot health
3. **Sequence synchronization** during restore
4. **Duplicate handling** for crash scenarios

### Primary Limitation

**DDL is not captured by logical replication.** This is the most significant limitation. You **must** implement one of the DDL tracking solutions (event triggers recommended).

### When This Approach Makes Sense

- You need human-readable backups for compliance/archival
- You want continuous incremental backups without WAL shipping
- You can implement DDL tracking (event triggers or migration system)
- Your database has reasonable change velocity
- You have operational maturity to monitor replication slots

### When to Consider Alternatives

- If you need zero-configuration backup (use `pg_dump` + `pg_basebackup`)
- If DDL tracking complexity is too high (use WAL archiving + `pg_basebackup`)
- If you need native PostgreSQL point-in-time recovery (use continuous archiving)

### Next Steps for Proof of Concept

1. Implement the setup and collection scripts
2. Add DDL tracking via event triggers
3. Build transformation layer from `test_decoding` output to clean SQL
4. Test crash recovery scenarios
5. Validate restore procedures
6. Measure storage and performance overhead
7. Document operational procedures

## References

- 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