Add Table Size from Exporter

[mowirth]

Proposal

We want to monitor database sizes over Prometheus to detect if tables are running full. However, this metric is not (yet ;)) exported by this exporter.

PostgreSQL has different metrics for measuring table size, from indexes, relations and total_relations. It should be possible to query these metrics with Prometheus, to setup alerts when a table is getting too large (notifying operators to take action) or to take storage consumption at all into consideration.

[Zurlys]

Hi @mowirth,

I needed such functionality, so I made a workaround for this without waiting. I hope you will find it useful. The approach is not the nicest one, but it works.

1st, create a function in PostgreSQL:

CREATE OR REPLACE FUNCTION get_all_database_table_sizes()
RETURNS TABLE(database_name TEXT, schema_name TEXT, table_name TEXT, table_size BIGINT) AS
$$
DECLARE
    db RECORD;
    conn_str TEXT;
    sql TEXT;
BEGIN
    FOR db IN
        SELECT datname FROM pg_database WHERE datistemplate = false
    LOOP
        conn_str := format('host=127.0.0.1 dbname=%s user=postgres_exporter password=YourPassword', db.datname);

        sql := '
            SELECT current_database() AS database_name, schemaname, relname, pg_total_relation_size(relid)
            FROM pg_catalog.pg_statio_user_tables';

        RETURN QUERY
        EXECUTE format('
            SELECT * FROM dblink(%L, %L) AS t(database_name TEXT, schema_name TEXT, table_name TEXT, table_size BIGINT)',
            conn_str,
            sql);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

You have to load a dblink extention for that:

CREATE EXTENSION dblink;

Create permission for postgres_exporter user to execute this function:

GRANT EXECUTE ON FUNCTION get_all_database_table_sizes() TO postgres_exporter;

Try to execute:

SELECT * FROM get_all_database_table_sizes();

If all works as expected - add the call to the /etc/prometheus/postgres_exporter_queries.yaml:

pg_table_size:
  query: "SELECT * FROM get_all_database_table_sizes();"
  cache_seconds: 300
  master: true
  metrics:
    - database_name:
        usage: "LABEL"
    - schema_name:
        usage: "LABEL"
    - table_name:
        usage: "LABEL"
    - table_size:
        usage: "GAUGE"
        description: "Size of the table in bytes"

Do not forget to modify your pg_hba.conf to allow postgres_exporter to connect. F.ex.:

host    all             postgres_exporter  127.0.0.1/32            md5
host    all             postgres_exporter  ::1/128                 md5

[xrl]

Does it have to be done with an extension and function?