Troubleshooting

This guide helps you diagnose and resolve common issues with the PostgreSQL Server Exporter.

Connection Issues

Connection refused

Error Message:

Error opening connection to database: dial tcp 127.0.0.1:5432: connect: connection refused

Causes:

PostgreSQL is not running
Wrong hostname or port
PostgreSQL not listening on the specified interface

Solutions:

Verify PostgreSQL is running:

# Linux
sudo systemctl status postgresql

# Check if PostgreSQL is listening
sudo ss -tulpn | grep 5432

Check connection string:

# Test connection with psql
psql "postgresql://username:password@hostname:5432/dbname"

Check PostgreSQL listen address:

# In postgresql.conf
listen_addresses = 'localhost'  # Change to '*' or specific IP

Docker networking:

# If PostgreSQL is in Docker, use container name or correct network
docker run --net=host ...  # or
docker run --net=postgres_network ...

Authentication failed

Error Message:

pq: password authentication failed for user "postgres_exporter"

Causes:

Wrong username or password
User doesn’t exist
pg_hba.conf doesn’t allow the connection

Solutions:

Verify credentials:

# Test with psql
psql -U postgres_exporter -h hostname -d postgres

Check pg_hba.conf:

# Add this line to allow password authentication
host    all    postgres_exporter    0.0.0.0/0    md5

# Reload PostgreSQL
sudo systemctl reload postgresql

Create user if missing:

CREATE USER postgres_exporter WITH PASSWORD 'your_password';
GRANT pg_monitor TO postgres_exporter;
GRANT CONNECT ON DATABASE postgres TO postgres_exporter;

Special characters in password:

# URL-encode special characters
# Password: pass@word#123 becomes pass%40word%23123
DATA_SOURCE_NAME="postgresql://user:pass%40word%23123@host:5432/db"

SSL/TLS errors

Error Message:

pq: SSL is not enabled on the server

x509: certificate signed by unknown authority

Solutions:

Disable SSL for local testing:

DATA_SOURCE_URI="localhost:5432/postgres?sslmode=disable"

Enable SSL in PostgreSQL:

# In postgresql.conf
ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'

Trust server certificate:

DATA_SOURCE_URI="host:5432/postgres?sslmode=require"

Verify CA certificate:

DATA_SOURCE_URI="host:5432/postgres?sslmode=verify-ca&sslrootcert=/path/to/ca.crt"

Skip verification (not recommended for production):

DATA_SOURCE_URI="host:5432/postgres?sslmode=require"

Timeout errors

Error Message:

context deadline exceeded

Causes:

Database is slow to respond
Collection timeout too short
Heavy load on database

Solutions:

Increase collection timeout:

export PG_EXPORTER_COLLECTION_TIMEOUT="2m"
./postgres_exporter

Increase connection timeout:

DATA_SOURCE_URI="host:5432/postgres?connect_timeout=10"

Check database performance:

-- Check for long-running queries
SELECT pid, now() - query_start as duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

Disable slow collectors:

./postgres_exporter \
  --no-collector.stat_user_tables \
  --no-collector.statio_user_tables

Too many connections

Error Message:

pq: sorry, too many clients already

Causes:

PostgreSQL connection limit reached
Connections not being closed properly
Too many concurrent scrapes

Solutions:

Increase PostgreSQL max_connections:

# In postgresql.conf
max_connections = 200

Lower collection timeout:

# Connections dropped faster
export PG_EXPORTER_COLLECTION_TIMEOUT="30s"

Check current connections:

SELECT count(*) FROM pg_stat_activity;
SELECT * FROM pg_stat_activity WHERE usename = 'postgres_exporter';

Increase scrape interval in Prometheus:

scrape_configs:
  - job_name: postgres
    scrape_interval: 60s  # Reduce frequency

Metrics Issues

No metrics returned

Symptoms: /metrics endpoint returns only exporter internal metricsSolutions:

Check exporter logs:
```
./postgres_exporter --log.level=debug
```

Verify DATA_SOURCE_NAME:

echo $DATA_SOURCE_NAME
# Should be a valid PostgreSQL connection string

Test connection manually:
```
psql "$DATA_SOURCE_NAME"
```

Check if disable-default-metrics is set:

# This disables all built-in metrics
unset PG_EXPORTER_DISABLE_DEFAULT_METRICS

Missing specific metrics

Symptoms: Some expected metrics are not presentCauses:

Collector is disabled
Insufficient permissions
PostgreSQL version incompatibility

Solutions:

Check collector status:

# Look for scrape_collector_success metrics
curl localhost:9187/metrics | grep scrape_collector_success

Enable the collector:

# Example: enable pg_stat_statements
./postgres_exporter --collector.stat_statements

Check permissions:

-- Verify user has pg_monitor role
SELECT * FROM pg_roles WHERE rolname = 'postgres_exporter';

-- Check specific table permissions
SELECT * FROM information_schema.table_privileges 
WHERE grantee = 'postgres_exporter';

Install required extensions:

-- For pg_stat_statements metrics
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Collector failures

Symptoms: pg_scrape_collector_success is 0 for some collectorsCheck logs:

./postgres_exporter --log.level=debug 2>&1 | grep "collector failed"

Common failures:

pg_stat_statements: Extension not installed or loaded

-- Check if extension exists
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

-- Check postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'

replication: User lacks replication permissions

-- Grant replication monitoring
GRANT pg_monitor TO postgres_exporter;

stat_user_tables: Permission denied on user tables

-- For PostgreSQL 10+
GRANT pg_monitor TO postgres_exporter;

Metrics have wrong values

Symptoms: Metrics exist but show unexpected valuesTroubleshooting steps:

Query PostgreSQL directly:

-- Compare with what PostgreSQL reports
SELECT * FROM pg_stat_database WHERE datname = 'your_database';

Check metric labels:

# Ensure you're looking at the right instance/database
curl localhost:9187/metrics | grep 'pg_stat_database_.*{datname="postgres"}'

Verify scrape timing:

# Prometheus config
scrape_interval: 30s
# Counters show rate, not absolute values in Prometheus

Check for multiple exporters:

# Ensure no duplicate exporters for same target
curl localhost:9187/metrics | grep pg_up

Permission Errors

Permission denied on pg_stat_*

Error Message:

ERROR: permission denied for table pg_stat_replication

Solutions:

Grant pg_monitor role (PostgreSQL 10+):
```
GRANT pg_monitor TO postgres_exporter;
```

Create security definer functions (PostgreSQL 9.x):

-- See Security Best Practices guide for full script
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;

Verify role membership:

SELECT r.rolname, m.rolname as member
FROM pg_roles r
JOIN pg_auth_members a ON r.oid = a.roleid
JOIN pg_roles m ON a.member = m.oid
WHERE m.rolname = 'postgres_exporter';

Cannot read password file

Error Message:

Error reading password file: permission denied

Solutions:

Fix file permissions:

chmod 600 /path/to/password/file
chown exporter-user:exporter-group /path/to/password/file

Docker container permissions:

# Container runs as uid:gid 65534:65534
chown 65534:65534 /path/to/password/file
chmod 400 /path/to/password/file

Kubernetes volume permissions:

securityContext:
  fsGroup: 65534
volumes:
  - name: password
    secret:
      secretName: postgres-password
      defaultMode: 0400

Multi-target Issues

Auth module not found

Error Message:

auth_module 'production' not found

Solutions:

Check config file is loaded:

./postgres_exporter --config.file=postgres_exporter.yml

Verify config file syntax:

auth_modules:
  production:  # Must match parameter exactly
    type: userpass
    userpass:
      username: user
      password: pass

Check config reload status:

curl localhost:9187/metrics | grep config_last_reload

Validate YAML:

# Install yamllint
yamllint postgres_exporter.yml

Target parameter missing

Error Message:

target is required

Solution:Ensure Prometheus relabeling is correct:

relabel_configs:
  - source_labels: [__address__]
    target_label: __param_target
  - source_labels: [__param_target]
    target_label: instance
  - target_label: __address__
    replacement: localhost:9187

Docker-Specific Issues

Container can't connect to host PostgreSQL

Solutions:

Use host networking:
```
docker run --net=host ...
```

Use host.docker.internal (Mac/Windows):

-e DATA_SOURCE_URI="host.docker.internal:5432/postgres"

Use Docker network:

docker network create monitoring
docker run --network monitoring --name postgres postgres
docker run --network monitoring \
  -e DATA_SOURCE_URI="postgres:5432/postgres" \
  postgres-exporter

Use host IP (Linux):

# Find host IP
ip addr show docker0

# Use that IP
-e DATA_SOURCE_URI="172.17.0.1:5432/postgres"

Environment variables not working

Symptoms: Container ignores DATA_SOURCE_NAME or other env varsSolutions:

Check variable names:

# Correct
-e DATA_SOURCE_URI="..."
-e DATA_SOURCE_USER="..."
-e DATA_SOURCE_PASS="..."

# NOT datasource, data_source, etc.

Quote values properly:

# Wrong - shell expands
-e DATA_SOURCE_PASS=$PASSWORD

# Correct
-e DATA_SOURCE_PASS="$PASSWORD"

Use env file:

# .env file
DATA_SOURCE_URI=localhost:5432/postgres
DATA_SOURCE_USER=postgres_exporter
DATA_SOURCE_PASS=password

# Run with
docker run --env-file .env postgres-exporter

Debugging Tips

Enable Debug Logging

./postgres_exporter --log.level=debug --log.format=logfmt

Debug logs show:

Connection attempts
Collector execution
Query durations
Error details

Check Exporter Health

# Metrics endpoint should respond
curl -v http://localhost:9187/metrics

# Check up metric
curl -s http://localhost:9187/metrics | grep pg_up
# pg_up 1 means connected
# pg_up 0 means connection failed

Test with psql

Always verify the connection string works with psql:

# Extract connection string from exporter config
psql "$DATA_SOURCE_NAME"

# Or construct from parts
psql -h hostname -p 5432 -U postgres_exporter -d postgres

Check PostgreSQL Logs

# Find PostgreSQL log location
psql -c "SHOW log_directory;"
psql -c "SHOW log_filename;"

# Watch logs
tail -f /var/log/postgresql/postgresql-*.log

Look for:

Connection attempts from exporter
Authentication failures
Permission errors
Query errors

Verify Collector Metrics

# See which collectors succeeded
curl -s localhost:9187/metrics | grep pg_scrape_collector_success

# See collector durations
curl -s localhost:9187/metrics | grep pg_scrape_collector_duration_seconds

# Find slow collectors
curl -s localhost:9187/metrics | \
  grep pg_scrape_collector_duration_seconds | \
  sort -t'{' -k2 -n

Getting Help

If you’re still experiencing issues:

Check existing issues: Search the GitHub issues

Gather information:

# Exporter version
./postgres_exporter --version

# PostgreSQL version
psql -c "SELECT version();"

# Debug logs
./postgres_exporter --log.level=debug 2>&1 | tee exporter.log

# Metrics output
curl -s localhost:9187/metrics > metrics.txt

Create a minimal reproduction:
- Simplify configuration to the bare minimum
- Test with a fresh PostgreSQL instance if possible
- Document exact steps to reproduce
Open an issue with:
- Exporter version
- PostgreSQL version
- Operating system
- Configuration (redact passwords)
- Error messages
- Debug logs

Get Started

Configuration

Deployment

Collectors

Advanced

Connection Issues

Metrics Issues

Permission Errors

Multi-target Issues

Docker-Specific Issues

Debugging Tips

Enable Debug Logging

Check Exporter Health

Test with psql

Check PostgreSQL Logs

Verify Collector Metrics

Getting Help

Next Steps

Security Best Practices

Performance Tuning

Build docs developers (and LLMs) love

Get Started

Configuration

Deployment

Collectors

Advanced

​Connection Issues

​Metrics Issues

​Permission Errors

​Multi-target Issues

​Docker-Specific Issues

​Debugging Tips

​Enable Debug Logging

​Check Exporter Health

​Test with psql

​Check PostgreSQL Logs

​Verify Collector Metrics

​Getting Help

​Next Steps

Security Best Practices

Performance Tuning

Build docs developers (and LLMs) love

Connection Issues

Metrics Issues

Permission Errors

Multi-target Issues

Docker-Specific Issues

Debugging Tips

Enable Debug Logging

Check Exporter Health

Test with psql

Check PostgreSQL Logs

Verify Collector Metrics

Getting Help

Next Steps