發表文章

目前顯示的是 三月, 2018的文章

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