Administrator yang baik tidak menunggu pengguna menelepon untuk tahu ada masalah. Monitoring mengubah kesehatan server dari perasaan menjadi angka: berapa koneksi yang terpakai, seberapa sering cache berhasil, berapa banyak baris mati menunggu VACUUM. Chapter penutup Section Administrasi PostgreSQL ini menyusun kerangka monitoring dari bahan yang sudah tersedia di server — view pg_stat_* — lalu melangkah ke arsitektur monitoring industri dengan Prometheus dan Grafana, dan menutup dengan filosofi alerting yang membedakan sinyal dari kebisingan.

Apa yang Perlu Dipantau #

Daftar indikator berikut mencakup gejala yang paling sering mendahului insiden nyata:

IndikatorSumberGejala masalah
Koneksi aktif vs max_connectionspg_stat_activityKoneksi menumpuk, aplikasi ditolak
Rasio cache hitpg_stat_databasePembacaan disk meningkat, query melambat
Transaksi commit vs rollbackpg_stat_databaseRollback membengkak, aplikasi gagal berulang
Deadlockpg_stat_databaseKontensi kunci antar sesi
Baris mati per tabelpg_stat_user_tablesAutovacuum tertinggal, bloat tumbuh
File sementarapg_stat_databaseSortir/spill ke disk, work_mem kurang
Disk bebas pada data_directorysistem operasiServer berhenti total saat disk penuh
Lag replikasipg_stat_replicationReplica basi, failover berisiko (Section Replication)

Satu prinsip mengikat tabel ini: semua angka bergerak naik-turun sepanjang waktu, sehingga nilainya baru bermakna sebagai deret waktu — rasio cache 98% hari ini berbeda maknanya jika kemarin 99,5%. Itulah mengapa monitoring yang serius selalu menyimpan riwayat, bukan sekadar menampilkan nilai terkini.

Tabel Beban untuk Angka yang Hidup #

View statistik paling jujur ketika ada beban nyata untuk diukur. File berikut membuat tabel penjualan 200.000 baris dengan autovacuum sengaja dimatikan agar angkanya stabil untuk pembelajaran, lalu menjalankan lima kali pindai penuh:

sql buat-beban.sql 
-- Tabel beban: 200.000 baris penjualan (autovacuum dimatikan
-- agar angka pembelajaran stabil).
CREATE TABLE penjualan_harian (
  id integer PRIMARY KEY,
  nilai integer
) WITH (autovacuum_enabled = false);

INSERT INTO penjualan_harian (id, nilai)
SELECT g, (g * 13) % 997
FROM generate_series(1, 200000) AS g;

-- Lima kali pindai penuh: beban baca yang terkendali.
SELECT count(*) AS total_baris FROM penjualan_harian;
SELECT count(*) AS total_baris FROM penjualan_harian;
SELECT count(*) AS total_baris FROM penjualan_harian;
SELECT count(*) AS total_baris FROM penjualan_harian;
SELECT count(*) AS total_baris FROM penjualan_harian;

Output yang Dihasilkan:

text 
CREATE TABLE
INSERT 0 200000
 total_baris 
-------------
      200000
(1 row)

 total_baris 
-------------
      200000
(1 row)

 total_baris 
-------------
      200000
(1 row)

 total_baris 
-------------
      200000
(1 row)

 total_baris 
-------------
      200000
(1 row)

Lima hasil count(*) yang identik itu bukan sekadar isi tabel: setiap pindai menyentuh 200.000 baris dan halaman-halaman datanya — bekan yang akan terbaca di counter pg_stat_database pada bagian berikutnya.

Membaca Kesehatan dari pg_stat_database #

View pg_stat_database mengumpulkan counter per database: jumlah backend aktif, transaksi yang berkomit dan mundur, blok yang dibaca dari cache (blks_hit) versus dari disk (blks_read), deadlock, dan file sementara. Dua file berikut membaca angka-angka tersebut setelah beban di atas berjalan:

sql tur-stat-database.sql 
-- Kolom inti pg_stat_database untuk database yang sedang aktif.
SELECT datname,
       numbackends,
       xact_commit,
       xact_rollback,
       blks_hit,
       blks_read,
       deadlocks,
       conflicts,
       temp_files
FROM pg_stat_database
WHERE datname = current_database();

Output yang Dihasilkan:

text 
      datname       | numbackends | xact_commit | xact_rollback | blks_hit | blks_read | deadlocks | conflicts | temp_files 
--------------------+-------------+-------------+---------------+----------+-----------+-----------+-----------+------------
 pgtutor_31381aacd4 |           1 |           9 |             0 |   558381 |         1 |         0 |         0 |          1
(1 row)

Mari kita bedah kolom-kolomnya satu per satu:

  1. numbackends: jumlah koneksi yang sedang menempel pada database ini — pada sesi latihan kita angkanya satu, yaitu koneksi psql yang menjalankan file ini sendiri.
  2. xact_commit dan xact_rollback: hitungan transaksi yang berhasil dan yang mundur. Perhatikan nilainya kecil — setiap perintah dalam file berjalan sebagai transaksi autocommit tersendiri, dan counter hanya menghitung sejak database ini lahir.
  3. blks_hit dan blks_read: pasangan penentu rasio cache. Setelah lima pindai, mayoritas halaman sudah tinggal di shared_buffers, sehingga blks_hit jauh melampaui blks_read.
  4. deadlocks, conflicts, temp_files: tiga indikator masalah yang sehat ketika nol. temp_files membengkak berarti query sortir lebih besar dari work_mem dan tumpah ke disk.

Rasio cache hit adalah turunan paling sering dipakai dari pasangan tersebut:

sql rasio-cache.sql 
-- Counter yang sama setelah beban: angka sudah menumpuk sejak
-- database ini dibuat.
SELECT datname,
       numbackends,
       xact_commit,
       xact_rollback,
       blks_hit,
       blks_read,
       deadlocks,
       conflicts,
       temp_files
FROM pg_stat_database
WHERE datname = current_database();

-- Rasio cache hit: seberapa sering halaman data ditemukan
-- di memori (shared_buffers) alih-alih dibaca dari disk.
SELECT round(100.0 * blks_hit
             / nullif(blks_hit + blks_read, 0), 1) AS rasio_cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database();

Output yang Dihasilkan:

text 
      datname       | numbackends | xact_commit | xact_rollback | blks_hit | blks_read | deadlocks | conflicts | temp_files 
--------------------+-------------+-------------+---------------+----------+-----------+-----------+-----------+------------
 pgtutor_31381aacd4 |           1 |          11 |             0 |   558633 |         1 |         0 |         0 |          1
(1 row)

 rasio_cache_hit_pct 
---------------------
               100.0
(1 row)

Angka 100.0% pada output adalah hasil pindai berulang: pindai pertama mengisi cache, pindai kedua sampai kelima menemukan semuanya di memori. Di produksi, rasio sehat umumnya berada di atas 99%; angka yang merosot ke 95% pada beban yang sama adalah sinyal shared_buffers kurang atau pola baca berubah. nullif(..., 0) menjaga query tetap aman saat counter masih kosong di database baru.

Skrip Monitor Kilat #

Untuk server kecil yang belum punya tumpukan monitoring, satu skrip shell sudah menjawab empat pertanyaan terpenting. Skrip berikut sengaja hanya memakai psql dan alat bawaan, dan mencakup semua indikatornya ke database yang sedang dihubungi:

bash monitor-sederhana.sh 
#!/bin/bash
# Monitor kilat: satu skrip, empat angka kunci database aktif.
# Semua query sengaja dicakup ke database ini (current_database())
# agar hasilnya tidak tercampur aktivitas database lain.
set -euo pipefail

echo "== Server menerima koneksi? =="
pg_isready -h "$PGHOST" -p "$PGPORT"

echo "== Koneksi klien database ini / batas server =="
psql -X -At -c "SELECT (SELECT count(*)
                        FROM pg_stat_activity
                        WHERE datname = current_database()
                          AND backend_type = 'client backend')
                      || '/' || current_setting('max_connections');"

echo "== Rasio cache hit database ini =="
psql -X -At -c "SELECT round(100.0 * blks_hit
                             / nullif(blks_hit + blks_read, 0), 1) || '%'
                FROM pg_stat_database
                WHERE datname = current_database();"

echo "== Baris mati tabel penjualan_harian =="
psql -X -At -c "SELECT n_dead_tup
                FROM pg_stat_user_tables
                WHERE relname = 'penjualan_harian';"

Output yang Dihasilkan:

text 
== Server menerima koneksi? ==
/home/z/my-project/pgrun:54329 - accepting connections
== Koneksi klien database ini / batas server ==
1/100
== Rasio cache hit database ini ==
100.0%
== Baris mati tabel penjualan_harian ==
0

Empat keluaran skrip ini menjawab empat pertanyaan berurutan: server hidup (pg_isready), kapasitas koneksi belum kritis (perbandingan klien aktif terhadap max_connections), cache bekerja (rasio hit), dan VACUUM tidak tertinggal (baris mati). Pasang di cron tiap lima menit, arahkan outputnya ke file, dan kita sudah punya deret waktu paling sederhana yang bisa dibaca grep.

Arsitektur Monitoring Industri #

Ketika server lebih dari satu dan riwayat penting, skrip cron tidak lagi memadai. Tumpukan standar industri terdiri dari tiga lapisan:

  1. postgres_exporter berjalan di dekat server PostgreSQL dan menerjemahkan query pg_stat_* menjadi format metrik HTTP — daftar metrik bawaannya mencakup hampir seluruh tabel indikator di awal chapter ini.
  2. Prometheus mengambil (scrape) metrik tersebut setiap 15–60 detik dan menyimpannya sebagai deret waktu, lengkap dengan bahasa kueri (PromQL) untuk menghitung rasio dan laju perubahan.
  3. Grafana mengubah deret waktu menjadi dasbor; Alertmanager mengevaluasi aturan seperti “rasio cache hit 5 menit terakhir < 97%” dan mengirim notifikasi ke tim.

Keunggulan arsitektur ini bukan teknologinya, melainkan disiplinnya: setiap grafik dibangun dari query eksplisit yang bisa direview, dan setiap alert tercatat sebagai aturan di repositori — bukan intuisi administrator.

Filosofi Alerting #

Alert yang baik memiliki tiga sifat: menggambarkan gejala yang pengguna rasakan (query lambat, koneksi ditolak — bukan sekadar “CPU tinggi”), bisa ditindak (setiap alert punya runbook: langkah diagnosis yang jelas), dan jarang salah bunyi (alarm yang meraung tiap hari mengajarkan tim untuk mengabaikannya). Praktik umumnya membedakan dua tingkat: warning untuk tren yang meroset (disk tinggal 20%, rasio cache menurun) yang cukup ditangguhkan ke jam kerja, dan critical untuk kondisi yang memutus layanan (server tidak merespons, disk penuh) yang membangunkan orang.

Formulasi alert yang sehat juga memakai jendela waktu, bukan nilai sesaat: rate(rasio_cache_5m) < 0.97 selama 10 menit lebih bermakna daripada satu sampel buruk. Untuk PostgreSQL sendiri, kandidat critical paling awal biasanya tiga: koneksi menyentuh max_connections, disk data_directory hampir penuh, dan pg_isready gagal berulang.

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

buat-beban.sql

Unduh berkas
sql 
-- Tabel beban: 200.000 baris penjualan (autovacuum dimatikan
-- agar angka pembelajaran stabil).
CREATE TABLE penjualan_harian (
  id integer PRIMARY KEY,
  nilai integer
) WITH (autovacuum_enabled = false);

INSERT INTO penjualan_harian (id, nilai)
SELECT g, (g * 13) % 997
FROM generate_series(1, 200000) AS g;

-- Lima kali pindai penuh: beban baca yang terkendali.
SELECT count(*) AS total_baris FROM penjualan_harian;
SELECT count(*) AS total_baris FROM penjualan_harian;
SELECT count(*) AS total_baris FROM penjualan_harian;
SELECT count(*) AS total_baris FROM penjualan_harian;
SELECT count(*) AS total_baris FROM penjualan_harian;

monitor-sederhana.sh

Unduh berkas
bash 
#!/bin/bash
# Monitor kilat: satu skrip, empat angka kunci database aktif.
# Semua query sengaja dicakup ke database ini (current_database())
# agar hasilnya tidak tercampur aktivitas database lain.
set -euo pipefail

echo "== Server menerima koneksi? =="
pg_isready -h "$PGHOST" -p "$PGPORT"

echo "== Koneksi klien database ini / batas server =="
psql -X -At -c "SELECT (SELECT count(*)
                        FROM pg_stat_activity
                        WHERE datname = current_database()
                          AND backend_type = 'client backend')
                      || '/' || current_setting('max_connections');"

echo "== Rasio cache hit database ini =="
psql -X -At -c "SELECT round(100.0 * blks_hit
                             / nullif(blks_hit + blks_read, 0), 1) || '%'
                FROM pg_stat_database
                WHERE datname = current_database();"

echo "== Baris mati tabel penjualan_harian =="
psql -X -At -c "SELECT n_dead_tup
                FROM pg_stat_user_tables
                WHERE relname = 'penjualan_harian';"

rasio-cache.sql

Unduh berkas
sql 
-- Counter yang sama setelah beban: angka sudah menumpuk sejak
-- database ini dibuat.
SELECT datname,
       numbackends,
       xact_commit,
       xact_rollback,
       blks_hit,
       blks_read,
       deadlocks,
       conflicts,
       temp_files
FROM pg_stat_database
WHERE datname = current_database();

-- Rasio cache hit: seberapa sering halaman data ditemukan
-- di memori (shared_buffers) alih-alih dibaca dari disk.
SELECT round(100.0 * blks_hit
             / nullif(blks_hit + blks_read, 0), 1) AS rasio_cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database();

tur-stat-database.sql

Unduh berkas
sql 
-- Kolom inti pg_stat_database untuk database yang sedang aktif.
SELECT datname,
       numbackends,
       xact_commit,
       xact_rollback,
       blks_hit,
       blks_read,
       deadlocks,
       conflicts,
       temp_files
FROM pg_stat_database
WHERE datname = current_database();