201.PostgreSQL日常操作汇总

1、常用元命令：

(在psql交互界面执行)元命令能够方便便捷的管理数据库，

\h, \help        --获取所有可执行命令
\h ALTER                 --获取命令ALTER的详细说明
\l                             --获取所有数据库信息，相当于mysql的show databases
\l+              --获取所有数据库信息+更多描述信息
\dt                            --列出所有表，相当于show tables
\dn                         --列出当前库下的schema信息
\du                         --获取所有用户信息
\du+                        --就比du命令多了一个Description列
\db+             --查看表空间信息
\s               --打印历史命令
\d                            --列出当前库下的表
\d table_name    --查看表定义信息
\dt+ table_name  --查看表占用空间大小
\di+ index_name  --查看索引占用空间大小               
\q , exit                    --退出psql
\c mydb                    --切换数据库，相当于MySQL的use mydb
\c mydb schemaname    --切换到mydb库下的某个schema下
\x                ---将结果集竖行显示，相当于MySQL在SQL结尾的\G功能；
\?                            --列出所有的元命令
\timing                    --SQL计时器，记录某个SQL执行完所花费的时间

show hba_file    --查看参数hba_file

常用系统表

• pg_roles            --角色系统表
• pg_tables            --数据库
• pg_user              --用户系统表

1.1关闭数据库

pg_ctl stop -m fast

关闭数据库的几种模式：

• ms, 默认模式，等待所有连接事务完成，不允许新的事务，shutdown之前会进行checkpoint确保所有已提交事务落盘
• mf, 快速模式，建议在繁忙系统使用，kill掉所有开启的事务，shutdown之前会进行checkpoint确保所有已提交事务落盘
• mi, 立即模式，紧急情况下使用，立即kill掉所有进程，实例重启后需要Crash Recovery

2.查看数据库当前活跃回话的SQL：

mydb=# select pid,usename,datname,query,client_addr from pg_stat_activity where pid<>pg_backend_pid() and state='active' order by query;

pid | usename | datname | query | client_addr
-----+---------+---------+-------+-------------
(0 rows)

注：state可以为‘active’或‘idle’，

为了方面，我们可以创建~/.psqlrc文件，然后自定义命令行变量，将如下内容写入文件中：

--check the current active sessions:
\set active_session 'select pid,usename,datname,query,client_addr from pg_stat_activity where pid<>pg_backend_pid() and state=\'active\' order by query;'

使用方法：
连接到psql交互界面：

mydb=# :active_session;                --直接使用变量active_session即可调用上述SQL命令；
pid | usename | datname | query | client_addr
-----+---------+---------+-------+-------------
(0 rows)

3.查看会话等待事件

mydb=# select pid,usename,datname,query,client_addr,wait_event_type,wait_event from pg_stat_activity where pid<>pg_backend_pid() and wait_event is not null order by wait_event_type;

4.查看数据库连接数

mydb=# select datname,usename,client_addr,count(*) from pg_stat_activity where pid<>pg_backend_pid()  group by 1,2,3 order by 1,2,4 desc;

5.计算缓存命中率

缓存命中率应该非常接近1，否则应该调整shard_buffers参数的配置

select blks_hit::float/(blks_read+blks_hit) as cache_hit_ratio from pg_stat_database where datname=current_database();

6.计算事务提交率

通过事务提交率则可以知道我们应用的健康情况，它应该等于或非常接近1，否则检查是否有死锁或者其他超时太多。

select xact_commit::float/(xact_commit+xact_rollback) as successful_xact_ratio from pg_stat_database where datname=current_database();

7.查询平均执行时间最长的3条语句

注：不过需要首先开通pg_stat_statements模块功能

select calls,total_time/calls as avg_time, left(query,80) from pg_stat_statements order by 2 desc limit 3;

8.获取某表上索引的大小以及扫描情况跟：

select schemaname,relname,indexrelname,pg_relation_size(indexrelid) as index_size,idx_scan,idx_tup_read,idx_tup_fetch
from pg_stat_user_indexes
where indexrelname in (
    select indexname from pg_indexes where schemaname='public' and tablename='test_1');

结果如下：

schemaname | relname |  indexrelname   | index_size | idx_scan | idx_tup_read | idx_tup_fetch
------------+---------+-----------------+------------+----------+--------------+---------------
public     | test_1  | test_1_pkey     |    2260992 |       25 |           26 |            21
public     | test_1  | test_1_name_idx |    3178496 |        0 |            0 |             0

参考网址：
元命令详述： https://blog.51cto.com/wujianwei/1980277