Configure AlloyDB Omni log rotation

Select a documentation version:

This document describes how to configure rotation of AlloyDB Omni diagnostic logs when you use the AlloyDB Omni Kubernetes operator.

The following log files are located in the /obs/diagnostic/ directory:

  1. postgresql.audit: This log file collects session and object access audit logs. To collect audit logs, you need to enable audit logs.

  2. postgresql.log: This log file collects PostgreSQL server logs. These logs are always collected and don't need to be enabled.

When a log file rotates, the following occurs:

  1. The log file is copied to the /obs/diagnostic/archive/ directory. If a log file with the same name exists in that directory, then it's overwritten.

  2. The contents of the original rotated log file are deleted so that the file is empty.

  3. Log information immediately begins writing to the empty rotated log file. Log information is written to the log file until the file reaches a size or age threshold, at which point it rotates again. Logs rotate so that they don't become too large.

By default, the rotation setting is for each log file to rotate when its size reaches 200 MB. The default rotation doesn't include an age setting.

Archived files are retained for 7 days. Archived files that are older than 7 days are automatically removed, except for the file that was archived during the last rotation. For example, if log_rotation_age is older than 7 days, then the archived file reaches the 7-day threshold before the rotation of the current file. In this case, this archived file isn't removed until the next rotation generates a new archived file.

Each rotated log filename follows this format: postgresql-%Y-%m-%d_%H%M%S.log. The timestamp is determined at the time of log rotation and is expressed in Coordinated Universal Time (UTC). For example, if the log is rotated at 13:01:02 of 12/20/2024 UTC, the archived filename is postgresql-2024-12-20_130102.log.

Each archived file is individually compressed using the Gzip file format.

Enable audit logs

To enable session and object access logging, you must configure pgAudit parameters in your database cluster and install the extension in your databases.

Step 1: Configure pgAudit in the DBCluster parameters

For session and object access logs to be collected in the postgresql.audit file, you must enable pgAudit and configure what statements to log using pgaudit.log.

Add the following lines to the parameters section of the v1_dbcluster_parameters.yaml file:

alloydb.enable_pgaudit: "on"
pgaudit.log: "all"

The following is an example of how this looks in the DBCluster manifest:

apiVersion: v1
kind: Secret
...
apiVersion: alloydbomni.dbadmin.goog/v1
kind: DBCluster
metadata:
   name: DB_CLUSTER_NAME
spec:
  databaseVersion: "18.3.0"
  primarySpec:
    ...
    parameters:
      ...
      alloydb.enable_pgaudit: "on"
      pgaudit.log: "all"

For more information, see pgaudit in Supported database extensions.

Step 2: Create the pgAudit extension in the database

After configuring the parameters and ensuring the database has restarted, you must connect to your database and install the pgAudit extension:

CREATE EXTENSION IF NOT EXISTS pgaudit;

You must run this command in every database where you want to enable audit logging. This step is required to install the event triggers needed for DDL auditing.

pgAudit parameters (GUCs)

The following table describes key pgAudit Grand Unified Configuration parameters (GUCs) that you can configure in the parameters section of your DBCluster spec. For more information, see the pgAudit documentation.

Parameter Description Default Value
pgaudit.log Specifies which classes of statements are logged by session audit logging. Valid values are none, all, read, write, function, role, ddl, misc, misc_set. none
pgaudit.log_catalog Specifies that session logging is enabled if all relations in a statement are in pg_catalog. Disabling this reduces log noise from database clients. on
pgaudit.log_parameter Specifies that audit logging includes the parameters passed with the statement. off
pgaudit.log_relation Specifies whether session audit creates a separate log entry for each relation (table, view, etc.) referenced in a SELECT or DML statement. off
pgaudit.log_rows Specifies that audit logging include the number of rows retrieved or affected by a statement. off
pgaudit.log_statement Specifies whether logging includes the statement text and parameters. on
pgaudit.role Specifies tparameters.he primary role to use for object audit logging. None

PostgreSQL server logs are always collected in the postgresql.log file and don't require enabling pgAudit.

View the path of the audit log file

Use the SQL function alloydb_audit_current_logfile to view the path of the audit log file. If auditing is disabled, then the result is NULL.

SELECT alloydb_audit_current_logfile();

 alloydb_audit_current_logfile
----------------------------------
 /obs/diagnostic/postgresql.audit

Configure log rotation

If you want more control over when logs rotate, configure a maximum file size, a duration between log rotations, or both. The duration between log rotations is also called the age of the log. If you use both settings, then each log rotates when it reaches one of the thresholds.

To configure log rotation, you set one or both of the following parameters in the parameters section of the DBCluster manifest:

  • log_rotation_size: "SIZE_IN_KB"
  • log_rotation_age: "AGE_IN_MINUTES"

To disable one of the log rotation settings, set it to zero, "0". To retain the default setting that rotates logs when their file size reaches 200 MB, don't set either parameter.

Log rotation maximum log size and duration example

The following sample sets logs to rotate when their file size reaches 400 MB or when the time between log rotations reaches one day, whichever happens first:

apiVersion: alloydbomni.dbadmin.goog/v1
kind: DBCluster
metadata:
  name: DB_CLUSTER_NAME
spec:
...
  primarySpec:
  ...
    parameters:
      log_rotation_size: "400000" # 400 MB
      log_rotation_age: "1440" # 24 hours * 60 minutes = 1 day

Log rotation maximum log size example

The following sample sets logs to rotate when their file size reaches 400 MB:

apiVersion: alloydbomni.dbadmin.goog/v1
kind: DBCluster
metadata:
  name: DB_CLUSTER_NAME
spec:
...
  primarySpec:
  ...
    parameters:
      log_rotation_size: "400000" # 400 MB
      log_rotation_age: "0" # Set to 0 to disable

Log rotation duration example

The following sample sets logs to rotate once every 24 hours:

apiVersion: alloydbomni.dbadmin.goog/v1
kind: DBCluster
metadata:
  name: DB_CLUSTER_NAME
spec:
...
  primarySpec:
  ...
    parameters:
      log_rotation_size: "0" # Set to 0 to disable
      log_rotation_age: "1440" # 24 hours * 60 minutes = 1 day

Forward audit logs to a centralized sink

AlloyDB Omni writes audit logs to the /obs/diagnostic/postgresql.audit file inside the database container. If you want to forward these logs to a centralized logging sink (such as Cloud Logging, Datadog, Splunk, or Elasticsearch), you can deploy a sidecar container using the Sidecar Custom Resource (CR).

The sidecar container runs in the same Pod as the database, mounts the log volume (obsdisk), and tails the log file to forward it to your logging destination.

Example: forwarding logs using a fluent bit sidecar

The following example demonstrates how to use a Fluent Bit sidecar to tail pgAudit logs and output them to standard output, where they can be collected by standard Kubernetes cluster log collectors (such as the Google Cloud Logging agent on Google Kubernetes Engine (GKE)).

  1. Create a ConfigMap for Fluent Bit configuration:

    Create a ConfigMap containing the Fluent Bit configuration to tail the audit log.

    apiVersion: v1
kind: ConfigMap
metadata:
  name: fluentbit-config
  namespace: NAMESPACE
data:
  fluent-bit.conf: |
    [SERVICE]
        Flush        1
        Daemon       Off
        Log_Level    info

    [INPUT]
        Name         tail
        Path         /obs/diagnostic/postgresql.audit
        Tag          pgaudit
        DB           /tmp/fluent-bit-pgaudit.db

    [OUTPUT]
        Name         stdout
        Match        *

    Replace NAMESPACE with the namespace of your database cluster.

  2. Create the Sidecar Custom Resource:

    Create a Sidecar manifest that defines the Fluent Bit container and mounts both the obsdisk volume (containing AlloyDB Omni logs) and the ConfigMap volume.

    apiVersion: alloydbomni.dbadmin.goog/v1
kind: Sidecar
metadata:
  name: pgaudit-forwarder
  namespace: NAMESPACE
spec:
  additionalVolumes:
  - name: fluentbit-config-volume
    configMap:
      name: fluentbit-config
  sidecars:
  - name: fluent-bit
    image: fluent/fluent-bit:3.0
    command: ["/fluent-bit/bin/fluent-bit"]
    args: ["-c", "/fluent-bit/etc/fluent-bit.conf"]
    volumeMounts:
    - name: obsdisk
      mountPath: /obs
    - name: fluentbit-config-volume
      mountPath: /fluent-bit/etc/

  3. Register the sidecar with your database cluster:

    Update your DBCluster manifest to reference the sidecar, or patch the cluster using the following command:

    kubectl patch dbclusters.alloydbomni.dbadmin.goog DB_CLUSTER_NAME -p '{"spec":{"primarySpec":{"sidecarRef":{"name":"pgaudit-forwarder"}}}}' --type=merge

    Replace DB_CLUSTER_NAME with the name of your database cluster.

    For more information on managing sidecars, see Configure a sidecar container.

What's next