Remove postgres_exporter's views pg_stat_wal_sender, pg_stat_activity, and pg_stat_statements, making all monitoring code work directly with original views
The three views – postgres_exporter.pg_stat_statements
, postgres_exporter.pg_stat_activity
, and postgres_exporter.pg_stat_wal_sender
– turned out to be a blocker for the PG upgrade, causing schema dump/restore errors like column reference "XXX" is ambiguous
: production#8611 (comment 1337744396)
Here I explain what's happening during pg_upgrade (that involves dump/restore): production#8611 (comment 1338092447).
So to run upgrade, we need to drop them and then recreate.
And to run gprd test, we either need to drop them temporarily (and keep living without them for up to a couple of dozen of minutes), and then recreate – or to just get rid of them completely, since in modern Postgres (including PG12 we're currently running), we have special roles, pg_monitor
/ pg_read_all_stats
. And user postgres_exporter
already has pg_read_all_stats
, so it can read directly from the original views.
So the idea is to get rid of these views and corresponding underlying functions, and make all the code work directly with the original system views: public.pg_stat_statements
, pg_catalog.pg_stat_activity
, and pg_catalog.pg_stat_wal_sender
.
We indeed are able to get rid of them and switch to direct reading, because postgres_exporter
role already has pg_read_all_stats
:
gitlabhq_production=# \du+ postgres_exporter
List of roles
Role name | Attributes | Member of | Description
-------------------+------------+---------------------+-------------
postgres_exporter | | {pg_read_all_stats} |
gitlabhq_production=# set role to postgres_exporter;
SET
gitlabhq_production=> select * from pg_catalog.pg_stat_activity limit 1;
gitlabhq_production=> select pid from pg_catalog.pg_stat_activity limit 1;
pid
---------
1582169
(1 row)
The question is, what code needs to be changed?
Once we identify it, we can revoke permissions from the views, making them "deprecated", and eventually drop.
This is needed to unblock Postgres major upgrade activities – particularly, tests in production (since upgrade.yml
is designed in such way that dropping these view/function pairs cannot be avoided).
Related discussion 1y ago: gitlab-org&6235 (comment 624317205).
@alexander-sosna @bshah11 @rhenchen.gitlab @vitabaks @kwanyangu