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

   1 # pg_scribe Tutorial
   2
   3 Watch a database under load become plain SQL backups in real-time. Three terminals recommended.
   4
   5 ## Prerequisites
   6
   7 ```bash
   8 # Install the extension
   9 cd wal2sql && make && make install
  10
  11 # Configure PostgreSQL for logical replication
  12 echo "wal_level = logical" >> ~/.pgenv/pgsql/data/postgresql.conf
  13 echo "max_replication_slots = 10" >> ~/.pgenv/pgsql/data/postgresql.conf
  14 echo "max_wal_senders = 10" >> ~/.pgenv/pgsql/data/postgresql.conf
  15 pgenv restart
  16 ```
  17
  18 ## Phase 1: Initialize with pgbench workload
  19
  20 **Terminal 1** - Initialize database and backup system:
  21
  22 ```bash
  23 # Create database and populate with pgbench tables
  24 createdb -U postgres demo
  25 pgbench -i -s 10 -U postgres demo  # Creates 1M rows in pgbench_accounts
  26
  27 # Try to initialize pg_scribe
  28 pg_scribe --init -d demo -f /tmp/demo_backup -U postgres
  29 ```
  30
  31 This will fail with a validation error:
  32
  33 ```
  34 ERROR: CRITICAL: The following tables lack adequate replica identity:
  35 ERROR:   - public.pgbench_history
  36 ERROR:   Fix: Add a primary key or set replica identity:
  37 ERROR:     ALTER TABLE <table> ADD PRIMARY KEY (id);
  38 ERROR:     -- OR --
  39 ERROR:     ALTER TABLE <table> REPLICA IDENTITY FULL;
  40 ```
  41
  42 **Why?** The `pgbench_history` table is append-only (no primary key) and needs replica identity for logical replication. pg_scribe validates this upfront to prevent silent data loss.
  43
  44 **Fix the issue:**
  45
  46 ```bash
  47 # Set replica identity for the append-only history table
  48 psql -U postgres demo -c "ALTER TABLE pgbench_history REPLICA IDENTITY FULL;"
  49
  50 # Now initialize pg_scribe successfully
  51 pg_scribe --init -d demo -f /tmp/demo_backup -U postgres
  52
  53 # Start streaming changes to active.sql
  54 pg_scribe --start -d demo -f /tmp/demo_backup -U postgres
  55 ```
  56
  57 Leave Terminal 1 streaming. Every database change now becomes SQL.
  58
  59 **Terminal 2** - Generate realistic load:
  60
  61 ```bash
  62 # Run pgbench workload in background: 5 clients, 10 minutes
  63 # This will run continuously throughout the tutorial
  64 pgbench -c 5 -T 600 -U postgres demo &
  65 ```
  66
  67 Leave this running in the background. It will generate continuous load for the rest of the tutorial.
  68
  69 **Terminal 3** - Watch the backup grow:
  70
  71 ```bash
  72 # Check replication status (run this a few times while pgbench is running)
  73 pg_scribe --status -d demo -f /tmp/demo_backup -U postgres
  74 ```
  75
  76 You'll see actual replication metrics updating in real-time: LSN positions, lag bytes, transaction counts, and file sizes growing!
  77
  78 ```bash
  79 # Peek at the SQL being captured
  80 tail -n 30 /tmp/demo_backup/chain-*/active.sql
  81 ```
  82
  83 You'll see thousands of INSERT/UPDATE transactions accumulating as plain SQL!
  84
  85 ## Phase 2: Rotate differentials under load
  86
  87 (pgbench is still running in the background from Phase 1)
  88
  89 **Terminal 3** - Rotate differential while load continues:
  90
  91 ```bash
  92 pg_scribe --rotate-diff -f /tmp/demo_backup
  93 ```
  94
  95 **Terminal 3** - Verify the rotation:
  96
  97 ```bash
  98 # Check status - you'll see the new active.sql starting fresh
  99 pg_scribe --status -d demo -f /tmp/demo_backup -U postgres
 100
 101 # Or list the files to see the sealed differential with timestamp
 102 ls -lht /tmp/demo_backup/chain-*/
 103 ```
 104
 105 **What happened:** The old `active.sql` was sealed with a timestamp (e.g., `diff-YYYYMMDD-HHMMSS.sql`). New transactions now write to a fresh `active.sql`. The rotation happened instantly with zero transaction loss. Replication never stopped.
 106
 107 ## Phase 3: Chain transfer under load (zero-downtime operation)
 108
 109 Chains let you periodically create fresh compressed base backups (e.g., weekly). This keeps old chains archive-ready while new data streams to the new chain.
 110
 111 (pgbench is still running in the background from Phase 1)
 112
 113 **Terminal 3** - Create new chain and transfer streaming to it:
 114
 115 ```bash
 116 # Stops old streaming, creates new compressed base backup, starts streaming to new chain
 117 pg_scribe --new-chain --start -d demo -f /tmp/demo_backup -Z gzip:6 -U postgres
 118 ```
 119
 120 This automatically stops the old stream in Terminal 1. Terminal 3 now holds the new streaming process.
 121
 122 **Terminal 1** - Verify new chain is active (Terminal 1 is now free):
 123
 124 ```bash
 125 pg_scribe --status -d demo -f /tmp/demo_backup -U postgres
 126 ```
 127
 128 The status output will show which chain is active and confirm streaming is working.
 129
 130 ```bash
 131 # You can also list directory to see both chains
 132 ls -lh /tmp/demo_backup/
 133 # chain-YYYYMMDD-HHMMSS/  (old chain, sealed)
 134 # chain-YYYYMMDD-HHMMSS/  (new chain, active)
 135 ```
 136
 137 The old chain's final differential was sealed. The new chain has a fresh compressed base backup. No transactions were lost during the transfer.
 138
 139 ## Phase 4: Catching up after pause (shows resilience)
 140
 141 **Terminal 1** - Stop streaming (this stops Terminal 3's stream):
 142
 143 ```bash
 144 pg_scribe --stop -f /tmp/demo_backup
 145 ```
 146
 147 **Terminal 2** - Stop background pgbench (if still running) and generate load while streaming is stopped:
 148
 149 ```bash
 150 # Stop the background pgbench if it's still running
 151 pkill pgbench
 152
 153 # Generate new load while streaming is stopped
 154 pgbench -c 5 -t 1000 -U postgres demo
 155 ```
 156
 157 **Terminal 1** - Check status (streaming stopped, WAL accumulating):
 158
 159 ```bash
 160 pg_scribe --status -d demo -f /tmp/demo_backup -U postgres
 161 ```
 162
 163 You'll see something like: "Replication slot is 45 MB behind current WAL position"
 164
 165 The replication slot preserved the WAL even though streaming stopped!
 166
 167 **Terminal 1** - Restart streaming and watch it catch up:
 168
 169 ```bash
 170 pg_scribe --start -d demo -f /tmp/demo_backup -U postgres
 171 ```
 172
 173 Leave Terminal 1 streaming.
 174
 175 **Terminal 3** - Monitor catch-up progress:
 176
 177 ```bash
 178 watch -n 1 'pg_scribe --status -d demo -f /tmp/demo_backup -U postgres'
 179 ```
 180
 181 Watch the lag decrease as it replays accumulated WAL. Ctrl+C when caught up.
 182
 183 ## Phase 5: Restore and verify
 184
 185 **Terminal 1** - Seal final differential, then stop streaming:
 186
 187 ```bash
 188 pg_scribe --rotate-diff -f /tmp/demo_backup
 189 pg_scribe --stop -f /tmp/demo_backup
 190 ```
 191
 192 **Terminal 1** - Restore to new database:
 193
 194 ```bash
 195 pg_scribe --restore -d demo_restored -f /tmp/demo_backup -C -U postgres
 196 ```
 197
 198 **Terminal 1** - Verify data matches:
 199
 200 ```bash
 201 # Compare row counts
 202 psql -U postgres demo -c "SELECT count(*) FROM pgbench_accounts;"
 203 psql -U postgres demo_restored -c "SELECT count(*) FROM pgbench_accounts;"
 204
 205 # Compare transaction history (shows all transactions were captured)
 206 psql -U postgres demo -c "SELECT count(*) FROM pgbench_history;"
 207 psql -U postgres demo_restored -c "SELECT count(*) FROM pgbench_history;"
 208
 209 # Compare balances (proves data integrity)
 210 psql -U postgres demo -c "SELECT sum(abalance) FROM pgbench_accounts;"
 211 psql -U postgres demo_restored -c "SELECT sum(abalance) FROM pgbench_accounts;"
 212 ```
 213
 214 Perfect match!
 215
 216 ## Cleanup
 217
 218 ```bash
 219 # Stop pgbench if still running
 220 pkill pgbench
 221
 222 # Clean up databases and backup files
 223 dropdb -U postgres demo_restored
 224 dropdb -U postgres demo
 225 rm -rf /tmp/demo_backup
 226 ```
 227
 228 ## What just happened
 229
 230 - `pgbench -i` created realistic TPC-B-like tables with 1M rows
 231 - `pg_scribe --init` created the wal2sql extension, replication slot, and base backup
 232 - `pg_scribe --start` streamed thousands of transactions as plain SQL
 233 - Differential rotation worked safely during active writes
 234 - Chain transfer happened with zero transaction loss
 235 - Replication slot preserved WAL during streaming pause
 236 - System caught up gracefully from lag
 237 - `--restore` perfectly reconstructed the database
 238 - All backups are plain SQL readable with `less`
 239
 240 ## Production usage
 241
 242 ```bash
 243 # Initialize once
 244 pg_scribe --init -d mydb -f /backups/mydb -U postgres
 245
 246 # Run continuously (use systemd or supervisor)
 247 pg_scribe --start -d mydb -f /backups/mydb -U postgres
 248
 249 # Rotate differentials daily (cron job)
 250 0 0 * * * pg_scribe --rotate-diff -f /backups/mydb
 251
 252 # Create new chain weekly with compressed base backup
 253 # This automatically stops old streaming and starts streaming to new chain
 254 0 0 * * 0 pg_scribe --new-chain --start -d mydb -f /backups/mydb -Z gzip:6 -U postgres
 255
 256 # Monitor replication lag (nagios/prometheus)
 257 pg_scribe --status -d mydb -f /backups/mydb -U postgres
 258 ```