MOP 系列|MOP 三种主流数据库常用 SQL(三)PG篇

作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）

MOP 不用多说，指的就是 MySQL、Oracle、PostgreSQL 三种目前最主流的数据库，MOP 系列打算更新 MOP 三种数据库的索引知识、高可用架构及常用 SQL 语句等等，上面已经更新了 MOP 索引相关的文章，今天打算整理一下这三种数据库的常用 SQL 知识，由于文章过长，今天更新中间的一篇之 MySQL 篇。第一篇 Oracle 相关的详见下方链接：MOP 系列|MOP 三种主流数据库常用 SQL(一)。第二篇 MySQL 常用 SQL详见下方链接：MOP 系列|MOP 三种主流数据库常用 SQL(二)。

PostgreSQL 常用 SQL 大全

1、基础信息常用命令

0）查看版本

代码语言：javascript复制

cat $PGDATA/PG_VERSION 
psql --version 
show server_version; 
select version();
select now();

1）查看 PostgreSQL 数据库连接及数量

代码语言：javascript复制

select datid,datname,pid,usename,state,client_addr,query from pg_stat_activity;

2）用户与角色

代码语言：javascript复制

查询用户 # select user; 
查询当前用户 # select * from current_user; 
select current_user;
select * from pg_user;
select * from pg_roles;

3）查看数据库实例启动时间

代码语言：javascript复制

select pg_postmaster_start_time();

4）查看表空间

代码语言：javascript复制

select * from pg_tablespace;

5)查看所有 schema

代码语言：javascript复制

select * from information_schema.schemata;
select nspname from pg_namespace;
dnS

6)查看最后 load 配置文件的时间

代码语言：javascript复制

select pg_conf_load_time();

7)查看表名

代码语言：javascript复制

dt     --只能查看到当前数据库下public的表名
SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename;
SELECT * FROM information_schema.tables WHERE table_name='test1';

8)查看表结构

代码语言：javascript复制

d tablename
select * from information_schema.columns where table_schema='public' and table_name='t1';

9)查看索引

代码语言：javascript复制

di
select * from pg_index;

10)查看视图

代码语言：javascript复制

dv
select * from pg_views where schemaname = 'public';
select * from information_schema.views where table_schema = 'public';

11)查看约束

代码语言：javascript复制

select * from pg_constraint where contype = 'p'
select a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from pg_class a,pg_constraint b where a.oid = b.conrelid and a.relname = 'cc';

12)查看触发器

代码语言：javascript复制

select * from information_schema.triggers;

13)查看序列

代码语言：javascript复制

select * from information_schema.sequences where sequence_schema = 'public';

14)查询当前session所在客户端的IP地址及端口

代码语言：javascript复制

select inet_client_addr(),inet_client_port();

15)查询当前数据库服务器的IP地址及端口

代码语言：javascript复制

select inet_server_addr(),inet_server_port();

16)查询当前session的后台服务进程的PID

代码语言：javascript复制

select pg_backend_pid();

17)查看当前正在写的WAL文件

代码语言：javascript复制

select pg_xlogfile_name(pg_current_xlog_location());

18)查看当前参数配置情况

代码语言：javascript复制

show shared_buffers; 
select current_setting('shared_buffers');

修改当前session的参数配置
set maintenance_work_mem to '128MB';
SELECT set_config('maintenance_work_mem', '128MB', false);

19)查看某数据库的大小

代码语言：javascript复制

SELECT pg_size_pretty(pg_database_size('XX')) As dbsize;
查看所有数据库的大小
select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS total_size from pg_database;

20)查看各数据库数据创建时间

代码语言：javascript复制

select datname,(pg_stat_file(format('%s/%s/PG_VERSION',case when spcname='pg_default' then 'base' else 'pg_tblspc/'||t2.oid||'/PG_14_202107181/' end, t1.oid))).* from pg_database t1,pg_tablespace t2 where t1.dattablespace=t2.oid;

21)按占空间大小，顺序查看所有表的大小

代码语言：javascript复制

select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;
按占空间大小，顺序查看索引大小
select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;
查看表大小(不含索引等信息)
select pg_relation_size('test');
select pg_size_pretty(pg_relation_size('test'))
查看表上所有索引的大小
select pg_size_pretty(pg_indexes_size('tabname'));

22)查看表空间大小

代码语言：javascript复制

select pg_size_pretty(pg_tablespace_size('pg_global'));
select pg_size_pretty(pg_tablespace_size('pg_default'));

23)查看表对应的数据文件

代码语言：javascript复制

select pg_relation_filepath('test'); 
SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'test';

24)查看表上存在哪些索引以及大小

代码语言：javascript复制

select relname,n.amname as index_type from pg_class m,pg_am n where m.relam = n.oid and m.oid in
(select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'cc');

SELECT c.relname,c2.relname, c2.relpages as size_kb FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname ='cc' AND c.oid =i.indrelid AND c2.oid =i.indexrelid ORDER BY c2.relname;

25)查看索引定义

代码语言：javascript复制

select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'cc';
select pg_get_indexdef(b.indexrelid);

26)查看过程函数定义

代码语言：javascript复制

select oid,* from pg_proc where proname = 'proc_test1'; 
select * from pg_get_functiondef();

27)查看数据库实例是否正在做基础备份

代码语言：javascript复制

select pg_is_in_backup(), pg_backup_start_time();

28)查看当前数据库实例状态

代码语言：javascript复制

查看当前数据库实例处于Hot Standby状态还是正常数据库状态
select pg_is_in_recovery();
查询有哪些 slot，任意一个数据库下都可以查，查询的结果都一样
select * from pg_replication_slots;

29)查看是否归档

代码语言：javascript复制

show archive_mode;

30)查看日志相关

查看运行日志的相关配置，运行日志包括Error信息，定位慢查询SQL，数据库的启动关闭信息，checkpoint过于频繁等的告警信息。

代码语言：javascript复制

show logging_collector;  --启动日志收集
show log_directory; --日志输出路径
show log_filename;  --日志文件名
show log_truncate_on_rotation; --当生成新的文件时如果文件名已存在，是否覆盖同名旧文件名
show log_statement; --设置日志记录内容
show log_min_duration_statement; --运行XX毫秒的语句会被记录到日志中，-1表示禁用这个功能，0表示记录所有语句，类似mysql的慢查询配置

2、管理维护常用命令

1）清理pg_wal日志

代码语言：javascript复制

pg_archivecleanup /pgdata/data/pg_wal 00000001000000000000001F

表示删除 00000001000000000000001F 之前的所有日志

pg_wal日志没有设置保留周期的参数，即没有类似mysql的参数expire_logs_days，pg_wal日志永久保留，除非shell脚步删除几天前或pg-rman备份时候设置保留策略

2）切换pg_wal日志

代码语言：javascript复制

select pg_switch_wal();
select pg_switch_xlog();
手动生成checkpoint
Checkpoint;

3）切换下一个 log 日志文件

代码语言：javascript复制

select pg_rotate_logfile();

4）查杀正在执行的 SQL

取消正在长时间执行的SQL命令的方法有以下两种。 ·pg_cancel_backend(pid)：取消一个正在执行的SQL命令。 ·pg_terminate_backend(pid)：终止一个后台服务进程，同时释放此后台服务进程的资源。

这两个函数的区别是，pg_cancel_backend() 函数实际上是给正在执行的 SQL 任务置一个取消标志，正在执行的任务在合适的时候检测到此标志后会主动退出；但如果该任务没有主动检测到此标志就无法正常退出，此时就需要使用 pg_terminate_backend 命令来中止SQL命令的执行。

通常先查询 pg_stat_activity 以找出长时间运行的 SQL 命令

代码语言：javascript复制

select pid,usename,query_start,query from pg_stat_activity;

然后再使用 pg_cancel_backend() 取消该 SQL 命令，如果 pg_cancel_backend() 取消失败，再使用 pg_terminate_backend()，命令如下：

代码语言：javascript复制

select pg_cancel_backend(10716);
select pid,usename,query_start,query from pg_stat_activity;
select pg_terminate_backend(10716);
select pid,usename,query_start,query from pg_stat_activity;

5）修改参数并重新加载生效

代码语言：javascript复制

alter system set work_mem='8MB';

使用 alter system 命令将修改 postgresql.auto.conf 文件，而不是postgresql.conf，这样可以很好的保护 postgresql.conf 文件，假如你使用很多alter system 命令后搞的一团糟，那么你只需要删除 postgresql.auto.conf，再执行pg_ctl reload 加载 postgresql.conf 文件即可实现参数的重新加载。

如果修改了配置文件“postgresql.conf”后，要想让修改生效，有以下两种方法。 方法一：在操作系统下使用如下命令：

pg_ctl reload

方法二：在psql中使用如下命令：

osdba=# select pg_reload_conf();

代码语言：javascript复制

pg_reload_conf 
---------------- 
t 
(1 row)

注意，如果是需要重启数据库服务才能使修改生效的配置项，使 用上面的方法无效。使用上面的方法能使修改生效的配置项都是不需要重启数据库服务就能使修改生效的配置项。

6）查找行锁源头并查杀

代码语言：javascript复制

WITH sos AS (
    SELECT array_cat(array_agg(pid),
           array_agg((pg_blocking_pids(pid))[array_length(pg_blocking_pids(pid),1)])) pids
    FROM pg_locks
    WHERE NOT granted
)
SELECT a.pid, a.usename, a.datname, a.state,
       a.wait_event_type || ': ' || a.wait_event AS wait_event,
       current_timestamp-a.state_change time_in_state,
       current_timestamp-a.xact_start time_in_xact,
       l.relation::regclass relname,
       l.locktype, l.mode, l.page, l.tuple,
       pg_blocking_pids(l.pid) blocking_pids,
       (pg_blocking_pids(l.pid))[array_length(pg_blocking_pids(l.pid),1)] last_session,
       coalesce((pg_blocking_pids(l.pid))[1]||'.'||coalesce(case when locktype='transactionid' then 1 else array_length(pg_blocking_pids(l.pid),1) 1 end,0),a.pid||'.0') lock_depth,
       a.query
FROM pg_stat_activity a
     JOIN sos s on (a.pid = any(s.pids))
     LEFT OUTER JOIN pg_locks l on (a.pid = l.pid and not l.granted)
ORDER BY lock_depth;

  pid  | usename  | datname |        state        |     wait_event      |  time_in_state  |  time_in_xact   | relname |   locktype    |   mode    | page | tuple | blocking_pids | last_session | lock_depth |                   query       

------- ---------- --------- --------------------- --------------------- ----------------- ----------------- --------- --------------- ----------- ------ ------- --------------- -------------- ------------ -------------------------------
------------
 16383 | postgres | jiekexu | idle in transaction | Client: ClientRead  | 00:07:59.724724 | 00:08:05.575664 |         |               |           |      |       |               |              | 16383.0    | update t set id=1 where addr='
beijing';
 19170 | postgres | jiekexu | active              | Lock: transactionid | 00:07:02.168133 | 00:07:21.859316 |         | transactionid | ShareLock |      |       | {16383}       |        16383 | 16383.1    | update t set id=120 where addr
='beijing';
(2 rows)

postgres=# select pg_terminate_backend(16383);
 pg_terminate_backend 
----------------------
 t
(1 row)

postgres=# select name,setting from pg_settings where name='default_transaction_isolation';
             name              |    setting     
------------------------------- ----------------
 default_transaction_isolation | read committed
(1 row)

--上面查到的 idle in transaction 对应的 PID。
select pg_terminate_backend(16383);

全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~

数据库 sql 配置 日志 索引

0 人点赞