[筆記] 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;