PSQL-percona error pgstat monitoring

https://pganalyze.com/docs/install/01_enabling_pg_stat_statements

Enabling pg_stat_statements

We use the pg_stat_statements extension (officially bundled with PostgreSQL) for tracking which queries get executed in your database.

The query information collected by the extension is cleaned and then sent to our servers using our collector script.

The extension's normalization code cleans the actual values from the queries,
turning SELECT * FROM t WHERE secret = '123' into SELECT * FROM t WHERE secret = ?.

We still recommend that you use prepared statements if you query for highly sensitive data.

Note: We never collect any stored data, or data returned by queries.

Install official PostgreSQL contrib package

On your database server, make sure that the extensions package is installed.

Debian/Ubuntu:

Debian/Ubuntu includes pg_stat_statements in the main Postgres package, therefore no additional install is required.

RedHat/CentOS:

sudo yum install postgresql-contrib

Configure PostgreSQL

There are a number of settings that define how pg_stat_statements will collect statistics. The right configuration will depend on your server, workload, and preferences, but the following is the example of what to modify. You can apply these changes by updating your postgresql.conf file.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_io_timing = on

Name	Value	Description
pg_stat_statements.track	all (default: top)	Enables tracking of queries inside stored procedures or functions.
pg_stat_statements.max	10000 (default: 5000)	Increases the maximum number of statements tracked. Recommended to increase further if the database tends to have lots of distinct queries. Restart is required for changes to take effect.
track_io_timing	on (default: off)	Enables tracking of per-query I/O statistics. Might be turned on by default for some managed databases.

The pg_stat_statements_info view

Queries that have not been seen recently will be pruned from pg_stat_statements once pg_stat_statements.max is hit. The pruning happens in batches, so you may see the count lower than the max, even if the max has been reached. The dealloc column of the pg_stat_statements_info view tracks the number of pruned queries. This is useful to check if the pg_stat_statements.max should be increased.

The pg_stat_statements_info view, introduced in PostgreSQL 14, tracks the statistics of pg_stat_statements. In addition to deallocation information, this view also tracks when the pg_stat_statements_reset() function was last called to reset the statistics.

Restart the PostgreSQL daemon

If you change shared_preload_libraries or pg_stat_statements.max above, a restart is required:

sudo systemctl restart postgresql




 =======================================================================

Step 2: Enable pg_stat_statements

Enable pg_stat_statements

Run the following SQL statement as a superuser, to enable the pg_stat_statements extension:

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

Restart the PostgreSQL daemon

If you have not used pg_stat_statements before, you will need a full Postgres restart to enable it for the first time:

sudo systemctl restart postgresql

Verify that pg_stat_statements returns data

As a superuser, run the following statements:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT calls, query FROM pg_stat_statements LIMIT 1;

If you have configured your database correctly, this will return a result like this:

 calls | query
-------+-------
     8 | SELECT * FROM t WHERE field = ?
(1 row)

If you get an error you might not have restarted the server. If you get stuck feel free to reach out to us, we're happy to help.

---

Next we continue by installing the pganalyze collector:

Proceed to Step 3: Install the collector