Postgres 日志配置

可用的日志设置： #

🌐 Available log settings:

表格列出了可配置的日志设置。有关每个设置的详细信息，请参见对应部分。

🌐 The table lists configurable log settings. See each setting's section for details.

要查看你项目的日志设置，你可以运行：

🌐 To view log settings for your project, you can run:

1
select
2
  name,
3
  setting,
4
  unit,
5
  short_desc,
6
  extra_desc,
7
  context,
8
  enumvals,
9
  reset_val,
10
  case
11
    when sourcefile = '/etc/postgresql-custom/custom-overrides.conf' then 'set by CLI/API'
12
    else 'platform default'
13
  end as configuration_source
14
from "pg_settings"
15
where
16
  category in ('Reporting and Logging / When to Log', 'Reporting and Logging / What to Log')
17
  or (name like 'auto_explain.%' or name like 'pgaudit.%' or name = 'cron.log_statement');

要查看针对特定数据库角色的设置，你可以运行：

🌐 To view settings targeting specific database roles, you can run:

1
select
2
  rolname,
3
  rolconfig
4
from pg_roles
5
where
6
  rolname in (
7
    'anon',
8
    'authenticated',
9
    'postgres',
10
    'service_role'
11
    -- ,<ANY CUSTOM ROLES>
12
  );

配置日志设置 #

🌐 Configuring log settings

有三种可能的方法来更改日志设置：

🌐 There are three potential ways to change log settings:

• Supabase CLI
• Supabase 管理 API
• SQL 命令

后台活动 #

🌐 Background activity

记录 Postgres 后台进程和工具的活动。帮助诊断和发现性能及操作问题。

🌐 Logs the activity of Postgres background processes and utilities. Helps diagnose and detect performance and operational issues.

log_autovacuum_min_duration#

update 和 delete 命令会留下过时的行版本，以支持回滚和并发查询。有一个叫做 Autovacuum 的后台进程会在稍后的批处理任务中永久删除这些行。这个设置会在 Autovacuum 运行时间超过限制时进行记录。

适用场景：

• 监控吸尘器活动
• 识别由真空引起的资源压力，比如IO使用

示例日志：

1
# records tables vacuumed
2
automatic vacuum of table "postgres.public.vac_test": index scans: 0
3
automatic analyze of table "postgres.public.vac_test"

log_checkpoints#

检查点是将内存中修改过的数据写入磁盘的后台操作。它使 Postgres 可以丢弃那些本来必须保留用于数据恢复和复制的 WAL 文件。

🌐 Checkpoints are background operations that write modified data from memory to disk. It enables Postgres to discard WAL files that otherwise must be retained for data recovery and replication.

该设置会记录自动检查点事件。

🌐 The setting records automatic checkpoint events.

适用场景：

• 测量检查点写入量
• 识别过多的检查点相关磁盘活动
• 检测只读副本问题
• 决定是否应该调整检查点设置

示例日志：

1
# Monitoring checkpointer activity
2
checkpoint starting: time
3
checkpoint complete: wrote 405563 buffers (6.4%); 0 WAL file(s) added, 0 removed, 465 recycled; write=269.656 s, sync=3.570 s, total=274.133 s; sync files=2393, longest=0.375 s, average=0.002 s; distance=6635965 kB, estimate=7953512 kB

1
# Monitoring replay activity from read replicas
2
restartpoint starting: time
3
recovery restart point at 0/B837D6F0
4
restartpoint complete: wrote 266 buffers (0.4%); 0 WAL file(s) added, 1 removed, 0 recycled; write=25.760 s, sync=0.004 s, total=25.773 s; sync files=20, longest=0.003 s, average=0.001 s; distance=19779 kB, estimate=565114 kB; lsn=0/B837D748, redo lsn=0/B837D6F0

log_lock_waits#

当操作因为数据库锁被阻塞超过 1s 时进行日志记录。关于锁管理的更多信息，请参考 postgreslocksexplained.com。

🌐 Logs when operations are blocked by database locks for more than 1s. For more information on lock management, reference postgreslocksexplained.com.

适用场景：

• 识别被锁阻止的查询
• 找出哪些查询被阻塞
• 测量查询被阻塞的时间有多长

示例日志：

1
# records when a process is waiting on a lock for 1+s
2
process 1017208 still waiting for "lock_type" on relation 75874 of database 5 after 1001.872 ms

1
# records when a process is finally able to claim its lock
2
process 1007982 acquired "lock_type" on transaction 445264 after 2000.880 ms

log_recovery_conflict_waits#

如果一个只读副本正在操作被主节点修改或丢弃的数据，那么它可能会等一下，以确定这些数据是否应该可用，然后再做出响应。设置 log_recovery_conflict_waits 决定副本是否应该报告持续时间超过 1s 的等待。

🌐 If a read-replica is acting on data that is being modified/discarded by the primary, then it may wait to determine if the data should be available or not before responding. The setting log_recovery_conflict_waits determines if the replica should report waits that last more than 1s.

适用场景：

• 检测干扰副本查询的操作
• 检测可能导致复制延迟的操作

示例日志：

1
recovery still waiting after 1000.156 ms: recovery conflict on lock

log_startup_progress_interval#

当服务器从崩溃中恢复时，它必须经过几个检查才能再次正常运行。为了让恢复的进度更清楚，这个设置会让 Postgres 在启动任务如果花费时间超过设定间隔时记录当前的启动任务。

🌐 When a server is recovering from a crash, it has to go through several checks before it becomes operational again. To provide more clarity about a recovery's progress, the setting causes Postgres to log its current startup task if it takes longer than the interval.

适用场景：

• 确定服务器在启动/恢复期间是否有响应

示例日志：

1
syncing data directory (pre-fsync), elapsed time: 0.00 s, current path: ./base/4/13456

log_temp_files#

有些查询需要排序、哈希或其他占用大量内存的操作。当这些操作超出主要由 work_mem 和 hash_mem_multiplier 设置管理的内存限制时，Postgres 会使用磁盘上的临时文件来完成它们。

🌐 Some queries require sorting, hashing, or other memory-intensive operations. When these operations exceed the memory limits primarily managed by the work_mem and hash_mem_multiplier settings, Postgres uses temporary files on disk to complete them.

当创建的临时文件大于 log_temp_files 限制时，Postgres 会记录该事件，帮助识别可以通过内存调优获益的查询。

🌐 When temp files larger than the log_temp_files limit are created, Postgres logs the event, helping identify queries that can benefit from memory tuning.

适用场景：

• 确定什么时候应该调整内存限制设置
• 识别由临时文件引起的磁盘压力

示例日志：

1
# records the creation of a temp file that is 8.33MB in size
2
temporary file: path "base/pgsql_tmp/pgsql_tmp306918.0", size 8331264

网络监控 #

🌐 Network monitoring

记录关于客户端连接/断开数据库的信息。一些可以捕获的有用数据包括：

🌐 Logs information about clients connecting/disconnecting from the database. Some insightful values that can be captured include:

• 当客户端第一次验证时
• 他们连接了多久
• 他们的IP地址

log_connections#

当客户端建立新的数据库连接时记录日志，包括接收连接、身份验证和授权。

🌐 Logs when a client establishes a new database connection, including connection receipt, authentication, and authorization.

适用场景：

• 监控成功的身份验证尝试
• 审计数据库访问

示例日志：

1
connection received: host=127.0.0.1
2
connection authorized: user=postgres database=postgres application_name=Supavisor auth_query
3
connection authenticated: identity="pgbouncer" method=scram-sha-256

log_disconnections#

当数据库连接正常关闭时记录日志。

🌐 Logs when a database connection gracefully closes.

适用场景：

• 监控连接持续的时间

示例日志：

1
disconnection: session time: 0:00:01.492 user=postgres database=postgres host=127.0.0.1

查询活动 #

🌐 Query activity

记录查询或查询的元数据。

🌐 Records queries or metadata about queries.

cron.log_statement#

记录 pg_cron 扩展 启动 cron 任务时的情况。

🌐 Logs when the pg_cron extension starts a cron job.

适用场景：

• 监控成功的定时任务执行情况

示例日志：

1
cron job 1 starting: select 1

auto_explain.*#

auto_explain 是一个安装在所有 Supabase 项目的 Postgres 模块。它会记录执行时间超过设置限制的查询语句和 执行计划。

适用场景：

• 监控和优化慢查询

示例日志：

1
duration: 1661.934 ms plan: Query Text: SELECT * FROM example;
2
Seq Scan on example (cost=0.00..1443.00 rows=100000 width=36)

log_duration#

它会记录所有查询的持续时间，但不会记录查询内容。

🌐 It logs the duration of all queries, but not the queries themselves.

适用场景：

• 监控查询耗时

示例日志：

1
duration: 0.599 ms

注意，尽管查询不会被记录，但与查询关联的主要命令会被记录在 command 子字段中：

🌐 Note, even though the query will not be logged, the primary command associated with the query will be recorded in the command subfield:

1
...other subfields
2
command_tag: "SELECT"

log_min_duration_statement#

它跟 auto_explain.log_min_duration 类似，但更轻量。它只记录运行时间超过设置限制的查询语句。

🌐 It is similar to auto_explain.log_min_duration, but it lighter weight. It only logs query statements that run longer than the setting's limit.

适用场景：

• 监控和优化慢查询

示例日志：

1
duration: 1.097 ms statement: select * from example_table limit 100;

log_min_error_statement#

当一个事件被记录时，除了主要信息外，还会捕获多个子字段，比如 状态码。log_min_error_statement 字段决定是否也应该在 query 子字段下记录生成该日志的查询。

🌐 When an event is logged, beyond the primary message, multiple subfields are also captured, such as the status code. The log_min_error_statement field determines if the query responsible for the log should be recorded, too, under the query subfield.

如果事件的严重程度与 log_min_error_statement 相同或更高，查询将会被捕获。要查看不同的严重级别，请参考 log_min_messages。

🌐 If the event is equally or more severe than log_min_error_statement, the query will be captured. To view the varying severity levels, reference log_min_messages.

适用场景：

• 检测哪些查询导致了特定的日志
• 检测哪些查询导致了特定错误

示例日志：

1
duplicate key value violates unique constraint "example_pkey"
2
...
3
# affiliated subfield
4
query: "insert into example (id) values (1), (1);

log_min_messages#

根据下面的表格，决定根据严重等级记录哪些 query 生成的 日志（不包括后台或网络日志）。

🌐 Determines what query generated logs (not background or networking logs) are recorded based on severity level, as described in the table below.

举个例子来说，log_min_messages 是如何工作的，如果设置改为 error，Postgres 就会停止记录严重级别为 warning、notice、info 和 debug1 ... debug5 的日志事件。不过，它仍然会继续记录所有 error、log、fatal 和 panic 的发生情况。

🌐 As an example of how log_min_messages works, if the setting were changed to error, Postgres would stop recording logs with the severity levels warning, notice, info, and debug1 ... debug5 events. However, it would continue recording all error, log, fatal, and panic occurrences.

适用场景：

• 控制整体上记录哪些 query 生成的日志

log_statement#

记录符合配置操作类型的查询：

🌐 Logs queries that match the configured action type:

• ddl：记录所有 alter、drop 和 create 命令
• all：记录所有查询
• mod：记录 update、insert、delete 和 merge 命令
• none：不记录任何内容（禁用此设置）

适用场景：

• 监控你平台上的查询

示例日志：

1
# Logging a select query
2
statement: select * from testing WHERE id = 5 limit 100;

pgaudit.*#

一套由 pgAudit 扩展 启用的日志设置。与 log_statement 不同，它允许你对特定表的查询进行监控，粒度更高。

🌐 A suite of log settings enabled by the pgAudit extension. Unlike log_statement, it allows you to monitor queries against specific tables, with a higher degree of granularity.

适用场景：

• 监控你平台上的查询

示例日志：

1
# Logging a DDL query
2
AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account(
3
  id int,
4
  name text,
5
  description text
6
); <not logged>

配置方法：

查看 pgAudit 文档 获取更多配置细节。

🌐 Review the pgAudit docs for more configuration details.

资源 #

🌐 Resources

• 高级日志过滤
• 数据库功能日志
• Supabase 日志