PostgreSQL运维常用SQL大全

数据库运维之道 2024-05-23

884

目录
1、基础信息常用命令
    1）查看 PostgreSQL 数据库连接及数量
    2）用户与角色
    3）查看数据库实例启动时间
    4）查看表空间
    5)查看所有 schema
    6)查看最后 load 配置文件的时间
    7)查看表名
    8)查看表结构
    9)查看索引
    10)查看视图
    11)查看约束
    12)查看触发器
    13)查看序列
    14)查询当前session所在客户端的IP地址及端口
    15)查询当前数据库服务器的IP地址及端口
    16)查询当前session的后台服务进程的PID
    17)查看当前正在写的WAL文件
    18)查看当前参数配置情况
    19)查看某数据库的大小
    20)查看各数据库数据创建时间
    21)按占空间大小，顺序查看所有表的大小
    22)查看表空间大小
    23)查看表对应的数据文件
    24)查看表上存在哪些索引以及大小
    25)查看索引定义
    26)查看过程函数定义
    27)查看数据库实例是否正在做基础备份
    28)查看当前数据库实例处于Hot Standby状态还是正常数据库状态
    29)查看是否归档
    30)查看日志相关
2、管理维护常用命令
    1）清理pg_wal日志
    2）切换pg_wal日志
    3）切换下一个 log 日志文件
    4）查杀正在执行的 SQL
    5）修改参数并重新加载生效
   6）查找行锁源头并查杀

1、基础信息常用命令

0）查看版本

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

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

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

2）用户与角色

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

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

select pg_postmaster_start_time();
复制

4）查看表空间

select * from pg_tablespace;
复制

5)查看所有 schema

select * from information_schema.schemata;
select nspname from pg_namespace;
\dnS
复制

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

select pg_conf_load_time();
复制

7)查看表名

\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)查看表结构

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

9)查看索引

\di
select * from pg_index;
复制

10)查看视图

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

11)查看约束

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)查看触发器

select * from information_schema.triggers;
复制

13)查看序列

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

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

select inet_client_addr(),inet_client_port();
复制

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

select inet_server_addr(),inet_server_port();
复制

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

select pg_backend_pid();
复制

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

select pg_xlogfile_name(pg_current_xlog_location());
复制

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

show shared_buffers; 
select current_setting('shared_buffers');


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

最大连接数
select setting::int8 as max_conn from pg_settings 
where name = 'max_connections';


复制

19)查看某数据库的大小

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)查看各数据库数据创建时间

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)按占空间大小，顺序查看所有表的大小

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)查看表空间大小

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

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

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

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

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)查看索引定义

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)查看过程函数定义

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

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

select pg_is_in_backup(), pg_backup_start_time();
复制

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

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

29)查看是否归档

show archive_mode;
复制

30)查看日志相关

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

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日志

pg_archivecleanup /pgdata/data/pg_wal 00000001000000000000001F
复制

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

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

2）切换pg_wal日志

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

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

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 命令

select pid,usename,query_start,query from pg_stat_activity;
复制

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

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）修改参数并重新加载生效

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();

pg_reload_conf
---------------- 
t
(1 row)
复制

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

6）查找行锁源头并查杀

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);
复制

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

❤️ 欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！

分享几个数据库备份脚本

一文搞懂 Oracle 统计信息
复制

我的 Oracle ACE 心路历程
MOP 系列|MOP 三种主流数据库索引简介
Oracle 主流版本不同架构下的静默安装指南
关机重启导致 ASM 磁盘丢失数据库无法启动
Oracle SQL 性能分析(SPA)原理与实战演练
复制

Oracle 11g 升级到 19c 需要关注的几个问题
Windows 10 环境下 MySQL 8.0.33 安装指南
SQL 大全（四）|数据库迁移升级时常用 SQL 语句
OGG|使用 OGG19c 迁移 Oracle11g 到 19C(第二版)
Oracle 大数据量导出工具——sqluldr2 的安装与使用
从国产数据库调研报告中你都能了解哪些信息及我的总结建议
使用数据泵利用 rowid 分片导出导入 lob 大表及最佳实践
在归档模式下直接 rm dbf 数据文件并重启数据库还有救吗？
复制

欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！
————————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
ITPUB：https://blog.itpub.net/69968215
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————

文章转载自数据库运维之道，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。