PostgreSQL adalah salah satu sistem manajemen basis data favorit saya. Ini kuat, mudah digunakan, dan menggunakan kueri SQL asli yang sangat familiar.
Salah satu fitur hebat tentang PostgreSQL adalah kemampuan untuk mengambil informasi dalam kueri SQL minimal. Itu karena ia memiliki skema dan tabel yang terorganisir dengan baik dan disediakan untuk informasi tertentu, termasuk memantau aktivitas server.
Tutorial ini akan memandu Anda melalui berbagai cara untuk mengidentifikasi koneksi server dan aktivitas menggunakan server. Ini dapat membantu Anda mengidentifikasi koneksi, mengelolanya, atau menghentikannya jika tidak berguna.
Katalog Sistem PostgreSQL
Sebelum kita menanyakan PostgreSQL untuk koneksi aktif di server, mari kita pahami di mana pengumpulan informasi ini terjadi.
PostgreSQL berisi skema dengan tabel dan tampilan yang diisi dengan informasi metadata tentang objek lain di server. Itu mirip dengan tabel INFORMATION_SCHEMA di MySQL.
Skema katalog Sistem memungkinkan Anda untuk mengumpulkan informasi tentang sistem, termasuk proses sistem, indeks, akses tabel, metode akses data, dan banyak lagi.
Karena katalog Sistem sangat besar dan berisi banyak sekali informasi, kami tidak dapat membahasnya dalam satu artikel. Namun, ada satu tabel yang kami minati: pg_stat_activity.
Tabel ini menunjukkan log koneksi yang dibuat ke server dan data terkaitnya, database yang terhubung, PID dari proses yang setara, dan banyak lagi.
pg_stat_aktivitas
Informasi yang dapat kami kumpulkan dari tabel pg_stat_activity meliputi:
- datid – Menunjukkan OID database yang terhubung dengan koneksi.
- datname – Nama koneksi database.
- pid – memproses ID koneksi.
- usesysid – OID user yang login.
- usename – Nama user yang masuk.
- application_name – Nama aplikasi terhubung dalam sesi tertentu
- client_addr – Alamat IP client yang terkait dengan koneksi tertentu.
- client_hostname – Nama host client dari koneksi tertentu.
- client_port – Ini menunjukkan nomor port TCP yang digunakan untuk terhubung ke server oleh client yang ditetapkan.
- backend_start – Saat proses dimulai—itu sesuai dengan waktu client terhubung ke server.
- query_stat –Waktu saat kueri yang sedang berjalan dimulai. Jika tidak ada kueri aktif, ini menunjukkan query_stat dari kueri sebelumnya.
- state –keadaan koneksi saat ini. Ini bisa menjadi idle, aktif, nonaktif, idle dalam transaksi, idle dalam transaksi (dibatalkan), dan panggilan fungsi jalur cepat.
- Query – menunjukkan kueri terbaru dari koneksi.
- Waiting – benar atau salah jika koneksi menunggu kunci.
Dengan informasi itu, sekarang kita dapat menanyakan informasi rinci tentang koneksi aktif di server.
Cara Query Koneksi Aktif
Untuk menanyakan koneksi aktif, kita mulai dengan masuk ke server. Jika Anda menggunakan instalasi PostgreSQL default, gunakan psql dengan nama user dan password Anda sebagai
psql -U postgres -W
Password:
psql (13.3)
Ketik “bantuan” untuk bantuan.
Mari kita mulai dengan mengumpulkan semua informasi dari tabel pg_stat_activity. Gunakan kueri seperti yang ditunjukkan di bawah ini
select * from pg_stat_activity;
Setelah Anda menjalankan kueri di atas, PostgreSQL akan membuang banyak informasi tentang Anda, yang sebagian besar mungkin tidak Anda perlukan. Untuk mengatasi ini, kita dapat memilih kolom tertentu seperti yang ditunjukkan pada kueri di bawah ini:
SELECT datname as database_name,
pid as Process_ID,
usename as username,
application_name,
client_addr as Host,
client_port as Port,
state,
query
from pg_stat_activity;
Ini hanya akan memilih informasi yang diperlukan dan mengaturnya dalam bentuk tabel, seperti yang ditunjukkan pada tangkapan layar.
Akhirnya, kita dapat memfilter di mana statusnya tidak sama dengan aktif untuk mengumpulkan hanya koneksi yang aktif.
Query untuk itu adalah:
SELECT datname as database_name,
pid as Process_ID,
usename as username,
application_name,
client_addr as Host,
client_port as Port,
state,
query
from pg_stat_activity
WHERE state <> 'idle';
Ini akan menampilkan output yang mirip dengan yang ditunjukkan di bawah ini:
Terakhir, cara sederhana untuk mendapatkan informasi serupa adalah dengan menggunakan pgAdmin. pgAdmin adalah alat administrasi GUI yang kuat untuk database PostgreSQL.
Setelah Anda masuk ke pgAdmin, navigasikan ke dasbor dan buka server_activity. Segarkan untuk menunjukkan kepada Anda semua koneksi.
Kesimpulan
Panduan ini membahas cara menanyakan aktivitas koneksi dari PostgreSQL dengan menggunakan tabel pg_stat_activity dan pgAdmin.
