How to Interpret and Explore the Postgres Logs

为 API 日志 制作了一本免费指南

通过日志调试和监控 Postgres #

🌐 Debugging and monitoring Postgres with logs

日志可以让你了解 Postgres 的操作情况。它们有助于满足合规要求、发现可疑活动以及排查问题。

🌐 Logs provide insights into Postgres operations. They help meet compliance requirements, detect suspicious activity, and troubleshoot problems.

目录 #

🌐 Table of contents

• 查询日志
  • postgres_logs 表结构
• 筛选日志
  • 日常事件
  • 按时间段
  • 按错误严重性
  • 按查询
  • 按 API/角色
  • 通过 Supabase 仪表板查询
  • 查找错误的完整示例
• 合规与安全的日志记录
• 查看日志设置
• 更改日志设置
  • 严重程度
  • 配置已记录的查询
  • 函数内的日志记录
• 常见问题
• 其他资源

查询日志 #

🌐 Querying logs

探索和筛选日志最实用的方法是通过 日志浏览器。

🌐 The most practical way to explore and filter logs is through the Logs Explorer.

它使用 BigQuery SQL 语法的一个子集，并且会预解析查询以进行优化。这带来了三个主要限制：

🌐 It uses a subset of BigQuery SQL syntax and pre-parses queries for optimization. This imposes three primary limitations:

• 不允许子查询或 WITH 语句
• 列名不能用 * 通配符
• 没有 ILIKE 语句

虽然有很多过滤日志的策略，比如 like 和 in 语句，但一个叫做 regexp_contains 的辅助函数提供了最大的灵活性和控制力。

🌐 Although there are many strategies to filter logs, such as like and in statements, a helper function called regexp_contains provides the most flexibility and control.

postgres_logs 表包含 Postgres 事件。

🌐 The postgres_logs table contains Postgres events.

postgres_logs#

🌐 postgres_logs table structure

这个表格有3个基本列：

🌐 The table contains 3 fundamental columns:

解析后的元数据列是一个包含事件相关信息的数组。要访问这些信息，必须将其展开。这可以使用 cross join 来完成。

🌐 The parsed metadata column is an array that contains relevant information about events. To access the information, it must be unnested. This is done with a cross join.

展开嵌套示例

1
select
2
  event_message,
3
  parsed.<column name>
4
from
5
  postgres_logs
6
-- Unpack data stored in the 'metadata' field
7
cross join unnest(metadata) AS metadata
8
-- After unpacking the 'metadata' field, extract the 'parsed' field from it
9
cross join unnest(parsed) AS parsed;

解析的元数据字段 #

🌐 Parsed metadata fields

查询信息 [#query-information]

🌐 Query information

建议使用场景：

• 识别慢查询
• 识别失败的查询

错误/警告信息 [#errorwarning-information]

🌐 Error/Warning information

建议使用场景：

• 按错误严重性或 SQL 代码筛选
• 获取关于错误事件的提示、细节和上下文

连接/身份信息 [#connectionidentification-information]

🌐 Connection/Identification information

建议使用场景：

• 按服务器/API 识别事件
• 按IP筛选连接
• 识别与特定数据库的连接
• 按会话过滤连接以进行调试
• 识别扩展事件

筛选日志 #

🌐 Filtering logs

不包括日常事件 #

🌐 Excluding routine events

在正常时期，大多数 Postgres 日志都是日常事件，比如连接授权和检查点。要查看默认记录的事件类型，你可以查看这个指南。

🌐 Most Postgres logs during normal periods are routine events, such as connection authorizations and checkpoints. To see the default types of events that are logged, you can check this guide.

在查看日志中异常行为时，通常策略是先过滤掉预期值。可以通过在你的查询中添加以下过滤器来实现：

🌐 When exploring the logs for atypical behavior, it's often strategic to filter out expected values. This can be done by adding the following filter to your queries:

1
...query
2
where
3
  -- Excluding routine events related to cron, PgBouncer, checkpoints, and successful connections
4
  not regexp_contains(event_message, '^cron|PgBouncer|checkpoint|connection received|authenticated|authorized');

按时间框 #

🌐 By timeframe

调查某个特定时期的问题：

🌐 To investigate issues around a specific period:

1
-- filtering by time period
2
...query
3
where
4
  timestamp between '2024-05-06 04:44:00' and '2024-05-06 04:45:00'

按错误严重性 #

🌐 By error severity

这个过滤器会找到所有错误、致命错误和崩溃：

🌐 This filter finds all errors, fatals, and panics:

1
-- find error events
2
... query
3
where
4
  parsed.error_severity in ('ERROR', 'FATAL', 'PANIC')

失败事件包括一个可以在 Postgres 文档 中查阅的 sql_state_code

🌐 Failure events include an sql_state_code that can be referenced in the Postgres Docs

通过查询 #

🌐 By query

注意：除非配置了 pg_audit，否则只会记录失败的查询

1
-- find queries executed by the Dashboard
2
...query
3
where
4
  regexp_contains(parsed.query, '(?i)select . <some table>')

查询可以使用复杂的语法，所以通常通过引用的数据库对象来隔离是很有帮助的，比如 functions、tables 和 columns。由于查询结构可能很复杂，建议使用 正则 来查找匹配项。一些常见的正则模式有：

🌐 Queries can use complex syntax, so it is often helpful to isolate by referenced database objects, such as functions, tables, and columns. Because query structures can be complex, it is advised to use regex to find matches. Some common regex patterns are:

• (?i)：忽略大小写
• .：wildcard
• ^：查找字符串开头的值
• |：或运算符

按 API/角色 #

🌐 By APIs/roles

所有失败的查询，包括来自 PostgREST、Auth 和外部库（例如 Prisma）的查询，都会记录带有有用错误信息的日志以便调试。

🌐 All failed queries, including those from PostgREST, Auth, and external libraries (e.g., Prisma) are logged with helpful error messages for debugging.

服务器/角色对应 #

🌐 Server/Role mapping

API 服务器已经分配了用于连接数据库的数据库角色：

🌐 API servers have assigned database roles for connecting to the database:

按 parsed.user_name 角色筛选，只获取特定角色的日志：

🌐 Filter by the parsed.user_name role to only retrieve logs made by specific roles:

1
-- find events based on role/server
2
... query
3
where
4
  -- find events from the relevant role
5
  parsed.user_name = '<ROLE>'
6
...

通过仪表板查询 #

🌐 By Dashboard queries

来自 Supabase 仪表板的查询是以 postgres 角色执行的，并包含注释 -- source: dashboard。在调试时，如果想隔离或排除仪表板的请求，你可以通过这个注释来过滤。

🌐 Queries from the Supabase Dashboard are executed under the postgres role and include the comment -- source: dashboard. To isolate or exclude Dashboard requests during debugging, you can filter by this comment.

1
-- find queries executed by the Dashboard
2
...query
3
where
4
  regexp_contains(parsed.query, '-- source: dashboard')

查找错误的完整示例 #

🌐 Full example for finding errors

1
select
2
  cast(postgres_logs.timestamp as datetime) as timestamp,
3
  event_message,
4
  parsed.error_severity,
5
  parsed.user_name,
6
  parsed.query,
7
  parsed.detail,
8
  parsed.hint,
9
  parsed.sql_state_code,
10
  parsed.backend_type
11
from
12
  postgres_logs
13
  cross join unnest(metadata) as metadata
14
  cross join unnest(metadata.parsed) as parsed
15
where
16
  regexp_contains(parsed.error_severity, 'ERROR|FATAL|PANIC')
17
  and parsed.user_name = 'postgres'
18
  and regexp_contains(event_message, 'duration|operator')
19
  and not regexp_contains(parsed.query, '<key words>')
20
  and postgres_logs.timestamp between '2024-04-15 10:50:00' and '2024-04-15 10:50:27'
21
order by timestamp desc
22
limit 100;

出于合规和安全的日志记录 #

🌐 Logging for compliance and security

自定义对象和角色活动日志 #

🌐 Customized object and role activity logging

⚠️ 注意：这特别适用于使用 postgres 角色或 自定义角色 与他们的数据库交互的人。使用数据库 REST API 的人应该参考 数据库 API 日志指南。

在记录谁访问了什么时，基于数据库角色和对象的日志记录是确保活动踪迹可靠的最可靠方式。

🌐 When recording what is accessed and by whom, logging based on database roles and objects is the most reliable way to ensure a proper trail of activity.

你可以使用 pg_audit 扩展，根据特定角色对特定数据库对象，选择性地记录相关查询，而不仅仅是错误。

🌐 You can use the pg_audit extension to selectively log relevant queries, not only errors, by certain roles, against specific database objects.

在使用这个扩展时，你应该注意不要记录所有的数据库事件，只记录绝对必要的。过度记录会给数据库带来压力，还会产生大量日志噪音，让筛选相关事件变得困难。

🌐 You should take care when using the extension to not log all database events, but only what is absolutely necessary. Over-logging can strain the database and create log noise that makes it difficult to filter for relevant events.

按 pg_audit 过滤：

1
... query
2
where
3
 -- all pg_audit recorded events start with 'AUDIT'
4
 regexp_contains(event_message, '^AUDIT')
5
  and
6
 -- Finding queries executed from the relevant role (e.g., 'API_role')
7
 parsed.user_name = 'API_role'

按IP筛选 #

🌐 Filtering by IP

如果你是从已知的、有限范围的IP地址连接，应该启用网络限制。

在处理动态地址（例如无服务器或边缘环境的地址）时，监控 IP 会变得棘手。当依赖某些连接池工具时，这个问题会更加突出，比如 Prisma Accelerate、Supavisor 或 Cloudflare 的 Hyperdrive，因为它们记录的是连接池的 IP，而不是实际的来源。

🌐 Monitoring IPs becomes tricky when dealing with dynamic addressing, such as those from serverless or edge environments. This challenge amplifies when relying on certain poolers, such as Prisma Accelerate, Supavisor, or Cloudflare's Hyperdrive, as they record the pooler's IP, not the true origin.

当持续依赖拥有静态IP地址的服务器的直接数据库连接时，IP跟踪是最有效的：

🌐 IP tracking is most effective when consistently relying on direct database connections from servers with static IP addresses:

1
-- filter by IP
2
select
3
  event_message,
4
  connection_from as ip,
5
  count(connection_from) as ip_count
6
from
7
  postgres_logs
8
  cross join unnest(metadata) as metadata
9
  cross join unnest(parsed) as parsed
10
where
11
  regexp_contains(user_name, '<ROLE>')
12
  and regexp_contains(backend_type, 'client backend') -- only search for connections from outside the database (excludes cron jobs)
13
  and regexp_contains(event_message, '^connection authenticated') -- only view successful authentication events
14
group by connection_from, event_message
15
order by ip_count desc
16
limit 100;

查看日志设置 #

🌐 Reviewing log settings

pg_settings 表描述了系统和日志配置。

🌐 The pg_settings table describes system and logging configurations.

1
-- view system variables
2
select * from pg_settings;

影响日志的设置分为以下类别：

🌐 The settings that affect logs are categorized under:

要查看你数据库的所有日志设置，可以执行以下 SQL：

🌐 To view all log settings for your database, you can execute the following SQL:

1
-- view all log related settings
2
select *
3
from pg_settings
4
where
5
  (
6
    category like 'Reporting and Logging / What to Log'
7
    or category like 'Reporting and Logging / When to Log'
8
    or category = 'Customized Options'
9
  )
10
  and name like '%log%';

更改日志设置 #

🌐 Changing log settings

警告：宽松的设置可能会导致过度记录，影响数据库性能，同时在日志中产生噪音。

严重程度 #

🌐 Severity levels

log_min_messages 变量决定了什么程度的问题会被记录。这里是来自 Postgres 文档 的严重性阈值。

🌐 The log_min_messages variable determines what is severe enough to log. Here are the severity thresholds from the Postgres docs.

在大多数情况下，默认设置就足够了。不过，如果你必须调整设置，可以用下面的查询进行：

🌐 In most cases, the default is adequate. However, if you must adjust the setting, you can do so with the following query:

1
alter role postgres set log_min_messages = '<NEW VALUE>';
2
3
-- view new setting
4
show log_min_messages; -- default WARNING

配置已记录的查询 #

🌐 Configuring queries logged

默认情况下，只有失败的查询会被记录。PGAudit 扩展 扩展了 Postgres 内置的日志功能。它可以用来有选择地跟踪你数据库中的所有查询，方法是：

🌐 By default, only failed queries are logged. The PGAudit extension extends Postgres's built-in logging abilities. It can be used to selectively track all queries in your database by:

• role
• session
• 数据库对象
• 整个数据库

在数据库函数中记录日志 #

🌐 Logging within database functions

要跟踪或调试函数，可以按照函数调试指南来配置日志

🌐 To track or debug functions, logging can be configured by following the function debugging guide

常见问题 #

🌐 Frequently Asked Questions

如何合并不同的日志表 #

🌐 How to join different log tables

不，日志表彼此独立，不共享任何用于关联的主键/外键关系。

🌐 No, log tables are independent from each other and do not share any primary/foreign key relations for joining.

如何下载日志 #

🌐 How to download logs

目前，下载日志的方式是通过日志仪表板以 CSV 格式下载

🌐 At the moment, the way to download logs is through the Log Dashboard as a CSV

记录了什么？ #

🌐 What is logged?

要查看默认记录的事件类型，你可以查看这个指南。

🌐 To see the default types of events that are logged, you can check this guide.

其他资源： #

🌐 Other resources:

• 用于过滤日志的正则表达式
• 使用数据库 API 日志进行调试
• 调试数据库功能
• pg_audit
• Supabase 日志
• 自托管日志