postgres 常用查询命令

查看等待事件
SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event is NOT NULL;

查看数据库对应的base下的目录：
select oid,datname from pg_database ;

看是否为备库：
select pg_is_in_recovery();
查看备库流复制信息。
select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
查看备库落后主库多少字节。
select pg_xlog_location_diff(pg_current_xlog_location(),replay_location) from pg_stat_replication;
 查看所有用户。
cat /etc/passwd|grep -v nologin|grep -v halt|grep -v shutdown|awk -F":" '{ print $1"|"$3"|"$4 }'|more

1、查询表中存在的锁
select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname
from pg_locks a
join pg_class b on a.relation = b.oid
where upper(b.relname) = 'TABLE_NAME';
2、查看数据库实例的版本
select version();

3、查看数据库的启动时间：
select pg_postmaster_start_time();

4、查看最后load配置文件的时间：
select pg_conf_load_time();

5、使用pg_ctl reload 改变配置的装载时间：
pg_ctl reload
select pg_conf_load_time();

6、显示当前数据库时区：
show timezone;

7、查看当前实例有哪些数据库。
psql -l 或者在数据库中  \l

8、查看当前用户名
select  user;
select current_user;

9、查看session用户
select session_user;

10、查询当前session所在客户端的IP地址及端口：
select inet_client_addr(),inet_client_port();

11、查询当前数据库服务器的IP地址及端口：
select inet_server_addr(),inet_server_port();

12、查询当前session的后台服务进程的pid
select pg_backend_pid();

13、查看当前的一些参数配置情况
show shared_buffers;
select current_setting('shared_buffers');
14、修改当前session的参数配置
set maintenance_work_mem to '128MB';
select set_config('maintenance_work_mem','128MB',false);

15、查看当前正在写的WAL文件
select pg_xlogfile_name(pg_current_xlog_location());

16、查看当前WAL的buffer中还有多少字节的数据没有写到磁盘中
select pg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location());

17、查看数据库实例是否正在做基础备份
select pg_is_in_backup(),pg_backup_start_time();

18、查看数据库实例是Hot Standy 状态还算是正常数据库状态
select pg_is_in_recovery();

19、
数据库
select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database;                     ---查看所有数据库的大小。
表
 \d                                           ---查看数据库的所有表
 \d a                                        -----查看表的信息（如果表中有索引会在下面显示索引的内容）
select pg_relation_size('a');    ----查看表的大小
select pg_size_pretty(pg_relation_size('a'));    ------以kb、mb、gb的形式显示表的大小
select pg_size_pretty(pg_total_relation_size('a'));   -----表的总大小，包括索引的大小
索引
\di                                           -------查看数据库的所有索引
select pg_size_pretty(pg_relation_size('a_index'));   -----查看索引大小
表空间 
 \db                                          ------查看所有的表空间以及表空间对应的目录（pg_default、                               
                                                           pg_global为默认的表空间在data目录下）
select pg_size_pretty(pg_tablespace_size('pg_default'));       ----查看表空间的大小
select pg_tablespace_size('pg_default')/1024/1024 as "SIZE M";  
查看表对应的数据文件：
select pg_relation_filepath ('tablename');


系统维护的常用命令

修改配置文件postgresql.conf后，让修改生效的方法有两种：
方法一：在操作系统使用如下命令
pg_ctl reload
方法二：在psql中使用如下命令
select pg_reload_conf();
上述方法仅限于不需要重启数据库服务的参数


切换log日志文件到下一个的命令
select pg_rotate_logfile();
切换WAL日志文件的命令
select pg_switch_xlog();



查询长时间运行的SQL
select pid,usename,query_start,query from pg_stat_activity;
取消SQL
select pg_cancel_backend(pid);
如果取消不了
select pg_terminate_backend(pid);


select pg_stop_backup();

以找出长时间运行的SQL
窗口一
testdb=# select pid,usename,query_start,query from pg_stat_activity;
 pid  | usename |          query_start          |                            query                            
------+---------+-------------------------------+-------------------------------------------------------------
 2637 | qwe     | 2017-02-20 11:47:04.190518+08 | select pid,usename,query_start,query from pg_stat_activity;
(1 row)


testdb=# select pg_sleep(300);

（执行此步骤之后光标会一直停留，查询在后台运行）
窗口二：
testdb=#  select pid,usename,query_start,query from pg_stat_activity;
 pid  | usename |          query_start          |                            query                            
------+---------+-------------------------------+-------------------------------------------------------------
 2637 | qwe     | 2017-02-20 12:08:32.731631+08 | select pg_sleep(300);
 5629 | qwe     | 2017-02-20 12:08:56.019362+08 | select pid,usename,query_start,query from pg_stat_activity;
(2 rows)

testdb=# select pg_cancel_backend(567);       发送取消执行标志
WARNING:  PID 567 is not a PostgreSQL server process
 pg_cancel_backend 
-------------------
 f
(1 row)

testdb=# select pg_cancel_backend(2637);
 pg_cancel_backend 
-------------------
 t
(1 row)

testdb=#  select pid,usename,query_start,query from pg_stat_activity;
 pid  | usename |          query_start          |                            query                            
------+---------+-------------------------------+-------------------------------------------------------------
 2637 | qwe     | 2017-02-20 12:08:32.731631+08 | select pg_sleep(300);
 5629 | qwe     | 2017-02-20 12:09:46.648482+08 | select pid,usename,query_start,query from pg_stat_activity;
(2 rows)
可以发现进程没有接受标志，即未成功取消
testdb=# select pg_terminate_backend(2637);     终止后台进程
 pg_terminate_backend 
----------------------
 t
(1 row)

testdb=#  select pid,usename,query_start,query from pg_stat_activity;
 pid  | usename |          query_start          |                            query                            
------+---------+-------------------------------+-------------------------------------------------------------
 5629 | qwe     | 2017-02-20 12:10:22.936444+08 | select pid,usename,query_start,query from pg_stat_activity;
(1 row)

成功取消查询

进入窗口1查看状态

pg_cancel_backend(pid)  取消正在执行的ＳＱＬ语句。发送的是一个标志,需要
pg_terminate_backend(pid)   终止一个后台服务进程，同时 释放次后台服务进程的资源



解压目录下：/tmp/postgresql-9.4.4/contrib   放着很多工具，   直接make+make install   
然后select * from pg_available_extensions;查看安装的扩展工具  
create extension 名字；      安装外部工具
testdb=# create extension chkpass;
CREATE EXTENSION