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
    

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 confiugration to take effect:

$ systemctl restart pganalyze-collector