Connection Management

Setiap koneksi ke PostgreSQL bukan sekadar baris di daftar — server men-fork satu proses sistem operasi khusus untuk koneksi itu. Seratus koneksi berarti seratus proses dengan memori masing-masing, dan batasnya ditetapkan jauh sebelum klien pertama tiba. Chapter ini membahas pengelolaan sambungan dari sisi administrator: berapa koneksi yang ditampung, bagaimana membaca state setiap koneksi lewat pg_stat_activity, mengapa koneksi yang menganggur di tengah transaction berbahaya, dan bagaimana menghentikan koneksi yang menolak pergi — semuanya dengan query yang bisa dijalankan ulang.

max_connections dan Slot Cadangan #

Dua parameter di postgresql.conf mengatur daya tampung koneksi cluster: max_connections sebagai batas total, dan superuser_reserved_connections sebagai slot yang hanya boleh dipakai superuser. File berikut membaca keduanya, lalu menghitung berapa koneksi yang sedang benar-benar hidup di database tempat query itu berjalan:

-- Batas jumlah koneksi untuk seluruh cluster
SHOW max_connections;

-- Slot yang dicadangkan khusus untuk superuser
SHOW superuser_reserved_connections;

-- Berapa koneksi yang sedang ada di database ini
SELECT count(*) AS koneksi_aktif
FROM pg_stat_activity
WHERE datname = current_database();

Output yang Dihasilkan:

 max_connections 
-----------------
 100
(1 row)

 superuser_reserved_connections 
--------------------------------
 3
(1 row)

 koneksi_aktif 
---------------
             1
(1 row)

Interpretasinya: server ini menampung maksimal 100 koneksi serentak, dan 3 di antaranya dicadangkan untuk superuser. Ketika koneksi biasa sudah memakai 97 slot, klien ke-98 menerima galat FATAL: too many connections — tetapi administrator masih bisa masuk lewat slot cadangan untuk mendiagnosis dan membersihkan. Filter datname = current_database() menghitung hanya koneksi ke database kita; angka 1 di output adalah session psql yang menjalankan query itu sendiri, karena view pg_stat_activity memuat juga baris untuk dirinya sendiri.

Setiap Koneksi Adalah Sebuah Proses #

Model process-per-connection ini punya konsekuensi nyata: koneksi mahal. Satu backend kosong menghabiskan memori untuk struktur internal sekitar 1-2 MB sebelum query pertama dijalankan, dan pembukaannya menuntut fork dari postmaster — detail arsitekturnya dibahas di chapter Arsitektur PostgreSQL . Aplikasi web yang membuka “satu koneksi per request” akan menghabiskan slot, memori, dan waktu fork dengan cepat.

Karena itu pola yang benar untuk aplikasi sibuk adalah connection pooling: klien meminjam koneksi dari kolam kecil yang dipelihara terus-menerus. PostgreSQL sendiri tidak membawa pooler bawaan untuk skala besar, sehingga admin biasa memasang PgBouncer di depan server — pembahasan lengkapnya ada di chapter Connection Pooling .

Unix Socket atau TCP #

Klien PostgreSQL terhubung lewat salah satu dari dua jalur: Unix domain socket (komunikasi antar-proses di mesin yang sama) atau TCP/IP (lintas jaringan). Jalurnya ditentukan parameter listen_addresses: nilai bawaan localhost hanya membuka TCP di alamat lokal, sedangkan server uji materi ini justru mematikan TCP total (listen_addresses kosong) dan hanya menyediakan socket di /home/z/my-project/pgrun:

# lewat Unix socket: -h menunjuk ke direktori socket
psql -h /home/z/my-project/pgrun -p 54329 -d latihan

# lewat TCP: -h menunjuk ke nama host atau alamat IP
psql -h db.tokomajujaya.example.com -p 5432 -U budi -d latihan

Bila TCP dimatikan, setiap upaya koneksi lewat TCP ditolak sistem operasi sebelum menyentuh PostgreSQL. File skrip berikut membuktikannya di lingkungan uji kita:

# Server uji ini hanya mendengarkan Unix socket, bukan TCP
psql -h 127.0.0.1 -p 54329 -d postgres -c "SELECT 1;"

Output yang Dihasilkan:

psql: error: connection to server at "127.0.0.1", port 54329 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?

Pesan Connection refused berasal dari sistem operasi: tidak ada proses yang mendengarkan port 54329 pada antarmuka TCP. Di server produksi dengan listen_addresses = '*', kegagalan yang sama menandakan firewall, port yang salah, atau server yang mati.

Tiga State Koneksi #

View pg_stat_activity melaporkan state setiap backend. Tiga state yang paling sering ditemui:

Untuk melihat transisi antar state secara nyata, kita butuh dua koneksi sekaligus. Ekstensi dblink memungkinkan hal itu dari dalam satu file SQL — query kita membuka koneksi kedua, mengamatinya, mengubah perilakunya, lalu menutupnya:

-- Ekstensi dblink membuka koneksi kedua dari dalam SQL
CREATE EXTENSION IF NOT EXISTS dblink;

-- Sambungkan sesi kedua ke database yang sama
SELECT dblink_connect('sekunder',
  'host=/home/z/my-project/pgrun port=54329 user=postgres dbname='
  || current_database());

-- Simpan PID sesi kedua agar bisa diamati berulang kali
CREATE TEMP TABLE sesi_kedua AS
  SELECT pid FROM dblink('sekunder', 'SELECT pg_backend_pid()') AS t(pid int);

-- Sesi kedua menunggu perintah berikutnya
SELECT state, query
FROM pg_stat_activity
WHERE pid = (SELECT pid FROM sesi_kedua);

-- Buka transaction di sesi kedua, lalu biarkan menganggur
SELECT dblink_exec('sekunder', 'BEGIN');

-- Statusnya berubah menjadi idle in transaction
SELECT state, query
FROM pg_stat_activity
WHERE pid = (SELECT pid FROM sesi_kedua);

-- Hentikan sesi kedua dari sesi utama
SELECT pg_terminate_backend((SELECT pid FROM sesi_kedua)) AS dihentikan;

-- Barisnya hilang dari pg_stat_activity
SELECT count(*) AS masih_ada
FROM pg_stat_activity
WHERE pid = (SELECT pid FROM sesi_kedua);

Output yang Dihasilkan:

CREATE EXTENSION
 dblink_connect 
----------------
 OK
(1 row)

SELECT 1
 state |          query          
-------+-------------------------
 idle  | SELECT pg_backend_pid()
(1 row)

 dblink_exec 
-------------
 BEGIN
(1 row)

        state        | query 
---------------------+-------
 idle in transaction | BEGIN
(1 row)

 dihentikan 
------------
 t
(1 row)

 masih_ada 
-----------
         0
(1 row)

Mari kita bedah perjalanan sesi kedua di file tersebut:

1. dblink_connect('sekunder', ...): membuka koneksi TCP/socket kedua dari dalam session pertama. Nama sekunder dipakai untuk merujuk koneksi itu pada pemanggilan dblink berikutnya; string koneksinya mengikuti lingkungan uji materi ini dan perlu disesuaikan dengan host, port, serta user server Anda.
2. CREATE TEMP TABLE sesi_kedua: menyimpan PID milik backend kedua — angka yang berbeda setiap eksekusi, sehingga tidak kita tampilkan, hanya dipakai sebagai penunjuk.
3. SELECT state, query ... pertama: koneksi kedua baru saja menjalankan SELECT pg_backend_pid() lalu menunggu. State-nya idle, dan kolom query masih menampilkan teks query terakhir yang selesai — perilaku standar untuk backend yang menganggur.
4. dblink_exec('sekunder', 'BEGIN'): membuka transaction di koneksi kedua. Query pengamatan berikutnya memperlihatkan state idle in transaction — transaction hidup, query terakhirnya BEGIN, dan keduanya akan terus menahan apa pun yang sudah dikunci.
5. pg_terminate_backend(...): menghentikan backend kedua dari luar. Nilai kembaliannya t, dan baris sesi kedua lenyap dari pg_stat_activity — dibuktikan masih_ada bernilai 0.

Batas Umur idle in Transaction #

State idle in transaction adalah state yang paling layak diawasi. Aplikasi yang lupa COMMIT — misalnya kode yang menangkap exception tanpa menutup transaction — akan menahan lock dan snapshot lama berjam-jam, membuat tabel bloat dan koneksi lain mengantre. Parameter idle_in_transaction_session_timeout memotongnya: session yang menganggur di dalam transaction melewati batas waktu akan dihentikan server secara otomatis. File berikut membuktikan mekanismenya dengan batas 500 milidetik:

-- Sambungkan sesi kedua ke database yang sama
SELECT dblink_connect('sekunder',
  'host=/home/z/my-project/pgrun port=54329 user=postgres dbname='
  || current_database());
CREATE TEMP TABLE sesi_kedua AS
  SELECT pid FROM dblink('sekunder', 'SELECT pg_backend_pid()') AS t(pid int);

-- Batasi idle in transaction hanya 500 milidetik untuk sesi kedua
SELECT dblink_exec('sekunder', 'SET idle_in_transaction_session_timeout = ''500ms''');

-- Mulai transaction lalu biarkan menganggur
SELECT dblink_exec('sekunder', 'BEGIN');
SELECT state
FROM pg_stat_activity
WHERE pid = (SELECT pid FROM sesi_kedua);

-- Tunggu melewati batas timeout
SELECT pg_sleep(1);

-- Sesi kedua sudah dihentikan server secara otomatis
SELECT count(*) AS masih_ada
FROM pg_stat_activity
WHERE pid = (SELECT pid FROM sesi_kedua);

Output yang Dihasilkan:

 dblink_connect 
----------------
 OK
(1 row)

SELECT 1
 dblink_exec 
-------------
 SET
(1 row)

 dblink_exec 
-------------
 BEGIN
(1 row)

        state        
---------------------
 idle in transaction
(1 row)

 pg_sleep 
----------
 
(1 row)

 masih_ada 
-----------
         0
(1 row)

Urutan kejadiannya: SET memasang batas hanya untuk session kedua (bukan cluster), BEGIN membuka transaction, lalu pg_sleep(1) membuat session utama menunggu satu detik — cukup lama bagi server untuk menghentikan session kedua yang menganggur 500 milidetik lebih lama dari batasnya. Nilai 0 seperti bawaan berarti fitur ini mati; nilai produksi yang lazim berkisar 5min sampai 30min, dipasang lewat ALTER SYSTEM agar berlaku ke semua session baru.

Menghentikan Koneksi yang Nakal #

Dua function administratif menangani koneksi bermasalah: pg_cancel_backend(pid) membatalkan query yang sedang berjalan tetapi membiarkan koneksinya hidup, sedangkan pg_terminate_backend(pid) memutus koneksi sekaligus. Keduanya menerima PID yang terbaca dari kolom pg_stat_activity.pid. Bila PID itu tidak dimiliki proses PostgreSQL mana pun, function mengembalikan f tanpa menghentikan apa pun:

-- PID yang tidak dimiliki proses PostgreSQL mana pun
SELECT pg_terminate_backend(99999999) AS pid_tidak_ada;

Output yang Dihasilkan:

 pid_tidak_ada 
---------------
 f
(1 row)

Nilai kembali f berarti tidak ada yang dihentikan — koneksi target sudah mati sebelumnya, atau PID salah ketik. Saat Anda menjalankan contoh ini, psql juga menampilkan WARNING: PID 99999999 is not a PostgreSQL backend process di jalur stderr; baris itu tidak ikut hasil SELECT sehingga tidak tampil pada output di atas.

Bagaimana jika yang dihentikan justru koneksi kita sendiri? Server memprosesnya tanpa pandang bulu — koneksi terputus seketika, dan psql melaporkan kegagalan koneksi:

-- Menghentikan koneksi sendiri: server memutus koneksi yang sedang berjalan
SELECT pg_terminate_backend(pg_backend_pid());

Output yang Dihasilkan:

psql:mati-sendiri.sql:2: FATAL:  terminating connection due to administrator command
psql:mati-sendiri.sql:2: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
psql:mati-sendiri.sql:2: error: connection to server was lost

Baris FATAL: terminating connection due to administrator command adalah pesan yang juga dilihat korban pg_terminate_backend di sisi klien — persis yang dialami session kedua pada contoh status-dua-sesi.sql. Exit code psql menjadi 2 karena kegagalan koneksi, bukan galat SQL. Dalam praktik, PID yang Anda hentikan harus milik session lain; menghentikan diri sendiri hanya berguna sebagai eksperimen seperti ini.

Kita kini bisa menghitung, mengamati, membatasi, dan memutus koneksi satu per satu. Pertanyaan berikutnya bagi administrator: di antara puluhan proses itu, mana yang sedang benar-benar bekerja, menunggu apa, dan menahan siapa — semua itu terjawab di chapter Process dan Activity Monitoring.

Referensi #

• Connections and Authentication
• pg_stat_activity View
• SET Reference

-- Batas jumlah koneksi untuk seluruh cluster
SHOW max_connections;

-- Slot yang dicadangkan khusus untuk superuser
SHOW superuser_reserved_connections;

-- Berapa koneksi yang sedang ada di database ini
SELECT count(*) AS koneksi_aktif
FROM pg_stat_activity
WHERE datname = current_database();

-- PID yang tidak dimiliki proses PostgreSQL mana pun
SELECT pg_terminate_backend(99999999) AS pid_tidak_ada;

-- Menghentikan koneksi sendiri: server memutus koneksi yang sedang berjalan
SELECT pg_terminate_backend(pg_backend_pid());

-- Ekstensi dblink membuka koneksi kedua dari dalam SQL
CREATE EXTENSION IF NOT EXISTS dblink;

-- Sambungkan sesi kedua ke database yang sama
SELECT dblink_connect('sekunder',
  'host=/home/z/my-project/pgrun port=54329 user=postgres dbname='
  || current_database());

-- Simpan PID sesi kedua agar bisa diamati berulang kali
CREATE TEMP TABLE sesi_kedua AS
  SELECT pid FROM dblink('sekunder', 'SELECT pg_backend_pid()') AS t(pid int);

-- Sesi kedua menunggu perintah berikutnya
SELECT state, query
FROM pg_stat_activity
WHERE pid = (SELECT pid FROM sesi_kedua);

-- Buka transaction di sesi kedua, lalu biarkan menganggur
SELECT dblink_exec('sekunder', 'BEGIN');

-- Statusnya berubah menjadi idle in transaction
SELECT state, query
FROM pg_stat_activity
WHERE pid = (SELECT pid FROM sesi_kedua);

-- Hentikan sesi kedua dari sesi utama
SELECT pg_terminate_backend((SELECT pid FROM sesi_kedua)) AS dihentikan;

-- Barisnya hilang dari pg_stat_activity
SELECT count(*) AS masih_ada
FROM pg_stat_activity
WHERE pid = (SELECT pid FROM sesi_kedua);

# Server uji ini hanya mendengarkan Unix socket, bukan TCP
psql -h 127.0.0.1 -p 54329 -d postgres -c "SELECT 1;"

-- Sambungkan sesi kedua ke database yang sama
SELECT dblink_connect('sekunder',
  'host=/home/z/my-project/pgrun port=54329 user=postgres dbname='
  || current_database());
CREATE TEMP TABLE sesi_kedua AS
  SELECT pid FROM dblink('sekunder', 'SELECT pg_backend_pid()') AS t(pid int);

-- Batasi idle in transaction hanya 500 milidetik untuk sesi kedua
SELECT dblink_exec('sekunder', 'SET idle_in_transaction_session_timeout = ''500ms''');

-- Mulai transaction lalu biarkan menganggur
SELECT dblink_exec('sekunder', 'BEGIN');
SELECT state
FROM pg_stat_activity
WHERE pid = (SELECT pid FROM sesi_kedua);

-- Tunggu melewati batas timeout
SELECT pg_sleep(1);

-- Sesi kedua sudah dihentikan server secara otomatis
SELECT count(*) AS masih_ada
FROM pg_stat_activity
WHERE pid = (SELECT pid FROM sesi_kedua);