Các câu lệnh kiểm tra thông tin database (monitor_db_daily) trong PostgreSQL

Mục đích: Các câu lệnh kiểm tra thông tin database (monitor_db_daily) trong PostgreSQL

--version 2:

-- =============================

-- MONITORING POSTGRESQL FULL BY @BINHTV_VIETDBA

-- Tương đương Oracle Daily Monitoring

-- =============================

-- 1. Thông tin hệ thống & phiên bản:"PostgreSQL 11.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit"

SELECT version();

SELECT now() - pg_postmaster_start_time() AS uptime;

-- 2. Tổng quan session hiện hành

SELECT datname, usename, application_name, state, count(*) AS connections

FROM pg_stat_activity

GROUP BY datname, usename, application_name, state

ORDER BY count(*) DESC;

-- 3. Các truy vấn đang chạy lâu

SELECT pid, usename, datname, application_name, state, query_start, now() - query_start AS duration, query

FROM pg_stat_activity

WHERE state = 'active' AND now() - query_start > interval '2 minutes'

ORDER BY duration DESC;

-- 4. Lock & blocking session

SELECT blocked.pid AS blocked_pid,

       blocked.query AS blocked_query,

       blocking.pid AS blocking_pid,

       blocking.query AS blocking_query

FROM pg_locks blocked_locks

JOIN pg_stat_activity blocked ON blocked_locks.pid = blocked.pid

JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype

   AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database

   AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation

   AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page

   AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple

   AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid

   AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid

   AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid

   AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid

   AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid

   AND blocking_locks.pid != blocked_locks.pid

JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid

WHERE NOT blocked_locks.granted;

-- 5. Kích thước các database:"sp" "1989 GB"

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size

FROM pg_database

ORDER BY pg_database_size(datname) DESC;

-- 6. Kích thước tablespace:"pg_default" "1989 GB"

SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) AS size

FROM pg_tablespace;

-- 7. Kích thước các bảng lớn:"list_dt_new" "203 GB","PART_202407" "26 GB"...,"selfcare_log" "21 GB"

SELECT relname AS table, pg_size_pretty(pg_total_relation_size(relid)) AS total_size

FROM pg_catalog.pg_statio_user_tables

ORDER BY pg_total_relation_size(relid) DESC

LIMIT 20;

-- 8. Autovacuum & dead tuple:"PART_202601" 37859395 4299158

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum

FROM pg_stat_user_tables

ORDER BY n_dead_tup DESC

LIMIT 20;

-- 9. Checkpoint statistics

SELECT * FROM pg_stat_bgwriter;

-- 10. WAL và LSN

SELECT pg_current_wal_lsn() AS current_lsn,

       pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') AS diff_bytes;

-- 11. Tỷ lệ Buffer Cache Hit:99.71

SELECT blks_hit, blks_read, round(100 * blks_hit::numeric / nullif(blks_hit + blks_read, 0), 2) AS hit_ratio

FROM pg_stat_database

WHERE datname = current_database();

-- 12. I/O theo table:"PART_202507" 1545073178 113456336920 98.66

SELECT relname AS table, heap_blks_read, heap_blks_hit, round(100 * heap_blks_hit::numeric / nullif(heap_blks_hit + heap_blks_read,0), 2) AS hit_ratio

FROM pg_statio_user_tables

ORDER BY heap_blks_read DESC

LIMIT 20;

-- 13. Thống kê backend activity theo DB

--"sp" 642 57887294492 6612893360 963762244555797 7226127152590

--"cd" 9 1903519 48 1421191267 6544738

--"ilin" 9 2211386 6 2524288745 4500840

--"postgres" 6 2199522 6 1765405280 4390945

--"template1" 0 749708 0 394948551 4137497

--"template0" 0 746728 0 393371363 4119145

SELECT datname, numbackends, xact_commit, xact_rollback, tup_returned, tup_fetched

FROM pg_stat_database

ORDER BY numbackends DESC;

-- 14. Deadlock, temp files, lỗi hệ thống

SELECT * FROM pg_stat_database_conflicts;

SELECT * FROM pg_stat_archiver;

----------------------------------------version 1:-------------------------------------

-- 1. Thông tin phiên bản PostgreSQL: "PostgreSQL 11.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit"

SELECT version();

-- 2. Uptime của PostgreSQL: "281 days 18:52:22.747409"

SELECT now() - pg_postmaster_start_time() AS uptime;

-- 3. Số lượng session hiện tại 671

SELECT count(*) FROM pg_stat_activity;

-- 4. Các session đang active: 23

SELECT pid, usename, state, query_start, query

FROM pg_stat_activity

WHERE state != 'idle'

ORDER BY query_start DESC;

-- 5. Sử dụng CPU/Memory/Load: cần lấy từ OS (vmstat, top, etc.)

-- 6. Tablespace usage: "1989 GB"

SELECT spcname AS tablespace,

       pg_size_pretty(pg_tablespace_size(spcname)) AS size

FROM pg_tablespace;

-- 7. Database size: "rps" "1989 GB"

SELECT datname,

       pg_size_pretty(pg_database_size(datname)) AS size

FROM pg_database;

-- 8. Table/index size trong schema hiện tại: "l_detail_new" "203 GB"

SELECT relname AS object,

       pg_size_pretty(pg_total_relation_size(relid)) AS total_size

FROM pg_catalog.pg_statio_user_tables

ORDER BY pg_total_relation_size(relid) DESC

LIMIT 20;

-- 9. Các truy vấn đang chạy lâu

SELECT pid, now() - query_start AS duration, query

FROM pg_stat_activity

WHERE state = 'active' AND now() - query_start > interval '5 minutes';

-- 10. Lock hiện tại: Err column reference "pid" is ambiguous

SELECT pid, locktype, relation::regclass, mode, granted

FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid;

-- 11. WAL info: ..

SELECT pg_current_wal_lsn(), pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0');

-- 12. Checkpoint info: ..

SELECT * FROM pg_stat_bgwriter;

-- 13. Autovacuum activity

SELECT relname, last_vacuum, last_autovacuum, n_dead_tup

FROM pg_stat_user_tables

ORDER BY n_dead_tup DESC

LIMIT 20;

=============================
TƯ VẤN: Click Here hoặc Hotline/Zalo 090.29.12.888
=============================
Website không chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn không muốn bị AI thay thế và tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp hay làm chủ Database thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ bí kíp thực chiến, thủ tục, quy trình của gần 20 năm kinh nghiệm (mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google) từ đó giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
cơ sở dữ liệu, cơ sở dữ liệu quốc gia, database, AI, trí tuệ nhân tạo, artificial intelligence, machine learning, deep learning, LLM, ChatGPT, DeepSeek, Grok, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c/21c/23c/23ai, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, docker, k8s, micro service, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty