pganalyze

pganalyze analyses the query plans of PostgreSQL. Currently it collects information about

  • schema with tables (columns, constraints, trigger definitions) and indices

  • Statistics on tables indices, databases and queries

  • Operating system (OS, RAM, storage)

See also

Installation

  1. Create a monitoring user for pganalyze:

    CREATE USER pganalyze WITH PASSWORD '…' CONNECTION LIMIT 5;
GRANT pg_monitor TO pganalyze;
CREATE SCHEMA pganalyze;
GRANT USAGE ON SCHEMA pganalyze TO pganalyze;
REVOKE ALL ON SCHEMA public FROM pganalyze;
CREATE OR REPLACE FUNCTION pganalyze.get_stat_replication() RETURNS SETOF pg_stat_replication AS
$$ /* pganalyze-collector */ SELECT * FROM pg_catalog.pg_stat_replication;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

  2. Check the connection:

    PGPASSWORD=...  psql -h localhost -d mydb -U pganalyze

  3. Activate the pg_stat_statements:

    ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

  4. Restart of the PostgreSQL daemon:

    $ sudo service postgresql restart

  5. Checking pg_stat_statements:

    CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT calls, query FROM pg_stat_statements LIMIT 1;
 calls | query
-------+-------
     8 | SELECT * FROM t WHERE field = ?
(1 row)

  6. Installing the Collector:

    $ curl -L https://packages.pganalyze.com/pganalyze_signing_key.asc | sudo apt-key add -
$ echo "deb [arch=amd64] https://packages.pganalyze.com/ubuntu/bionic/ stable main" | sudo tee /etc/apt/sources.list.d/pganalyze_collector.list
$ sudo apt-get update
$ sudo apt-get install pganalyze-collector

  7. Creating the API key

    For the next step you need the pganalyze api_key. You can create this at the site https://app.pganalyze.com/

  8. Configure the collector:

    [pganalyze]
api_key: …

[server]
db_host: 127.0.0.1
db_port: 5432
db_name: postgres, *
db_username: pganalyze
db_password: …

  9. Testing the Collector configuration:

    $ sudo pganalyze-collector --test --reload

See also

Log analysis

In order to continuously monitor, classify and statistically evaluate the local log files, db_log_location must be specified in pganalyze-collector.conf. pganalyze-collector provides help to find the log files:

$ pganalyze-collector --discover-log-location

The output can then look like this, for example:

db_log_location = /var/log/postgresql/postgresql-12-main.log

After this result has been entered in the pganalyze-collector.conf configuration file you can test it with:

$ pganalyze-collector --test

The result can then look like this, for example:

2021/02/06 06:40:06 I [server1] Testing statistics collection...
2021/02/06 06:40:07 I [server1] Test submission successful (15.8 KB received)
2021/02/06 06:40:07 I [server1] Testing local log tailing...
2021/02/06 06:40:13 I [server1] Log test successful
2021/02/06 06:40:13 I Re-running log test with reduced privileges of "pganalyze" user (uid = 107, gid = 113)
2021/02/06 06:40:13 I [server1] Testing local log tailing...
2021/02/06 06:40:19 I [server1] Log test successful

If the test was successful, the Collector must be restarted for the configuration to take effect:

$ systemctl restart pganalyze-collector