Backup, Recovery & Online Migrations

1. Loại Backup

Production thường dùng cả hai: physical backup hằng ngày + WAL archive liên tục cho PITR; logical dump tuần cho migration cross-version.

2. pg_dump — logical backup

# Dump 1 DB ra file SQL:
pg_dump -h host -U user -d shop > shop.sql

# Custom format (compress, parallel restore được):
pg_dump -h host -U user -d shop -F c -f shop.dump

# Parallel jobs (Postgres 9.3+):
pg_dump -h host -U user -d shop -F d -j 4 -f shop_dir/

# Chỉ schema (không data):
pg_dump -d shop --schema-only > shop_schema.sql

# Chỉ data:
pg_dump -d shop --data-only > shop_data.sql

# 1 bảng:
pg_dump -d shop -t users -t orders > subset.sql

# Dump tất cả DB + role + global object:
pg_dumpall -h host -U postgres > cluster.sql

2.1. Restore

# Plain SQL:
psql -h host -U user -d shop_new < shop.sql

# Custom format:
pg_restore -h host -U user -d shop_new shop.dump

# Parallel:
pg_restore -h host -U user -d shop_new -j 4 -F d shop_dir/

# Chỉ table cụ thể:
pg_restore -d shop_new -t users shop.dump

2.2. Pitfalls

• pg_dump dùng snapshot transaction → consistent. Nhưng giữ snapshot lâu trên DB lớn → ngăn vacuum.
• Không backup permission của user khác (chỉ dump objects). Cần pg_dumpall -g để dump role.
• Chậm trên DB > 100GB. Dùng physical backup thay.

3. pg_basebackup — physical backup

# Dùng replication slot, copy data + WAL:
pg_basebackup \
  -h primary \
  -U replicator \
  -D /backups/2026-05-09 \
  -F tar \
  -X stream \
  -P \
  -c fast

# Output:
# /backups/2026-05-09/base.tar      (data files)
# /backups/2026-05-09/pg_wal.tar    (WAL needed for consistency)

Nhanh hơn pg_dump nhiều cho DB lớn. Backup này có thể restore làm replica hoặc làm primary mới.

3.1. Filesystem snapshot

ZFS, btrfs, LVM, EBS snapshot — tạo snapshot atomic của filesystem chứa data dir. Cực nhanh, nhưng đảm bảo Postgres flush WAL trước:

SELECT pg_start_backup('label');
-- Snapshot ở đây
SELECT pg_stop_backup();

Postgres 15+ dùng non-exclusive backup mode an toàn hơn.

AWS RDS, GCP CloudSQL dùng EBS/PD snapshot dưới hood để backup nhanh.

4. Point-in-Time Recovery (PITR)

"Restore DB về trạng thái 14:23:45 ngày hôm qua" — trước khi script SQL điên chạy phá data.

4.1. Cấu phần PITR

1. Base backup (pg_basebackup) — snapshot 1 thời điểm.
2. WAL archive liên tục — mỗi WAL segment được copy ra storage an toàn (S3, NFS).
3. Khi restore: copy base + replay WAL đến thời điểm muốn.

4.2. Setup WAL archive

# postgresql.conf:
wal_level = replica
archive_mode = on
archive_command = 'aws s3 cp %p s3://my-wal-archive/%f'
# %p = path file WAL, %f = filename
# command phải return 0 nếu thành công

Mỗi WAL segment 16MB. Postgres rotate xong → archive_command chạy. Không archive được → WAL tích lũy → disk full → DB hangs.

4.3. Restore

# 1. Stop Postgres
sudo systemctl stop postgresql

# 2. Restore base backup
rm -rf /var/lib/postgresql/data/*
tar -xf /backups/2026-05-09/base.tar -C /var/lib/postgresql/data/

# 3. Tạo recovery.signal + cấu hình recovery target
echo "" > /var/lib/postgresql/data/recovery.signal

# postgresql.conf:
restore_command = 'aws s3 cp s3://my-wal-archive/%f %p'
recovery_target_time = '2026-05-09 14:23:45 UTC'
recovery_target_action = 'pause'   # pause, promote, shutdown

# 4. Start Postgres → tự replay WAL đến time target
sudo systemctl start postgresql

# 5. Sau khi pause, kiểm tra data đúng → resume:
SELECT pg_wal_replay_resume();

4.4. Tools managed PITR

• WAL-G — encrypted WAL archive, S3/GCS, parallel.
• pgBackRest — incremental, parallel, retention policy.
• Barman — quản lý nhiều cluster.
• RDS / CloudSQL / Crunchy Bridge — managed.

5. Test Backup — "untested backup = no backup"

Backup không test = giả thuyết. Khi cần restore thật, mới phát hiện:

• Backup file corrupt.
• Permission missing (user không có).
• Version mismatch.
• Size lớn hơn tưởng → restore quá lâu.
• Document quy trình lỗi/thiếu.

5.1. Schedule test định kỳ

1. Hằng tháng: full restore test trên environment riêng.
2. Sau mỗi backup: tự động pg_restore --list để verify file readable.
3. Quý: PITR test — restore đến thời điểm cụ thể, verify data.
4. Hàng năm: full DR drill — giả lập primary chết, restore + promote replica.

5.2. RPO & RTO target

6. Disaster Recovery Plan

Tài liệu hóa:

1. Threats: AZ outage, region outage, ransomware, accidental DROP.
2. Backup location: backup đặt ở region khác source (3-2-1 rule: 3 copies, 2 media, 1 offsite).
3. Runbook step-by-step cho từng kịch bản.
4. Người chịu trách nhiệm + kênh liên lạc.
5. Encryption: backup encrypt at rest + in transit.
6. Retention: bao lâu giữ backup (legal, GDPR).
7. Test schedule + lịch sử.

7. Online Schema Migration — đau đầu thực sự

Mọi ALTER TABLE trên bảng > 100M dòng có thể block production phút/giờ. Online migration = thay đổi schema mà không downtime.

7.1. Vì sao ALTER chậm?

• Hầu hết ALTER cần ACCESS EXCLUSIVE lock → block reads + writes.
• Một số ALTER rewrite toàn bảng (đổi type, thêm cột với default phức tạp ở Postgres < 11).
• CREATE INDEX không CONCURRENTLY block writes.

7.2. Lock-light operations (Postgres 11+)

Đỡ hơn ngày xưa nhiều:

• ADD COLUMN ... DEFAULT 'x' — không rewrite (lưu default trong catalog, materialize khi UPDATE).
• ADD COLUMN ... NULL — instant.
• ADD CONSTRAINT NOT VALID + VALIDATE CONSTRAINT — chia thành 2 bước.

7.3. Quy trình "expand & contract"

Pattern phổ biến cho schema thay đổi mà giữ app working liên tục:

1. Expand: thêm cột/bảng mới SONG SONG với cũ.
2. App ghi cả 2 (dual-write).
3. Backfill data cũ sang mới (script chạy nền).
4. App đọc từ cả 2, prefer mới.
5. App chỉ đọc mới.
6. Contract: drop cột/bảng cũ.

8. Tools: pg_repack, gh-ost, pt-online-schema-change

8.1. pg_repack (Postgres)

"VACUUM FULL không lock". Tạo bản sao bảng (CONCURRENTLY), sync changes qua trigger, swap ở cuối.

# Cài:
sudo apt install postgresql-16-repack

# Repack 1 bảng:
pg_repack -d shop -t users

# Reindex (rebuild bloat index):
pg_repack -d shop -t users --only-indexes

Hữu ích khi bảng bloat nặng cần defrag, hoặc CLUSTER theo index khác.

8.2. gh-ost (MySQL, Github's tool)

Online ALTER cho MySQL không cần trigger (đọc binlog). Used heavily ở Github, Shopify.

8.3. pt-online-schema-change (Percona, MySQL)

Cũ hơn gh-ost, dùng trigger.

8.4. Postgres ALTER TABLE patterns thực tế

Postgres không có công cụ migration online "magic" như gh-ost. Nhưng các pattern thủ công + Postgres 11+ smarts đủ cho hầu hết case.

9. Migration patterns — thực hành

9.1. Add column nullable

-- Trong Postgres 11+: instant, không rewrite
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

9.2. Add column NOT NULL với default

-- ❌ Postgres < 11: rewrite cả bảng
ALTER TABLE users ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

-- ✓ Postgres 11+: instant (default lưu trong catalog)
ALTER TABLE users ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

-- ✓ Pattern "expand-contract" cho version cũ:
-- Bước 1:
ALTER TABLE users ADD COLUMN status VARCHAR(20);
-- Bước 2: backfill batch:
UPDATE users SET status = 'active' WHERE status IS NULL AND id BETWEEN 1 AND 1000000;
-- (lặp theo batch)
-- Bước 3: thêm DEFAULT + NOT NULL:
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
ALTER TABLE users ALTER COLUMN status SET NOT NULL;

9.3. Drop column

-- Instant (chỉ mark trong catalog, data thật xóa khi vacuum/rewrite)
ALTER TABLE users DROP COLUMN deprecated_field;

-- ⚠ Coordinate với app: deploy code không dùng cột này TRƯỚC khi drop
-- Nếu code cũ vẫn SELECT cột → lỗi.

9.4. Rename column

Đổi tên 1 bước cực rủi ro với app deployed (rolling deploy có thể có 2 version code).

Pattern an toàn:

-- Bước 1: thêm cột mới
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- Bước 2: trigger sync 2 chiều (hoặc dual-write trong app)
CREATE OR REPLACE FUNCTION sync_name() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.name IS DISTINCT FROM OLD.name THEN
    NEW.full_name = NEW.name;
  ELSIF NEW.full_name IS DISTINCT FROM OLD.full_name THEN
    NEW.name = NEW.full_name;
  END IF;
  RETURN NEW;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER sync_name_trg BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION sync_name();

-- Bước 3: backfill
UPDATE users SET full_name = name WHERE full_name IS NULL;

-- Bước 4: deploy app version dùng full_name
-- Bước 5: drop trigger + drop cột name
DROP TRIGGER sync_name_trg ON users;
ALTER TABLE users DROP COLUMN name;

9.5. Change column type

Tương tự rename: thêm cột mới, dual-write, backfill, drop cũ.

9.6. Add index

-- ❌ Block writes trên bảng đang prod
CREATE INDEX idx_users_email ON users(email);

-- ✓ Online:
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- Nếu fail:
DROP INDEX CONCURRENTLY idx_users_email;
-- Rồi tạo lại

9.7. Add foreign key

-- ❌ Validate ngay → scan toàn bảng, lock dài
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

-- ✓ 2 bước:
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id)
  REFERENCES users(id) NOT VALID;
-- → constraint áp dụng cho dòng MỚI, không validate dòng cũ → instant.

ALTER TABLE orders VALIDATE CONSTRAINT fk_user;
-- → validate dòng cũ. Lock SHARE UPDATE EXCLUSIVE — KHÔNG block reads/writes.

10. Connection Pooling — bắt buộc cho production

Mỗi Postgres connection tốn ~10MB RAM (process model). 10 000 connection = 100GB RAM. Không scale.

10.1. PgBouncer

Connection pooler nhẹ — multiplexing client connections (rất nhiều) qua server connections (ít).

10.2. Pool modes

• session — mỗi client giữ 1 server connection cho cả session. An toàn nhưng kém scale.
• transaction — server connection trả về pool sau mỗi transaction. Phổ biến nhất.
• statement — sau mỗi statement. Cực aggressive, gãy nhiều feature.

Chú ý transaction mode: session-level state (SET, prepared statements, advisory lock session) không hoạt động.

10.3. Khi nào không cần pooler?

Serverless / Lambda / edge — connection ngắn hạn, không lâu dài. Có thể dùng RDS Proxy hoặc Neon's pooler.

Long-running app (Node/Java) → luôn cần PgBouncer hoặc app-level pool.

11. Anti-patterns thường gặp

11.1. N+1 query

Đã đề cập Ch8. ORM lazy loading là thủ phạm chính. Sửa: eager load (Sequelize include, Prisma include, Active Record includes).

11.2. SELECT * everywhere

Lấy mọi cột → tốn RAM, network, không tận dụng được Index Only Scan. Chỉ chọn cột cần.

11.3. Soft delete tràn lan

Mọi bảng có deleted_at. Mọi query phải WHERE deleted_at IS NULL. Quên 1 chỗ = bug bảo mật/UX. Cân nhắc archive table riêng.

11.4. UPDATE counter row mỗi request

-- ❌ Mọi page view UPDATE 1 row → contention
UPDATE counters SET value = value + 1 WHERE name = 'page_view';

-- ✓ INSERT log + aggregate batch
INSERT INTO counter_log VALUES ('page_view', NOW());

11.5. EAV ở mọi nơi

"Schemaless" qua bảng (entity, attr, value). Slow, no type safety, JOIN dày. Dùng JSONB.

11.6. Polymorphic FK

commentable_id + commentable_type không enforce FK. Khó maintain. Tách bảng.

11.7. Long-running transaction

Mở BEGIN trước network call → giữ snapshot lâu → bloat. Set idle_in_transaction_session_timeout.

11.8. Index trên mọi cột

Mỗi index làm INSERT chậm, tốn disk. Index theo query thật.

11.9. Không monitoring

Không metrics → không phát hiện slow query, lock wait, replication lag, disk full đến lúc đã chết. Setup pg_stat_statements, Prometheus, Grafana.

12. Production Checklist — DB sẵn sàng cho prod

12.1. Cấu hình

• ☐ shared_buffers ~ 25% RAM
• ☐ effective_cache_size ~ 50-75% RAM
• ☐ work_mem theo workload (default 4MB thường thấp)
• ☐ maintenance_work_mem > 256MB cho VACUUM/CREATE INDEX nhanh
• ☐ random_page_cost = 1.1 cho SSD
• ☐ max_connections không quá cao — dùng PgBouncer
• ☐ idle_in_transaction_session_timeout = '5min'
• ☐ statement_timeout cho query không treo vô hạn

12.2. Backup & DR

• ☐ Daily physical backup + WAL archive offsite
• ☐ Retention policy theo compliance
• ☐ Test restore mỗi tháng
• ☐ Documented runbook
• ☐ DR drill quý

12.3. HA

• ☐ ≥ 1 streaming replica (sync hoặc async theo RPO)
• ☐ Auto failover tool (Patroni / pg_auto_failover)
• ☐ Connection routing (HAProxy / PgBouncer)

12.4. Monitoring

• ☐ pg_stat_statements enabled
• ☐ Metrics: connection count, replication lag, cache hit ratio, dead tuples, slow query count
• ☐ Alert: disk > 80%, replication lag > 30s, connection count > 80%
• ☐ Logging: slow queries (> 1s), errors, deadlocks

12.5. Security

• ☐ Encryption at rest (filesystem / cloud provider)
• ☐ TLS for connections
• ☐ App user khác superuser
• ☐ Secret management (Vault, AWS Secrets Manager)
• ☐ Audit log nếu cần (compliance)
• ☐ Network: DB không expose public, chỉ private subnet

12.6. Schema & Code

• ☐ Mọi cột thường-WHERE có index phù hợp
• ☐ FK constraints đầy đủ
• ☐ Migrations versioned (Flyway, Liquibase, dbmate)
• ☐ ORM eager-load để tránh N+1
• ☐ Connection pool app-level + PgBouncer
• ☐ Retry logic cho serialization failure / deadlock

13. Bài tập

1. Setup pg_basebackup hằng ngày + WAL archive lên S3 cho 1 Postgres local. Test restore PITR đến thời điểm 1 phút trước.
2. Practice migration "rename column" theo expand-contract pattern. Đo downtime (kỳ vọng = 0).
3. Cài PgBouncer trước Postgres. Bench với 1000 connection từ app. Đo throughput vs không có PgBouncer.
4. Implement statement_timeout + idle_in_transaction_session_timeout. Test với query treo cố ý.
5. Dùng pg_stat_statements tìm 5 query chậm nhất trong DB của bạn. Tối ưu top 1.
6. Đề xuất DR plan đầy đủ cho 1 e-commerce SaaS với RPO < 1min, RTO < 5min. Liệt kê tools, schedule, runbook outline.

14. Quiz