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: 5 clients, 30 seconds
  63 pgbench -c 5 -T 30 -U postgres demo
  64 ```
  65
  66 **Terminal 3** - Watch the backup file grow:
  67
  68 ```bash
  69 # Check file size (run this a few times while pgbench is running)
  70 ls -lh /tmp/demo_backup/chain-*/active.sql
  71
  72 # Peek at the SQL being captured
  73 tail -n 30 /tmp/demo_backup/chain-*/active.sql
  74 ```
  75
  76 You'll see thousands of INSERT/UPDATE transactions accumulating!
  77
  78 **Terminal 3** - Check replication status:
  79
  80 ```bash
  81 pg_scribe --status -d demo -f /tmp/demo_backup -U postgres
  82 ```
  83
  84 You'll see actual replication metrics: LSN positions, lag bytes, transaction counts.
  85
  86 ## Phase 2: Rotate differentials under load
  87
  88 **Terminal 2** - Start longer background workload:
  89
  90 ```bash
  91 # Run for 2 minutes in background
  92 pgbench -c 5 -T 120 -U postgres demo &
  93 ```
  94
  95 **Terminal 3** - Watch status update continuously:
  96
  97 ```bash
  98 # Refresh status every 2 seconds
  99 watch -n 2 'pg_scribe --status -d demo -f /tmp/demo_backup -U postgres'
 100 ```
 101
 102 See transactions flowing through, backup file growing.
 103
 104 **Terminal 3** - Rotate differential while load continues (Ctrl+C to exit watch, then):
 105
 106 ```bash
 107 pg_scribe --rotate-diff -f /tmp/demo_backup
 108 ```
 109
 110 **Terminal 3** - See the rotation happen:
 111
 112 ```bash
 113 # Old active.sql sealed with timestamp, new active.sql starts fresh
 114 ls -lht /tmp/demo_backup/chain-*/
 115 ```
 116
 117 The sealed differential is an immutable point-in-time snapshot. New writes go to the new active.sql.
 118
 119 ## Phase 3: Chain transfer under load (zero-downtime operation)
 120
 121 **Terminal 2** - Keep load running:
 122
 123 ```bash
 124 pgbench -c 5 -T 60 -U postgres demo &
 125 ```
 126
 127 **Terminal 1** - Create new chain and transfer streaming to it:
 128
 129 ```bash
 130 # Stops old streaming, creates new compressed base backup, starts streaming to new chain
 131 pg_scribe --new-chain --start -d demo -f /tmp/demo_backup -Z gzip:6 -U postgres
 132 ```
 133
 134 Leave Terminal 1 streaming on the new chain.
 135
 136 **Terminal 3** - Verify new chain is active:
 137
 138 ```bash
 139 pg_scribe --status -d demo -f /tmp/demo_backup -U postgres
 140 ```
 141
 142 **Terminal 3** - See two chains:
 143
 144 ```bash
 145 ls -lh /tmp/demo_backup/
 146 # chain-YYYYMMDD-HHMMSS/  (old chain, sealed)
 147 # chain-YYYYMMDD-HHMMSS/  (new chain, active)
 148 ```
 149
 150 No transactions lost during the transfer! The old chain's final differential was sealed, new chain has fresh compressed base backup.
 151
 152 ## Phase 4: Catching up after pause (shows resilience)
 153
 154 **Terminal 1** - Stop streaming:
 155
 156 ```bash
 157 pg_scribe --stop -f /tmp/demo_backup
 158 ```
 159
 160 **Terminal 2** - Generate load while streaming is stopped:
 161
 162 ```bash
 163 pgbench -c 5 -t 1000 -U postgres demo
 164 ```
 165
 166 **Terminal 1** - Check status (streaming stopped, WAL accumulating):
 167
 168 ```bash
 169 pg_scribe --status -d demo -f /tmp/demo_backup -U postgres
 170 ```
 171
 172 You'll see something like: "Replication slot is 45 MB behind current WAL position"
 173
 174 The replication slot preserved the WAL even though streaming stopped!
 175
 176 **Terminal 1** - Restart streaming and watch it catch up:
 177
 178 ```bash
 179 pg_scribe --start -d demo -f /tmp/demo_backup -U postgres
 180 ```
 181
 182 Leave Terminal 1 streaming.
 183
 184 **Terminal 3** - Monitor catch-up progress:
 185
 186 ```bash
 187 watch -n 1 'pg_scribe --status -d demo -f /tmp/demo_backup -U postgres'
 188 ```
 189
 190 Watch the lag decrease as it replays accumulated WAL. Ctrl+C when caught up.
 191
 192 ## Phase 5: Restore and verify
 193
 194 **Terminal 1** - Stop streaming and seal final differential:
 195
 196 ```bash
 197 pg_scribe --stop -f /tmp/demo_backup
 198 pg_scribe --rotate-diff -f /tmp/demo_backup
 199 ```
 200
 201 **Terminal 1** - Restore to new database:
 202
 203 ```bash
 204 pg_scribe --restore -d demo_restored -f /tmp/demo_backup -C -U postgres
 205 ```
 206
 207 **Terminal 1** - Verify data matches:
 208
 209 ```bash
 210 # Compare row counts
 211 psql -U postgres demo -c "SELECT count(*) FROM pgbench_accounts;"
 212 psql -U postgres demo_restored -c "SELECT count(*) FROM pgbench_accounts;"
 213
 214 # Compare transaction history (shows all transactions were captured)
 215 psql -U postgres demo -c "SELECT count(*) FROM pgbench_history;"
 216 psql -U postgres demo_restored -c "SELECT count(*) FROM pgbench_history;"
 217
 218 # Compare balances (proves data integrity)
 219 psql -U postgres demo -c "SELECT sum(abalance) FROM pgbench_accounts;"
 220 psql -U postgres demo_restored -c "SELECT sum(abalance) FROM pgbench_accounts;"
 221 ```
 222
 223 Perfect match!
 224
 225 ## Cleanup
 226
 227 ```bash
 228 dropdb -U postgres demo_restored
 229 dropdb -U postgres demo
 230 rm -rf /tmp/demo_backup
 231 ```
 232
 233 ## What just happened
 234
 235 - `pgbench -i` created realistic TPC-B-like tables with 1M rows
 236 - `pg_scribe --init` created the wal2sql extension, replication slot, and base backup
 237 - `pg_scribe --start` streamed thousands of transactions as plain SQL
 238 - Differential rotation worked safely during active writes
 239 - Chain transfer happened with zero transaction loss
 240 - Replication slot preserved WAL during streaming pause
 241 - System caught up gracefully from lag
 242 - `--restore` perfectly reconstructed the database
 243 - All backups are plain SQL readable with `less`
 244
 245 ## Production usage
 246
 247 ```bash
 248 # Initialize once
 249 pg_scribe --init -d mydb -f /backups/mydb -U postgres
 250
 251 # Run continuously (use systemd or supervisor)
 252 pg_scribe --start -d mydb -f /backups/mydb -U postgres
 253
 254 # Rotate differentials daily (cron job)
 255 0 0 * * * pg_scribe --rotate-diff -f /backups/mydb
 256
 257 # Create new chain weekly with compressed base backup
 258 # This automatically stops old streaming and starts streaming to new chain
 259 0 0 * * 0 pg_scribe --new-chain --start -d mydb -f /backups/mydb -Z gzip:6 -U postgres
 260
 261 # Monitor replication lag (nagios/prometheus)
 262 pg_scribe --status -d mydb -f /backups/mydb -U postgres
 263 ```