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 杀会话

select pg_size_pretty(pg_tablespace_size('pg_default'));

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

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

select pg_size_pretty(pg_database_size('db_hr'));

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;

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

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 ;

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='表名';

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

[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