PostgreSQL Integration for Watchlog

Step 1: Install the Watchlog Agent

If you haven't already installed the Watchlog Agent, download and install it on your server. Refer to the installation guide for detailed instructions.

Step 2: Configure PostgreSQL in integration.json

Locate the integration.json file and enable PostgreSQL integration by adding or updating the following configuration:

  {
      "service": "postgresql",
      "monitor": true,
      "host": "localhost",
      "port": "5432",
      "username": "",
      "password": "",
      "database": ["postgres"]
  }
                

Replace the username, password, and database fields with your actual PostgreSQL credentials and the list of databases you want to monitor.

On an Ubuntu Agent, this file is typically located at /opt/watchlog-agent/integration.json.

For the Source Agent, it is found in the Watchlog Agent directory.

Step 2.5: Enable Query Performance Metrics (pg_stat_statements)

To collect detailed query performance data such as call count, average execution time, and rows returned, you need to enable the PostgreSQL extension pg_stat_statements.

1. Edit postgresql.conf

Add or update the following line:

shared_preload_libraries = 'pg_stat_statements'
    

After saving the file, restart PostgreSQL to apply changes:

sudo systemctl restart postgresql
    

2. Enable the extension

Connect to each database you want to monitor and run the following SQL command:

CREATE EXTENSION pg_stat_statements;
    

3. Verify

To confirm that the extension is enabled, run:

SELECT * FROM pg_stat_statements LIMIT 5;
    

If you see results, the extension is active and query performance metrics will be collected by Watchlog.

Step 3: Restart Watchlog Agent

Restart the Watchlog Agent to apply the changes:

  sudo pm2 reload watchlog-agent