PostgreSQL

The PostgreSQL integration collects database-usage metrics, such as the database size, the number of backends, or the number of operations. The integration also collects PostgreSQL logs and parses them into a JSON payload. This result includes fields for role, user, level, and message.

For more information about PostgreSQL, see postgresql.org.

Prerequisites

To collect and ingest PostgreSQL logs and metrics, you must install Ops Agent version 2.9.0 or higher.

This receiver supports PostgreSQL versions 10.18 and higher.

Configure your PostgreSQL instance

The postgresql receiver connects by default to a local postgresql server using a Unix socket and Unix authentication as the root user.

Configure the Ops Agent for PostgreSQL

Following the guide for Configuring the Ops Agent, add the required elements to collect logs and metrics from your PostgreSQL instances, and restart the agent.

Example configuration

The following command creates the configuration file to collect and ingest logs and metrics for PostgreSQL and restarts the Ops Agent on Linux.

sudo tee /etc/google-cloud-ops-agent/config.yaml > /dev/null << EOF
logging:
  receivers:
    postgresql_general:
      type: postgresql_general
  service:
    pipelines:
      postgresql:
        receivers:
          - postgresql_general
metrics:
  receivers:
    postgresql:
      type: postgresql
      collection_interval: 60s
      username: usr
      password: pwd
  service:
    pipelines:
      postgresql_pipeline:
        receivers:
          - postgresql
EOF
sudo service google-cloud-ops-agent restart

Configure logs collection

To ingest logs from PostgreSQL, you must create receivers for the logs that PostgreSQL produces and then create a pipeline for the new receivers.

To configure a receiver for your postgresql_general logs, specify the following fields:

Field Default Description
type The value must be postgresql_general.
include_paths [/var/log/postgresql/postgresql*.log, /var/lib/pgsql/data/log/postgresql*.log, /var/lib/pgsql/*/data/log/postgresql*.log] The log files to read.
exclude_paths The log files to exclude, if include_paths contains a glob or directory.

What is logged

The logName of the postgresql_general logs are derived from the receiver IDs specified in the configuration. Detailed fields inside the LogEntry are as follows.

postgresql_general
These logs contain the following fields in the LogEntry:

Field Type Description
jsonPayload.tid number Thread ID where the log originated
jsonPayload.role string Authenticated role for the action being logged when relevant
jsonPayload.user string Authenticated user for the action being logged when relevant
jsonPayload.level string Log severity or type of database interaction type for some logs
jsonPayload.message string Log of the database action
severity string (LogSeverity) Log entry level (translated)
timestamp string (Timestamp) Time the entry was logged

Configure metrics collection

To collect metrics from PostgreSQL, you must create a receiver for PostgreSQL metrics and then create a pipeline for the new receiver. To configure a receiver for your PostgreSQL metrics, specify the following fields:

Field Default Description
type The value must be postgresql.
endpoint /var/run/postgresql/.s.PGSQL.5432 The hostname:port or socket path starting with / used to connect to the postgresql server.
collection_interval 60s A time.Duration value, such as 30s or 5m.
username The username used to connect to the server.
password The password used to connect to the server.
insecure true Sets whether or not to use a secure TLS connection. If set to false, then TLS is enabled.
insecure_skip_verify false Sets whether or not to skip verifying the certificate. If insecure is set to true, then the insecure_skip_verify value is not used.
cert_file Path to the TLS certificate to use for TLS-required connections.
key_file Path to the TLS key to use for TLS-required connections.
ca_file Path to the CA certificate. As a client, this verifies the server certificate. If empty, the receiver uses the system root CA.

What is monitored

The following table provides the list of metrics that the Ops Agent collects from the PostgreSQL instance.

Metric type 
Kind, Type
Monitored resources
Labels
workload.googleapis.com/postgresql.backends
GAUGEINT64
gce_instance
database
workload.googleapis.com/postgresql.commits
CUMULATIVEINT64
gce_instance
database
workload.googleapis.com/postgresql.db_size
GAUGEINT64
gce_instance
database
workload.googleapis.com/postgresql.rollbacks
CUMULATIVEINT64
gce_instance
database

Verify the configuration

You can use the Logs Explorer and Metrics Explorer to verify that you correctly configured the PostgreSQL receiver. It might take one or two minutes for the Ops Agent to begin collecting logs and metrics.

To verify the logs are ingested, go to the Logs Explorer and run the following query to view the PostgreSQL logs:

resource.type="gce_instance"
logName=("projects/PROJECT_ID/logs/postgresql_general")


To verify the metrics are ingested, go to Metrics Explorer and run the following query in the MQL tab.

fetch gce_instance
| metric 'workload.googleapis.com/postgresql.operations'
| align rate(1m)
| every 1m

What's next

For a walkthrough on how to use Ansible to install the Ops Agent, configure a third-party application, and install a sample dashboard, see the Install the Ops Agent to troubleshoot third-party applications video.