Q.PostgreSQL里recovery模式和standby模式有什么区别？
问题描述
PostgreSQL里经常能看到recovery模式和standby模式，这两种模式有什么区别呢？
问题解答
recovery模式是一种恢复应用WAL的过程状态，standby模式是主从架构下的一种只读模式，可以提供查询。
Q.PostgreSQL里是否可以单独做归档备份？
问题描述
PostgreSQL备份数据除了使用pg_basebackup进行基础备份以外，是否可以只备份归档文件，从而节省磁盘空间呢？
问题解答
可以使用pg_receivewal的工具在线备份wal起到虚拟备库的功能，它比使用归档命令备份WAL更加安全，也不需要等待WAL文件写满。该工具基于流复制协议流式传输WAL，当服务器重启时不会发生数据丢失或数据损坏，并且搭配-Z/–compress压缩选项更适合。
Q.使用pg_cron定时任务插件时，为什么任务延迟了8个小时呢？
问题描述
我的定时任务使用了pg_cron插件，发现任务都延时了8个小时，为什么呢？
问题解答
pg_cron插件采用GMT时间，数据库的时区不一致因此体现出延迟。
Q."oldest xmin is far in the pass"这个告警信息是什么原因引起？
问题描述
数据库日志中大量出现"oldest xmin is far in the pass"的告警信息，是什么原因引起呢？
问题解答
经过排查分析得知数据库中存在失效的逻辑复制槽，导致autovacuum不能及时回收旧事务，致使WAL日志占据的磁盘空间越来越大。大量的warning事件也引起数据库日志文件迅速增长，日志占据的磁盘空间也越来越大。
解决方案：与业务确认复制槽使用情况，如需继续使用，需要立即进行修复。如确认不再使用，及时使用pg_drop_replication_slot函数删除。
Q.数据库日志频繁记录“incomplete startup packet”
问题描述
PostgreSQL 10主备同步进程出现故障，观察主库错误日志信息看，有大量的“incomplete startup packet”信息，以及磁盘空间不足的提示。
问题解答
频繁的客户端连接使用了不规范的消息格式导致数据库服务器大量记录“incomplete startup packet”到日志文件，引起磁盘空间写满。
详细分析及建议可以参考：预防客户端bad connection连接
Q.PITR配置恢复相关的参数是否可以配置在触发文件里？
问题描述
PostgreSQL 12之前的recover.signal或者12开始的standby.signal文件是否可以配置restore_command等相关参数，还是需要同时配置recovery.conf或者postgresql.conf。
问题解答
触发文件不需要配置，一般是一个空文件，如果配置恢复相关的参数，不会起作用，正确的方式是配置在recovery.conf文件(版本<12)，或者postgresql.conf(版本>=12)。
Q.DDL如何防止SQL注入？
问题描述
JDBC可以使用PreparedStatement对DML进行语句预编译来防注入，但DDL是失效的。
问题解答
可以采用动态语句变通实现，使用函数封装：
CREATE OR REPLACE FUNCTION public.f_enum_add_val(enmu_val character varying)
RETURNS void
LANGUAGE plpgsql
AS $function$
begin
execute format('alter type currency add value %L ',enmu_val);
end;
$function$;
Q.游标可以跨事务使用吗？
问题描述
通常游标只能存在于数据库transaction范围内：因为游标就是单一的SQL语句，SQL语句总是事务的一部分，游标在事务结束后会自动关闭。但有些场景是否可以跨transaction使用游标呢？
问题解答
PostgreSQL 11开始对procedure支持WITH HOLD特性的游标，使用WITH HOLD，可以突破单个transaction这个限制，这个特性比较有用，但我们必须要考虑commit对性能的影响，并且记得close游标，释放资源。
Q.子事务有什么危害？
问题描述
子事务对应的功能是savepoint，即使不显式使用savepoint，存储过程中的begin… exception块也会隐式的引入子事务，那么子事务有什么危害？
问题解答
子事务的危害：
- 事务ID快速增长，增加事务ID回卷风险
- 逻辑订阅场景下，可能产生大量临时小文件(逻辑复制槽目录过快产生.spill落盘文件)
- 单个会话中子事务数超过64后，进入suboverflow状态，高并发场景下可能导致数据库整体性能大幅下降
建议谨慎使用子事务，特别是在大事务，长事务中。
Q. 如何修改客户端编码为GBK？
问题描述
一些场景下，如何永久将client_encoding由默认的UTF8修改为GBK？试过使用alter user … set client_encoding或者alter database…set client_encoding都不生效。
问题解答
参考官方文档的这段描述
If both standard input and standard output are a terminal, then psql sets the client encoding to “auto”, which will detect the appropriate client encoding from the locale settings (LC_CTYPE environment variable on Unix systems). If this doesn’t work out as expected, the client encoding can be overridden using the environment variable PGCLIENTENCODING.
使用环境变量PGCLIENTENCODING可以解决：
export PGCLIENTENCODING='GBK'
Q. 如何强制删除数据库？
问题描述
PostgreSQL中在持续有客户端连接的情况下，是否可以强制删除数据库呢？
问题解答
PostgreSQL 13对DROP DATABASE添加了一个FORCE选项，允许在有客户端连接的情况下强制删除数据库，语法如下：
DROP DATABASE database_name WITH (FORCE);
注意：如果目标数据库中存在prepared transaction、活动的逻辑复制槽或subscriptions，则 DROP DATABASE强制删除会失败。
Q.一个事务频繁更新同一行有什么危害？
问题描述
在PostgreSQL中，在一个事务中频繁更新同一行有什么危害？
问题解答
生产环境中在一个事务里频繁对单行数据进行更新操作会引起vacuum问题，此时基于触发器统计更新数据会变得越来越慢，一个较好的方式可以使用触发器的过渡表特性，使用一条语句进行批量处理。
https://www.cybertec-postgresql.com/en/why-are-my-postgresql-updates-getting-slower/
Q.combined indexes 和 multicolumn indexes有什么区别？
问题描述
什么时候使用combined indexes(组合索引)，什么时候使用multicolumn indexes(多列索引)？
问题解答
多列索引是指一个索引定义在表的多个列上，例如
CREATE INDEX idx_test_multi ON test (a, b);
组合索引是指查询时可以使用多个索引，例如下面的语句：
postgres=# explain (costs off) select * from t where a <= 100 and b = 'x';
QUERY PLAN
--------------------------------------------------
Bitmap Heap Scan on t
Recheck Cond: ((a <= 100) AND (b = 'x'::text))
-> BitmapAnd
-> Bitmap Index Scan on t_a_idx
Index Cond: (a <= 100)
-> Bitmap Index Scan on t_b_idx
Index Cond: (b = 'x'::text)
(7 rows)
从执行计划观察查询规划器执行两个位图索引扫描，然后通过BitmapAnd将它们组合成用于扫描堆表。
基于组合索引的位图扫描虽然高效，但每次额外的位图扫描都会增加总查询的成本。多列索引更有针对性，通常返回的结果也更少，这意味着更高效的扫描效率。总体来说多列索引适用于查询结果少，组合索引更针对复杂查询。
关于使用索引的扩展阅读推荐： <<[译] 通过Postgres索引创建理解操作符类及索引类型等概念>>
Q.character类型有哪些别名？
问题描述
PostgreSQL里character类型有哪些别名？
问题解答
PostgreSQL里character类型可以使用char(n)和bpchar(n) (blank-padded char)，bpchar是数据库内部使用的一种类型，使用char、bpchar类型，数据库都会转换为character类型。
Q.使用with查询，对数据库的性能有什么影响？
问题描述
PostgreSQL 研发的人员经常使用with查询，这个操作有什么影响吗？
问题解答
首先with查询是一个标准用法，PostgreSQL、MySQL、Oracle、SQL Server等都支持。
with子句可以看作是一个查询语句在内存中的临时表。在内存中解析，提高执行效率，并且提高SQL语句的可读性，用完即销毁。
with查询常见使用场景的递归查询，比如查询动态菜单树。对性能的影响要看具体语句写法，通常对复杂的语句是可以提速的。
Q.PostgreSQL秒能精确到纳秒吗？
问题描述
PostgreSQL里时间类型timestamp能精确到纳秒吗？
问题解答
时间类型timestamp只能存储到微秒，同时SQL标准2016定义了FF1-FF6时间格式，从PostgreSQL 13开始得到了支持，FF1-FF6表示时间数据类型秒后的第一位到第六位，比如FF6就是最高精度微秒。
postgres=# SELECT now(),
to_char(now(),'HH24:MI:SS.FF1'),
to_char(now(),'HH24:MI:SS.FF2'),
to_char(now(),'HH24:MI:SS.FF3'),
to_char(now(),'HH24:MI:SS.FF4'),
to_char(now(),'HH24:MI:SS.FF5'),
to_char(now(),'HH24:MI:SS.FF6');
-[ RECORD 1 ]--------------------------
now | 2022-09-25 08:53:18.443337+08
to_char | 08:53:18.4
to_char | 08:53:18.44
to_char | 08:53:18.443
to_char | 08:53:18.4433
to_char | 08:53:18.44333
to_char | 08:53:18.443337
Q.PostgreSQL物理备份和恢复的两台机器版本不一致，能否恢复成功？
问题描述
例如用pg_basebackup在14版本备份了一个实例，能否恢复到版本12的机器上，或者12版本用pg_basebackup备份，能否恢复到14版本的机器上。
问题解答
不可以，物理备份恢复的大版本必须匹配。PostgreSQL有版本碰撞机制，启动数据目录时会进行检查，另外在同一个大版本的不同beta版本也会有这个触发机制。
Q.如何定义大对象和如何查看一个库中有哪些表使用大对象类型的字段？
问题描述
PostgreSQL中如何使用大对象，如何查找哪些表的哪些字段使用了大对象类型。
问题解答
PostgreSQL中大对象需要使用lo类型，可以参考这篇文章：PostgreSQL二进制类型存取测试。
查找大对象类型可以使用如下语句：
select attrelid ::regclass, attname, atttypid ::regtype
from pg_attribute
where attnum > 0
and attisdropped = 'f'
and atttypid='lo'::regtype;
Q.主备延迟大，有什么方法可以加快备库回放的速度？
问题描述
逻辑复制场景下，主备延迟较大，有什么方法可以加快备库回放的速度？
问题解答
有两个方法可以尝试：
- 增大逻辑解码的内存参数logical_decoding_work_mem
- 使用PostgreSQL 14的streaming特性开启订阅
Q.创建数据库会触发检查点吗？
问题描述
在我们创建数据库的过程中，会触发检查点将缓冲区的脏数据刷写到存储吗？
问题解答
PostgreSQL 15之前创建数据库会立刻触发检查点，数据库日志会看到如下的片段信息：
checkpoint starting: immediate force wait flush-all
PostgreSQL 15创建数据库时新增了strategy参数，该参数的默认值是wal_log，也就是对模板数据库数据文件通过写wal的方式逐个拷贝数据块。这种方式可以避免立刻触发检查点。
