lightdb/PostgreSQL查看数据库，索引，表，表空间大小，事务和LSN（管理函数）

　　postgresql/lightdb和mysql、oracle不一样，通过一堆表函数提供查看各个对象的大小。

　　如查看每个表的物理位置、所属表空间和数据库：

pg_relation_filepath(relation regclass) text 返回关系的物理路径，不支持TOAST，可以通过pg_class查询。

zjh@test=# select relname,reltablespace,current_database(),setting || '/' || pg_relation_filepath(relname::text) from pg_class,(select setting from pg_settings where name='data_directory') where relkind='r' and relisshared='f' limit 5;
relname | reltablespace | current_database | ?column?
-----------------------+---------------+------------------+--------------------------------------
t | 0 | test | /mnt/pmem1/zjh/data/base/16397/16398
pg_statistic | 0 | test | /mnt/pmem1/zjh/data/base/16397/2619
pg_type | 0 | test | /mnt/pmem1/zjh/data/base/16397/1247
pg_foreign_table | 0 | test | /mnt/pmem1/zjh/data/base/16397/3118
pg_statistic_ext_data | 0 | test | /mnt/pmem1/zjh/data/base/16397/3429
(5 rows)

数据库对象尺寸函数

函数名	返回类型	描述
pg_column_size(any)	int	存储一个指定的数值需要的字节数（可能压缩过）
pg_database_size(oid)	bigint	指定OID的数据库使用的磁盘空间，不包括索引和TOAST
pg_database_size(name)	bigint	指定名称的数据库使用的磁盘空间，不包括索引和TOAST
pg_indexes_size(regclass)	bigint	关联指定表OID或表名的表索引的使用总磁盘空间
pg_relation_size(relation regclass, fork text)	bigint	指定OID或名的表或索引，通过指定fork('main', 'fsm' 或'vm')所使用的磁盘空间
pg_relation_size(relation regclass)	bigint	`pg_relation_size(..., 'main')的缩写`
pg_size_pretty(bigint)	text	Converts a size in bytes expressed as a 64-bit integer into a human-readable format with size units
pg_size_pretty(numeric)	text	把以字节计算的数值转换成一个人类易读的尺寸单位
pg_table_size(regclass)	bigint	指定表OID或表名的表使用的磁盘空间，除去索引（但是包含TOAST，自由空间映射和可视映射）
pg_tablespace_size(oid)	bigint	指定OID的表空间使用的磁盘空间
pg_tablespace_size(name)	bigint	指定名称的表空间使用的磁盘空间
pg_total_relation_size(regclass)	bigint	指定表OID或表名使用的总磁盘空间，包括所有索引和TOAST数据

 public     | bmsql_order_line                     | table    | lightdb | permanent   | 233 GB     | 
 public     | bmsql_stock                          | table    | lightdb | permanent   | 40 GB      | 
 public     | bmsql_warehouse                      | table    | lightdb | permanent   | 61 MB      | 
(175 rows)

lightdb@benchmarksql1000=#  select pg_size_pretty(pg_total_relation_size('bmsql_order_line'));
 pg_size_pretty 
----------------
 296 GB
(1 row)

查看数据库的事务ID

zjh@postgres=# select pg_current_xact_id();
 pg_current_xact_id 
--------------------
            2594753
(1 row)

zjh@postgres=# select pg_current_xact_id_if_assigned();
 pg_current_xact_id_if_assigned 
--------------------------------
                               
(1 row)

zjh@postgres=# select pg_xact_status(pg_current_xact_id()::xid8);
 pg_xact_status 
----------------
 in progress
(1 row)

zjh@postgres=# select pg_current_snapshot();
 pg_current_snapshot 
---------------------
 2594755:2594755:
(1 row)

查看当前LSN和最新的WAL文件

　　有些时候除了事务ID，还需要知道当前的最新LSN和WAL文件。如逻辑复制设置起始和结束点的时候。

zjh@postgres=# select * from pg_control_checkpoint();
 checkpoint_lsn |  redo_lsn  |      redo_wal_file       | timeline_id | prev_timeline_id | full_page_writes |  next_xid  | next_oid | next_multixact_id | next_multi_offset | oldest_xid | olde
st_xid_dbid | oldest_active_xid | oldest_multi_xid | oldest_multi_dbid | oldest_commit_ts_xid | newest_commit_ts_xid |    checkpoint_time     
----------------+------------+--------------------------+-------------+------------------+------------------+------------+----------+-------------------+-------------------+------------+-----
------------+-------------------+------------------+-------------------+----------------------+----------------------+------------------------
 B/CB7DAA48     | B/CB7DAA10 | 000000010000000B00000006 |           1 |                1 | t                | 0:73587543 |    24700 |                 1 |                 0 |        479 |     
          1 |          73587543 |                1 |                 1 |                    0 |                    0 | 2022-06-04 00:10:34+08

　　因为checkpoint除非在写检查点的时候，否则它是异步的。所以next_xid、redo_lsn会比pg_current_xact_id()以及pg_current_wal_lsn()要稍微滞后。

查看最新额LSN以及对应的文件，如下：

zjh@mydb_for_ora=# select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());
pg_current_wal_lsn | pg_walfile_name | pg_walfile_name_offset
--------------------+--------------------------+--------------------------------------
0/30D880E0 | 000000010000000000000001 | (000000010000000000000001,282624224)

查看表的最后修改时间

zjh@postgres=# select pg_stat_file(setting || '/' || pg_relation_filepath(relname::text)) from pg_class,(select setting from pg_settings where name='data_directory') where relkind='r' limit 5;
pg_stat_file
--------------------------------------------------------------------------------------------
(0,"2022-05-28 22:30:15+08","2022-05-28 22:30:15+08","2022-05-28 22:30:15+08",,f)
(0,"2022-05-31 21:45:31+08","2022-05-31 21:45:31+08","2022-05-31 21:45:31+08",,f)
(172032,"2022-06-03 18:14:29+08","2022-06-03 18:40:33+08","2022-06-03 18:40:33+08",,f)
(81920,"2022-06-04 14:00:13+08","2022-06-03 18:40:32+08","2022-06-03 18:40:32+08",,f)
(2439987200,"2022-05-28 20:19:57+08","2022-05-28 20:38:36+08","2022-05-28 20:38:36+08",,f)

因为数据修改后，只保证WAL会刷新，数据文件不一定会更新，所以通过stat判断其实并不准确，只能作为参考。

详见：https://www.light-pg.com/docs/lightdb/13.3-22.2//functions-info.html

虽然也分类了，但是缺少了一级标签。LightDB 22.1 增加了补全。

https://www.light-pg.com/docs/lightdb/13.3-22.2//functions-admin.html

管理类分为配置、进程管理、备份、恢复、复制管理、目录相关类别。

9.27.1. Configuration Settings Functions

9.27.2. Server Signaling Functions

9.27.3. Backup Control Functions

9.27.4. Recovery Control Functions

9.27.5. Snapshot Synchronization Functions

9.27.6. Replication Management Functions

9.27.7. Database Object Management Functions