本期问答如下:
- 如何只备份序列
- 对默认权限的误解
- 自动展延用户有效期
- query字段被置null
- 使fetch参数避免客户端内存耗尽
Q1.pg_dump如何只备份序列
问题描述
在做数据库备份恢复时,如何只备份序列或者单独恢复序列呢。
问题解答
pg_dump可以使用-t或者–table对表进行过滤备份,支持正则匹配。序列其实也是表,也可以通过-t或者–table来进行导出。
另外在恢复时也可以使用备份清单,先使用-l, --list生成清单文件,在对清单文件过滤SEQUENCE,仅恢复序列。
Q2.对默认权限设置的误解
问题描述
当我们想要批量管理一些对象的权限时,在14版本之前没有全局的读或写预置角色可以使用,此时需要分两次操作:
- 现有对象使用grant赋权
- 将来创建的对象使用ALTER DEFAULT设置默认权限
不过我们对默认权限进行设置时,可能会存在误解。
问题解答
使用ALTER DEFAULT设置对象默认权限时可以设置FOR ROLE以及IN SCHEMA子句,这两个子句可以默认不设置或只设置某一个。
对于默认行为我们需要关注:
- FOR ROLE如果不设置
那我们的操作实际是FOR ROLE current user,也就是当前操作用户创建的对象,这一点不容易被理解,犹如我们可能忘记操作是在哪个database下。
- IN SCHEMA如果不设置
所有schema下创建的对象都会生效,理解这一点对于revoke反向操作尤为重要。
Q3.当用户修改密码之后如何自动延展有效期
问题描述
对数据库用户设置了有效期,有什么版本可以在用户改完密码后自动延展三个月有效期呢?
问题解答
可以使用事件触发器的功能来实现,参考文档如下
https://www.postgresql.org/docs/current/sql-createeventtrigger.html
用ddl_command_end事件捕获修改密码之后,自动对有效期进行延展。
Q4.为什么pg_stat_statements的query值都是null
pg_stat_statements是深入分析PG数据库性能的一个插件,从11版本开始它提供了归一化的查询ID。
相比pg_stat_activity里的query,pg_stat_statements里的一些query会使用磁盘进行存储而不会消耗共享内存,但如果累积的长query过于巨大,外部磁盘存储文件可能会无法管理。
此时pg_stat_statement插件会选择丢弃query,同时pg_stat_statement视图中现有数据的query也会被置成null,不过query相关的统计信息是保留的,只是query被丢弃。
使用pg_stat_statements_reset()可以重置恢复。
Q5.如何防止客户端一次性查询结果集过大
当客户端向数据库发送请求时,如果SQL查询结果集很大,可能会把客户端的内存耗尽,因为服务端默认会把整个结果集全返回。
当使用FDW进行远程访问时,从远程服务端日志可观测到固定是按100进行fetch;
从9.6版本开始可以在create server或者create foreign table时设置fetch_size参数。
在psql访问时可以使用\set FETCH_COUNT来设置fetch的值来预防内存耗尽。
JDBC里同样可以设置fetchSize参数来预防内存耗尽。