PostgreSQL运维常用命令

查看当前连接的用户名

1
2
select * from current_user
select user

给普通用户授权

1
GRANT USAGE ON SCHEMA sch_user TO usr_user;

查询表大小，不含索引

1
2
3
4
5
db_database=> select pg_size_pretty(pg_relation_size('sch_admin.tab_table0'));
 pg_size_pretty 
----------------
 8192 bytes
(1 row)

查询表大小，含索引

1
2
3
4
5
db_database=> select pg_size_pretty(pg_total_relation_size('sch_admin.tab_table0'));
 pg_size_pretty 
----------------
 24 kB
(1 row)

查WAL文件

1
2
3
4
5
6
7
8
9
10
 select *,size/1024/1024 as "MB" from pg_ls_waldir();
           name           |   size   |      modification      | MB 
--------------------------+----------+------------------------+----
 cleanup.log              |      463 | 2022-07-28 09:45:36+08 |  0
 000000010000000800000011 | 16777216 | 2022-08-24 10:10:01+08 | 16
 000000010000000800000012 | 16777216 | 2022-08-24 10:11:05+08 | 16
 000000010000000800000013 | 16777216 | 2022-08-24 09:58:01+08 | 16
 000000010000000800000014 | 16777216 | 2022-08-24 10:02:01+08 | 16
 000000010000000800000015 | 16777216 | 2022-08-24 10:06:01+08 | 16
(6 rows)

查数据库日志

1

PG查表大小

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' 
    || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 20;

            table_full_name            |   size   
---------------------------------------+----------
 xz_schema.ods1_bseg                   | 123 GB
 xz_schema.ods1_coep                   | 15 GB
 xz_schema.fj_voucher                  | 14 GB
 xz_schema.ods1_assets_card            | 12 GB
 xz_schema.ods2_ods_asset_list         | 11 GB
 xz_schema.fj_voucher_mark             | 11 GB
 xz_schema.ods1_anla                   | 10218 MB
 xz_schema.ods2_intf_t_install_account | 9761 MB
 fsti_schema.assets_card               | 8662 MB
 xz_schema.source_anlc                 | 8528 MB
 xz_schema.source_anlp_201901          | 7822 MB
 xz_schema.ods1_bkpf                   | 7485 MB
 xz_schema.ods1_wf_message_done        | 7233 MB
 xz_schema.source_anlp_201909          | 7164 MB
 xz_schema.source_anlp_201908          | 7155 MB
 xz_schema.source_anlp_201907          | 7146 MB
 xz_schema.source_anlp_201906          | 7124 MB
 xz_schema.ods1_anep                   | 7112 MB
 xz_schema.source_anlp_201910          | 7082 MB
 xz_schema.source_anlp_201912          | 7063 MB
(20 rows)