... | @@ -1197,27 +1197,15 @@ include the following line in `/etc/postgresql/11/main/pg_hba.conf`: |
... | @@ -1197,27 +1197,15 @@ include the following line in `/etc/postgresql/11/main/pg_hba.conf`: |
|
local all prometheus peer
|
|
local all prometheus peer
|
|
|
|
|
|
Then run the following SQL queries as the `postgres` user, for example
|
|
Then run the following SQL queries as the `postgres` user, for example
|
|
after `sudo -u postgres psql`:
|
|
after `sudo -u postgres psql`, you first create the monitoring user to
|
|
|
|
match the above:
|
|
CREATE USER prometheus PASSWORD 'password';
|
|
|
|
ALTER USER prometheus SET SEARCH_PATH TO postgres_exporter,pg_catalog;
|
|
-- To use IF statements, hence to be able to check if the user exists before
|
|
|
|
-- attempting creation, we need to switch to procedural SQL (PL/pgSQL)
|
|
CREATE SCHEMA postgres_exporter AUTHORIZATION prometheus;
|
|
-- instead of standard SQL.
|
|
|
|
-- More: https://www.postgresql.org/docs/9.3/plpgsql-overview.html
|
|
CREATE VIEW postgres_exporter.pg_stat_activity
|
|
-- To preserve compatibility with <9.0, DO blocks are not used; instead,
|
|
AS
|
|
-- a function is created and dropped.
|
|
SELECT * from pg_catalog.pg_stat_activity;
|
|
|
|
|
|
|
|
GRANT SELECT ON postgres_exporter.pg_stat_activity TO prometheus;
|
|
|
|
|
|
|
|
CREATE VIEW postgres_exporter.pg_stat_replication AS
|
|
|
|
SELECT * from pg_catalog.pg_stat_replication;
|
|
|
|
|
|
|
|
GRANT SELECT ON postgres_exporter.pg_stat_replication TO prometheus;
|
|
|
|
|
|
|
|
That was the procedure specified in the [0.4.7 README](https://github.com/prometheus-community/postgres_exporter/blob/v0.4.7/README.md). The new
|
|
|
|
procedure is more involved:
|
|
|
|
|
|
|
|
CREATE OR REPLACE FUNCTION __tmp_create_user() returns void as $$
|
|
CREATE OR REPLACE FUNCTION __tmp_create_user() returns void as $$
|
|
BEGIN
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
IF NOT EXISTS (
|
... | @@ -1231,58 +1219,37 @@ procedure is more involved: |
... | @@ -1231,58 +1219,37 @@ procedure is more involved: |
|
|
|
|
|
SELECT __tmp_create_user();
|
|
SELECT __tmp_create_user();
|
|
DROP FUNCTION __tmp_create_user();
|
|
DROP FUNCTION __tmp_create_user();
|
|
|
|
|
|
|
|
This will make the user connect to the right database by default:
|
|
|
|
|
|
ALTER USER prometheus SET SEARCH_PATH TO postgres_exporter,pg_catalog;
|
|
ALTER USER prometheus SET SEARCH_PATH TO postgres_exporter,pg_catalog;
|
|
|
|
|
|
-- If deploying as non-superuser (for example in AWS RDS), uncomment the GRANT
|
|
|
|
-- line below and replace <MASTER_USER> with your root user.
|
|
|
|
-- GRANT postgres_exporter TO <MASTER_USER>;
|
|
|
|
CREATE SCHEMA IF NOT EXISTS postgres_exporter;
|
|
|
|
GRANT USAGE ON SCHEMA postgres_exporter TO prometheus;
|
|
|
|
GRANT CONNECT ON DATABASE postgres TO prometheus;
|
|
GRANT CONNECT ON DATABASE postgres TO prometheus;
|
|
|
|
|
|
CREATE OR REPLACE FUNCTION get_pg_stat_activity() RETURNS SETOF pg_stat_activity AS
|
|
|
|
$$ SELECT * FROM pg_catalog.pg_stat_activity; $$
|
|
|
|
LANGUAGE sql
|
|
|
|
VOLATILE
|
|
|
|
SECURITY DEFINER;
|
|
|
|
|
|
|
|
CREATE OR REPLACE VIEW postgres_exporter.pg_stat_activity
|
|
|
|
AS
|
|
|
|
SELECT * from get_pg_stat_activity();
|
|
|
|
|
|
|
|
GRANT SELECT ON postgres_exporter.pg_stat_activity TO prometheus;
|
|
|
|
|
|
|
|
CREATE OR REPLACE FUNCTION get_pg_stat_replication() RETURNS SETOF pg_stat_replication AS
|
|
|
|
$$ SELECT * FROM pg_catalog.pg_stat_replication; $$
|
|
|
|
LANGUAGE sql
|
|
|
|
VOLATILE
|
|
|
|
SECURITY DEFINER;
|
|
|
|
|
|
|
|
CREATE OR REPLACE VIEW postgres_exporter.pg_stat_replication
|
|
|
|
AS
|
|
|
|
SELECT * FROM get_pg_stat_replication();
|
|
|
|
|
|
|
|
GRANT SELECT ON postgres_exporter.pg_stat_replication TO prometheus;
|
|
|
|
|
|
|
|
CREATE OR REPLACE FUNCTION get_pg_stat_statements() RETURNS SETOF pg_stat_statements AS
|
|
|
|
$$ SELECT * FROM public.pg_stat_statements; $$
|
|
|
|
LANGUAGE sql
|
|
|
|
VOLATILE
|
|
|
|
SECURITY DEFINER;
|
|
|
|
|
|
|
|
CREATE OR REPLACE VIEW postgres_exporter.pg_stat_statements
|
|
|
|
AS
|
|
|
|
SELECT * FROM get_pg_stat_statements();
|
|
|
|
|
|
|
|
GRANT SELECT ON postgres_exporter.pg_stat_statements TO prometheus;
|
|
|
|
|
|
|
|
That procedure may fail on `pg_stat_statements` because we do not
|
|
... and grant the required accesses to do the probes:
|
|
have the module loaded. That error can be safely ignored.
|
|
|
|
|
|
GRANT pg_monitor to prometheus;
|
|
|
|
|
|
|
|
Note the procedure was modified from [the upstream procedure](https://github.com/prometheus-community/postgres_exporter/blob/972d8e885171e5d9d06e66a0d3e83454a7328002/README.md#running-as-non-superuser) to
|
|
|
|
use the `prometheus` user (instead of `postgres_exporter`), and to
|
|
|
|
remove the hardcoded password (since we rely on the "peer"
|
|
|
|
authentication method).
|
|
|
|
|
|
|
|
A previous version of this documentation mistakenly recommended
|
|
|
|
creating views and other complex objects that were only required in
|
|
|
|
PostgreSQL < 10, and were never actually necessary. Those can be
|
|
|
|
cleaned up with the following:
|
|
|
|
|
|
|
|
DROP SCHEMA postgres_exporter CASCADE;
|
|
|
|
DROP FUNCTION get_pg_stat_replication;
|
|
|
|
DROP FUNCTION get_pg_stat_statements;
|
|
|
|
DROP FUNCTION get_pg_stat_activity;
|
|
|
|
|
|
|
|
... and it wouldn't hurt then to rerun the above install procedure to
|
|
|
|
grant the correct rights to the `prometheus` user.
|
|
|
|
|
|
|
|
Then restart the exporter to be sure everything still works:
|
|
|
|
|
|
Note that both procedures were modified to use the `prometheus`
|
|
systemctl restart prometheus-postgres-exporter.service
|
|
(instead of `postgres_exporter`) user, and to remove the hardcoded
|
|
|
|
password (since we rely on the "peer" authentication method).
|
|
|
|
|
|
|
|
## SLA
|
|
## SLA
|
|
|
|
|
... | | ... | |