postgresql系统函数

最新推荐文章于 2025-04-02 17:22:40 发布

weixin_33849942

最新推荐文章于 2025-04-02 17:22:40 发布

阅读量996

点赞数

文章标签：数据库

原文链接：https://yq.aliyun.com/articles/398145

版权

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_xlogfile_name_offset数据库实例最后的transaction log，可能还未写入到wal文件中。