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

For more information about PostgreSQL, see thePostgreSQL documentation.

Prerequisites

To collect PostgreSQL telemetry, you mustinstall the Ops Agent:

• For metrics, install version 2.21.0 or higher.
• For logs, install version 2.9.0 or higher.

This integration supports PostgreSQL version 10.18+.

Configure your PostgreSQL instance

Thepostgresql receiver connects by default to a localpostgresqlserver using a Unix socket and Unix authentication as theroot user.

Configure the Ops Agent for PostgreSQL

Following the guide toConfigure the OpsAgent, add the required elementsto collect telemetry from PostgreSQL instances, andrestart the agent.

Example configuration

The following commands create the configuration to collect and ingest telemetryfor PostgreSQL:

#ConfiguresOpsAgenttocollecttelemetryfromtheapp.Youmustrestarttheagentfortheconfigurationtotakeeffect.set-e#Checkifthefileexistsif[!-f/etc/google-cloud-ops-agent/config.yaml];then#Createthefileifitdoesn't exist.  sudo mkdir -p /etc/google-cloud-ops-agent  sudo touch /etc/google-cloud-ops-agent/config.yamlfi# Create a back up of the existing file so existing configurations are not lost.sudo cp /etc/google-cloud-ops-agent/config.yaml /etc/google-cloud-ops-agent/config.yaml.bak# Configure the Ops Agent.sudo tee /etc/google-cloud-ops-agent/config.yaml > /dev/null << EOFmetrics:  receivers:    postgresql:      type: postgresql      username: postgres      password: abc123      # To use the googlesecretmanager provider to replace plaintext passwords, refer to the example below.      # password: ${googlesecretmanager:projects/<project-id>/secrets/<secret-id>/versions/<version-id>}      insecure: true      endpoint: localhost:5432  service:    pipelines:      postgresql:        receivers:        - postgresqllogging:  receivers:    postgresql_general:      type: postgresql_general  service:    pipelines:      postgresql:        receivers:          - postgresql_generalEOF

For these changes to take effect, you must restart the Ops Agent:

Configure logs collection

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

To configure a receiver for yourpostgresql_general logs, specify the followingfields:

What is logged

ThelogName is derived fromthe receiver IDs specified in the configuration. Detailed fields inside theLogEntryare as follows.

Thepostgresql_general logs contain the following fields in theLogEntry:

Configure metrics collection

To ingest metrics from PostgreSQL, you must create a receiver for the metricsthat PostgreSQL produces and then create a pipeline for the new receiver.

This receiver does notsupport the use of multiple instances in the configuration, for example, tomonitor multiple endpoints. All such instances write to the same time series,and Cloud Monitoring has no way to distinguish among them.

To configure a receiver for yourpostgresql metrics, specify the followingfields:

What is monitored

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

Verify the configuration

This section describes how to verify that you correctly configured thePostgreSQL receiver. It might take one or twominutes for the Ops Agent to begin collecting telemetry.

To verify that PostgreSQL logs are being sent toCloud Logging, do the following:

1. In the Google Cloud console, go to theLogs Explorer page:

   Go toLogs Explorer

   If you use the search bar to find this page, then select the result whose subheading isLogging.

2. Enter the following query in the editor, and then clickRun query:
   

   resource.type="gce_instance"log_id("postgresql_general")

To verify that PostgreSQL metrics are being sent toCloud Monitoring, do the following:

1. In the Google Cloud console, go to theleaderboard Metrics explorer page:

   Go toMetrics explorer

   If you use the search bar to find this page, then select the result whose subheading isMonitoring.

2. In the toolbar of thequery-builder pane, select the button whose name is eithercode MQL orcode PromQL.
3. Verify thatPromQL is selectedin theLanguage toggle. The language toggle is in the same toolbar thatlets you format your query.
4. Enter the following query in the editor, and then clickRun query:
   

   {"workload.googleapis.com/postgresql.backends", monitored_resource="gce_instance"}

View dashboard

To view your PostgreSQL metrics, you must have a chart or dashboardconfigured.The PostgreSQL integration includes one or more dashboards for you.Any dashboards are automatically installed after you configure theintegration and the Ops Agent has begun collecting metric data.

You can also view static previews of dashboards withoutinstalling the integration.

To view an installed dashboard, do the following:

1. In the Google Cloud console, go to the Dashboards page:

   Go toDashboards

   If you use the search bar to find this page, then select the result whose subheading isMonitoring.

2. Select theDashboard List tab, and then choose theIntegrations category.
3. Click the name of the dashboard you want to view.

If you have configured an integration but the dashboard has not beeninstalled, then check that the Ops Agent is running. When there is nometric data for a chart in the dashboard, installation of the dashboard fails.After the Ops Agent begins collecting metrics, the dashboard is installedfor you.

To view a static preview of the dashboard, do the following:

1. In the Google Cloud console, go to the Integrations page:

   Go toIntegrations

   If you use the search bar to find this page, then select the result whose subheading isMonitoring.

2. Click theCompute Engine deployment-platform filter.
3. Locate the entry for PostgreSQL and clickView Details.
4. Select theDashboards tab to see a static preview. If the dashboard is installed, then you can navigate to it by clickingView dashboard.

For more information about dashboards in Cloud Monitoring, seeDashboards and charts.

For more information about using theIntegrations page, seeManage integrations.

Install alerting policies

Alerting policies instruct Cloud Monitoring to notify you whenspecified conditions occur.The PostgreSQL integration includes one or more alerting policies foryou to use.You can view and installthese alerting policies from theIntegrations page inMonitoring.

To view the descriptions of available alerting policies and install them, do the following:

1. In the Google Cloud console, go to the Integrations page:

   Go toIntegrations

   If you use the search bar to find this page, then select the result whose subheading isMonitoring.

2. Locate the entry for PostgreSQL and clickView Details.
3. Select theAlerts tab. This tab provides descriptions of available alerting policies and provides an interface for installing them.
4. Install alerting policies. Alerting policies need to know where to send notifications that the alert has been triggered, so they require information from you for installation. To install alerting policies, do the following:
   1. From the list of available alerting policies, select those that you want to install.

   2. In theConfigure notifications section, select one or more notification channels. You have the option to disable the use of notification channels, but if you do, then your alerting policies fire silently. You can check their status in Monitoring, but you receive no notifications.

      For more information about notification channels, seeManage notification channels.

   3. ClickCreate Policies.

For more information about alerting policies in Cloud Monitoring, seeIntroduction to alerting.

For more information about using theIntegrations page, seeManage integrations.

What's next

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