// DBA Guide

PostgreSQL DBA Guide

คู่มือสำหรับ Database Administrator — Backup, Replication, HA, Monitoring และ Maintenance ครบวงจร

💾

Backup & Recovery

pg_dump, pg_basebackup, Point-in-Time Recovery (PITR) และ WAL Archiving

🔄

Replication

Streaming Replication, Logical Replication, Synchronous/Asynchronous

🏗️

High Availability

Patroni, pgBouncer, pg_auto_failover และ Load Balancing

📊

Monitoring

pg_stat_*, pg_activity, pgBadger, Prometheus + postgres_exporter

🔧

Maintenance

VACUUM, ANALYZE, REINDEX, Partition Management, Bloat Management

📈

Capacity Planning

Storage Growth, Connection Management, Query Load Analysis

// Backup & Restore

Backup & Restore

Logical Backup ด้วย pg_dump

-- Backup Database เดียว
$ pg_dump -h localhost -U postgres \
  -F c -f mydb.dump mydb

-- Backup ทุก Database
$ pg_dumpall -h localhost -U postgres \
  > all_databases.sql

-- Restore จาก dump
$ pg_restore -h localhost -U postgres \
  -d mydb_new mydb.dump

-- Restore แบบ Parallel (เร็วกว่า)
$ pg_restore -j 4 -d mydb_new mydb.dump
          

Physical Backup + PITR

-- Base Backup
$ pg_basebackup -h localhost -U replicator \
  -D /backup/base -Ft -z -P

-- WAL Archiving (postgresql.conf)
archive_mode    = on
archive_command = 'cp %p /wal_archive/%f'

-- Point-in-Time Recovery (recovery.conf)
restore_command  = 'cp /wal_archive/%f %p'
recovery_target_time = '2024-12-01 03:00:00'
recovery_target_action = 'promote'
          

// Replication

Streaming Replication

-- Primary: postgresql.conf
wal_level               = replica
max_wal_senders         = 5
wal_keep_size           = 1GB

-- Primary: สร้าง Replication User
CREATE USER replicator REPLICATION LOGIN PASSWORD 'strong_pass';

-- Standby: สร้าง Base Backup จาก Primary
$ pg_basebackup -h primary-host -U replicator \
  -D $PGDATA -Xs -P

-- Standby: postgresql.conf
primary_conninfo = 'host=primary-host user=replicator password=strong_pass'

-- ตรวจสอบ Replication Status (บน Primary)
SELECT client_addr, state, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;
      

// Monitoring

Monitoring Queries ที่ DBA ควรรู้

-- ดู Active Connections
SELECT pid, usename, application_name, client_addr, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- ดู Long-running Queries
SELECT pid, NOW() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < NOW() - INTERVAL '5 minutes';

-- ขนาด Database และ Table
SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;

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 LIMIT 10;

-- Cache Hit Ratio (ควร > 99%)
SELECT
  round(100.0 * sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)), 2)
  AS cache_hit_ratio
FROM pg_statio_user_tables;

-- Kill Long-running Query
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = 12345;
      

ต้องการเรียนรู้ Security และ Extensions?

ศึกษา Row-Level Security, pgAudit และ Extensions สำหรับ Enterprise PostgreSQL

Security Guide → Extensions