Troubleshooting PostgreSQL

Pukul dua pagi, aplikasi produksi berhenti merespons — dan semua mata tertuju ke database. Momen seperti ini tidak menyisakan waktu untuk membaca dokumentasi dari awal; yang dibutuhkan adalah urutan diagnosis yang sudah tersimpan di kepala atau di runbook: dari gejala ke penyebab, dengan query pertama yang selalu sama untuk tiap gejala. Chapter penutup section ini menyusun kotak P3K PostgreSQL: triase koneksi, pemburuan query lambat dan lock, pengukuran ruang disk, penambangan log server, serta buku panduan darurat yang memetakan gejala → query pertama → penyebab paling mungkin. Semua query diagnosisnya dijalankan nyata di server latihan.

Pola Pikir Diagnosis: Ukur Dulu, Baru Bertindak #

Insting pertama saat server bermasalah adalah me-restart-nya. Insting itu harus dilawan. Restart menghapus tiga barang bukti sekaligus: isi pg_stat_activity (query mana yang sedang berjalan), status lock yang saling menahan, dan konteks kegagalan di memori. Setelah server menyala kembali, Anda menghadapi server yang sehat-sehat saja — sementara penyebabnya bebas bersembunyi sampai kejadian berikutnya.

Urutan yang benar untuk setiap gejala: ukur → identifikasi → putuskan. Jalankan query diagnosis yang sesuai gejala, baca hasilnya, baru tentukan tindakan — yang mungkin memang restart, tetapi restart sebagai keputusan berdasarkan data, bukan sebagai refleks. Untuk itu pula semua contoh chapter ini mengambil jalur “tunjukkan query diagnosisnya” alih-alih mensimulasikan kekacauan sungguhan di server bersama.

Gejala: Koneksi Ditolak #

Keluhan “database-nya mati” paling sering berarti “saya tidak bisa masuk” — dua hal yang berbeda. pg_isready memisahkannya dalam hitungan detik: alat ini hanya mengetuk server tanpa menjalankan query, sehingga aman dijalankan kapan pun:

#!/usr/bin/env bash
# Triase koneksi: servernya hidup? portnya benar? klien bisa masuk?
# pg_isready TIDAK menjalankan query -- hanya mengetuk pintu server.
echo '==> 1. server kursus: socket dan port yang benar'
pg_isready -h "$PGHOST" -p "$PGPORT"

echo '==> 2. port yang salah (tidak ada server di situ)'
pg_isready -h "$PGHOST" -p 59999
echo "kode keluar: $?"

echo '==> 3. psql ke port yang salah'
psql -X -p 59999 -c "SELECT 1;" 2>&1 || echo "kode keluar: $?"

Output yang Dihasilkan:

==> 1. server kursus: socket dan port yang benar
/home/z/my-project/pgrun:54329 - accepting connections
==> 2. port yang salah (tidak ada server di situ)
/home/z/my-project/pgrun:59999 - no response
kode keluar: 2
==> 3. psql ke port yang salah
psql: error: connection to server on socket "/home/z/my-project/pgrun/.s.PGSQL.59999" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?
kode keluar: 2

Tiga langkahnya berurutan: server kursus menjawab accepting connections (server hidup), port 59999 menjawab no response dengan kode keluar 2 (tidak ada server di port itu), dan psql memberi pesan yang memastikan hal sama dari sisi klien. Dari sini peta penyebabnya menyempit:

Dua entri terakhir bukan masalah jaringan — koneksi sampai ke server dan ditolak oleh kebijakan. Pengaturan pg_hba.conf dan batas koneksi dibahas detail di chapter Connection Management dan File Konfigurasi PostgreSQL .

Gejala: Tiba-tiba Lambat #

Lambatnya server hampir selalu punya dua tersangka utama: query yang berat, atau lock yang saling menunggu. Keduanya terlihat dari pg_stat_activity:

-- Diagnosis aktivitas: lihat sesi sendiri di pg_stat_activity.
-- Kolom pid sengaja tidak ditampilkan: nomor proses berbeda tiap eksekusi.
SELECT usename, state, backend_type
FROM pg_stat_activity
WHERE pid = pg_backend_pid();

-- Apakah sesi ini sedang ditahan sesi lain? (kosong = tidak diblokir)
SELECT pg_blocking_pids(pg_backend_pid()) AS pemblokir;

-- Berapa sesi klien lain yang aktif di database ini?
-- (patokan dasar: pada database latihan yang sepi, jawabannya nol)
SELECT count(*) AS sesi_klien_lain_aktif
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
  AND backend_type = 'client backend'
  AND state = 'active';

Output yang Dihasilkan:

 usename  | state  |  backend_type  
----------+--------+----------------
 postgres | active | client backend
(1 row)

 pemblokir 
-----------
 {}
(1 row)

 sesi_klien_lain_aktif 
-----------------------
                     0
(1 row)

Query pertama menunjukkan sesi diagnosis sendiri — baris ini adalah cetakan dasar: pengguna, state active, dan jenis backend. Yang penting saat insiden nyata adalah query ketiga dengan filter waktu: sesi lain yang active di database yang sama. Di server yang sedang dikeluhkan, query pemburunya berbentuk seperti ini (tampilkan pid, lama berjalan, dan potongan teks query-nya):

SELECT pid,
       now() - query_start AS berjalan_selama,
       wait_event_type,
       left(query, 60)     AS potongan_query
FROM pg_stat_activity
WHERE state = 'active'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

Tersangka kedua adalah lock. pg_blocking_pids() adalah fungsi diagnosis kunci: berikan pid sesi yang menggantung, fungsi ini mengembalikan array pid yang menahannya. Berikut demonstrasi bentuknya pada sesi sendiri — satu tabel dikunci eksklusif, lalu diperiksa dari dalam:

-- Diagnosis lock pada sesi sendiri: kunci satu tabel dengan ACCESS EXCLUSIVE,
-- lalu periksa pg_locks dan pg_blocking_pids dari dalam transaksi yang sama.
CREATE TABLE pesanan_demo (
    id integer PRIMARY KEY
);

BEGIN;
LOCK TABLE pesanan_demo IN ACCESS EXCLUSIVE MODE;

-- Lock milik sesi ini pada tabel pengguna (lock katalog sistem disaring).
SELECT c.relname, l.mode, l.granted
FROM pg_locks l
JOIN pg_class c ON c.oid = l.relation
WHERE l.pid = pg_backend_pid()
  AND l.relation IS NOT NULL
  AND c.relname !~ '^pg_'
ORDER BY c.relname;

-- Siapa memblokir siapa: array kosong berarti sesi ini tidak menunggu siapa pun.
SELECT pg_blocking_pids(pg_backend_pid()) AS pemblokir;

ROLLBACK;

Output yang Dihasilkan:

CREATE TABLE
BEGIN
LOCK TABLE
   relname    |        mode         | granted 
--------------+---------------------+---------
 pesanan_demo | AccessExclusiveLock | t
(1 row)

 pemblokir 
-----------
 {}
(1 row)

ROLLBACK

Hasilnya menjelaskan cara bacanya: tabel pesanan_demo terkunci AccessExclusiveLock dan granted bernilai t — permintaan lock kita sendiri sudah dikabulkan. Serangan baliknya di pg_blocking_pids kosong ({}) karena tidak ada yang menahan kita. Skenario insiden sebenarnya terjadi di dua sesi: aplikasi melaporkan UPDATE menggantung, Anda menjalankan pg_blocking_pids(pid_sesi_menggantung) dan mendapatkan pid tersangka, lalu memeriksa query apa yang sedang dijalankan pid itu. Resep lengkapnya ada di chapter Process dan Activity Monitoring .

Gejala: Disk Penuh #

Disk yang penuh membuat PostgreSQL berhenti menulis WAL — dan pada titik itu server berhenti menerima transaksi sama sekali. Pengukurannya berjenjang, dari satu tabel sampai seluruh database:

-- Diagnosis ruang disk: dari ukuran satu tabel sampai seluruh database.
-- pages = pg_relation_size / 8192 (ukuran satu block halaman bawaan).
CREATE TABLE log_aktivitas (
    id   integer PRIMARY KEY,
    pesan text NOT NULL
);

INSERT INTO log_aktivitas
SELECT i, repeat('catatan aktivitas ke-' || i, 4)
FROM generate_series(1, 50000) AS i;

SELECT pg_size_pretty(pg_total_relation_size('log_aktivitas')) AS ukuran_tabel,
       pg_relation_size('log_aktivitas') / 8192               AS jumlah_pages;

SELECT CASE
         WHEN pg_database_size(current_database()) > 0
         THEN 'LULUS: ukuran database terbaca ('
              || pg_size_pretty(pg_database_size(current_database())) || ')'
         ELSE 'GAGAL: ukuran database tidak terbaca'
       END AS uji_ukuran_database;

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 50000
 ukuran_tabel | jumlah_pages 
--------------+--------------
 8040 kB      |          862
(1 row)

          uji_ukuran_database           
----------------------------------------
 LULUS: ukuran database terbaca (15 MB)
(1 row)

Tabel 50.000 baris berukuran 8040 kB (862 pages — satu page standar 8192 byte), dan ukuran seluruh database terbaca lewat pg_database_size(). Saat angka ini tumbuh tak wajar, tiga penyumbang yang paling sering:

• WAL yang menumpuk — arsip/replika berhenti mengambil membuat pg_wal membengkak; periksa keberhasilan archiving dan slot replikasi yang tidak aktif.
• Log server yang tak pernah diputar — file log postgresql di log_directory tumbuh tanpa batas bila log_rotation tidak diatur; pembahasannya di chapter Logging Configuration .
• Tabel dan index yang bengkak — update/delete berat meninggalkan ruang mati yang hanya dipulihkan lewat VACUUM; lihat chapter VACUUM dan Analyze .

Tindakan darurat ketika disk benar-benar kritis: hapus log lama yang sudah diarsipkan, VACUUM (FULL) tabel terbesar untuk memulihkan ruang (perhatikan: mengunci tabel), dan periksa slot replikasi terbengkalai yang menahan WAL.

Gejala: Koneksi Habis #

Pesan di log tidak bisa lebih jelas: FATAL: remaining connection slots are reserved.... Batas max_connections tercapai. Diagnosisnya dua pertanyaan: berapa sesi yang hidup, dan berapa di antaranya benar-benar bekerja. Query pg_stat_activity dengan filter state = 'idle' menampilkan sesi menganggur — biasanya aplikasi yang lupa menutup koneksi. Solusi cepatnya pg_terminate_backend(pid) pada sesi idle berjam-jam; solusi jangka panjangnya connection pooling (chapter Connection Pooling ) atau menaikkan max_connections — dengan sadar bahwa tiap koneksi memakan memori.

Menambang Log Server #

Log server adalah tempat semua gejala di atas meninggalkan jejak tertulis. File log-contoh.log berikut adalah cuplikan log latihan yang memuat satu hari “berwarna” — checkpoint biasa, satu ERROR dari pelanggaran constraint, dua query lambat, klien yang terputus, dan satu kehabisan slot koneksi:

2026-01-15 08:03:12.452 WIB [2481] LOG:  checkpoint starting: time
2026-01-15 08:03:42.778 WIB [2481] LOG:  checkpoint complete: wrote 12 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=32.001 s, sync=0.011 s, total=32.328 s
2026-01-15 09:14:05.104 WIB [2497] ERROR:  duplicate key value violates unique constraint "barang_pkey"
2026-01-15 09:14:05.104 WIB [2497] DETAIL:  Key (kode)=(BRG-001) already exists.
2026-01-15 09:14:05.104 WIB [2497] STATEMENT:  INSERT INTO barang (kode, nama) VALUES ('BRG-001', 'Beras 5kg')
2026-01-15 10:22:41.330 WIB [2513] LOG:  duration: 5234.871 ms  statement: SELECT * FROM laporan_bulanan ORDER BY total DESC
2026-01-15 10:41:02.985 WIB [2514] LOG:  could not receive data from client: Connection reset by peer
2026-01-15 10:41:03.101 WIB [2514] LOG:  unexpected EOF on client connection
2026-01-15 11:07:19.220 WIB [2519] FATAL:  remaining connection slots are reserved for non-replication superuser connections
2026-01-15 11:32:44.518 WIB [2523] LOG:  duration: 1042.625 ms  statement: SELECT count(*) FROM pesanan WHERE status = 'menunggu'

Empat pola grep berikut menambangnya menjadi informasi siap baca:

#!/usr/bin/env bash
# Menambang log server: hitung per level pesan, bedah ERROR dengan konteksnya,
# temukan query lambat dan peristiwa FATAL.
set -u
cd "$(dirname "$0")"
log=log-contoh.log

echo '==> jumlah baris per level pesan'
grep -oE ' (ERROR|FATAL|LOG):' "$log" | sort | uniq -c

echo '==> baris ERROR beserta baris konteksnya'
grep -A1 ' ERROR: ' "$log"

echo '==> query yang lambat (baris duration:)'
grep 'duration:' "$log" | grep -v 'total='

echo '==> peristiwa FATAL'
grep 'FATAL' "$log"

Output yang Dihasilkan:

==> jumlah baris per level pesan
      1  ERROR:
      1  FATAL:
      6  LOG:
==> baris ERROR beserta baris konteksnya
2026-01-15 09:14:05.104 WIB [2497] ERROR:  duplicate key value violates unique constraint "barang_pkey"
2026-01-15 09:14:05.104 WIB [2497] DETAIL:  Key (kode)=(BRG-001) already exists.
==> query yang lambat (baris duration:)
2026-01-15 10:22:41.330 WIB [2513] LOG:  duration: 5234.871 ms  statement: SELECT * FROM laporan_bulanan ORDER BY total DESC
2026-01-15 11:32:44.518 WIB [2523] LOG:  duration: 1042.625 ms  statement: SELECT count(*) FROM pesanan WHERE status = 'menunggu'
==> peristiwa FATAL
2026-01-15 11:07:19.220 WIB [2519] FATAL:  remaining connection slots are reserved for non-replication superuser connections

Setiap pola menjawab satu pertanyaan: distribusi level pesan (ERROR dan FATAL adalah prioritas), konteks lengkap error (baris DETAIL dan STATEMENT selalu menempel pada baris ERROR-nya — makanya grep -A1), query yang melewati ambang log_min_duration_statement, dan peristiwa fatal yang layak dibedah. Cara mengaktifkan duration: di server sungguhan dibahas di chapter Server Logs .

Buku Panduan Darurat #

Satu tabel untuk dibaca ulang sebelum insiden — gejala, query pertama, dan penyebab paling mungkin:

Server Macet atau Korup #

Dua skenario terakhir yang menakutkan. Server macet total (pg_ctl stop tidak berfungsi, sesi tidak bisa dibunuh): naikkan deadlock_timeout tidak membantu — periksa dulu pg_stat_activity untuk proses unresponsive; tindakan berikutnya adalah pg_ctl stop -m immediate lalu biarkan crash recovery bekerja saat startup. Kecurigaan korupsi (error invalid page in block, hasil query tidak masuk akal): berhenti menulis, dokumentasikan gejala, dan pulihkan — itu persis skenario yang membuat latihan Menguji Prosedur Recovery ada: backup yang belum pernah diuji dipulihkan bukanlah backup.

Di titik ini perjalanan teknis Course PostgreSQL lengkap: data dimodelkan, di-query, dioptimalkan, diamankan, direplikasi, diuji, dan didiagnosis. Pengetahuan yang tersimpan rapi di tiga puluh section itu menemukan bentuk sesungguhannya hanya saat dipakai membangun — dan section berikutnya, Proyek, adalah tempatnya: enam proyek utuh dari skema pertama sampai REST API.

Referensi #

• pg_isready
• Monitoring Statistics (pg_stat_activity, pg_locks)
• Connection Settings (max_connections)

-- Diagnosis aktivitas: lihat sesi sendiri di pg_stat_activity.
-- Kolom pid sengaja tidak ditampilkan: nomor proses berbeda tiap eksekusi.
SELECT usename, state, backend_type
FROM pg_stat_activity
WHERE pid = pg_backend_pid();

-- Apakah sesi ini sedang ditahan sesi lain? (kosong = tidak diblokir)
SELECT pg_blocking_pids(pg_backend_pid()) AS pemblokir;

-- Berapa sesi klien lain yang aktif di database ini?
-- (patokan dasar: pada database latihan yang sepi, jawabannya nol)
SELECT count(*) AS sesi_klien_lain_aktif
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
  AND backend_type = 'client backend'
  AND state = 'active';

-- Diagnosis ruang disk: dari ukuran satu tabel sampai seluruh database.
-- pages = pg_relation_size / 8192 (ukuran satu block halaman bawaan).
CREATE TABLE log_aktivitas (
    id   integer PRIMARY KEY,
    pesan text NOT NULL
);

INSERT INTO log_aktivitas
SELECT i, repeat('catatan aktivitas ke-' || i, 4)
FROM generate_series(1, 50000) AS i;

SELECT pg_size_pretty(pg_total_relation_size('log_aktivitas')) AS ukuran_tabel,
       pg_relation_size('log_aktivitas') / 8192               AS jumlah_pages;

SELECT CASE
         WHEN pg_database_size(current_database()) > 0
         THEN 'LULUS: ukuran database terbaca ('
              || pg_size_pretty(pg_database_size(current_database())) || ')'
         ELSE 'GAGAL: ukuran database tidak terbaca'
       END AS uji_ukuran_database;

#!/usr/bin/env bash
# Triase koneksi: servernya hidup? portnya benar? klien bisa masuk?
# pg_isready TIDAK menjalankan query -- hanya mengetuk pintu server.
echo '==> 1. server kursus: socket dan port yang benar'
pg_isready -h "$PGHOST" -p "$PGPORT"

echo '==> 2. port yang salah (tidak ada server di situ)'
pg_isready -h "$PGHOST" -p 59999
echo "kode keluar: $?"

echo '==> 3. psql ke port yang salah'
psql -X -p 59999 -c "SELECT 1;" 2>&1 || echo "kode keluar: $?"

-- Diagnosis lock pada sesi sendiri: kunci satu tabel dengan ACCESS EXCLUSIVE,
-- lalu periksa pg_locks dan pg_blocking_pids dari dalam transaksi yang sama.
CREATE TABLE pesanan_demo (
    id integer PRIMARY KEY
);

BEGIN;
LOCK TABLE pesanan_demo IN ACCESS EXCLUSIVE MODE;

-- Lock milik sesi ini pada tabel pengguna (lock katalog sistem disaring).
SELECT c.relname, l.mode, l.granted
FROM pg_locks l
JOIN pg_class c ON c.oid = l.relation
WHERE l.pid = pg_backend_pid()
  AND l.relation IS NOT NULL
  AND c.relname !~ '^pg_'
ORDER BY c.relname;

-- Siapa memblokir siapa: array kosong berarti sesi ini tidak menunggu siapa pun.
SELECT pg_blocking_pids(pg_backend_pid()) AS pemblokir;

ROLLBACK;

#!/usr/bin/env bash
# Menambang log server: hitung per level pesan, bedah ERROR dengan konteksnya,
# temukan query lambat dan peristiwa FATAL.
set -u
cd "$(dirname "$0")"
log=log-contoh.log

echo '==> jumlah baris per level pesan'
grep -oE ' (ERROR|FATAL|LOG):' "$log" | sort | uniq -c

echo '==> baris ERROR beserta baris konteksnya'
grep -A1 ' ERROR: ' "$log"

echo '==> query yang lambat (baris duration:)'
grep 'duration:' "$log" | grep -v 'total='

echo '==> peristiwa FATAL'
grep 'FATAL' "$log"