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: 

Đặ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 đồ.