PostgreSQL 常用运维SQL整理

一、查询并杀会话

-- 查询会话 select pid,usename,client_addr,client_port,query_start,query,wait_event from pg_stat_activity; -- 杀会话 select pg_terminate_backend('pid号');

-- 使用如下命令自动生成杀会话语句 select datid,datname,pid,usesysid,usename,application_name,client_addr,client_port,state,query,'select pg_terminate_backend('||pid||');' kill_pid from pg_stat_activity;

-- 杀状态是空闲会话 select pg_terminate_backend(pid) from pg_stat_activity where state='idle';

-- 查询进程杀会话 select pid,state from  pg_stat_activity; -- 然后通过 kill -9 pid 杀会话

二、查看库-表-schema大小

1) 查看表空间大小

select pg_size_pretty(pg_tablespace_size('pg_default'));

2） 查看所有数据库大小

select pg_size_pretty(sum(pg_database_size(oid))) from pg_database;

3）查看每个数据库大小

select datname,pg_size_pretty(pg_database_size(oid)) from pg_database;

4） 查看指定数据库大小

select pg_size_pretty(pg_database_size('db_hr'));

5） 查看每个schema大小

select schemaname,pg_size_pretty(cast(sum(pg_relation_size(schemaname||'.'||tablename))as bigint)) from pg_tables t inner join pg_namespace d on t.schemaname=d.nspname group by schemaname;

6） 查看所有表大小并大小排序

select tableowner,schemaname,tablename,pg_table_size(schemaname||'.'||tablename) as table_size from pg_tables order by table_size desc;

7) 查看指定schema下所有表大小

select schemaname || '.' || tablename tname, pg_size_pretty(pg_total_relation_size('"' || schemaname || '"."' || tablename || '"')) from pg_tables where schemaname = '模式名' order by pg_total_relation_size('"' || schemaname || '"."' || tablename || '"')  desc ;

8) 查看单张表大小

select pg_size_pretty(pg_table_size('表名')); select tableowner,schemaname,tablename,pg_size_pretty(pg_table_size(schemaname||'.'||tablename)) as table_size from pg_tables where tablename='表名';

9) 查看表分区大小

select pg_size_pretty(pg_partition_size('表名',' 分区名'));

三、查询清理事务槽

1) 查询流复制槽

select pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn), * from pg_replication_slots;

2) 清理流复制槽

xxxdb=# \c xxx

You are now connected to database "xxx" as user "antdb".

xxx=# SELECT pg_drop_replication_slot('test_decoding');

xxx=# checkpoint ;    --- 要执行checkpoint

四、清理归档日志

可以使用pg_archivecleanup  /xxx/xxx目录  最旧文件

通过 pg_controldata $PGDATA 命令查询检查点以前xlog文件，可以清理：如：

 折叠源码

[antdb@opensource-db ~]$ pg_controldata -D /database/antdb pg_controldata: fatal: could not open file "/database/antdb/global/pg_control" for reading: No such file or directory [antdb@opensource-db ~]$ pg_controldata -D /database/antdb/data pg_control version number:            1300 Catalog version number:               202007201 Database system identifier:           7265663343146682289 Database cluster state:               in production pg_control last modified:             Fri 11 Aug 2023 01:18:33 PM CST Latest checkpoint location:           1F/5D1CFFD8 Latest checkpoint's REDO location:    1F/5D1CFFA0 Latest checkpoint's REDO WAL file:    000000010000001F0000005D Latest checkpoint's TimeLineID:       1 Latest checkpoint's PrevTimeLineID:   1 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID:          0:6059616 Latest checkpoint's NextOID:          24577 Latest checkpoint's NextMultiXactId:  1 Latest checkpoint's NextMultiOffset:  0 Latest checkpoint's oldestXID:        628 Latest checkpoint's oldestXID's DB:   1 Latest checkpoint's oldestActiveXID:  6059616 Latest checkpoint's oldestMultiXid:   1 Latest checkpoint's oldestMulti's DB: 1 Latest checkpoint's oldestCommitTsXid:0 Latest checkpoint's newestCommitTsXid:0 Time of latest checkpoint:            Fri 11 Aug 2023 01:18:24 PM CST Fake LSN counter for unlogged rels:   0/3E8 Minimum recovery ending location:     0/0 Min recovery ending loc's timeline:   0 Backup start location:                0/0 Backup end location:                  0/0 End-of-backup record required:        no wal_level setting:                    logical wal_log_hints setting:                on max_connections setting:              15000 max_worker_processes setting:         250 max_wal_senders setting:              64 max_prepared_xacts setting:           15000 max_locks_per_xact setting:           256 track_commit_timestamp setting:       off Maximum data alignment:               8 Database block size:                  8192 Blocks per segment of large relation: 524288 WAL block size:                       65536 Bytes per WAL segment:                16777216 Maximum length of identifiers:        64 Maximum columns in an index:          32 Maximum size of a TOAST chunk:        1996 Size of a large-object chunk:         2048 Date/time type storage:               64-bit integers Float8 argument passing:              by value Data page checksum version:           0 Mock authentication nonce:            6b5a172bd5b19f946299ec8858f522c45266783226874df6aec06a0b9840e561

然后可以使用 g_archivecleanup /database/antdb/data/pg_wal/ 000000010000001F0000005D 清理 之前的日志