PostgreSQL

Overview

This document describes how to collect statistical metrics introduced in PostgreSQL 9.1+ for long-term retention and monitoring in Axibase Time Series Database.

The process involves enabling a JDBC job in Axibase Collector to poll statistics views in the PostgreSQL database and uploading the counters and properties into ATSD for processing.

Requirements

  • PostgreSQL 9.1+

Installation Steps

Create a readonly user for Axibase Collector

  • Create a user:
adduser readonly
  • Log in as a postgres user and open the psql console:
sudo su postgres
psql
  • Run these commands in the psql console:
CREATE ROLE readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER USER readonly WITH PASSWORD 'readonly';
ALTER USER readonly VALID UNTIL 'infinity';
ALTER ROLE readonly login;
  • Open the file /etc/postgresql/${POSTGRES_VERSION}/main/pg_hba.conf.
  • Add the following record to allow remote access from the Axibase Collector server using its IP address:
host    all             readonly        192.0.2.1           trust
  • Open the file /etc/postgresql/${POSTGRES_VERSION}/main/postgresql.conf.
  • Enable the database to accept remote connections by specifying the external IP address:
listen_addresses = 'localhost,192.0.2.1' # what IP address(es) to listen on;

Import PostgreSQL Job into Axibase Collector

  • On Jobs page, select Import from the split-button below the table and upload the postgres-jobs.xml file.

Configure PostgreSQL Database Connection

  • Open the Data Sources > Databases page and select the postgresql database.
  • Provide connection parameters to the target PostgreSQL database displayed below:

  • Execute test query to check the connection:
SELECT 1
  • Query result must be Query OK.

Verify Job Configuration

  • Open the PostgreSQL job.
  • Set Data Source to postgresql.

  • Choose one of the target ATSD instances if your Collector instance is connected to multiple ATSD servers.
  • Save the Job.
  • Open each configuration, click Test, and review the output. See Data Queries.

Schedule the Job

  • Open the JDBC Job page and click Run for the PostgreSQL JDBC job.
  • Ensure that the job status is COMPLETED and Items Read and Sent commands are greater than 0.

  • If there are no errors, set the job status to Enabled and save.

Verify Metrics in ATSD

  • Log in to ATSD.
  • Click the Metrics tab and filter metrics by name postgres.*.

Viewing Data in ATSD

Metrics

Properties

Entity Groups

  • Open Settings > Entity Groups.
  • Create a new Entity Group with the split-button at the bottom of the page, navigate to the Expression tab under Members, and enter the following expression:
hasMetric('postgres.global_status.uptime')
  • Save and verify that the group contains PostgreSQL database hosts:

Portals

  • Open the Portals menu and select Configure. Import a postgresql portal from portal-postgres.xml.
  • Click the Assign link and associate the portal with the entity group you created earlier.
  • Open Entities tabs, find the postgresql database by name, and click the Portal icon.

PostgreSQL Live Portal

Data Queries

Metrics Queries select most recent statistics as described in the Data Queries Documentation.