argument exclude-databases is not honored

[m4h]

Hello,

Both exclude-databases cmd argument and PG_EXPORTER_EXCLUDE_DATABASES are not honored by postgre_exporter.
In both cases databases specified as string separated by ,.
After systemctl daemon-reload and restarting service, excluded databases metrics are still collected.

exporter version: postgres_exporter v0.8.0 (built with go1.11)
systemd nit:

[Unit]
Description=prometheus postgres exporter for XYZ database
Requires=network-online.target
After=network-online.target

[Service]
PrivateTmp=yes
User=prometheus
Group=prometheus
RuntimeDirectory=postgres_exporter
Environment=DATA_SOURCE_NAME="postgresql://root:[email protected]:5432/postgres?sslmode=disable"
Environment=PG_EXPORTER_CONSTANT_LABELS="sql_instance=XYZ"
ExecStart=/opt/postgres_exporter/bin/postgres_exporter --log.level=info --log.format='logger:stdout?txt=true' --web.listen-address=0.0.0.0:9187 --web.telemetry-path=/metrics --exclude-databases=template0,template1,cloudsqladmin
StandardOutput=journal
StandardError=journal
ExecReload=/bin/kill -HUP $MAINPID
KillSignal=SIGTERM
Restart=always

[Install]
WantedBy=multi-user.target

Please advice.
Thank you for your time.

[sfalkon]

Hi!

Maybe #343 .

Can you show metrics, which did not be exclude?

[frittentheke]

@sfalkon I just ran into this issue myself and the exporter does NOT exclude the template databases like template0, ... databases by default and it also (when using i.e. PG_EXPORTER_EXCLUDE_DATABASES="postgres") does not exclude explicitly defined database names.

[alexd88]

Same trouble

systemd unit file:

[Unit]
Description=PostgreSQL exporter for Prometheus
Requires=network-oneline.target
After=network-oneline.target

[Service]
User=telegraf
Group=telegraf
EnvironmentFile=/etc/sysconfig/postgres_exporter
ExecStart=/opt/postgres_exporter/postgres_exporter

[Install]
WantedBy=multi-user.target

EnvironmentFile:

DATA_SOURCE_NAME='postgresql://user:[email protected]:5432/postgres?sslmode=disable'
PG_EXPORTER_AUTO_DISCOVER_DATABASES=true
PG_EXPORTER_EXCLUDE_DATABASES='template0,template1'

(it changes nothing if i define PG_EXPORTER_EXCLUDE_DATABASES or not)

And what i see then:

[root@bash]# ./postgres_exporter --version
postgres_exporter v0.8.0 (built with go1.11)

[sfalkon]

It is normal. If you execute query "select * from pg_stat_user_tables;" for any database in a cluster. You will see it

Because it's global information.
You will see sessions from pg_stat_activity and queries from pg_state_statements for excluded databases, but you will not see metrics from pg_stat_user_tables and etc.

Sorry for my English

[kinzleb]

This causes a problem for me too, I'm using Azure Database for PostgreSQL server which adds a database named "azure_maintenance" that is not meant to be accessible and only used by the managed service.

For the following query for pg_database:
SELECT pg_database.datname, pg_database_size(pg_database.datname) as size_bytes FROM pg_database

I get an error:
permission denied for database azure_maintenance

Whereas if I change the query to add a where clause it returns results successfully:
SELECT pg_database.datname, pg_database_size(pg_database.datname) as size_bytes FROM pg_database where pg_database.datname <> 'azure_maintenance'

I'm using the following helm chart for my deployment (which was recently updated to use the image from quay.io/prometheuscommunity/postgres-exporter rather than wrouesnel/postgres_exporter):
https://github.com/prometheus-community/helm-charts/tree/main/charts/prometheus-postgres-exporter

And I am setting "config.excludeDatabases" to include "azure_maintenance" which previously was working but now no longer seems to be honored as the OP notes.

[petef19]

+1

I set PG_EXPORTER_EXCLUDE_DATABASES='template0,template1' as env var in docker-compose but both template0 and template1 still have metrics...

Thanks.

[psvampa]

+1

I've created a very small and simple custom query file:

    pg_database:
      query: "SELECT pg_database.datname, pg_database_size(pg_database.datname) as size_bytes FROM pg_database"
      master: true
      metrics:
        - datname:
            usage: "LABEL"
            description: "Name of the database"
        - size_bytes:
            usage: "GAUGE"
            description: "Disk space used by the database"

    pg_database_2:
      query: "SELECT pg_database.datname, pg_database_size(pg_database.datname) as size_bytes FROM pg_database WHERE datname = current_database()"
      master: true
      metrics:
        - datname:
            usage: "LABEL"
            description: "Name of the database"
        - size_bytes:
            usage: "GAUGE"
            description: "Disk space used by the database"

My expectation on this one. pg_database should provide database and size for all DBs on given instance. pg_database_2 should provide ONLY the databse size on the DB I am connecting to since I explicitly added WHERE datname = current_database()

My exporter command and configuration:
exec postgres_exporter --web.listen-address :9189 --disable-default-metrics --auto-discover-databases --exclude-databases postgres,template0,template1 --extend.query-path /tmp/queries.yaml

So, according the documentation it should avoid connecting to any of postgres,template0,template1 DBs. Then pg_database_2 should report target DB names and their sizes

Scraping from the exposed metrics:

[root@nsoengas-db-0 /]# curl http://localhost:9189/metrics | grep pg_database
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0# HELP pg_database_2_size_bytes Disk space used by the database
# TYPE pg_database_2_size_bytes gauge
pg_database_2_size_bytes{datname="postgres",server="localhost:5432"} 7.526936e+06
# HELP pg_database_size_bytes Disk space used by the database
# TYPE pg_database_size_bytes gauge
pg_database_size_bytes{datname="nsoengas",server="localhost:5432"} 7.58428e+06
pg_database_size_bytes{datname="postgres",server="localhost:5432"} 7.526936e+06
pg_database_size_bytes{datname="template0",server="localhost:5432"} 7.414276e+06
pg_database_size_bytes{datname="template1",server="localhost:5432"} 7.414276e+06
100 51180    0 51180    0     0  4543k      0 --:--:-- --:--:-- --:--:-- 4543k
[root@nsoengas-db-0 /]#

As you could see pg_database shows all the 4 DB names and sizes as expected. However, pg_database_2 is reporting postgres DB size which is wrong. IMO it should be reporting "nsoengas" DB size.

If somebody have any idea it will be welcome. I've already tested several configuration and input formats on the excluding list

Thank you in advance

[rbudiharso]

+1
I get Error running query on database "somehost.rds.amazonaws.com:5432": pg_database pq: permission denied for database rdsadmin on RDS instance eventhough I already exclude database rdsadmin

[arm82]

+1
Any news? The issue is still exists,
permission denied for database rdsadmin error and excluding list is not making sense

[MichaelDBA]

Also interested in this getting resolved

[sysadmind]

v0.12.0 should fix this. If there are still problems, please let me know. Because this issue is so old, a more recent issue with all the relevant information would be good (version, logs, redacted config, etc).

[hmrks]

Confirming that this is fixed in version v0.12.0. Previous version we tested (v0.11.0) still produced the error message about the rdsadmin database being unreachable even with the exclude flag being set, but the latest version works as expected.