.. SPDX-FileCopyrightText: 2021 Veit Schiele
..
.. SPDX-License-Identifier: BSD-3-Clause
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)
.. seealso::
* `GitHub `_
* `Docs `_
Installation
------------
#. Create a monitoring user for pganalyze:
.. code-block:: postgresql
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;
#. Check the connection:
.. code-block:: postgresql
PGPASSWORD=... psql -h localhost -d mydb -U pganalyze
#. Activate the ``pg_stat_statements``:
.. code-block:: postgresql
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
#. Restart of the PostgreSQL daemon:
.. code-block:: console
$ sudo service postgresql restart
#. Checking ``pg_stat_statements``:
.. code-block:: postgresql
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)
#. Installing the *Collector*:
.. code-block:: console
$ 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
#. 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/
#. Configure the *collector*:
.. code-block:: ini
[pganalyze]
api_key: …
[server]
db_host: 127.0.0.1
db_port: 5432
db_name: postgres, *
db_username: pganalyze
db_password: …
#. Testing the *Collector* configuration:
.. code-block:: console
$ sudo pganalyze-collector --test --reload
.. seealso::
* `Installation Guide `_
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:
.. code-block:: console
$ pganalyze-collector --discover-log-location
The output can then look like this, for example:
.. code-block:: console
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:
.. code-block:: console
$ pganalyze-collector --test
The result can then look like this, for example:
.. code-block:: console
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:
.. code-block:: console
$ systemctl restart pganalyze-collector