postgresql常用查詢語句

1.查找執(zhí)行較慢的sql：
select* from pg_stat_statements；

2.根據(jù)操作系統(tǒng)的pid查找回話：
select d.query from pg_stat_activity d where pid=18707;

3.查詢慢sql：

SELECT query,calls,total_time,(total_time / calls) AS average,ROWS,
100.0 * shared_blks_hit / NULLIF (shared_blks_hit + shared_blks_read,0) AS hit_percent
FROM pg_stat_statements ORDER BY average DESC LIMIT 10;

4.重置pg_stat_statements表：
select pg_stat_statements_reset();

5.授權(quán)：
schema只讀：
grant select on all tables in schema app_schema to app_user_readonly;
針對(duì)schema讀寫權(quán)限：
grant select,update,delete,insert on all tables in schema app_schema to app_user;

create database chunqiu;
create user u_chunqiu password 'u_chunqiu';
alter database chunqiu owner to u_chunqiu;
create schema crmdb;
alter schema crmdb owner to u_chunqiu;

6. 復(fù)制查看（在主庫執(zhí)行，備庫執(zhí)行無結(jié)果）：
   select * from pg_stat_replication;

7. 修改參數(shù)：
   postgres=# alter system set shared_buffers='1000MB';
   ALTER SYSTEM

8.參數(shù)查看：
show shared_buffers;
show hba_file;
show config_file;

9.干凈的關(guān)閉數(shù)據(jù)庫：
pg_ctl stop -m fast

10.查看主從復(fù)制延遲時(shí)間：
select extract(epoch from now() - pg_last_xact_replay_timestamp())；

11.刷新配置文件：
a.SELECT pg_reload_conf();
b.pg_ctl reload

12.常用查詢：
--查看所有的對(duì)象（表名字、索引名字、sequence等）：
SELECT from pg_class where relname = 'activity_history';
select from pg_attribute where attname = 'activity_history';
--查看所有信息：
select from pg_index;
--查看表和索引的對(duì)應(yīng)信息以及索引的創(chuàng)建信息：
select from pg_indexes where indexname = 'index_name';
--查看表的信息：
select from pg_tables where tablename = 'pg_class';
--查看視圖信息：
select from pg_views;
select from pg_type;
SELECT FROM information_schema.schemata;
--獲取表的字段和類型：
SELECT a.attname as name,pg_type.typname as typename,col_description(a.attrelid,a.attnum) as comment, a.attnotnull as notnull
FROM pg_class as c,pg_attribute as a inner join pg_type on pg_type.oid = a.atttypid
where c.relname = 'activity_history' and a.attrelid = c.oid and a.attnum>0

13.切換schema：
show search_path ;
set search_path to app ;
set search_path to app,public ;
SET search_path TO myschema,public;

14.統(tǒng)計(jì)信息相關(guān)：
PG提供了一下各個(gè)對(duì)象級(jí)別的統(tǒng)計(jì)信息視圖：
pg_stat_database
pg_stat_all_tables
pg_stat_sys_tables
pg_stat_user_tables
pg_stat_all_indexes
pg_stat_sys_indexes
pg_stat_user_indexes

根據(jù)pg提供的pg_test_timing工具測(cè)試打開track_io_timing參數(shù)是否會(huì)產(chǎn)生瓶頸：
PG還提供了對(duì)數(shù)據(jù)庫內(nèi)函數(shù)的調(diào)用次數(shù)及其他信息進(jìn)行統(tǒng)計(jì)的視圖：pg_stat_user_functions
PG還提供了一下各個(gè)對(duì)象上發(fā)生I/O情況的統(tǒng)計(jì)視圖：
pg_statio_all_tables
pg_statio_sys_tables
pg_statio_user_tables
pg_statio_all_indexes
pg_statio_sys_indexes
pg_statio_user_indexes
pg_statio_all_sequences
pg_statio_sys_sequences
pg_statio_user_sequences

15.常用維護(hù)：
顯示當(dāng)前session對(duì)應(yīng)的后臺(tái)進(jìn)程：
select pg_backend_pid();
向進(jìn)程發(fā)送INT信號(hào)把正在執(zhí)行的sql取消掉：
pg_ctl kill INT xxx
一般都是使用取消：
select pg_cancel_backend(xxx);
sql sleep多久，單位秒：
select pg_sleep(xxx);
查看數(shù)據(jù)庫啟動(dòng)時(shí)間：
select pg_postmaster_start_time();
查看配置文件最后load時(shí)間：
select pg_conf_load_time();
顯示數(shù)據(jù)庫當(dāng)前時(shí)區(qū)：
show timezone;
顯示當(dāng)前session所在的客戶端ip地址和端口：
select inet_client_addr(),inet_client_port();
顯示當(dāng)前數(shù)據(jù)庫服務(wù)器的ip地址和端口：
select inet_server_addr(),inet_server_port();
查看當(dāng)前正在寫的wal文件：
9.x版本：
select pg_xlogfile_name(pg_current_xlog_location());
10.x版本：
select pg_walfile_name(pg_current_wal_insert_lsn());

后續(xù)不斷更新。。。。。。。。。。