Azure Database for PostgreSQL - 单一服务器中的日志Logs in Azure Database for PostgreSQL - Single Server

Azure Database for PostgreSQL 允许配置和访问 Postgres 的标准日志。Azure Database for PostgreSQL allows you to configure and access Postgres's standard logs. 这些日志可用于识别、排除和修复配置错误和性能不佳问题。The logs can be used to identify, troubleshoot, and repair configuration errors and suboptimal performance. 可以配置和访问的日志信息包括错误、查询信息、autovacuum 记录、连接和检查点。Logging information you can configure and access includes errors, query information, autovacuum records, connections, and checkpoints. (无法访问事务日志)。(Access to transaction logs is not available).

审核日志是通过 Postgres 扩展 pgaudit 提供的。Audit logging is made available through a Postgres extension, pgaudit. 有关详细信息,请访问审核的概念一文。To learn more, visit the auditing concepts article.

配置日志记录Configure logging

可以使用日志记录服务器参数在服务器上配置 Postgres 标准日志记录。You can configure Postgres standard logging on your server using the logging server parameters. 在每个 Azure Database for PostgreSQL 服务器上,默认已启用 log_checkpointslog_connectionsOn each Azure Database for PostgreSQL server, log_checkpoints and log_connections are on by default. 还有一些其他参数,你可以调整它们来满足你的日志记录需求:There are additional parameters you can adjust to suit your logging needs:

Azure Database for PostgreSQL - 日志记录参数

若要详细了解 Postgres 日志参数,请访问 Postgres 文档的何时记录日志记录哪些内容部分。To learn more about Postgres log parameters, visit the When To Log and What To Log sections of the Postgres documentation. 可以在 Azure Database for PostgreSQL 中配置大部分(但并非所有)PostgreSQL 日志记录参数。Most, but not all, Postgres logging parameters are available to configure in Azure Database for PostgreSQL.

若要了解如何在 Azure Database for PostgreSQL 中配置参数,请参阅门户文档CLI 文档To learn how to configure parameters in Azure Database for PostgreSQL, see the portal documentation or the CLI documentation.


配置大量日志(例如,语句日志记录)可能会显著增大性能开销。Configuring a high volume of logs, for example statement logging, can add significant performance overhead.

访问 .log 文件Access .log files

Azure Database for PostgreSQL 中的默认日志格式为 .log。The default log format in Azure Database for PostgreSQL is .log. 此日志中的示例行如下所示:A sample line from this log looks like:

2019-10-14 17:00:03 UTC-5d773cc3.3c-LOG: connection received: host= port=34331 pid=16216

Azure Database for PostgreSQL 为 .log 文件提供短期存储位置。Azure Database for PostgreSQL provides a short-term storage location for the .log files. 每隔 1 小时或每达到 100 MB(以先达到的条件为准),就会开始创建一个新文件。A new file begins every 1 hour or 100 MB, whichever comes first. 从 Postgres 发出日志后,这些日志将追加到当前文件。Logs are appended to the current file as they are emitted from Postgres.

可以使用 log_retention_period 参数设置此短期日志存储的保留期。You can set the retention period for this short-term log storage using the log_retention_period parameter. 默认值为 3 天;最大值为 7 天。The default value is 3 days; the maximum value is 7 days. 短期存储位置最多可以存储 1 GB 日志文件。The short-term storage location can hold up to 1 GB of log files. 达到 1 GB 后,无论保留期有多长,都会删除最旧的文件,以便为新日志腾出空间。After 1 GB, the oldest files, regardless of retention period, will be deleted to make room for new logs.

对于长期保留的日志和日志分析,可以下载 .log 文件并将其移到第三方服务。For longer-term retention of logs and log analysis, you can download the .log files and move them to a third-party service. 可以使用 Azure 门户Azure CLI 下载文件。You can download the files using the Azure portal, Azure CLI. 此外,还可以配置 Azure Monitor 诊断设置,用于自动将日志(JSON 格式)发送到长期保留位置。Alternatively, you can configure Azure Monitor diagnostic settings which automatically emits your logs (in JSON format) to longer-term locations. 以下部分详细介绍了此选项。Learn more about this option in the section below.

可以通过将参数 logging_collector 设置为 OFF 来停止生成 .log 文件。You can stop generating .log files by setting the parameter logging_collector to OFF. 如果使用 Azure Monitor 诊断设置,则我们建议禁用 .log 文件生成。Turning off .log file generation is recommended if you are using Azure Monitor diagnostic settings. 此配置可以减轻附加的日志记录对性能造成的影响。This configuration will reduce the performance impact of additional logging.

资源日志Resource logs

Azure Database for PostgreSQL 与 Azure Monitor 诊断日志设置相集成。Azure Database for PostgreSQL is integrated with Azure Monitor diagnostic settings. 使用诊断设置可将 JSON 格式的 Postgres 日志发送到 Azure Monitor 日志用于分析和警报、发送到事件中心进行流式处理,或者发送到 Azure 存储进行存档。Diagnostic settings allows you to send your Postgres logs in JSON format to Azure Monitor Logs for analytics and alerting, Event Hubs for streaming, and Azure Storage for archiving.


服务器日志的此诊断功能仅适用于“常规用途”和“内存优化”的定价层This diagnostic feature for server logs is only available in the General Purpose and Memory Optimized pricing tiers.

配置诊断设置Configure diagnostic settings

可以使用 Azure 门户、CLI、REST API 和 Powershell 为 Postgres 服务器启用诊断设置。You can enable diagnostic settings for your Postgres server using the Azure portal, CLI, REST API, and Powershell. 要选择的日志类别为“PostgreSQLLogs”。The log category to select is PostgreSQLLogs. (如果使用查询存储,则还可以配置其他日志。)(There are other logs you can configure if you are using Query Store.)

若要使用 Azure 门户启用资源日志:To enable resource logs using the Azure portal:

  1. 在门户上 Postgres 服务器的导航菜单中,转到“诊断设置”。In the portal, go to Diagnostic Settings in the navigation menu of your Postgres server.
  2. 选择“添加诊断设置”。Select Add Diagnostic Setting.
  3. 为此设置命名。Name this setting.
  4. 选择首选的终结点(存储帐户、事件中心、Log Analytics)。Select your preferred endpoint (storage account, event hub, log analytics).
  5. 选择日志类型“PostgreSQLLogs”。Select the log type PostgreSQLLogs.
  6. 保存设置。Save your setting.

若要使用 Powershell、CLI 或 REST API 启用资源日志,请访问诊断设置一文。To enable resource logs using Powershell, CLI, or REST API, visit the diagnostic settings article.

访问资源日志Access resource logs

访问日志的方式取决于所选的终结点。The way you access the logs depends on which endpoint you choose. 有关 Azure 存储,请参阅日志存储帐户一文。For Azure Storage, see the logs storage account article. 有关事件中心,请参阅流式传输 Azure 日志一文。For Event Hubs, see the stream Azure logs article.

Azure Monitor 日志将发送到所选的工作区。For Azure Monitor Logs, logs are sent to the workspace you selected. Postgres 日志使用 AzureDiagnostics 收集模式,因此可以从 AzureDiagnostics 表查询它们。The Postgres logs use the AzureDiagnostics collection mode, so they can be queried from the AzureDiagnostics table. 下面描述了该表中的字段。The fields in the table are described below. Azure Monitor 日志查询概述中详细了解查询和警报。Learn more about querying and alerting in the Azure Monitor Logs query overview.

下面是可帮助你入门的查询。The following are queries you can try to get started. 可以基于查询配置警报。You can configure alerts based on queries.

搜索特定的服务器在过去一天生成的所有 Postgres 日志Search for all Postgres logs for a particular server in the last day

| where LogicalServerName_s == "myservername"
| where Category == "PostgreSQLLogs"
| where TimeGenerated > ago(1d) 

搜索所有非 localhost 连接尝试Search for all non-localhost connection attempts

| where Message contains "connection received" and Message !contains "host="
| where Category == "PostgreSQLLogs" and TimeGenerated > ago(6h)

上面的查询将显示在此工作区中记录的任何 Postgres 服务器在过去 6 小时内的结果。The query above will show results over the last 6 hours for any Postgres server logging in this workspace.

日志格式Log format

下表描述了 PostgreSQLLogs 类型的字段。The following table describes the fields for the PostgreSQLLogs type. 包括的字段以及它们的出现顺序可能有所不同,具体取决于你选择的输出终结点。Depending on the output endpoint you choose, the fields included and the order in which they appear may vary.

字段Field 说明Description
TenantIdTenantId 租户 IDYour tenant ID
SourceSystemSourceSystem Azure
TimeGenerated [UTC]TimeGenerated [UTC] 记录日志时的时间戳 (UTC)Time stamp when the log was recorded in UTC
类型Type 日志的类型。Type of the log. 始终是 AzureDiagnosticsAlways AzureDiagnostics
SubscriptionIdSubscriptionId 服务器所属的订阅的 GUIDGUID for the subscription that the server belongs to
resourceGroupResourceGroup 服务器所属的资源组的名称Name of the resource group the server belongs to
ResourceProviderResourceProvider 资源提供程序的名称。Name of the resource provider. 始终是 MICROSOFT.DBFORPOSTGRESQLAlways MICROSOFT.DBFORPOSTGRESQL
ResourceTypeResourceType Servers
ResourceIdResourceId 资源 URIResource URI
资源Resource 服务器的名称Name of the server
CategoryCategory PostgreSQLLogs
OperationNameOperationName LogEvent
errorLevelerrorLevel 日志记录级别,例如:LOG, ERROR, NOTICELogging level, example: LOG, ERROR, NOTICE
MessageMessage 主要日志消息Primary log message
Domain 服务器版本,示例:postgres-10Server version, example: postgres-10
详细信息Detail 辅助日志消息(如果适用)Secondary log message (if applicable)
ColumnNameColumnName 列名称(如果适用)Name of the column (if applicable)
SchemaNameSchemaName 架构名称(如果适用)Name of the schema (if applicable)
DatatypeNameDatatypeName 数据类型名称(如果适用)Name of the datatype (if applicable)
LogicalServerNameLogicalServerName 服务器的名称Name of the server
_ResourceId_ResourceId 资源 URIResource URI
前缀Prefix 日志行的前缀Log line's prefix

后续步骤Next steps