How to monitor PostgreSQL database with many schemas?

[Kosiarka]

Hello Everyone,

I have a database with one schema, and it works fine with full monitoring data. In this case, the database has the default user postgres, and all the tables are in the public schema.

Now, on another server, I want to monitor a PostgreSQL database that contains many (around 50) schemas and a few hundred tables. However, this database does not use the default postgres user, and there are no tables in the public schema.

Here’s the output from this server:

lua

Skopiuj kod

# python3 /usr/lib/check_mk_agent/plugins/mk_postgres.py
su: user checkmk does not exist or the user entry does not contain all the required fields
su: user checkmk does not exist or the user entry does not contain all the required fields
su: user checkmk does not exist or the user entry does not contain all the required fields
su: user checkmk does not exist or the user entry does not contain all the required fields
<<<postgres_instances>>>
[[[db1]]]
2829046 postgres: 16/main: pgsql db1(1545) idle
2829050 postgres: 16/main: pgsql db1(1547) idle
2829053 postgres: 16/main: pgsql db1(1548) idle
<<<postgres_stat_database:sep(59)>>>
[[[db1]]]

<<<postgres_version:sep(1)>>>
[[[db1]]]

<<<postgres_conn_time>>>
[[[db1]]]
0.004

Do you have any suggestions on how to configure this?

[Kosiarka]

I found the reason why it wasn’t working for me – I was missing a local user named checkmk, identical to the one I created in the database. After creating this user, all parameters are now being monitored. Below is the SQL that grants permissions to all schemas and tables in the database for the checkmk user

DO $
DECLARE
    schema_var text;
BEGIN
    FOR schema_var IN
        SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('pg_catalog', 'information_schema')
    LOOP
        EXECUTE format('GRANT CONNECT ON DATABASE database_name TO checkmk;');
        EXECUTE format('GRANT USAGE ON SCHEMA %I TO checkmk;', schema_var);
        EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO checkmk;', schema_var);
    END LOOP;
END $;