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
GAUGE, INT64 gce_instance	database
workload.googleapis.com/postgresql.commits
CUMULATIVE, INT64 gce_instance	database
workload.googleapis.com/postgresql.db_size
GAUGE, INT64 gce_instance	database
workload.googleapis.com/postgresql.rollbacks
CUMULATIVE, INT64 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.