postgresql常用查询语句

1.查找执行较慢的sql：
select* from pg_stat_statements；

2.根据操作系统的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.授权：
schema只读：
grant select on all tables in schema app_schema to app_user_readonly;
针对schema读写权限：
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. 复制查看（在主库执行，备库执行无结果）：
   select * from pg_stat_replication;

7. 修改参数：
   postgres=# alter system set shared_buffers='1000MB';
   ALTER SYSTEM

8.参数查看：
show shared_buffers;
show hba_file;
show config_file;

9.干净的关闭数据库：
pg_ctl stop -m fast

10.查看主从复制延迟时间：
select extract(epoch from now() - pg_last_xact_replay_timestamp())；

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

12.常用查询：
--查看所有的对象（表名字、索引名字、sequence等）：
SELECT from pg_class where relname = 'activity_history';
select from pg_attribute where attname = 'activity_history';
--查看所有信息：
select from pg_index;
--查看表和索引的对应信息以及索引的创建信息：
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.统计信息相关：
PG提供了一下各个对象级别的统计信息视图：
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

根据pg提供的pg_test_timing工具测试打开track_io_timing参数是否会产生瓶颈：
PG还提供了对数据库内函数的调用次数及其他信息进行统计的视图：pg_stat_user_functions
PG还提供了一下各个对象上发生I/O情况的统计视图：
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.常用维护：
显示当前session对应的后台进程：
select pg_backend_pid();
向进程发送INT信号把正在执行的sql取消掉：
pg_ctl kill INT xxx
一般都是使用取消：
select pg_cancel_backend(xxx);
sql sleep多久，单位秒：
select pg_sleep(xxx);
查看数据库启动时间：
select pg_postmaster_start_time();
查看配置文件最后load时间：
select pg_conf_load_time();
显示数据库当前时区：
show timezone;
显示当前session所在的客户端ip地址和端口：
select inet_client_addr(),inet_client_port();
显示当前数据库服务器的ip地址和端口：
select inet_server_addr(),inet_server_port();
查看当前正在写的wal文件：
9.x版本：
select pg_xlogfile_name(pg_current_xlog_location());
10.x版本：
select pg_walfile_name(pg_current_wal_insert_lsn());

后续不断更新。。。。。。。。。。