postgresql系统函数

Configuration Settings Functions

• current_setting(setting_name) 查询当前设置
• set_config(setting_name, new_value, is_local) 设置参数

Server Signaling Functions

• pg_cancel_backend(pid int) 结束进程
• pg_terminate_backend(pid int) 强制结束进程
• pg_reload_conf() 重载配置，修改pg_config
• pg_rotate_logfile() 切分当前日志文件

Backup Control Functions

• pg_current_xlog_location() 获取当前事务日志写入位置

postgres=# select pg_current_xlog_location();    pg_current_xlog_location    ------------------------------------------------------    138B/A280A860    (1 row)

• pg_current_xlog_insert_location() 获取当前事务日志插入位置

postgres=# select pg_current_xlog_location(),pg_current_xlog_insert_location();  pg_current_xlog_location | pg_current_xlog_insert_location  --------------------------+---------------------------------  138D/45A84000            | 138D/45AF7300 (1 row)

• pg_xlogfile_name(location pg_lsn) 通过事务日志转换为文件名

postgres=# select pg_xlogfile_name('15D2/32414638');  pg_xlogfile_name       --------------------------  00000001000015D200000032  (1 row)

可以在pg_xlog中找到相应的文件

-bash-4.2$ cd pg_xlog/-bash-4.2$ ll 00000001000015D200000032-rw-------. 1 postgres dba 16777216 Jan 24 18:09 00000001000015D200000032

• pg_xlogfile_name_offset(location pg_lsn) 把事务日志的位置转换为文件名和文件内部十进制字节的偏移量

postgres=# select pg_xlogfile_name_offset('15D2/32414638');      pg_xlogfile_name_offset       ------------------------------------ (00000001000015D200000032,4277816)(1 row)

• pg_xlog_location_diff(location pg_lsn, location pg_lsn) 计算两个事务日志差值

postgres=# select pg_xlog_location_diff(pg_current_xlog_location(),pg_current_xlog_insert_location()); pg_xlog_location_diff -----------------------               -126472(1 row)postgres=#

关于两个参数的区别，贴上官方解释：

pg_current_xlog_location displays the current transaction log write location in the same format used by the above functions. Similarly, pg_current_xlog_insert_location displays the current transaction log insertion point. The insertion point is the "logical" end of the transaction log at any instant, while the write location is the end of what has actually been written out from the server's internal buffers. The write location is the end of what can be examined from outside the server, and is usually what you want if you are interested in archiving partially-complete transaction log files. The insertion point is made available primarily for server debugging purposes. These are both read-only operations and do not require superuser permissions.

大概意思是 pg_xlog_location_diff表示已经写入到wal文件中transaction log事物号。

• pg_is_in_backup() 判断数据库是否正在备份
• pg_backup_start_time() 返回数据库开始备份时间
• pg_switch_xlog() 强制切换wal日志文件

Database Object Management Functions

• pg_database_size(name)
• pg_indexes_size(regclass)
• pg_relation_size(relation regclass)
• pg_size_pretty()
• pg_table_size(regclass) 表大小，不包括索引
• pg_tablespace_size(name)
• pg_total_relation_size(regclass) pg_table_size + pg_indexes_size 表加索引的大小

Advisory Lock Functions