CT1112 PostgreSQL¶

CT1112 is the central PostgreSQL database server for the CharlieHub cluster.

Overview¶

Property	Value
VMID	1112
IP	REDACTED_IP
Node	px1-silverstone
Type	LXC Container
Purpose	Central database

Databases¶

Database	Purpose	Primary User
`charliehub_domains`	Domain Manager	charliehub
`ingest_db`	IoT Platform - raw data	iot_platform
`transform_db`	IoT Platform - transformed data	iot_platform
`microshare_db`	IoT Platform - application data	iot_platform

Connection Details¶

Host: REDACTED_IP
Port: 5432
SSL: Optional (not required internally)

Connection String Examples¶

# Domain Manager
postgresql://charliehub:${YOUR_DB_PASSWORD}@REDACTED_IP:5432/charliehub_domains

# IoT Platform
postgresql://iot_platform:iotpass2025@REDACTED_IP:5432/ingest_db

Administration¶

Access PostgreSQL CLI¶

# From px1
pct exec 1112 -- sudo -u postgres psql

# Connect to specific database
pct exec 1112 -- sudo -u postgres psql -d charliehub_domains

# Remote connection
PGPASSWORD='${YOUR_DB_PASSWORD}' psql -h REDACTED_IP -U charliehub -d charliehub_domains

Common Queries¶

-- List all databases
\l

-- List tables in current database
\dt

-- Show table schema
\d table_name

-- Check database sizes
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) 
FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;

-- Check active connections
SELECT * FROM pg_stat_activity WHERE state = 'active';

User Management¶

-- Create new user
CREATE USER newuser WITH PASSWORD 'password';

-- Grant database access
GRANT ALL PRIVILEGES ON DATABASE dbname TO newuser;

-- Grant table access
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO newuser;

Backup¶

Automated Backups¶

CT1112 is included in the cluster backup strategy:

ZFS Snapshots: Daily at 02:00, 7-day retention
Vzdump: Daily, stored on USB and pikvm NFS

Manual Database Dump¶

# Dump single database
pct exec 1112 -- sudo -u postgres pg_dump charliehub_domains > backup.sql

# Dump all databases
pct exec 1112 -- sudo -u postgres pg_dumpall > all_databases.sql

# Compressed dump
pct exec 1112 -- sudo -u postgres pg_dump charliehub_domains | gzip > backup.sql.gz

Restore from Dump¶

# Restore single database
pct exec 1112 -- sudo -u postgres psql -d charliehub_domains < backup.sql

# Restore all databases
pct exec 1112 -- sudo -u postgres psql -f all_databases.sql

Monitoring¶

PostgreSQL Exporter¶

Metrics available at: http://REDACTED_IP:9187/metrics

Prometheus scrapes this endpoint for: - Connection counts - Query performance - Table statistics - Replication status

Health Check¶

# Check PostgreSQL service
pct exec 1112 -- systemctl status postgresql

# Check connections
pct exec 1112 -- sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"

# Check disk usage
pct exec 1112 -- df -h

Configuration¶

PostgreSQL Config¶

Location: /etc/postgresql/15/main/postgresql.conf

Key settings:

listen_addresses = '*'
max_connections = 100
shared_buffers = 256MB

Client Authentication¶

Location: /etc/postgresql/15/main/pg_hba.conf

# Allow connections from cluster network
host    all    all    REDACTED_SUBNET    md5
host    all    all    REDACTED_SUBNET    md5

Troubleshooting¶

Cannot Connect¶

# Check service is running
pct exec 1112 -- systemctl status postgresql

# Check listening ports
pct exec 1112 -- ss -tlnp | grep 5432

# Check pg_hba.conf for access rules
pct exec 1112 -- cat /etc/postgresql/15/main/pg_hba.conf

High Disk Usage¶

# Check database sizes
pct exec 1112 -- sudo -u postgres psql -c "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;"

# Vacuum databases
pct exec 1112 -- sudo -u postgres vacuumdb --all --analyze

Slow Queries¶

# Enable query logging (temporary)
pct exec 1112 -- sudo -u postgres psql -c "ALTER SYSTEM SET log_min_duration_statement = 1000;"
pct exec 1112 -- sudo -u postgres psql -c "SELECT pg_reload_conf();"

# Check slow queries in log
pct exec 1112 -- tail -100 /var/log/postgresql/postgresql-15-main.log

hub2 Domain Manager - Uses charliehub_domains database
CT1113 IoT Platform - Uses ingest_db, transform_db, microshare_db
hub2 Monitoring - Scrapes PostgreSQL exporter metrics (via WireGuard)