PostgreSQL知识分享-第30期

本期整理的几个问题如下：

• 如何提前绑定VIP
• 结果集的元数据如何获取
• 善用psql条件交互式
• 表空间size查询需要什么权限

Q1.监听如何提前绑定VIP？

在高可用环境中，主备节点会发生切换，通常使用虚拟IP（VIP）向外提供写服务，这样对于用户是无感的。

在PG里需要配置listen_addresses为“*”或者“0.0.0.0”(全链路监听)，即监听本机所有网卡上的IP，但这存在遭受恶意连接的风险。

为了进行安全加固，应该避免全链路监听。但是，当绑定一个非本地IP，即网卡没有绑定的IP时，绑定会失败，报错信息为：

Cannot assign requested address

此时，需修改操作系统的配置文件/etc/sysctl.conf:

net.ipv4.ip_nonlocal_bind=1

该配置项默认值为0，表示不允许绑定一个不存在的IP。

参数值修改为1后，执行命令sysctl -p生效，然后重启数据库服务可以成功绑定VIP。

该问题引自闫老师的微信公众号文章：PgSQL-安全加固实践-如何设置非全零监听

Q2.SQL Server数据库set fmtonly on功能在PG里如何替换？

SQL Server数据库可以使用set fmtonly on获取结果集的元数据信息，在PG里可以使用元命令\gdesc来获取。

psql里可以通过?查看它的帮助信息：

\gdesc describe result of query, without executing it

\gdesc不需要实际执行查询，就可以获取结果集的元数据信息，示例如下：

postgres=# select relname,reloptions from pg_class \gdesc
   Column   |  Type  
------------+--------
 relname    | name
 reloptions | text[]
(2 rows)

Q3.psql使用条件表达式让业务逻辑更清晰

下面这个需求是在主库执行一个存储过程

psql -d postgres -AtqXc "select not pg_is_in_recovery();" | grep -qx t || exit; psql -d postgres -qt -c "CALL xxx.proc1(TRUE);"

可以使用psql的条件表达式来让逻辑变得更清晰，改写参考如下：

psql  <<EOF
    select pg_is_in_recovery() as in_recovery \gset
    \if :in_recovery
        \echo -n 'do nothing ...'
    \else
        --call procedure or do something;
    \endif
EOF

Q4.表空间size查询需要什么权限

普通用户可以使用pg_tablespace_size函数查看pg_default默认表空间的size，但无法查看pg_global和其他自定义表空间的size。

postgres=> select pg_tablespace_size('pg_global');
ERROR:  permission denied for tablespace pg_global

上面的查询就提示对pg_global表空间无权限。

从PG 10开始，使用预置角色pg_read_all_stats或者pg_monitor可以解决上面的权限问题，PG 10之前的版本需要使用superuser权限。