[筆記] PostgreSQL 常用指令

這一篇會是記錄我常使用的Postgresql的語法 (持續更新)


  • 查看目前執行語法的狀態(很重要)
https://www.postgresql.org/docs/9.6/static/monitoring-stats.html
SELECT datname, usename, client_addr, wait_event, now(), state, query from pg_stat_activity;



  • 資料庫統計資料
 SELECT datname, numbackends, xact_commit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, pg_database_size(datname), pg_size_pretty(pg_database_size(datname)) FROM pg_stat_database;





  • 表格統計資料
SELECT schemaname, relname, n_tup_ins, n_tup_upd, n_tup_del, idx_scan, seq_scan from pg_stat_user_tables;

SELECT schemaname, relname, n_tup_ins, n_tup_upd, n_tup_del, idx_scan, seq_scan from pg_stat_all_tables;

  • Indexes 統計資料

SELECT * from pg_stat_all_indexes;

SELECT * from pg_stat_user_indexes;

  • 顯示目前的database
SELECT current_database();


  • 顯示所有的table
SELECT * FROM pg_catalog.pg_tables where schemaname = 'public';

  • 刪掉Lock Process
----- 以下還沒驗證 只是紀錄--------
SELECT
   pg_terminate_backend(pid) 
FROM
   pg_stat_activity 
WHERE
   -- don't kill my own connection!
   pid <> pg_backend_pid()
   -- don't kill the connections to other databases
   AND datname = 'database_name'
   ;
select psa.query, pl.pid from pg_locks pl LEFT JOIN pg_stat_activity psa on pl.pid = psa.pid;

留言

熱門文章