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

   1 # pg_scribe Tutorial
   2
   3 Watch database changes become plain SQL 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
  12 echo "wal_level = logical" >> ~/.pgenv/pgsql/data/postgresql.conf
  13 pgenv restart
  14 ```
  15
  16 ## Terminal 1: Initialize and start streaming
  17
  18 ```bash
  19 # Create a test database
  20 createdb -U postgres demo
  21
  22 # Initialize backup system (creates extension, slot, base backup)
  23 pg_scribe --init -d demo -f /tmp/demo_backup -U postgres
  24
  25 # Start streaming changes to active.sql
  26 pg_scribe --start -d demo -f /tmp/demo_backup -U postgres
  27 ```
  28
  29 Leave this running. Every database change now becomes SQL.
  30
  31 ## Terminal 2: Make changes
  32
  33 ```bash
  34 psql -U postgres demo
  35 ```
  36
  37 ```sql
  38 CREATE TABLE users (
  39     id SERIAL PRIMARY KEY,
  40     email TEXT UNIQUE,
  41     created_at TIMESTAMP DEFAULT now()
  42 );
  43
  44 INSERT INTO users (email) VALUES ('alice@example.com'), ('bob@example.com');
  45
  46 UPDATE users SET email = 'alice@newdomain.com' WHERE id = 1;
  47
  48 ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';
  49
  50 CREATE TABLE posts (
  51     id SERIAL PRIMARY KEY,
  52     user_id INTEGER REFERENCES users(id),
  53     title TEXT
  54 );
  55
  56 INSERT INTO posts (user_id, title) VALUES (1, 'Hello World');
  57
  58 DELETE FROM users WHERE id = 2;
  59
  60 DROP TABLE posts;
  61 ```
  62
  63 ## Terminal 3: Watch the backup
  64
  65 ```bash
  66 # View streaming SQL
  67 tail -f /tmp/demo_backup/chain-*/active.sql
  68 ```
  69
  70 Every change appears as executable SQL:
  71
  72 ```sql
  73 BEGIN;
  74 CREATE TABLE users (...);
  75 COMMIT;
  76
  77 BEGIN;
  78 INSERT INTO public.users (id, email, created_at) VALUES (1, 'alice@example.com', '2025-10-18 ...');
  79 INSERT INTO public.users (id, email, created_at) VALUES (2, 'bob@example.com', '2025-10-18 ...');
  80 COMMIT;
  81
  82 BEGIN;
  83 UPDATE public.users SET id = 1, email = 'alice@newdomain.com', ... WHERE id = 1;
  84 COMMIT;
  85 ```
  86
  87 Or check status:
  88
  89 ```bash
  90 pg_scribe --status -d demo -f /tmp/demo_backup -U postgres
  91 ```
  92
  93 ## Restore to a new database
  94
  95 ```bash
  96 # Seal the differential (while Terminal 1 is still streaming)
  97 pg_scribe --rotate-diff -f /tmp/demo_backup
  98
  99 # Stop streaming gracefully
 100 pg_scribe --stop -f /tmp/demo_backup
 101
 102 # Restore to a new database
 103 pg_scribe --restore -d demo_restored -f /tmp/demo_backup -C -U postgres
 104
 105 # Verify
 106 psql -U postgres demo_restored -c "SELECT * FROM users;"
 107 ```
 108
 109 ## Cleanup
 110
 111 ```bash
 112 dropdb -U postgres demo_restored
 113 dropdb -U postgres demo
 114 rm -rf /tmp/demo_backup
 115 ```
 116
 117 ## What just happened
 118
 119 - `--init` created the wal2sql extension, replication slot, and base backup
 120 - `--start` streamed every change to active.sql as plain SQL
 121 - DDL (CREATE, ALTER, DROP) and DML (INSERT, UPDATE, DELETE) captured in order
 122 - `--rotate-diff` sealed active.sql into a timestamped differential file
 123 - `--restore` replayed base backup + sealed differentials into a new database
 124 - No binary formats, no version lock-in, readable with `less`
 125
 126 ## Production usage
 127
 128 ```bash
 129 # Initialize once
 130 pg_scribe --init -d mydb -f /backups/mydb -S mydb_slot -U postgres
 131
 132 # Run continuously (use systemd or supervisor)
 133 pg_scribe --start -d mydb -f /backups/mydb -S mydb_slot -U postgres
 134
 135 # Rotate differentials daily
 136 pg_scribe --rotate-diff -f /backups/mydb
 137
 138 # Create new chain weekly with compressed base backup and auto-transition
 139 # This stops the old streaming process and starts streaming to the new chain
 140 pg_scribe --new-chain --start -d mydb -f /backups/mydb -S mydb_slot -Z gzip:6 -U postgres
 141
 142 # Or manually transition (if you prefer separate steps):
 143 # 1. Create new chain
 144 pg_scribe --new-chain -d mydb -f /backups/mydb -S mydb_slot -Z gzip:6 -U postgres
 145 # 2. Stop old streaming
 146 pg_scribe --stop -f /backups/mydb
 147 # 3. Start streaming to new chain
 148 pg_scribe --start -d mydb -f /backups/mydb -S mydb_slot -U postgres
 149
 150 # Monitor replication lag
 151 pg_scribe --status -d mydb -f /backups/mydb -S mydb_slot -U postgres
 152 ```