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