配置PostgreSQL的可观察性

PostgreSQL带有很多配置选项，但是更改其中一些选项的默认设置可以大大提高PostgreSQL服务器的可观察性。您需要在批量生产之前设置和配置这些选项，因为它们可以提供理解和解决这些问题所必需的信息。

继续阅读以了解有关设置和扩展的更多信息，这些设置和扩展公开了有关PostgreSQL服务器内部工作情况的指标和信息。

log_line_prefix配置选项确定PostgreSQL在每条日志行的开头写入什么内容。默认值取决于您使用的特定Linux发行版或托管解决方案，但通常不包含一些对跟踪行为异常的客户端非常有用的项目。试试这个log_line_prefix：

它包括时间戳记（％m），后端进程的PID（％p），客户端的应用程序名称（％a），客户端已连接的用户名（％u），客户端已连接的数据库（％d）以及连接来自（％h）的主机名或IP。结果是这样的日志行：

2021-01-30 05：06：03.675 UTC [73] psql postgres长凳172.17.0.1错误：关系＆＃34; pgbench_akkounts＆＃34;在字符152021-01-30 05：06：03.675 UTC不存在[73] psql postgres bench 172.17.0.1语句：从pgbench_akkounts中选择*；

比默认值有用得多。您可以看到一个客户端以用户postgres的身份从172.17.0.1连接到数据库基准，并且该应用程序是psql。绝对是对默认选项的改进，默认选项仅显示以下内容：

2021-01-30 05：13：22.630 UTC [63]错误：关系＆＃34; pgbench_akkounts＆＃34;在字符152021-01-30 05：13：22.630 UTC [63]声明中不存在：从pgbench_akkounts中选择*；

可以将PostgreSQL配置为记录执行查询所需的时间。它们进入相同的日志文件；没有像MySQL中那样的单独的低速查询日志文件。

要记录执行时间超过1秒的语句，请使用log_min_duration_statement选项，如下所示：

请注意，log_min_duration_statement将考虑所有语句（包括长时间运行的admin语句，例如REINDEX TABLE），而不考虑公正查询（SELECT）。这是此选项产生的一些日志条目：

2021-01-30 08：42：57.473 UTC [62] psql postgres postgres 172.17.0.1 LOG：持续时间：1016.283 ms语句：select pg_sleep（1）; 2021-01-30 08：52：00.541 UTC [62] psql postgres postgres 172.17.0.1 LOG：持续时间：1118.277 ms语句：select pg_sleep（1.1）;

如果这导致太多类似语句的日志，您可以使用以下方法告诉Postgres仅记录其中的一部分：

这仅记录符合记录条件的语句的25％（执行时间超过1秒的语句）。日志输出与以前相同。无法知道没有记录多少合格的语句。

要记录所有语句以及执行它们所花费的时间，请改用log_statement选项：

“ mod”选项告诉Postgres记录DDL和修改数据的语句。结果是这样的日志：

2021-01-30 08：35：08.985 UTC [64] pgbench postgres bench 172.17.0.1 LOG：语句：插入pgbench_tellers（tid，bid，tbalance）值（10,1,0）2021-01-30 08:35 ：08.985 UTC [64] pgbench postgres bench 172.17.0.1 LOG：持续时间：0.241 ms

请注意，无法通过这种方式启用示例日志记录示例，所有语句都将被记录，最终您将获得大量日志条目。

查询可能等待太久才能获取锁。通常情况下，使用选项lock_timeout设置等待时间的上限（通常在客户端）。如果查询已经等待了这么长时间以获取锁，则Postgres将取消此查询的执行并记录错误：

2021-01-30 09：35：52.415 UTC [67] psql postgres testdb 172.17.0.1错误：由于锁定超时而取消语句2021-01-30 09：35：52.415 UTC [67] psql postgres testdb 172.17.0.1陈述：群集;

假设您想将锁定超时时间设置为1分钟，但是将等待锁定的日志记录记录在30秒以上。您可以使用以下方法执行此操作：

2021-01-30 09：49：22.331 UTC [70] psql postgres testdb 172.17.0.1日志：进程70在30009.004 ms之后仍在等待事务493上的ShareLock 2021-01-30 09：49：22.331 UTC [70] psql postgres testdb 172.17.0.1详细信息：持有锁的进程：68。等待队列：70.2021-01-30 09：49：22.331 UTC [70] psql postgres testdb 172.17.0.1背景：在关系＆＃34中锁定元组（0,3）时; t＆＃34; 2021-01-30 09：49：22.331 UTC [70] psql postgres testdb 172.17.0.1语句：从*中选择*进行更新；

使用deadlock_timeout并不是一个错字：它是锁waitlogging机制使用的值。理想情况下，应该有类似log_min_duration_lock_wait之类的东西，但不幸的是，事实并非如此。

如果出现实际的死锁，Postgres将在Deadlock_timeout持续时间之后中止死锁的事务，并将记录有问题的语句。没有明确的配置是必要的。

2021-01-30 09：55：37.724 UTC [68] psql postgres testdb 172.17.0.1日志：进程68在30007.633 ms之后等待事务496上的ShareLock时检测到死锁2021-01-30 09：55：37.724 UTC [68] psql postgres testdb 172.17.0.1详细信息：持有锁的进程：70。等待队列：.2021-01-30 09：55：37.724 UTC [68] psql postgres testdb 172.17.0.1背景：在锁定相关的元组（0,3）时＆＃34; t＆＃34; 2021-01-30 09：55：37.724 UTC [68] psql postgres testdb 172.17.0.1语句：从*中选择*，其中a = 4进行更新； 2021-01-30 09:55： 37.725 UTC [68] psql postgres testdb 172.17.0.1错误：检测到死锁2021-01-30 09：55：37.725 UTC [68] psql postgres testdb 172.17.0.1详细信息：进程68等待事务496上的ShareLock。进程70阻塞。进程70等待事务495的ShareLock；过程68阻止。过程68：从t中选择*，其中a ＝ 4以进行更新；流程70：从*中选择*，其中a = 0进行更新; 2021-01-30 09：55：37.725 UTC [68] psql postgres testdb 172.17.0.1提示：请参阅服务器日志以获取查询详细信息.2021-01-30 09： 55：37.725 UTC [68] psql postgres testdb 172.17.0.1上下文：同时在与＆＃34; t＆＃34; 2021-01-30 09：55：37.725 UTC [68] psql postgres testdb关系中锁定元组（0,3） 172.17.0.1语句：从t中选择*，其中a = 4进行更新；

当Postgres确定表中的数据已充分更改以保证可以进行抽真空和分析时，将启动自动真空处理。要密切注意此过程，请启用自动真空运行记录：

2021-01-30 10：23：33.201 UTC [63]日志：表＆＃34; postgres.public.t＆＃34;的自动清理：索引扫描：0页：删除了0个，保留了95个，由于针脚而跳过了0个，0个跳过的冻结元组：删除了8991，剩余10000个，0个已死但尚未移除，最旧的xmin：492缓冲区使用率：215次命中，4次未中，4个弄脏的平均读取速率：1.885 MB / s，平均写入速率：1.885 MB / s系统使用率：CPU：用户：0.01 s，系统：0.00 s，经过：0.01 s WAL使用：244条记录，1张全页图像，67984字节2021-01-30 10：23：33.222 UTC [63]日志：自动分析表＆＃34; postgres.public.t＆＃34;系统使用率：CPU：用户：0.01 s，系统：0.00 s，经过时间：0.01 s

请注意，自动真空通常会在真空后触发分析，并且也会记录下来。

这些日志将帮助您确定如何最有效地调整自动真空参数，并有助于调查自动真空是否以及何时不如您预期的那样有效。

检查点是将WAL记录的更改推送到支​​持表的实际文件中的过程。理想情况下，检查点应该以规则的间隔而不是太频繁的间隔发生，因为它是CPU和磁盘密集型进程。由于各种原因，检查点也被迫在下一个预定时间之前发生，这导致查询性能降低。

2021-01-30 10：05：57.085 UTC [56]日志：检查点开始：立即强制等待2021-01-30 10：05：57.159 UTC [56] LOG：检查点完成：写了0个缓冲区（0.0％）;添加了0个WAL文件，删除了0个文件，回收了0个文件；写入= 0.000 s，同步= 0.000 s，总计= 0.074 s;同步文件= 0，最长= 0.000 s，平均= 0.000 s；距离= 0 kB，估计= 0 kB

第一行包含后端传递给检查指针的标志。您可以看到，即使没有对检查点的更改，“强制”也会导致检查点。如果未指定“立即”，则检查点将检查点向上到checkpoint_completion_target。

您可以在PostgreSQL配置中打开其他几个设置，这些设置将有助于诊断问题：

track_io_timing-将此设置为on可让您查看每个查询在磁盘I / O上花费的时间（与下面描述的pg_stat_statements扩展组合）。请参阅有关启用此警告的文档，但是在几乎所有现代Linux上都应该是安全的。如果不启用查询功能，就不可能看到查询的磁盘I / O成本。

track_commit_timestamp-将其设置为on在调试复制延迟和其他与复制相关的问题时很有用。

扩展pg_stat_statements是任何PostgreSQL部署的基本附件。它收集并记录执行的每个查询的统计信息，并将其显示为称为“ pg_stat_statements”的视图。这是一个扩展，这意味着您必须使用以下命令在需要数据的每个数据库中显式安装它：

由于扩展名依赖于.so，因此您需要使用shared_preload_libraries加载该扩展名：

不幸的是，这需要重新启动PostgreSQL服务器。因此，请确保在上线之前进行此操作。

如果您是从PostgreSQL的早期版本升级的，请确保也使用以下命令升级pg_stat_statement扩展：

pg_stat_statements扩展不记录任何内容，它是通过查询同名视图来使用的。有关更多详细信息，请参见官方文档。

auto_explain是核心PostgreSQL中的另一个扩展。它可以记录慢查询的执行计划。它仅需要添加到shared_preload_libraries中，并且不需要作为扩展安装。它还具有两个其他选项，通常需要将其设置为非默认值：

shared_preload_libraries =＆＃39; pg_stat_statements，auto_explain＆＃39; auto_explain。 log_min_duration = 1秒auto_explain。 log_analyze =在auto_explain上。 log_buffers =在auto_explain上。 log_triggers =在auto_explain上。 log_timing =在auto_explain上。 log_verbose = on auto_explain。 log_format = JSON

上面记录了所有花费1秒以上才能完成的查询的执行计划。这是一个示例输出：

2021-01-30 11：28：25.977 UTC [64] psql postgres postgres 172.17.0.1 LOG：持续时间：1.305 ms计划：{＆＃34; Query Text＆＃34 ;:＆＃34; SELECT n.nspname as \＆ ＃34; Schema \＆＃34; \ n c.relname为\＆＃34; Name \＆＃34; \ n CASE relkind当＆＃39; r＆＃39; THEN＆＃39;表＆＃39; v＆＃39;然后查看何时然后实体化视图当我THEN＆＃39;索引＆＃39;当＆＃39;然后序列何时然后特殊的当＆＃39;然后＆nbsp;外表＆＃39;何时THEN＆＃39;表＆＃39;当我THEN＆＃39;索引＆＃39; END作为\＆＃34; Type \＆＃34;，\ n pg_catalog.pg_get_userbyid（c.relowner）作为\＆＃34; Owner \＆＃34; \ nFROM pg_catalog.pg_class c \ n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace \ n在哪里c.relkind IN（＆＃39; r＆＃39;，＆＃39; p＆＃39;，＆＃39; v＆＃39;，＆＃39; m＆＃39; ，＆＃39;＆＃39; f＆＃39;，＃39;）\ n AND n.nspname＆lt;＆gt; ＆＃39; pg_catalog＆＃39;和n.nspname＆lt;＆gt; ＆＃39; information_schema＆＃39; \ n ANDn.nspname！〜＆＃39; ^ pg_toast＆＃39; \ n AND pg_catalog.pg_table_is_visible（c.oid）\ nORDER BY 1,2;＆＃34 ;,＆＃34 ; Plan＆＃34 ;: {＆＃34; Node Type＆＃34 ;:＆＃34; Sort＆＃34 ;,＆＃34; Parallel Aware＆＃34 ;: false，＆＃34; Startup Cost＆＃34 ;: 32.93， ＆＃34;总成本＆＃34 ;： 33.01，＆＃34;计划行＆＃34 ;： 32，＆＃34;计划宽度＆＃34 ;： 224，＆＃34;实际启动时间＆＃34 ;： 1.292，＆ ＃34; Actual Total Time＆＃34 ;: 1.298，＆＃34; Actual Rows＆＃34 ;: 0，[...大量文本被截断...]

扩展pg_stat_statements和auto_explain是PostgreSQL在查询性能管理和查询计划管理中仅有的两个受广泛支持的选项。了解这两个功能并提前计划在生产中使用它们是值得的。

应用程序名称是一个客户端参数，通常可以在应用程序用于连接信息的DSN或libpq样式的连接字符串中设置。 PostgreSQL生态系统中的许多工具和实用程序都可以理解应用程序名称，这有助于将其设置为有意义的值，例如：

pgDash是专门针对PostgreSQL部署而设计的现代深度监控解决方案。 pgDash向您显示使用开源工具pgmetrics收集的有关PostgreSQL数据库服务器各个方面的信息和度量标准。

pgDash提供了核心的报告和可视化功能，包括收集和显示PostgreSQL信息以及提供时序图，详细的报告，诊断，警报，团队等等。在此处查看功能或立即注册以免费试用。