You can ship PostgreSQL metrics using Metricbeat.

Metricbeat setup

Before you begin, you’ll need:

Download the Logz.io public certificate to your Metricbeat server

For HTTPS shipping, download the Logz.io public certificate to your certificate authority folder.

sudo curl https://raw.githubusercontent.com/logzio/public-certificates/master/AAACertificateServices.crt --create-dirs -o /etc/pki/tls/certs/COMODORSADomainValidationSecureServerCA.crt
Add Logz.io to your Metricbeat configuration

Open the Metricbeat configuration file (<<PATH_TO_METRICBEAT>>/metricbeat.yml) with your preferred text editor.

Copy and paste the code block below, overwriting the previous contents, to replace the general configuration with the following Logz.io settings:

# ===== General =====
fields:
  logzio_codec: json
  token: <<SHIPPING-TOKEN>>
fields_under_root: true

Replace <<SHIPPING-TOKEN>> with a token for the Metrics account you want to ship to.
Here’s how to look up your Metrics token.

Set Logz.io as the output

Still in the same configuration file, check if Logz.io is already an output. If not, add it now.

# ===== Outputs =====
output.logstash:
  hosts: ["<<LISTENER-HOST>>:5015"]
    ssl.certificate_authorities: ['/etc/pki/tls/certs/COMODORSADomainValidationSecureServerCA.crt']

Replace <<LISTENER-HOST>> with your region’s listener host (for example, listener.logz.io). For more information on finding your account’s region, see Account region.

One last validation - make sure Logz.io is the only output and appears only once. If the file has other outputs, remove them.

Add PostgreSQL module configuration
metricbeat.modules:
- module: postgresql
  enabled: true
  metricsets:
    - database
    - bgwriter
    - activity
  period: 10s
  # connenction URI format: postgresql://[user[:password]@][netloc][:port][,...][/dbname][?param1=value1&...]
  # Replace with your postgres connection URI 👇
  hosts: ["<<URI>>"]
  # Logz.io prebuild dashboard for PostgreSQl also uses metrics from the system module
- module: system
  metricsets:
    - cpu
    - network
    - memory
  enabled: true
  period: 10s

processors:
  - add_host_metadata: ~
  - include_fields: # Collected metrics ⬇️
      fields: ["postgresql.activity.database.name", "postgresql.activity.state", "postgresql.bgwriter.buffers.allocated", "postgresql.bgwriter.checkpoints.scheduled", "postgresql.database.blocks.hit", "postgresql.database.blocks.time.read.ms", "postgresql.database.blocks.time.write.ms", "postgresql.database.conflicts", "postgresql.database.deadlocks", "postgresql.database.name", "postgresql.database.rows.deleted", "postgresql.database.rows.fetched", "postgresql.database.rows.inserted", "postgresql.database.rows.returned", "postgresql.database.rows.updated", "postgresql.database.temporary.bytes", "postgresql.database.transactions.commit", "postgresql.database.transactions.rollback", "system.cpu.idle.pct", "system.memory.used.pct", "system.network.in.bytes", "system.network.out.bytes", "token", "logzio_codec", "event.module", "metricset.name", "host.name", "agent.hostname", "type", "service.type"]

fields:
  logzio_codec: json
  token: <<SHIPPING-TOKEN>>
fields_under_root: true
ignore_older: 3hr
type: metrics
output.logstash:
  hosts: ["<<LISTENER-HOST>>:5015"]
  ssl.certificate_authorities: ['/etc/pki/tls/certs/COMODORSADomainValidationSecureServerCA.crt']

For the full Metricbeat configuration options for the PostgreSQL module, please see Metricbeat’s documentation.

Replace the placeholders in the configuration

Still in the same configuration file, replace the placeholders to match your specifics.

  • Replace <<SHIPPING-TOKEN>> with a token for the Metrics account you want to ship to.
    Here’s how to look up your Metrics token.

  • Replace <<LISTENER-HOST>> with your region’s listener host (for example, listener.logz.io). For more information on finding your account’s region, see Account region.

  • When configuring the hosts option, you must use a valid PostgreSQL URI (example: postgresql://localhost:5432/postgres?sslmode=disable).

    Replace the placeholder <<URI>> using the following format: [postgresql://[user[:password]@][netloc][:port][,...][/dbname][?param1=value1&...]

    For more information about PostgreSQL URI and connection string parmeters, please see PostgreSQL’s documentation.

Start Metricbeat

Start or restart Metricbeat for the changes to take effect.

Open your PostgrSQL dashboard in Logz.io

Give your metrics a few minutes to get from your system to ours, and then open Logz.io.

Your metrics should appear in the preconfigured dashboard in your Metrics account. To open it, click > Manage in the left menu, then click Logz.io Dashboards > PostgrSQL.