# pg_scribe Tutorial

Watch database changes become plain SQL in real-time. Three terminals recommended.

## Prerequisites

```bash
# Install the extension
cd wal2sql && make && make install

# Configure PostgreSQL
echo "wal_level = logical" >> ~/.pgenv/pgsql/data/postgresql.conf
pgenv restart
```

## Terminal 1: Initialize and start streaming

```bash
# Create a test database
createdb -U postgres demo

# Initialize backup system (creates extension, slot, base backup)
pg_scribe --init -d demo -f /tmp/demo_backup -U postgres

# Start streaming changes to active.sql
pg_scribe --start -d demo -f /tmp/demo_backup -U postgres
```

Leave this running. Every database change now becomes SQL.

## Terminal 2: Make changes

```bash
psql -U postgres demo
```

```sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE,
    created_at TIMESTAMP DEFAULT now()
);

INSERT INTO users (email) VALUES ('alice@example.com'), ('bob@example.com');

UPDATE users SET email = 'alice@newdomain.com' WHERE id = 1;

ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    title TEXT
);

INSERT INTO posts (user_id, title) VALUES (1, 'Hello World');

DELETE FROM users WHERE id = 2;

DROP TABLE posts;
```

## Terminal 3: Watch the backup

```bash
# View streaming SQL
tail -f /tmp/demo_backup/chain-*/active.sql
```

Every change appears as executable SQL:

```sql
BEGIN;
CREATE TABLE users (...);
COMMIT;

BEGIN;
INSERT INTO public.users (id, email, created_at) VALUES (1, 'alice@example.com', '2025-10-18 ...');
INSERT INTO public.users (id, email, created_at) VALUES (2, 'bob@example.com', '2025-10-18 ...');
COMMIT;

BEGIN;
UPDATE public.users SET id = 1, email = 'alice@newdomain.com', ... WHERE id = 1;
COMMIT;
```

Or check status:

```bash
pg_scribe --status -d demo -f /tmp/demo_backup -U postgres
```

## Restore to a new database

```bash
# Seal the differential (while Terminal 1 is still streaming)
pg_scribe --rotate-diff -f /tmp/demo_backup

# Stop streaming (Ctrl+C in Terminal 1)

# Restore to a new database
pg_scribe --restore -d demo_restored -f /tmp/demo_backup -C -U postgres

# Verify
psql -U postgres demo_restored -c "SELECT * FROM users;"
```

## Cleanup

```bash
dropdb -U postgres demo_restored
dropdb -U postgres demo
rm -rf /tmp/demo_backup
```

## What just happened

- `--init` created the wal2sql extension, replication slot, and base backup
- `--start` streamed every change to active.sql as plain SQL
- DDL (CREATE, ALTER, DROP) and DML (INSERT, UPDATE, DELETE) captured in order
- `--rotate-diff` sealed active.sql into a timestamped differential file
- `--restore` replayed base backup + sealed differentials into a new database
- No binary formats, no version lock-in, readable with `less`

## Production usage

```bash
# Initialize once
pg_scribe --init -d mydb -f /backups/mydb -S mydb_slot -U postgres

# Run continuously (use systemd or supervisor)
pg_scribe --start -d mydb -f /backups/mydb -S mydb_slot -U postgres

# Rotate differentials daily
pg_scribe --rotate-diff -f /backups/mydb

# Create new chain weekly with compressed base backup
pg_scribe --new-chain -d mydb -f /backups/mydb -S mydb_slot -Z gzip:6 -U postgres

# Monitor replication lag
pg_scribe --status -d mydb -f /backups/mydb -S mydb_slot -U postgres
```