Administration Guide

# Network
host                = 0.0.0.0
port                = 5433          # PostgreSQL wire
rest_port           = 8080
grpc_port           = 9090
redis_port          = 6379

# Storage
data_dir            = /var/lib/absdb
wal_dir             = /var/lib/absdb/wal
buffer_pool_mb      = 256           # LIRS buffer pool size
page_size           = 4096          # Default: 4 KB (OLTP)
max_connections     = 500
max_active_txns     = 4096

# WAL & Durability
wal_group_commit    = 64            # Records per fsync
wal_sync_mode       = dsync         # dsync | fsync | none (unsafe)
checkpoint_interval = 300           # Seconds between checkpoints

# Security
tls_cert            = /etc/absdb/server.crt
tls_key             = /etc/absdb/server.key
tls_ca_cert         = /etc/absdb/ca.crt    # for mTLS
require_ssl         = off
pqc_hybrid          = on             # ML-KEM-768 + X25519 hybrid TLS

# Logging
log_level           = INFO           # DEBUG | INFO | WARN | ERROR
log_slow_query_ms   = 1              # Log queries slower than N ms
log_file            = /var/log/absdb/absdb.log
audit_log           = on             # SHA-256 hash-chained audit log

# Performance
parallel_workers    = 0              # 0 = auto (logical CPU count, max 64)
jit_enabled         = on
io_uring            = on             # Linux io_uring (falls back to standard direct I/O)

# Reload without restart (most parameters)
absdb-admin --reload

# Or via SQL
SELECT absdb_reload_config();

-- Create a user
CREATE ROLE alice WITH LOGIN PASSWORD 'secure_pass_123';

-- Create a superuser
CREATE ROLE dbadmin WITH LOGIN SUPERUSER PASSWORD 'admin_pass';

-- Alter password
ALTER ROLE alice PASSWORD 'new_password_456';

-- Drop user
DROP ROLE alice;

-- List all users
SELECT rolname, rolsuper, rolcreatedb, rolcanlogin
FROM pg_roles ORDER BY rolname;

-- Create application role
CREATE ROLE app_readonly;
CREATE ROLE app_readwrite;

-- Grant schema privileges
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

GRANT USAGE ON SCHEMA public TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readwrite;

-- Assign role to user
GRANT app_readonly TO alice;
GRANT app_readwrite TO bob;

-- Row-Level Security (RLS)
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::INTEGER);

-- Dynamic Data Masking (Enterprise)
ALTER TABLE users ALTER COLUMN email SET (mask = 'email');
ALTER TABLE patients ALTER COLUMN ssn SET (hipaa_phi = true);

-- Create database
CREATE DATABASE myapp;
CREATE DATABASE myapp OWNER alice;

-- Drop database
DROP DATABASE myapp;

-- List databases
\l
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;

-- Connect to database
\c myapp

-- Branch (copy-on-write clone — zero storage cost until first write)
CREATE DATABASE dev_branch FROM myapp;
SELECT * FROM absdb_branches;
SELECT * FROM absdb_branch_diff('dev_branch', 'myapp');

-- Schemas
CREATE SCHEMA analytics;
SET search_path = analytics, public;
GRANT USAGE ON SCHEMA analytics TO alice;

# Local backup
absdb-admin backup --database myapp --output /backups/myapp_20260406.absdb

# Backup to S3
absdb-admin backup --database myapp \
  --output s3://my-bucket/backups/myapp_20260406.absdb \
  --compress zstd

# Backup to GCS
absdb-admin backup --database myapp \
  --output gcs://my-bucket/backups/myapp_$(date +%Y%m%d).absdb

# Incremental backup (WAL-based)
absdb-admin backup --incremental \
  --since-lsn 0/3A000000 \
  --output s3://my-bucket/wal/

# Restore from backup file
absdb-admin restore \
  --input /backups/myapp_20260406.absdb \
  --database myapp_restored

# Restore from S3
absdb-admin restore \
  --input s3://my-bucket/backups/myapp_20260406.absdb \
  --database myapp

# Enable continuous WAL streaming to S3
absdb-admin wal-stream \
  --target s3://my-bucket/wal-archive/ \
  --compress lz4

# Restore to specific timestamp
absdb-admin restore \
  --input /backups/myapp_base.absdb \
  --wal-archive s3://my-bucket/wal-archive/ \
  --target-time "2026-04-06 09:30:00" \
  --database myapp_recovered

# Restore to specific WAL LSN
absdb-admin restore \
  --input /backups/myapp_base.absdb \
  --wal-archive s3://my-bucket/wal-archive/ \
  --target-lsn "0/3A2F0080" \
  --database myapp_recovered

-- Query historical state at a specific SCN
SELECT * FROM orders AS OF SCN 8192
WHERE status = 'pending';

-- Current SCN
SELECT absdb_current_scn();

-- Active queries
SELECT pid, query, state, wait_event, query_start,
       now() - query_start AS duration
FROM absdb_active_queries
ORDER BY duration DESC;

-- Database statistics
SELECT * FROM absdb_stats;

-- Table sizes
SELECT relname,
       pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class WHERE relkind = 'r' ORDER BY pg_total_relation_size(oid) DESC;

-- LIRS buffer pool hit rate
SELECT hit_rate, hot_pages, cold_pages, capacity_pages
FROM absdb_buffer_pool_stats;

-- Lock waits
SELECT * FROM absdb_lock_waits;

-- Slow queries (logged when > log_slow_query_ms)
SELECT query, calls, mean_time_us, max_time_us, rows
FROM absdb_query_stats ORDER BY mean_time_us DESC LIMIT 20;

-- Auto-indexing recommendations
SELECT * FROM absdb_advisor_recommendations;

scrape_configs:
  - job_name: 'absolutedb'
    static_configs:
      - targets: ['localhost:8080']
    metrics_path: /metrics
    scrape_interval: 15s

-- Reclaim dead tuple space
VACUUM orders;
VACUUM FULL orders;    -- full rewrite, exclusive lock

-- Update query planner statistics
ANALYZE orders;
ANALYZE;               -- all tables in current database

-- Combined
VACUUM ANALYZE orders;

-- Auto-vacuum is enabled by default; check settings:
SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;

-- Rebuild index
REINDEX TABLE orders;
REINDEX INDEX orders_customer_id_idx;

-- Check table bloat
SELECT relname,
       pg_size_pretty(pg_relation_size(oid)) AS table_size,
       n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

# Node 1 (initial leader)
absdb-server --data /data --cluster-init \
  --node-id node1 --cluster-addr node1:9091

# Node 2 (join existing cluster)
absdb-server --data /data \
  --node-id node2 --cluster-addr node2:9091 \
  --join node1:9091

# Node 3
absdb-server --data /data \
  --node-id node3 --cluster-addr node3:9091 \
  --join node1:9091

-- Raft cluster status
SELECT * FROM absdb_cluster_nodes;

-- Current leader
SELECT absdb_cluster_leader();

-- C-RAID disk status
SELECT * FROM absdb_raid_status;

-- Auto-balancer status
SELECT * FROM absdb_balancer_status;

# 1. Download new binary
curl -fsSL https://downloads.absolutedb.com/install.sh | sh

# 2. Upgrade followers first (one at a time)
absdb-admin upgrade --node node2 --binary /usr/local/bin/absdb-server

# 3. Transfer leadership away from node1
absdb-admin transfer-leadership --from node1 --to node2

# 4. Upgrade original leader (now follower)
absdb-admin upgrade --node node1 --binary /usr/local/bin/absdb-server

# Zero downtime — clients see no interruption during follower upgrades

-- Add column (online, non-blocking)
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMP;

-- Online index creation (background, non-blocking)
CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer_id);

-- Add constraint (validates in background)
ALTER TABLE orders ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id)
  NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT fk_customer;  -- validates existing rows

-- Monitor online index build progress
SELECT * FROM absdb_index_build_progress;

-- Create isolated tenant (dedicated schema + DEK)
SELECT absdb_tenant_create('acme_corp',
  '{"quota_gb": 10, "max_connections": 50}'::jsonb);

-- Set tenant context
SET app.tenant_id = 'acme_corp';

-- List tenants
SELECT * FROM absdb_tenants;

-- Export tenant snapshot
SELECT absdb_tenant_export('acme_corp', 's3://bucket/exports/acme_corp.absdb');

-- Drop tenant (and all data)
SELECT absdb_tenant_drop('acme_corp');

-- View recent audit log entries (SHA-256 hash-chained)
SELECT event_time, user_name, action, object, client_addr
FROM _absdb_audit_log
ORDER BY event_time DESC LIMIT 50;

-- Verify audit chain integrity
SELECT absdb_verify_audit_chain('2026-04-01', '2026-04-06');

-- DDL change log (SOC 2 evidence)
SELECT * FROM _absdb_ddl_log ORDER BY ts DESC;

-- GDPR erasure
CALL absdb_gdpr_erase('users', 'email', 'alice@example.com');
SELECT * FROM absdb_gdpr_report('alice@example.com');