Postgres & SQL Server Production Ops

Status: planned, not started. Commands are the plan. Nothing has run yet.

Everything so far is MySQL-first. Postgres only appears as the CDC target, and SQL Server not at all. This project runs both as real production systems: backups with a point-in-time recovery drill, high availability, security, and live troubleshooting of locks and waits. Each leg follows the same drill-and-measure style as the MySQL work, so the results compare side by side.

Tools (planned)

Tool	Will be used for
PostgreSQL 16	pg_basebackup + WAL archiving, roles, row-level security, pg_stat_statements
SQL Server 2022 Developer Edition (Docker, Linux)	Full/differential/log backups, Query Store, DMVs, TDE
Always On availability groups (Linux) or log shipping	SQL Server HA, whichever runs without a Windows cluster
Prometheus + Grafana (existing)	Capacity dashboards for both

Plan

Postgres leg

1. Backup and point-in-time recovery. Take a base backup with continuous WAL archiving, then restore to one minute before a deliberate bad DELETE.

   pg_basebackup -D /backups/base -Ft -z -X stream -P
   # postgresql.conf: archive_mode=on, archive_command='cp %p /wal/%f'
   # restore: recovery_target_time = '2026-…-… 10:41:00'
   

2. Security. Roles, GRANT/REVOKE, row-level security, and a tightened pg_hba.conf.

3. Troubleshooting. Cause a real lock wait between two sessions and diagnose it live.

   SELECT pid, wait_event_type, wait_event, state, query
   FROM pg_stat_activity WHERE wait_event IS NOT NULL;
   
   SELECT blocked.pid AS blocked, blocking.pid AS blocking
   FROM pg_locks blocked JOIN pg_locks blocking
     ON blocking.locktype = blocked.locktype AND blocking.relation = blocked.relation
    AND blocking.pid <> blocked.pid
   WHERE NOT blocked.granted AND blocking.granted;
   

4. Tuning. Use EXPLAIN (ANALYZE, BUFFERS) and pg_stat_statements to find and fix the worst query.

SQL Server leg

1. Backup chain and point-in-time restore.

   BACKUP DATABASE Shop TO DISK = '/backups/shop_full.bak' WITH COMPRESSION;
   BACKUP LOG Shop TO DISK = '/backups/shop_log1.trn';
   RESTORE DATABASE Shop FROM DISK = '/backups/shop_full.bak' WITH NORECOVERY;
   RESTORE LOG Shop FROM DISK = '/backups/shop_log1.trn' WITH STOPAT = '2026-…T10:41:00', RECOVERY;
   

2. Waits and slow queries. Query Store plus DMVs.

   SELECT TOP 10 wait_type, wait_time_ms FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC;
   SELECT session_id, blocking_session_id, wait_type, wait_time FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;
   

3. Security. Logins vs users, roles, ownership chains, and Transparent Data Encryption.

4. High availability. A basic availability group, with log shipping as the fallback.

Shared

• Runbooks: one for each backup/recovery drill, in the same style as the MySQL failover runbook.
• Monitoring: exporters for both databases added to the existing Prometheus/Grafana stack.

Known risks

• Memory: the home server is already near its limits (three MySQL servers, Kafka, Airflow). SQL Server needs about 2 GB minimum, so check headroom first, or run it on the second server.
• AG support: availability groups on Linux without a cluster manager have limits, so log shipping may be the realistic HA.