PostgreSQL 常用运维SQL整理
一、查询并杀会话-- 查询会话
select pid,usename,client_addr,client_port,query_start,query,wait_event from pg_stat_activity;
-- 杀会话
select pg_terminate_backend('pid号');
https://i-blog.csdnimg.cn/direct/28663aa8151e47489dc2290961baf137.png
-- 利用如下命令主动生成杀会话语句
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;
https://i-blog.csdnimg.cn/direct/195f217dff4f46dfaa796a4d00dd7529.png
-- 杀状态是空闲会话
select pg_terminate_backend(pid) from pg_stat_activity where state='idle';
https://i-blog.csdnimg.cn/direct/a78f9538e0df489782789d30fb79e396.png
-- 查询进程杀会话
select pid,state from pg_stat_activity;
-- 然后通过 kill -9 pid 杀会话
https://i-blog.csdnimg.cn/direct/5e4850f462a749c7911d04c7aa68628d.png
二、查看库-表-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;
https://i-blog.csdnimg.cn/direct/8d2ff83d56914600bbdf627f9131c79a.png
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
https://i-blog.csdnimg.cn/direct/8479b0530b894ee19577bdeea72f4dfe.png
四、整理归档日志
可以利用pg_archivecleanup /xxx/xxx目录 最旧文件
通过 pg_controldata $PGDATA 命令查询查抄点从前xlog文件，可以整理：如：
折叠源码
$ pg_controldata -D /database/antdb
pg_controldata: fatal: could not open file "/database/antdb/global/pg_control" for reading: No such file or directory
$ 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 整理 之前的日志
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]