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.