postgres常用的查询语句

1、查询配置文件的位置

show config_file;

2、查询当前参数值和该参数可以配置哪些值

eg:

select name, setting, enumvals, context  from pg_settings where name = 'archive_mode';

postgres=# select name, setting, enumvals, context  from pg_settings where name = 'archive_mode';
     name     | setting |    enumvals     |  context   
--------------+---------+-----------------+------------
 archive_mode | off     | {always,on,off} | postmaster

其中enumvals是参数可以的配置选项，context代表了参数的生效方式

postgres=# select distinct context from pg_settings;
      context     
-------------------
 backend
 user
 internal
 postmaster
 superuser
 sighup
 superuser-backend
(7 rows)

其中
internal: 编译期间的设置，只有重新编译才能生效。
postmaster: 只有服务重启才能生效。
sighup: 不需要重启数据库，但要向postmaster进程发送sighup信号，即需要pg_ctl reload命令
backend:无需重启数据库，只需向postmaster进程发送sighup信号。但新的配置值只能在之后的新连接中生效，已有连接中这些参数值不会改变
superuser: 使用superuser(如postgres)才能更改，不用重新加载所有配置即可生效。这类参数可以由超级用户使用set修改。参数设置后只会影响超级用户自身的session配置，不会影响其他用户。
user: 单个会话用户可以在任意时间做修改，只会影响该会话。
superuser-backend

其中重新加载生效的方式如下：
2.1. 用超级用户运行
postgres=# SELECT pg_reload_conf();

2.2. 用UNIX的kill手动发起HUP信号
$kill -HUP PID

2.3.使用pg_ctl命令触发SIGHUP信号
$pg_ctl reload

 

3、查询主备库角色的方法

3.1 查询pg_is_in_recovery视图，返回f表示主库， 返回t表示从库

3.2 pg_controldata 命令，查看Database cluster state的值，如果是"in production"代表主库，"in archive recovery" 代表是备库的意思

3.3 查询pg_stat_replication视图，如果有返回值的是主库，无返回值的为备库

3.4 查询后台进程，如果有walsender为主库，walreceiver的为备库

4、显示备库最近接收的WAL日志位置(备库查询)

select pg_last_wal_receive_lsn();

5、显示备库最近应用的WAL日志位置(备库查询)

select pg_last_wal_replay_lsn();

6、显示备库最近事物的应用时间(备库查询)

select pg_last_xact_replay_timestamp();

7、显示主库WAL当前的写入位置(主库查询)

select pg_current_wal_lsn();

8、计算两个WAL日志位置的偏移量

select pg_wal_lsn_diff('0/3033760','0/3033759');