查看等待事件
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) 取消正在执行的SQL语句。发送的是一个标志,需要
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
postgres 常用查询命令
最新推荐文章于 2024-07-21 19:15:03 发布