Automation & Infrastructure as CodeAutomation & Infrastructure as Code

Thuộc bộ kiến thức PostgreSQL DBA Roadmap.

Tổng quan

Có một thời điểm rất cụ thể phân biệt một DBA đang quản lý một database với một DBA đang quản lý cả một hạm đội server: đó là khi một tác vụ cần được thực hiện giống hệt nhau, nhiều hơn một lần, dưới áp lực thời gian. SSH vào server và gõ ALTER SYSTEM SET ... hay tự tay sửa pg_hba.conf thì không sao ở lần đầu tiên, trên một máy, khi mọi thứ đang yên bình. Nhưng nó sẽ hỏng ngay khi cùng một thay đổi cần áp dụng lên một primary, hai replica và một DR standby mà không ai được phép quên bước nào, và nó hỏng hoàn toàn lúc 3 giờ sáng khi runbook failover chỉ tồn tại dưới dạng một trang wiki với hai mươi bước đánh số mà ai đó giờ phải thực hiện hoàn hảo theo trí nhớ, trong khi bị page và còn chưa tỉnh táo.

Khoảng cách đó — giữa “tôi biết làm việc này” và “việc này sẽ được làm đúng, theo cùng một cách, mỗi lần, bởi bất kỳ ai, kể cả dưới áp lực” — chính là thứ mà automation và infrastructure as code (IaC) thu hẹp lại. Ba đặc tính quan trọng hơn bất kỳ công cụ cụ thể nào: tính nhất quán (mọi server lẽ ra phải giống nhau thì thực sự giống nhau, vì chúng được dựng từ cùng một định nghĩa chứ không phải bằng tay); tính lặp lại được (một buổi diễn tập disaster recovery hay một lần failover là chạy một script hay một playbook, không phải một bài kiểm tra trí nhớ); và khả năng kiểm toán (thay đổi làm shared_buffers đổi trên mọi replica là một commit có tác giả, có thời gian, có diff, không phải một dòng trong shell history đã trôi mất khỏi ba server từ lâu). Không có gì trong số này là tư tưởng DevOps xa lạ được nhập khẩu vào thế giới database cho vui — đây là câu trả lời trực tiếp, thực dụng cho hai kiểu thất bại gây hại nhiều nhất cho DBA: cấu hình âm thầm lệch nhau giữa primary và replica từ sáu tháng trước mà không ai nhận ra cho đến khi một lần failover phơi bày sự khác biệt đó, và quy trình khôi phục đúng trong đầu ai đó nhưng sai trên giấy (hoặc không có ở đâu cả) đúng cái lần nó thực sự phải chạy.

Bài này bàn về automation và IaC cụ thể dưới góc nhìn của một PostgreSQL DBA: bắt đầu từ đâu (script thuần túy, trước khi với tới một công cụ configuration management đầy đủ), configuration management tool áp dụng vào PostgreSQL cụ thể như thế nào, một Ansible playbook thật để provision và cấu hình một PostgreSQL server, năng lực hạ tầng rộng hơn mà một DBA hiện đại cần ngoài SQL, và cách tự động hóa các tác vụ vận hành lặp lại — kiểm tra backup, kiểm thử failover, tạo partition — chính là những việc mà một con người mệt mỏi hoặc vội vàng dễ làm sai nhất.

Kiến thức nền tảng

Tự động hóa phần nhàm chán, lặp lại trước tiên

Rất dễ nghe đến “automation” rồi nhảy thẳng tới một nền tảng configuration management đầy đủ, một lớp orchestration, và một pipeline. Trên thực tế, phần lớn automation có giá trị cao nhất mà một PostgreSQL DBA viết ra nhỏ hơn thế rất nhiều: một shell script bọc quanh một lệnh mà lẽ ra bạn phải gõ tay, hoặc một pipeline awk/sed/grep biến cả một bức tường log thành đúng một dòng cần quan tâm. Việc này đáng làm trước, trước khi với tới Ansible hay Terraform, vì một lý do đơn giản — một script mười lăm dòng mà bạn hiểu hoàn toàn, chạy từ cron hay terminal, có tỷ lệ chi phí/lợi ích tốt hơn nhiều cho một tác vụ bạn làm vài lần một tuần so với việc dựng cả một nền tảng configuration management chỉ để làm việc tương tự. Dành công cụ nặng hơn cho việc chúng thực sự giỏi: giữ nhiều server ở một trạng thái nhất quán, đã khai báo. Dùng script cho việc script giỏi: biến một hành động thủ công, dễ sai, lặp lại thành một lệnh một dòng.

File log của PostgreSQL và output của các lệnh chẩn đoán (psql, pg_stat_activity, pg_stat_statements) đều là văn bản, và văn bản chính là thứ grep, awk, sed sinh ra để xử lý. Xem ./16-monitoring-logging-and-diagnostics.md để biết các công cụ và view chẩn đoán mà các script này bọc quanh — bài này bàn về việc biến một câu truy vấn chẩn đoán một lần bạn vẫn gõ tay vào psql thành thứ chạy tự động và cảnh báo cho bạn.

#!/usr/bin/env bash
# check_slow_queries.sh — grep the PostgreSQL log for statements over a threshold,
# extract duration and query text with awk, and alert if any exceed a hard limit.
set -euo pipefail

LOG_FILE="/var/log/postgresql/postgresql-16-main.log"
THRESHOLD_MS=5000   # alert on anything slower than 5 seconds
SINCE_MINUTES=15

# log_min_duration_statement must be set (e.g. to 1000) in postgresql.conf
# for "duration: ... ms  statement: ..." lines to appear at all.
recent_slow=$(
  find "$LOG_FILE" -newermt "-${SINCE_MINUTES} minutes" -printf '' 2>/dev/null || true
  grep --text 'duration:' "$LOG_FILE" \
    | awk -v threshold="$THRESHOLD_MS" '
        {
          for (i = 1; i <= NF; i++) {
            if ($i == "duration:") {
              dur = $(i + 1) + 0     # milliseconds, as a number
              if (dur >= threshold) print
            }
          }
        }'
)

if [[ -n "$recent_slow" ]]; then
  count=$(echo "$recent_slow" | wc -l)
  echo "ALERT: ${count} statement(s) over ${THRESHOLD_MS}ms in the last check:" >&2
  echo "$recent_slow" | tail -n 5 >&2
  # Wire this into your alerting channel instead of just stderr:
  # curl -s -X POST "$SLACK_WEBHOOK_URL" -d "{\"text\": \"${count} slow queries detected\"}"
  exit 1
fi

echo "OK: no statements over ${THRESHOLD_MS}ms"

Đây là điểm khởi đầu thực dụng: một script mà ai trong nhóm cũng đọc hết từ trên xuống dưới trong ba mươi giây, làm đúng một việc, mà bạn có thể chạy tay khi đang debug hoặc thả vào cron hay một systemd timer một khi đã tin tưởng nó. Cùng một mẫu hình đó — grep để tìm dòng cần quan tâm, awk để rút trường ra khỏi chúng, sed để định dạng lại — bao phủ một phần rất lớn công việc phân loại log DBA thường ngày: đếm số dòng FATAL/ERROR mỗi giờ, trích thời gian checkpoint (grep 'checkpoint complete'), hay theo dõi could not receive data from client khi kết nối bị reset. Chỉ nên với tới configuration management khi bài toán trở thành “giữ N server ở trạng thái này,” chứ không phải trước đó — một script chỉ mình bạn chạy, trên một máy, để trả lời một câu hỏi thì không cần control node, inventory, hay một phương ngữ YAML nào cả.

Configuration management cho PostgreSQL

Một khi cùng một tác vụ cần diễn ra nhất quán trên cả một hạm đội — cài PostgreSQL giống hệt nhau trên mọi node, giữ postgresql.conf và pg_hba.conf đồng bộ giữa primary và replica, đảm bảo một DR node vừa dựng lại y hệt từng bit với node nó thay thế — script thuần túy không còn là công cụ phù hợp nữa, và configuration management (CM) tiếp quản. ../devops/en/13-configuration-management.md đã bàn sâu về cơ chế chung của Ansible, Chef, Puppet, Salt — mô hình push vs. pull, idempotency, bức tranh công cụ — và đó là nơi nên đọc để hiểu các tool này hoạt động thế nào nói chung. Phần này chỉ tập trung hẹp vào những gì thay đổi khi thứ được cấu hình là một PostgreSQL server thay vì một web tier bình thường.

Áp dụng vào PostgreSQL, một CM tool thường đảm nhận bốn trách nhiệm:

• Cài đúng phiên bản PostgreSQL một cách nhất quán. Ghim đúng major và minor version (qua repo PGDG apt/yum, một module package manager, hay một image tag container) để “đang chạy phiên bản nào” không bao giờ là câu đoán mò trên từng server, và để một lần nâng minor version là một thay đổi đã review trên một biến, chứ không phải một lệnh apt upgrade ai đó chạy tay trên một máy rồi quên chạy trên các máy còn lại.
• Template hóa postgresql.conf và pg_hba.conf từ template có version control, thay vì sửa tay. Một postgresql.conf sửa tay trên production primary trở thành một “snowflake” ngay khoảnh khắc ai đó SSH vào và đổi shared_buffers mà không ghi lại lý do; một template Jinja2 (hoặc ERB, với Chef/Puppet) được điều khiển bằng biến nghĩa là cùng một nguồn sự thật sinh ra cấu hình của mọi node, và git history của template đó chính là lịch sử thay đổi của setting.
• Chủ động giữ cấu hình primary, replica, DR đồng bộ. Một số setting bắt buộc phải khác nhau giữa các role (hot_standby, primary_conninfo chỉ có ý nghĩa trên standby), và một số bắt buộc phải giống nhau (max_connections, wal_level, và các setting khác mà một physical replica kế thừa từ primary phải khớp, nếu không replication sẽ hỏng). Một CM tool cho phép bạn diễn đạt rõ ràng “những biến này dùng chung cho cả cụm, những biến này theo từng role,” thay vì trông chờ ai đó nhớ được setting nào được phép lệch.
• Quản lý bề mặt vận hành xung quanh chính database: package của OS, tham số kernel liên quan đến PostgreSQL (vm.overcommit_memory, vm.swappiness, transparent huge pages), user OS postgres, log rotation, tùy chọn mount filesystem, và unit systemd hay init script khởi động server — không cái nào là SQL cả, nhưng tất cả đều thuộc về câu hỏi “server này có thực sự chạy PostgreSQL đúng cách hay không.”

Bức tranh công cụ tự nó không thay đổi vì là PostgreSQL: Ansible vẫn là lựa chọn phổ biến nhất cho automation do DBA viết vì nó agentless (không có tiến trình agent thường trực cạnh tranh tài nguyên với PostgreSQL trên host database) và tập module của nó (postgresql_db, postgresql_user, postgresql_privs, postgresql_query trong collection community.postgresql) bao phủ trực tiếp các object cấp database, không chỉ trạng thái OS. Puppet và Chef xuất hiện nhiều hơn ở những nơi đã chuẩn hóa dùng chúng cho phần còn lại của hạm đội và chỉ đơn giản mở rộng cùng mô hình pull-based, hội tụ liên tục đó sang tầng database.

Một Ansible playbook thật cho PostgreSQL

Playbook dưới đây cài PostgreSQL từ repo PGDG, template hóa postgresql.conf từ biến (nên cùng một role sinh ra một cấu hình cỡ lớn cho primary và một cấu hình nhỏ hơn cho replica/dev chỉ bằng cách đổi biến), và đảm bảo service đang chạy và được enable — cùng mô hình “mô tả đích đến, không phải hành trình” đã nói ở ../devops/en/13-configuration-management.md, áp dụng vào một database thay vì một web tier.

# postgresql.yml — provision and configure a PostgreSQL server
- name: Configure PostgreSQL server
  hosts: postgresql_servers
  become: true
  vars:
    pg_version: "16"
    pg_data_dir: "/var/lib/postgresql/{{ pg_version }}/main"
    pg_config_dir: "/etc/postgresql/{{ pg_version }}/main"
    pg_listen_addresses: "*"
    pg_max_connections: 200
    pg_shared_buffers: "4GB"
    pg_effective_cache_size: "12GB"
    pg_wal_level: "replica"
    pg_hba_rules:
      - { type: "host", database: "all", user: "app_user", address: "10.0.1.0/24", method: "scram-sha-256" }
      - { type: "host", database: "replication", user: "replicator", address: "10.0.2.0/24", method: "scram-sha-256" }

  tasks:
    - name: Add PGDG APT signing key
      ansible.builtin.apt_key:
        url: https://www.postgresql.org/media/keys/ACCC4CF8.asc
        state: present

    - name: Add PGDG APT repository
      ansible.builtin.apt_repository:
        repo: "deb http://apt.postgresql.org/pub/repos/apt {{ ansible_distribution_release }}-pgdg main"
        state: present
        filename: pgdg

    - name: Install PostgreSQL {{ pg_version }} and client tools
      ansible.builtin.apt:
        name:
          - "postgresql-{{ pg_version }}"
          - "postgresql-client-{{ pg_version }}"
          - "python3-psycopg2"   # required by Ansible's postgresql_* modules
        state: present
        update_cache: true

    - name: Template postgresql.conf from version-controlled source
      ansible.builtin.template:
        src: templates/postgresql.conf.j2
        dest: "{{ pg_config_dir }}/postgresql.conf"
        owner: postgres
        group: postgres
        mode: "0644"
      notify: Reload PostgreSQL

    - name: Template pg_hba.conf from version-controlled source
      ansible.builtin.template:
        src: templates/pg_hba.conf.j2
        dest: "{{ pg_config_dir }}/pg_hba.conf"
        owner: postgres
        group: postgres
        mode: "0640"
      notify: Reload PostgreSQL

    - name: Ensure PostgreSQL is running and enabled on boot
      ansible.builtin.service:
        name: "postgresql@{{ pg_version }}-main"
        state: started
        enabled: true

    - name: Wait for PostgreSQL to accept connections
      ansible.builtin.wait_for:
        port: 5432
        host: "{{ ansible_default_ipv4.address }}"
        timeout: 30

  handlers:
    - name: Reload PostgreSQL
      ansible.builtin.service:
        name: "postgresql@{{ pg_version }}-main"
        state: reloaded

Template Jinja2 mà nó render ra — điểm quan trọng là mọi giá trị hợp lý khác nhau giữa một primary mạnh và một replica nhỏ đều là biến, không phải con số gõ tay:

{# templates/postgresql.conf.j2 #}
listen_addresses = '{{ pg_listen_addresses }}'
port = 5432
max_connections = {{ pg_max_connections }}

shared_buffers = '{{ pg_shared_buffers }}'
effective_cache_size = '{{ pg_effective_cache_size }}'

wal_level = '{{ pg_wal_level }}'
max_wal_senders = 10
wal_keep_size = '1GB'
hot_standby = on

log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_min_duration_statement = 1000   # ms — feeds the check_slow_queries.sh script above
log_line_prefix = '%m [%p] %q%u@%d '

Và pg_hba.conf.j2, được sinh ra từ danh sách pg_hba_rules thay vì duy trì thủ công, nên thêm một application hay replica mới nghĩa là thêm một entry vào danh sách biến, không phải nhớ chính xác cách căn cột của file:

{# templates/pg_hba.conf.j2 #}
# TYPE  DATABASE  USER  ADDRESS  METHOD
local   all       postgres              peer
{% for rule in pg_hba_rules %}
{{ rule.type }}  {{ rule.database }}  {{ rule.user }}  {{ rule.address }}  {{ rule.method }}
{% endfor %}

Chạy nó bằng ansible-playbook -i inventory/prod postgresql.yml --check --diff trước để xem trước chính xác điều gì sẽ thay đổi trên từng server — một diff của postgresql.conf và pg_hba.conf dễ review đúng hơn nhiều so với việc tin rằng một lần sửa tay đã được áp dụng giống hệt trên ba replica — rồi mới chạy không có --check để apply thật. Vì module template chỉ báo changed khi file được render thực sự khác với những gì đang có trên đĩa, một lần chạy thứ hai trên server đã hội tụ sẽ báo không có thay đổi nào, và handler notify: Reload PostgreSQL chỉ kích hoạt — reload, không bao giờ restart, nên các kết nối hiện có vẫn sống sót — khi file config thực sự đổi.

Khái niệm chính

Năng lực hạ tầng: bộ kỹ năng DBA ngoài SQL

Một PostgreSQL DBA hai mươi năm trước hoàn toàn có thể dành cả sự nghiệp đắm chìm trong SQL, query plan, và công cụ backup mà không bao giờ đụng đến một firewall rule. Điều đó không còn đúng nữa. Database bây giờ chạy trên hạ tầng mà DBA được kỳ vọng tự lý giải trực tiếp — một VM cloud hay một pod Kubernetes, không phải một máy chuyên dụng ai đó khác đã rack và đấu cáp sẵn — và ba kỹ năng liên quan đã trở thành một phần của công việc chứ không còn là vấn đề của người khác:

• Quản trị Linux — đủ để đọc iostat, vmstat, dmesg khi PostgreSQL chậm vì một lý do không nằm trong pg_stat_activity; đủ để lý giải về tùy chọn mount filesystem (noatime), I/O scheduler, và ulimit ảnh hưởng đến max_connections; đủ để không bó tay khi vấn đề của database hóa ra là vấn đề của host.
• Kiến thức networking cơ bản — đủ để cấu hình firewall rule (ufw, iptables, một security group trên cloud) chỉ mở port 5432 cho các subnet ứng dụng và các peer replication, không bao giờ cho 0.0.0.0/0; đủ để chẩn đoán “connection refused” khác với “connection timed out” như hai loại lỗi khác nhau (cái trước nghĩa là không có gì đang lắng nghe hoặc pg_hba.conf từ chối bạn trước cả TLS; cái sau thường nghĩa là một firewall hay security group đang âm thầm drop gói tin).
• Một ngôn ngữ lập trình đa dụng, thường là Python — cho phần keo dán automation và monitoring không vừa gọn trong một dòng shell hay một module Ansible: một script truy vấn pg_stat_replication trên mọi node của một cụm rồi post một báo cáo lag tổng hợp, một exporter Prometheus tùy chỉnh cho một metric mà không exporter nào có sẵn bao phủ, hay logic orchestration đằng sau một cơ chế kiểm tra failover tự chế. psycopg2/psycopg3 cho giao thức wire của PostgreSQL, cộng với subprocess và argparse trong standard library, bao phủ phần lớn áp đảo những gì một DBA cần viết.

Không điều gì trong số này thay thế kiến thức sâu về SQL và internals của PostgreSQL — nó nằm cạnh kiến thức đó. Cách đóng khung thực tế là một DBA hiện đại gần với một kỹ sư hạ tầng chuyên về PostgreSQL hơn là một chuyên gia SQL thuần túy tình cờ có quyền truy cập server, và các thói quen automation trong bài này chính là nơi bộ kỹ năng rộng hơn đó được áp dụng hàng ngày.

Tự động hóa các tác vụ vận hành thường nhật

Automation có đòn bẩy cao nhất mà một DBA viết ra không hào nhoáng gì — đó là biến các tác vụ lẽ ra phải diễn ra đều đặn, và là thảm họa nếu bị bỏ qua hoặc làm sai, thành thứ chạy mà không cần một con người nhớ để làm nó.

Kiểm tra backup bằng script. ./14-backup-and-recovery.md đã bàn về điều gì thực sự làm nên một backup hợp lệ — một backup chưa ai từng restore chỉ là một giả thuyết, không phải một backup. Tự động hóa việc kiểm tra đó nghĩa là một job theo lịch sẽ restore backup mới nhất vào một instance scratch, chạy pg_verifybackup (với backup lấy bằng pg_basebackup) hoặc kiểm tra hợp lý bằng pg_restore --list (với archive định dạng custom của pg_dump), rồi chạy vài kiểm tra cấp ứng dụng — đếm số dòng trên các bảng quan trọng, timestamp gần nhất trong một audit log — trước khi công nhận backup là tốt:

#!/usr/bin/env bash
# verify_backup.sh — restore last night's base backup into a scratch instance
# and confirm it actually comes up and holds recent data.
set -euo pipefail

BACKUP_DIR="/backups/basebackup/$(date +%F)"
SCRATCH_DATA_DIR="/var/lib/postgresql/16/scratch_verify"
SCRATCH_PORT=5433

rm -rf "$SCRATCH_DATA_DIR"
cp -a "$BACKUP_DIR" "$SCRATCH_DATA_DIR"

pg_verifybackup "$BACKUP_DIR" || { echo "FAIL: pg_verifybackup reported corruption"; exit 1; }

pg_ctl -D "$SCRATCH_DATA_DIR" -o "-p ${SCRATCH_PORT}" -l /tmp/verify_restore.log start
trap 'pg_ctl -D "$SCRATCH_DATA_DIR" stop -m immediate' EXIT

for i in $(seq 1 30); do
  pg_isready -p "$SCRATCH_PORT" && break
  sleep 2
done

row_count=$(psql -p "$SCRATCH_PORT" -d appdb -tAc "SELECT count(*) FROM orders;")
latest_row=$(psql -p "$SCRATCH_PORT" -d appdb -tAc "SELECT max(created_at) FROM orders;")

if [[ "$row_count" -lt 1000 ]]; then
  echo "FAIL: restored orders table has suspiciously few rows ($row_count)"; exit 1
fi

echo "OK: backup restored successfully — ${row_count} rows, latest order at ${latest_row}"

Chạy script này mỗi đêm từ cron hoặc lịch CI, cảnh báo khi exit code khác 0, và câu hỏi “backup của chúng ta có thực sự cứu được chúng ta không” thôi là thứ bạn phải phát hiện ra giữa một sự cố thật.

Kiểm thử failover bằng script. ./13-replication-and-high-availability.md đã bàn về cơ chế failover do Patroni quản lý; góc nhìn automation ở đây là một runbook failover chưa ai thực hiện trong sáu tháng chính là kịch bản hai mươi bước dưới áp lực đã mở đầu bài này. Cách khắc phục là chủ động kích hoạt một lần failover theo lịch, ở staging, và kiểm chứng kết quả bằng chương trình thay vì chờ production tự kiểm tra hộ bạn:

#!/usr/bin/env bash
# test_failover.sh — trigger a scheduled Patroni switchover in staging and
# verify the new primary is accepting writes within an acceptable window.
set -euo pipefail

CLUSTER_NAME="staging-pg-cluster"
OLD_PRIMARY=$(patronictl -c /etc/patroni/patroni.yml list "$CLUSTER_NAME" \
  --format json | jq -r '.[] | select(.Role=="Leader") | .Member')

echo "Triggering switchover away from ${OLD_PRIMARY}..."
patronictl -c /etc/patroni/patroni.yml switchover "$CLUSTER_NAME" \
  --master "$OLD_PRIMARY" --force

start=$(date +%s)
new_primary=""
while [[ -z "$new_primary" ]]; do
  new_primary=$(patronictl -c /etc/patroni/patroni.yml list "$CLUSTER_NAME" \
    --format json | jq -r --arg old "$OLD_PRIMARY" \
    '.[] | select(.Role=="Leader" and .Member != $old) | .Member')
  [[ $(( $(date +%s) - start )) -gt 60 ]] && { echo "FAIL: no new leader after 60s"; exit 1; }
  sleep 2
done
elapsed=$(( $(date +%s) - start ))

psql -h "$new_primary" -d appdb -c "CREATE TABLE IF NOT EXISTS failover_probe(ts timestamptz);" \
  -c "INSERT INTO failover_probe VALUES (now());" \
  || { echo "FAIL: new primary ${new_primary} did not accept writes"; exit 1; }

echo "OK: switchover completed in ${elapsed}s, new primary ${new_primary} accepts writes"
# Alert if elapsed exceeds your RTO target, e.g.:
# [[ "$elapsed" -gt 30 ]] && curl -X POST "$SLACK_WEBHOOK_URL" -d "{\"text\":\"Failover took ${elapsed}s, exceeds RTO\"}"

Lên lịch chạy việc này hàng tháng ở staging (không bao giờ ở production, nơi một lần switchover ngoài kế hoạch là một sự cố thật, không phải một buổi diễn tập) làm được hai việc cùng lúc: chứng minh runbook và cấu hình Patroni thực sự hoạt động, và đo thời gian failover thật so với mục tiêu RTO thay vì một con số giả định.

Tự động tạo partition trước khi cần đến. ./12-partitioning-and-sharding.md đã bàn về lý do một bảng range-partition cần partition kế tiếp tồn tại trước khi dữ liệu của giai đoạn đó bắt đầu đến, và một script theo lịch hoặc extension pg_partman là cách chuẩn để đảm bảo điều đó. Điểm về automation đáng nhắc lại ở đây: đây không phải một cron job “có thì tốt,” mà là ngăn chặn một chế độ lỗi cứng — thiếu một partition tương lai nghĩa là các câu insert hoặc báo lỗi ngay lập tức hoặc âm thầm dồn vào một bucket DEFAULT, và cả hai kết cục đều tệ hơn năm phút cần để lên lịch cho hàm bảo trì của pg_partman hoặc một script nhỏ chạy CREATE TABLE ... PARTITION OF ... trước lịch một tháng.

Infrastructure as code cho tầng database trên cloud

Mọi thứ ở trên giả định DBA kiểm soát OS mà database chạy trên đó. Trong bối cảnh managed database — Amazon RDS, Aurora, Google Cloud SQL, Azure Database for PostgreSQL — không có OS nào để cấu hình cả: nhà cung cấp sở hữu instance, và công việc của DBA chuyển sang khai báo managed instance nên trông như thế nào (engine version, instance class, storage, parameter group, backup retention, vị trí network) dưới dạng code, để việc provision một môi trường mới là một diff đã review thay vì một chuỗi cú click console không ai ghi lại. ../devops/en/12-infrastructure-as-code.md đã bàn sâu về cơ chế chung của Terraform — state, plan/apply, module; phần riêng của PostgreSQL đơn giản là chính database trở thành một resource được khai báo thêm:

# rds_postgres.tf — a Terraform-managed RDS PostgreSQL instance
resource "aws_db_parameter_group" "pg16" {
  name   = "app-pg16-params"
  family = "postgres16"

  parameter {
    name  = "log_min_duration_statement"
    value = "1000"
  }
  parameter {
    name  = "shared_preload_libraries"
    value = "pg_stat_statements"
  }
}

resource "aws_db_instance" "app" {
  identifier     = "app-prod-db"
  engine         = "postgres"
  engine_version = "16.4"
  instance_class = "db.r6g.xlarge"

  allocated_storage     = 200
  max_allocated_storage = 1000   # storage autoscaling ceiling
  storage_encrypted     = true

  db_subnet_group_name   = aws_db_subnet_group.app.name
  vpc_security_group_ids = [aws_security_group.db.id]
  parameter_group_name   = aws_db_parameter_group.pg16.name

  multi_az                    = true
  backup_retention_period      = 14
  performance_insights_enabled = true

  lifecycle {
    prevent_destroy = true   # apply fails rather than dropping the production DB
  }
}

Sự dịch chuyển quan trọng so với chạy PostgreSQL trên một VM do DBA cấu hình bằng Ansible: parameter { name = "log_min_duration_statement" ... } thay cho việc tự template postgresql.conf, multi_az thay cho physical replication do Patroni quản lý, và backup_retention_period thay cho một cron job pg_basebackup — các khái niệm vận hành xuyên suốt bài này (cấu hình nhất quán, replication cho HA, backup retention, failover đã kiểm chứng) không biến mất, dịch vụ managed chỉ chuyển ai là người triển khai cơ chế, còn IaC vẫn giữ phần khai báo của những gì nên tồn tại trong version control dù thế nào đi nữa. prevent_destroy đáng được nhắc riêng: một terraform apply có dòng -/+ (destroy rồi tạo lại) nhắm vào một aws_db_instance production là dòng nguy hiểm nhất mà một DBA từng phải review trong một plan, và lifecycle guard này biến một sự kiện mất dữ liệu ngoài ý muốn thành một lần apply thất bại.

Bảng tham chiếu tác vụ automation

Tác vụ	Tool thường dùng	Vì sao nên tự động hóa
Phân tích log tìm slow query / lỗi	Shell script grep/awk/sed, cron	Tần suất cao (liên tục), soi bằng less/grep tay không scale được quá một sự cố
Cài PostgreSQL + package OS nhất quán	Ansible / Chef / Puppet	Mọi node mới phải khớp hạm đội chính xác; cài tay dễ lệch về package/version
Template hóa postgresql.conf / pg_hba.conf	Ansible (Jinja2) / Puppet / Chef	Rủi ro sai sót thủ công cao (gõ nhầm một setting bộ nhớ, sai CIDR trong một rule HBA); phải giữ giống hệt giữa primary/replica/DR
Kiểm tra backup (restore + kiểm tra số dòng/nội dung)	Shell script + lịch cron/CI	Cực kỳ quan trọng — một backup chưa kiểm chứng chỉ là giả thuyết; thảm họa thật hiếm khi xảy ra nên nếu không tự động thì việc kiểm tra tay sẽ không bao giờ được làm
Kiểm thử failover / switchover	Shell script + patronictl, lên lịch ở staging	Quan trọng và hiếm gặp ở production, nên chỉ có cách diễn tập lặp lại, có kịch bản mới biết runbook có hoạt động không
Tạo partition trước cửa sổ retention	pg_partman hoặc script theo lịch	Tần suất cao (hàng tuần/tháng), chế độ lỗi cứng âm thầm nếu bỏ sót (insert lỗi hoặc dồn vào DEFAULT)
Provision managed database instance (RDS, Cloud SQL)	Terraform / OpenTofu	Nhất quán giữa các môi trường, diff có thể review, prevent_destroy chặn sai sót thủ công thảm khốc
Việc keo dán ad-hoc: exporter tùy chỉnh, báo cáo lag tổng hợp, migration một lần	Python (psycopg2/psycopg3)	Không vừa gọn một dòng shell hay một module CM; cần luồng điều khiển thật, xử lý lỗi, hoặc một API client

Best Practices

Bắt đầu mọi nỗ lực automation ở mức độ phức tạp mà bài toán thực sự cần, không phải mức độ trông ấn tượng. Một script grep/awk ba dòng chạy từ cron là câu trả lời đúng cho “cảnh báo tôi khi một query chậm,” và nhảy thẳng tới một nền tảng CM đầy đủ hay một dịch vụ Python tự chế cho cùng bài toán đó là over-engineering, thêm gánh nặng bảo trì mà không thêm an toàn. Chỉ nâng lên configuration management khi nỗi đau thực sự là tính nhất quán trên hạm đội — cùng một setting trong postgresql.conf cần giống hệt nhau giữa một primary và vài replica, hoặc một DR node cần dựng lại được từ định nghĩa thay vì từ trí nhớ ai đó về cách bản gốc từng được thiết lập.

Coi mọi file cấu hình mà một CM tool quản lý là nguồn sự thật duy nhất, và coi bất kỳ chỉnh sửa tay nào thực hiện trực tiếp trên server là drift cần bắt lấy, không phải điều được dung thứ. Giá trị của việc template hóa postgresql.conf và pg_hba.conf tan biến ngay khoảnh khắc ai đó SSH vào “chỉ lần này thôi” để tăng một setting dưới áp lực mà không đưa thay đổi đó ngược lại vào template — lần chạy CM kế tiếp hoặc âm thầm revert lại sửa chữa của họ (nếu tool tự áp dụng lại vô điều kiện) hoặc sửa chữa đó âm thầm lệch khỏi những gì trong git (nếu không), và dù kiểu nào thì file trên đĩa và file trong version control giờ đã bất đồng về sự thật. Chạy playbook với --check --diff trước khi apply vào production, y hệt cách bạn review một Terraform plan, và coi trọng một diff khác không trên một server không ai định đụng vào — đó là drift đang chỉ cho bạn biết cần nhìn vào đâu.

Dành lập trình thật (Python, phổ biến nhất) cho phần automation mà script và module CM thực sự không diễn đạt gọn gàng được: logic orchestration nhiều bước, các lệnh gọi tới nhiều API cần được đối chiếu với nhau, hay các kiểm tra monitoring có logic rẽ nhánh thật thay vì một ngưỡng pass/fail đơn giản. Viết một script ngay từ đầu, bằng bất kỳ ngôn ngữ nào phù hợp, luôn tốt hơn không tự động hóa một tác vụ lặp lại — mục đích của việc xây dựng năng lực hạ tầng rộng hơn với tư cách một DBA là có sẵn công cụ đúng kích cỡ cho từng tầng của bài toán, chứ không phải mặc định dùng công cụ mạnh nhất theo thói quen.

Cuối cùng, biến chính việc kiểm chứng thành một quá trình theo lịch, không người canh, thay vì một niềm tin. Một backup chưa từng được restore và một runbook failover chưa từng được thực thi, xét về mặt chức năng, đều là code chưa được test — và code chưa được test hỏng đúng lúc bạn không thể chịu đựng nổi, mà với một DBA thường nghĩa là ngay giữa sự cố thật mà automation lẽ ra phải giúp an toàn. Lên lịch cho việc kiểm tra restore backup và diễn tập failover ở staging như đã mô tả biến “chúng tôi nghĩ disaster recovery của mình hoạt động” thành “chúng tôi biết, vì nó đã chạy thành công thứ Ba tuần trước” — và biến một thảm họa thật, khi nó cuối cùng xảy ra, thành việc thực thi một quy trình đã được kiểm chứng thay vì lần thử nghiệm thật đầu tiên của một quy trình chưa từng được chứng minh.

Cuối cùng, giữ cho chính automation có thể quan sát và review được giống như bạn vẫn đòi hỏi với application code: đặt tên task và bước rõ ràng để output của một lần chạy nói cho bạn biết chuyện gì đã xảy ra, cảnh báo khi thất bại thay vì bắt ai đó phải để ý một status page xanh/đỏ, và giữ mọi playbook, script, module Terraform trong version control với cùng kỷ luật review như một schema migration — vì ở quy mô mà automation vận hành (nhiều server, không người canh, theo lịch), một sai sót trong chính automation là một sai sót được nhân bản ở mọi nơi nó chạy, ngay lập tức, đúng là chế độ lỗi mà toàn bộ ngành này tồn tại để ngăn chặn ngay từ đầu.

Tài liệu tham khảo

• roadmap.sh — PostgreSQL DBA Roadmap
• Ansible documentation — community.postgresql collection
• Patroni documentation — switchover and failover
• PostgreSQL Documentation — pg_verifybackup
• pg_partman — automated partition management
• Terraform Registry — aws_db_instance
• Google Cloud SQL — Terraform provider reference
• psycopg — PostgreSQL adapter for Python

Xem thêm: ../devops/en/13-configuration-management.md, ../devops/en/12-infrastructure-as-code.md, ./13-replication-and-high-availability.md, ./14-backup-and-recovery.md, ./16-monitoring-logging-and-diagnostics.md, ./12-partitioning-and-sharding.md.

#!/usr/bin/env bash
# check_slow_queries.sh — grep the PostgreSQL log for statements over a threshold,
# extract duration and query text with awk, and alert if any exceed a hard limit.
set -euo pipefail

LOG_FILE="/var/log/postgresql/postgresql-16-main.log"
THRESHOLD_MS=5000   # alert on anything slower than 5 seconds
SINCE_MINUTES=15

# log_min_duration_statement must be set (e.g. to 1000) in postgresql.conf
# for "duration: ... ms  statement: ..." lines to appear at all.
recent_slow=$(
  find "$LOG_FILE" -newermt "-${SINCE_MINUTES} minutes" -printf '' 2>/dev/null || true
  grep --text 'duration:' "$LOG_FILE" \
    | awk -v threshold="$THRESHOLD_MS" '
        {
          for (i = 1; i <= NF; i++) {
            if ($i == "duration:") {
              dur = $(i + 1) + 0     # milliseconds, as a number
              if (dur >= threshold) print
            }
          }
        }'
)

if [[ -n "$recent_slow" ]]; then
  count=$(echo "$recent_slow" | wc -l)
  echo "ALERT: ${count} statement(s) over ${THRESHOLD_MS}ms in the last check:" >&2
  echo "$recent_slow" | tail -n 5 >&2
  # Wire this into your alerting channel instead of just stderr:
  # curl -s -X POST "$SLACK_WEBHOOK_URL" -d "{\"text\": \"${count} slow queries detected\"}"
  exit 1
fi

echo "OK: no statements over ${THRESHOLD_MS}ms"

# postgresql.yml — provision and configure a PostgreSQL server
- name: Configure PostgreSQL server
  hosts: postgresql_servers
  become: true
  vars:
    pg_version: "16"
    pg_data_dir: "/var/lib/postgresql/{{ pg_version }}/main"
    pg_config_dir: "/etc/postgresql/{{ pg_version }}/main"
    pg_listen_addresses: "*"
    pg_max_connections: 200
    pg_shared_buffers: "4GB"
    pg_effective_cache_size: "12GB"
    pg_wal_level: "replica"
    pg_hba_rules:
      - { type: "host", database: "all", user: "app_user", address: "10.0.1.0/24", method: "scram-sha-256" }
      - { type: "host", database: "replication", user: "replicator", address: "10.0.2.0/24", method: "scram-sha-256" }

  tasks:
    - name: Add PGDG APT signing key
      ansible.builtin.apt_key:
        url: https://www.postgresql.org/media/keys/ACCC4CF8.asc
        state: present

    - name: Add PGDG APT repository
      ansible.builtin.apt_repository:
        repo: "deb http://apt.postgresql.org/pub/repos/apt {{ ansible_distribution_release }}-pgdg main"
        state: present
        filename: pgdg

    - name: Install PostgreSQL {{ pg_version }} and client tools
      ansible.builtin.apt:
        name:
          - "postgresql-{{ pg_version }}"
          - "postgresql-client-{{ pg_version }}"
          - "python3-psycopg2"   # required by Ansible's postgresql_* modules
        state: present
        update_cache: true

    - name: Template postgresql.conf from version-controlled source
      ansible.builtin.template:
        src: templates/postgresql.conf.j2
        dest: "{{ pg_config_dir }}/postgresql.conf"
        owner: postgres
        group: postgres
        mode: "0644"
      notify: Reload PostgreSQL

    - name: Template pg_hba.conf from version-controlled source
      ansible.builtin.template:
        src: templates/pg_hba.conf.j2
        dest: "{{ pg_config_dir }}/pg_hba.conf"
        owner: postgres
        group: postgres
        mode: "0640"
      notify: Reload PostgreSQL

    - name: Ensure PostgreSQL is running and enabled on boot
      ansible.builtin.service:
        name: "postgresql@{{ pg_version }}-main"
        state: started
        enabled: true

    - name: Wait for PostgreSQL to accept connections
      ansible.builtin.wait_for:
        port: 5432
        host: "{{ ansible_default_ipv4.address }}"
        timeout: 30

  handlers:
    - name: Reload PostgreSQL
      ansible.builtin.service:
        name: "postgresql@{{ pg_version }}-main"
        state: reloaded

{# templates/postgresql.conf.j2 #}
listen_addresses = '{{ pg_listen_addresses }}'
port = 5432
max_connections = {{ pg_max_connections }}

shared_buffers = '{{ pg_shared_buffers }}'
effective_cache_size = '{{ pg_effective_cache_size }}'

wal_level = '{{ pg_wal_level }}'
max_wal_senders = 10
wal_keep_size = '1GB'
hot_standby = on

log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_min_duration_statement = 1000   # ms — feeds the check_slow_queries.sh script above
log_line_prefix = '%m [%p] %q%u@%d '

{# templates/pg_hba.conf.j2 #}
# TYPE  DATABASE  USER  ADDRESS  METHOD
local   all       postgres              peer
{% for rule in pg_hba_rules %}
{{ rule.type }}  {{ rule.database }}  {{ rule.user }}  {{ rule.address }}  {{ rule.method }}
{% endfor %}

#!/usr/bin/env bash
# verify_backup.sh — restore last night's base backup into a scratch instance
# and confirm it actually comes up and holds recent data.
set -euo pipefail

BACKUP_DIR="/backups/basebackup/$(date +%F)"
SCRATCH_DATA_DIR="/var/lib/postgresql/16/scratch_verify"
SCRATCH_PORT=5433

rm -rf "$SCRATCH_DATA_DIR"
cp -a "$BACKUP_DIR" "$SCRATCH_DATA_DIR"

pg_verifybackup "$BACKUP_DIR" || { echo "FAIL: pg_verifybackup reported corruption"; exit 1; }

pg_ctl -D "$SCRATCH_DATA_DIR" -o "-p ${SCRATCH_PORT}" -l /tmp/verify_restore.log start
trap 'pg_ctl -D "$SCRATCH_DATA_DIR" stop -m immediate' EXIT

for i in $(seq 1 30); do
  pg_isready -p "$SCRATCH_PORT" && break
  sleep 2
done

row_count=$(psql -p "$SCRATCH_PORT" -d appdb -tAc "SELECT count(*) FROM orders;")
latest_row=$(psql -p "$SCRATCH_PORT" -d appdb -tAc "SELECT max(created_at) FROM orders;")

if [[ "$row_count" -lt 1000 ]]; then
  echo "FAIL: restored orders table has suspiciously few rows ($row_count)"; exit 1
fi

echo "OK: backup restored successfully — ${row_count} rows, latest order at ${latest_row}"

#!/usr/bin/env bash
# test_failover.sh — trigger a scheduled Patroni switchover in staging and
# verify the new primary is accepting writes within an acceptable window.
set -euo pipefail

CLUSTER_NAME="staging-pg-cluster"
OLD_PRIMARY=$(patronictl -c /etc/patroni/patroni.yml list "$CLUSTER_NAME" \
  --format json | jq -r '.[] | select(.Role=="Leader") | .Member')

echo "Triggering switchover away from ${OLD_PRIMARY}..."
patronictl -c /etc/patroni/patroni.yml switchover "$CLUSTER_NAME" \
  --master "$OLD_PRIMARY" --force

start=$(date +%s)
new_primary=""
while [[ -z "$new_primary" ]]; do
  new_primary=$(patronictl -c /etc/patroni/patroni.yml list "$CLUSTER_NAME" \
    --format json | jq -r --arg old "$OLD_PRIMARY" \
    '.[] | select(.Role=="Leader" and .Member != $old) | .Member')
  [[ $(( $(date +%s) - start )) -gt 60 ]] && { echo "FAIL: no new leader after 60s"; exit 1; }
  sleep 2
done
elapsed=$(( $(date +%s) - start ))

psql -h "$new_primary" -d appdb -c "CREATE TABLE IF NOT EXISTS failover_probe(ts timestamptz);" \
  -c "INSERT INTO failover_probe VALUES (now());" \
  || { echo "FAIL: new primary ${new_primary} did not accept writes"; exit 1; }

echo "OK: switchover completed in ${elapsed}s, new primary ${new_primary} accepts writes"
# Alert if elapsed exceeds your RTO target, e.g.:
# [[ "$elapsed" -gt 30 ]] && curl -X POST "$SLACK_WEBHOOK_URL" -d "{\"text\":\"Failover took ${elapsed}s, exceeds RTO\"}"

# rds_postgres.tf — a Terraform-managed RDS PostgreSQL instance
resource "aws_db_parameter_group" "pg16" {
  name   = "app-pg16-params"
  family = "postgres16"

  parameter {
    name  = "log_min_duration_statement"
    value = "1000"
  }
  parameter {
    name  = "shared_preload_libraries"
    value = "pg_stat_statements"
  }
}

resource "aws_db_instance" "app" {
  identifier     = "app-prod-db"
  engine         = "postgres"
  engine_version = "16.4"
  instance_class = "db.r6g.xlarge"

  allocated_storage     = 200
  max_allocated_storage = 1000   # storage autoscaling ceiling
  storage_encrypted     = true

  db_subnet_group_name   = aws_db_subnet_group.app.name
  vpc_security_group_ids = [aws_security_group.db.id]
  parameter_group_name   = aws_db_parameter_group.pg16.name

  multi_az                    = true
  backup_retention_period      = 14
  performance_insights_enabled = true

  lifecycle {
    prevent_destroy = true   # apply fails rather than dropping the production DB
  }
}