... | @@ -1026,6 +1026,101 @@ packages. For example, in Debian bullseye: |
... | @@ -1026,6 +1026,101 @@ packages. For example, in Debian bullseye: |
|
To ensure backups are properly performed, the server must also have
|
|
To ensure backups are properly performed, the server must also have
|
|
the `profile::postgres` class.
|
|
the `profile::postgres` class.
|
|
|
|
|
|
|
|
### Prometheus PostgreSQL exporter deployment
|
|
|
|
|
|
|
|
Prometheus metrics collection is not enabled by default. To enable it,
|
|
|
|
include the following line in `/etc/postgresql/11/main/pg_hba.conf`:
|
|
|
|
|
|
|
|
local all prometheus peer
|
|
|
|
|
|
|
|
Then run the following SQL queries as the `postgres` user, for example
|
|
|
|
after `sudo -u postgres psql`:
|
|
|
|
|
|
|
|
CREATE USER prometheus PASSWORD 'password';
|
|
|
|
ALTER USER prometheus SET SEARCH_PATH TO postgres_exporter,pg_catalog;
|
|
|
|
|
|
|
|
CREATE SCHEMA postgres_exporter AUTHORIZATION prometheus;
|
|
|
|
|
|
|
|
CREATE VIEW postgres_exporter.pg_stat_activity
|
|
|
|
AS
|
|
|
|
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 is 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 $$
|
|
|
|
BEGIN
|
|
|
|
IF NOT EXISTS (
|
|
|
|
SELECT -- SELECT list can stay empty for this
|
|
|
|
FROM pg_catalog.pg_user
|
|
|
|
WHERE usename = 'prometheus') THEN
|
|
|
|
CREATE USER prometheus;
|
|
|
|
END IF;
|
|
|
|
END;
|
|
|
|
$$ language plpgsql;
|
|
|
|
|
|
|
|
SELECT __tmp_create_user();
|
|
|
|
DROP FUNCTION __tmp_create_user();
|
|
|
|
|
|
|
|
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;
|
|
|
|
|
|
|
|
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
|
|
|
|
have the module loaded. That error can be safely ignored.
|
|
|
|
|
|
|
|
Note that both procedures were modified to use the `prometheus`
|
|
|
|
(instead of `postgres_exporter`) user, and to remove the hardcoded
|
|
|
|
password (since we rely on the "peer" authentication method).
|
|
|
|
|
|
## SLA
|
|
## SLA
|
|
|
|
|
|
No service level is defined for this service.
|
|
No service level is defined for this service.
|
... | @@ -1105,8 +1200,9 @@ dashboards in various working states: |
... | @@ -1105,8 +1200,9 @@ dashboards in various working states: |
|
* [Postgres Overview](https://grafana.torproject.org/d/wGgaPlciz/postgres-overview) - basic dashboard with minimal metrics
|
|
* [Postgres Overview](https://grafana.torproject.org/d/wGgaPlciz/postgres-overview) - basic dashboard with minimal metrics
|
|
|
|
|
|
We do have a Puppet class (`profile::prometheus::postgres_exporter`
|
|
We do have a Puppet class (`profile::prometheus::postgres_exporter`
|
|
which can monitor PostgreSQL servers, but it is not deployed on
|
|
which can monitor PostgreSQL servers, but it is not deployed on all
|
|
servers at the time of writing.
|
|
hosts. This is because there is still a manual step in the
|
|
|
|
configuration of the exporter. See "installation" above.
|
|
|
|
|
|
Note that there is a program called [pgstatsmon](https://github.com/joyent/pgstatsmon) which can provide
|
|
Note that there is a program called [pgstatsmon](https://github.com/joyent/pgstatsmon) which can provide
|
|
very detailed information about the state of a PostgreSQL database,
|
|
very detailed information about the state of a PostgreSQL database,
|
... | | ... | |