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 (Ctrl+C in Terminal 1)
 100
 101 # Restore to a new database
 102 pg_scribe --restore -d demo_restored -f /tmp/demo_backup -C -U postgres
 103
 104 # Verify
 105 psql -U postgres demo_restored -c "SELECT * FROM users;"
 106 ```
 107
 108 ## Cleanup
 109
 110 ```bash
 111 dropdb -U postgres demo_restored
 112 dropdb -U postgres demo
 113 rm -rf /tmp/demo_backup
 114 ```
 115
 116 ## What just happened
 117
 118 - `--init` created the wal2sql extension, replication slot, and base backup
 119 - `--start` streamed every change to active.sql as plain SQL
 120 - DDL (CREATE, ALTER, DROP) and DML (INSERT, UPDATE, DELETE) captured in order
 121 - `--rotate-diff` sealed active.sql into a timestamped differential file
 122 - `--restore` replayed base backup + sealed differentials into a new database
 123 - No binary formats, no version lock-in, readable with `less`
 124
 125 ## Production usage
 126
 127 ```bash
 128 # Initialize once
 129 pg_scribe --init -d mydb -f /backups/mydb -S mydb_slot -U postgres
 130
 131 # Run continuously (use systemd or supervisor)
 132 pg_scribe --start -d mydb -f /backups/mydb -S mydb_slot -U postgres
 133
 134 # Rotate differentials daily
 135 pg_scribe --rotate-diff -f /backups/mydb
 136
 137 # Create new chain weekly with compressed base backup
 138 pg_scribe --new-chain -d mydb -f /backups/mydb -S mydb_slot -Z gzip:6 -U postgres
 139
 140 # Monitor replication lag
 141 pg_scribe --status -d mydb -f /backups/mydb -S mydb_slot -U postgres
 142 ```