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;
-
复制查看（在主库执行，备库执行无结果）： select * from pg_stat_replication;
-
修改参数： 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());
后续不断更新。。。。。。。。。。
