Add a Database — Self-Managed PostgreSQL — MonPG Docs

Self-managed PostgreSQL means you run the server yourself, on a VM, in a container, on bare metal, or in your own data center. The MonPG hosted collector almost never works for these — your DB is usually behind a firewall and not internet-reachable. Plan on agent mode unless you've specifically exposed Postgres to the world (please don't).

What you'll do

You create the monitor role yourself with the SQL we provide, edit pg_hba.conf to allow it to connect, optionally update postgresql.conf to enable a few logging settings, then run our collector container alongside Postgres. The collector pushes metrics to api.monpg.app over HTTPS using a token you generate in our UI.

Provisioning SQL

Run as a superuser on each database you want to monitor. We've kept this minimal:

-- Once, cluster-wide:
CREATE ROLE monpg_monitor_$(openssl rand -hex 6) WITH LOGIN
  PASSWORD 'pick-a-strong-random-password'
  CONNECTION LIMIT 10
  NOSUPERUSER NOCREATEROLE NOCREATEDB NOBYPASSRLS;
GRANT pg_monitor TO monpg_monitor_xxx;

-- For each database to monitor:
\c production
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE SCHEMA IF NOT EXISTS monpg;

CREATE OR REPLACE FUNCTION monpg.get_column_stats()
RETURNS TABLE(schemaname name, tablename name, attname name, inherited bool,
              null_frac real, avg_width int, n_distinct real, correlation real)
LANGUAGE sql VOLATILE SECURITY DEFINER SET search_path = pg_catalog AS $$
  SELECT schemaname, tablename, attname, inherited,
         null_frac, avg_width, n_distinct, correlation
  FROM pg_catalog.pg_stats
  WHERE schemaname NOT IN ('pg_catalog','information_schema')
    AND tablename != 'pg_subscription';
$$;

CREATE OR REPLACE FUNCTION monpg.get_relation_stats_ext()
RETURNS TABLE(statistics_schemaname text, statistics_name text, inherited boolean,
              n_distinct pg_ndistinct, dependencies pg_dependencies,
              most_common_val_nulls boolean[], most_common_freqs float8[],
              most_common_base_freqs float8[])
LANGUAGE sql VOLATILE SECURITY DEFINER SET search_path = pg_catalog AS $$
  SELECT statistics_schemaname::text, statistics_name::text,
         (row_to_json(se.*)::jsonb ->> 'inherited')::boolean,
         n_distinct, dependencies,
         most_common_val_nulls, most_common_freqs, most_common_base_freqs
  FROM pg_catalog.pg_stats_ext se
  WHERE schemaname NOT IN ('pg_catalog','information_schema');
$$;

GRANT USAGE ON SCHEMA monpg TO pg_monitor;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA monpg TO pg_monitor;

The two SECURITY DEFINER helpers exist because pg_stats and pg_stats_ext have row-visibility rules that pg_monitor alone can't satisfy. The functions are read-only, scoped to user schemas (the WHERE clauses exclude pg_catalog and information_schema), and run with our monitor role's privileges. Nothing here grants write access.

pg_hba.conf

For a same-host agent (the simplest case):

host  all  monpg_monitor_xxx  127.0.0.1/32  scram-sha-256

For an agent on a different host on the same VPN or VPC:

host  all  monpg_monitor_xxx  10.0.0.0/8  scram-sha-256

(Tighten the CIDR to whatever's actually true — /8 is permissive.)

Reload Postgres after the edit: SELECT pg_reload_conf();. No restart needed for pg_hba changes.

postgresql.conf

The minimum for MonPG to work is shared_preload_libraries = 'pg_stat_statements'. Most builds already have it. If yours doesn't, you'll need to restart after adding it.

While you're in there, set a few logging defaults that make Log Insights useful: track_io_timing = on, log_min_duration_statement = 1000, log_lock_waits = on, log_checkpoints = on, log_autovacuum_min_duration = 1000, log_connections = on. None of those need a restart, just SELECT pg_reload_conf();.

Run the collector

Generate an agent token in app.monpg.app → Settings → Agent Tokens. Then start our container:

docker run -d --name monpg-collector --restart unless-stopped \
  -e MONPG_AGENT_TOKEN="mpa_..." \
  -e MONPG_API_ENDPOINT="https://api.monpg.app" \
  -e MONPG_DB_HOST="localhost" \
  -e MONPG_DB_USER="monpg_monitor_xxx" \
  -e MONPG_DB_PASSWORD="..." \
  -e MONPG_DB_NAME="postgres" \
  -e MONPG_DB_SSLMODE="require" \
  monpgacr4wjl3deb.azurecr.io/monpg-collector:latest

Within thirty seconds your server appears in the MonPG UI. If it doesn't, check the container logs. The most common failures are covered here.

Other deploy targets

Kubernetes (Helm chart): guide. systemd (bare metal / VMs): guide. Plain binary (no container, no systemd): guide. Air-gapped environments: guide.