Những lệnh psql sử dụng khi PostgreSQL gặp sự cố

Khi vận hành PostgreSQL trong môi trường production, chúng ta thường nghĩ ngay đến các công cụ như Grafana, Prometheus, pgAdmin hay các giải pháp giám sát chuyên dụng. Tuy nhiên, trong nhiều trường hợp, công cụ đầu tiên tôi mở lại chỉ là psql.
psql không chỉ là một command line client để chạy SQL. Đây còn là một công cụ cực kỳ mạnh mẽ giúp DBA, DevOps và SRE nhanh chóng xác định tình trạng của hệ thống, truy tìm nguyên nhân gây chậm và xử lý các sự cố phổ biến.
Trong bài viết này, tôi sẽ chia sẻ những câu lệnh mà tôi thường sử dụng nhất khi cần kiểm tra một hệ thống PostgreSQL đang gặp vấn đề.
1. Kiểm tra các kết nối hiện tại
Một trong những việc đầu tiên cần làm khi database phản hồi chậm là kiểm tra xem có bao nhiêu session đang kết nối.
SELECT pid, usename, application_name, client_addr, state FROM pg_stat_activity; Kết quả sẽ cho biết:
- Ai đang kết nối
- Kết nối đến từ địa chỉ IP nào
- Ứng dụng nào đang sử dụng database
- Trạng thái của từng session
Một số trạng thái thường gặp:
| State | Ý nghĩa |
| active | Đang thực thi query |
| idle | Đang chờ lệnh mới |
| idle in transaction | Transaction đang mở nhưng không thực hiện gì |
Đặc biệt cần chú ý đến các session ở trạng thái idle in transaction vì chúng có thể gây ra nhiều vấn đề liên quan đến lock và VACUUM.
2. Tìm các query chạy lâu
Nếu CPU tăng cao hoặc người dùng phản ánh ứng dụng phản hồi chậm, đây thường là truy vấn đầu tiên tôi chạy.
SELECT
pid,
now() - query_start AS duration,
state,
query
FROM pg_stat_activity
WHERE state <> 'idle' ORDER BY duration DESC; Thông tin quan trọng nhất là cột duration.
Một query chạy vài giây có thể bình thường.
Một query chạy hàng chục phút hoặc hàng giờ thường là dấu hiệu của:
- Thiếu index
- Deadlock
- Truy vấn không tối ưu
- Dữ liệu tăng đột biến
3. Hủy hoặc ngắt query gây sự cố
Sau khi xác định được query gây ảnh hưởng đến hệ thống, PostgreSQL cho phép xử lý theo hai mức độ.
Hủy query:
SELECT pg_cancel_backend(12345); Ngắt toàn bộ session:
SELECT pg_terminate_backend(12345); Sự khác nhau:
- pg_cancel_backend() chỉ dừng truy vấn hiện tại
- pg_terminate_backend() đóng hoàn toàn kết nối
Thông thường nên thử cancel trước khi sử dụng terminate.
4. Kiểm tra lock trong hệ thống
Nhiều trường hợp PostgreSQL không hề quá tải nhưng ứng dụng vẫn bị treo.
Nguyên nhân có thể đến từ lock.
SELECT
pid,
relation::regclass,
mode,
granted
FROM pg_locks; Một số dấu hiệu đáng chú ý:
- Query chờ rất lâu
- ALTER TABLE không thực hiện được
- UPDATE hoặc DELETE bị treo
Khi đó việc kiểm tra lock thường giúp tìm ra thủ phạm rất nhanh.
5. Tìm transaction tồn tại quá lâu
Các transaction mở lâu là "kẻ thù" của VACUUM.
SELECT
pid,
usename,
now() - xact_start AS age,
query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY age DESC;Nếu phát hiện transaction tồn tại nhiều giờ hoặc nhiều ngày, bạn nên điều tra nguyên nhân ngay.
Các transaction này có thể:
- Giữ lại nhiều phiên bản dữ liệu cũ
- Làm tăng kích thước bảng
- Khiến autovacuum hoạt động kém hiệu quả
6. Xem bảng nào đang chiếm nhiều dung lượng nhất
Khi dung lượng ổ đĩa tăng nhanh, việc đầu tiên cần làm là xác định bảng nào đang tiêu thụ nhiều không gian.
SELECT
relname,
pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20; Kết quả sẽ cho thấy:
- Bảng dữ liệu lớn
- Bảng index lớn
- Tổng dung lượng thực tế
Từ đó có thể xác định đối tượng cần tối ưu hoặc lưu trữ lâu dài.
7. Kiểm tra dung lượng từng database
Nếu cluster chứa nhiều database khác nhau:
SELECT
datname,
pg_size_pretty(pg_database_size(datname))
FROM pg_database
ORDER BY pg_database_size(datname) DESC; Thông tin này đặc biệt hữu ích khi cần dự báo dung lượng lưu trữ hoặc chuẩn bị backup.
8. Theo dõi WAL hiện tại
Nếu bạn làm việc với Replication, Backup hoặc PITR thì WAL là thành phần không thể bỏ qua.
Kiểm tra vị trí WAL hiện tại:
SELECT pg_current_wal_lsn(); Giá trị này gọi là LSN (Log Sequence Number).
LSN đại diện cho vị trí hiện tại trong luồng WAL và được PostgreSQL sử dụng cho:
- Crash Recovery
- Streaming Replication
- Point-In-Time Recovery
9. Theo dõi trạng thái Replication
Trên Primary:
SELECT
application_name,
state,
sync_state,
sent_lsn,
replay_lsn
FROM pg_stat_replication; Một số thông tin hữu ích:
- Standby nào đang kết nối
- Đang sử dụng synchronous hay asynchronous replication
- Độ trễ giữa Primary và Standby
Đây là truy vấn tôi sử dụng gần như mỗi ngày khi vận hành cụm PostgreSQL HA.
10. Biến psql thành công cụ monitoring thời gian thực
Một tính năng ít người biết của psql là chế độ tự động refresh.
Ví dụ:
SELECT count(*) FROM pg_stat_activity;
\watch 2 Kết quả sẽ tự động cập nhật sau mỗi 2 giây.
Tính năng này cực kỳ hữu ích khi:
- Theo dõi số lượng kết nối
- Quan sát replication lag
- Giám sát tiến trình backup
- Kiểm tra hoạt động của VACUUM
Bạn có thể coi đây như một dashboard mini chạy ngay trong terminal.
Kết luận
Mặc dù PostgreSQL có rất nhiều công cụ quản trị hiện đại, psql vẫn là công cụ mạnh mẽ nhất mà mọi DBA và DevOps nên thành thạo.
Chỉ với vài truy vấn đơn giản, bạn có thể:
- Kiểm tra kết nối
- Phát hiện query chậm
- Tìm lock
- Theo dõi replication
- Quan sát WAL
- Giám sát hệ thống theo thời gian thực
Trong nhiều sự cố production, khả năng sử dụng thành thạo psql thường giúp xác định nguyên nhân nhanh hơn rất nhiều so với việc mở hàng loạt dashboard và biểu đồ.



