Connect PostgreSQL to Mini DBA

Use the PostgreSQL connection workflow to add a PostgreSQL server to Mini DBA monitoring. PostgreSQL monitoring uses database roles, optional extensions, PostgreSQL log configuration, and optional host OS login for CPU, memory, drive, and disk I/O metrics.

Add server fields

In Servers, select Add Server and choose PostgreSQL.

The Mini DBA Engine must be able to reach PostgreSQL, normally on port 5432 unless your server uses a different port.

What Mini DBA monitors after connection

With the recommended PostgreSQL roles and extensions, Mini DBA can collect:

• PostgreSQL activity, sessions, waits, and blocked or blocking workload.
• Query statistics, query history, regressions, and selected plan history from pg_stat_statements.
• Database inventory and PostgreSQL server settings.
• Cache-hit ratios and configured PostgreSQL memory settings without requiring pg_buffercache.
• Table scan, index usage, table maintenance, optional bloat, and missing-index diagnostics.
• Extension-specific checks for TimescaleDB, PostGIS, and pgvector when those extensions are installed.
• PostgreSQL logs, slow query events, and deadlock evidence when logging and file_fdw are configured.
• Host CPU, memory, drive capacity, and disk I/O when Host OS Login is configured.

Required PostgreSQL roles and extensions

The PostgreSQL permissions modal checks:

• Super User: useful for testing, but not recommended for routine monitoring.
• pg_monitor: broad monitoring role. Useful when narrower roles are not all granted.
• pg_read_all_settings: essential for reading PostgreSQL configuration.
• pg_read_all_stats: important for activity, database, and query statistics.
• pg_stat_scan_tables: useful for index and table scan diagnostics.
• pg_read_server_files: useful for PostgreSQL log file access.
• pg_signal_backend: optional, allows canceling or terminating sessions.
• pg_stat_statements extension: required for execution statistics and current PID activity.
• file_fdw extension: required for querying PostgreSQL log files through a foreign data wrapper.
• pgstattuple extension: optional, improves table-maintenance and bloat evidence where permitted.

SQL example: create a monitoring user

Run this as a PostgreSQL administrator. Change the password first.

CREATE USER minidba_monitor WITH PASSWORD 'UseASecretPasswordHere';

GRANT pg_monitor TO minidba_monitor;
GRANT pg_read_all_settings TO minidba_monitor;
GRANT pg_read_all_stats TO minidba_monitor;
GRANT pg_stat_scan_tables TO minidba_monitor;
GRANT pg_read_server_files TO minidba_monitor;
GRANT pg_signal_backend TO minidba_monitor;
GRANT pg_read_all_data TO minidba_monitor;

If your security policy does not allow pg_read_all_data, grant schema or table-specific SELECT permissions instead for the databases Mini DBA should inspect.

SQL example: enable recommended extensions

Run these in each database where the extension should be available:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS file_fdw;
CREATE EXTENSION IF NOT EXISTS pgstattuple;

For pg_stat_statements, PostgreSQL normally also requires this in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Reload or restart PostgreSQL as required by your PostgreSQL version and hosting model.

TimescaleDB, PostGIS, and pgvector are application extensions rather than Mini DBA-only requirements. When they are installed in a monitored database, Mini DBA automatically shows the relevant capability-gated checks on the PostgreSQL Extensions page.

SQL example: optional debug workload schema

Mini DBA debug workload buttons need a writable non-system schema in the monitored database. This is only required for debug/demo validation buttons such as Trigger Query Workload, Trigger Plan Change, Trigger Lock Chain, Trigger Missing Index Workload, and Trigger Vacuum Pressure.

Run this as a PostgreSQL administrator in each database where those debug workloads should be available:

CREATE SCHEMA IF NOT EXISTS minidba_debug AUTHORIZATION minidba_monitor;
GRANT USAGE, CREATE ON SCHEMA minidba_debug TO minidba_monitor;

Change minidba_monitor to match the monitoring login. Production monitoring does not require this schema unless you intend to use the debug validation workloads.

PostgreSQL log reading

For richer log, slow query, and deadlock visibility, configure PostgreSQL logging. A typical starting point is:

logging_collector = 'on'
log_destination = 'stderr,csvlog'

Apply the change with a service reload, or run:

SELECT pg_reload_conf();

If you use file_fdw for log access, create a foreign data wrapper server:

CREATE SERVER log_file_server FOREIGN DATA WRAPPER file_fdw;

Verify permissions in Mini DBA

After adding PostgreSQL, open the PostgreSQL server and open the PostgreSQL Connectivity Permissions modal. Select Refresh to probe roles, extensions, and host OS status.

Use the modal after:

• Creating a new monitoring role.
• Enabling or disabling extensions.
• Changing PostgreSQL log settings.
• Adding an SSH or cloud host login.
• Troubleshooting missing PostgreSQL activity, queries, logs, indexes, waits, or memory data.

Host OS login and metrics

PostgreSQL host OS login is configured from the PostgreSQL permissions modal after the server is added. On Linux, use SSH with password or private key authentication; Mini DBA uses host commands such as top, df, and iostat. On Windows, use an account with suitable WMI and performance counter access where the PostgreSQL host supports Windows host monitoring.

Host OS login can add:

• Host CPU percentage.
• Total, used, and free memory.
• File system capacity and free space.
• Disk reads and writes per second.
• Disk throughput, latency, queue length, and utilization.

For detailed setup, see Host OS Login.

PostgreSQL connection troubleshooting

• If connection fails, confirm the Mini DBA Engine can reach the PostgreSQL host and port.
• If authentication fails, check pg_hba.conf, SSL requirements, username, and password.
• If query statistics are empty, enable pg_stat_statements and confirm it is loaded.
• If Query History is empty, confirm pg_stat_statements is created in the monitored database, the engine has sampled after workload has run, and the selected History filters are not excluding the rows.
• If debug workload buttons report a missing writable schema, create and grant the optional minidba_debug schema in the monitored database.
• If bloat estimates are missing, enable pgstattuple in the monitored database and confirm the monitoring login can call the extension functions.
• If PostgreSQL logs are unavailable, configure logging_collector, log destination, file_fdw, and server-side file access.
• If host metrics are missing, configure SSH credentials and ensure top, df, and iostat are available.
• Right-click the PostgreSQL server in the tree and open Connectivity Log for PostgreSQL authentication, pg_hba.conf, SSL, timeout, extension, and data-access errors captured by the engine.

PostgreSQL monitoring permissions FAQ

Is superuser required?

No. Superuser can make testing easier, but routine monitoring should use narrower roles such as pg_monitor, pg_read_all_settings, pg_read_all_stats, and related read roles.

Why does Mini DBA ask for extensions?

PostgreSQL exposes some important performance information through extensions. pg_stat_statements is especially important for query monitoring. Mini DBA reports cache-hit ratios and configured memory capacity but intentionally does not inspect live shared-buffer occupancy, so pg_buffercache is not required.

Does host OS login replace PostgreSQL roles?

No. PostgreSQL roles give database-level visibility. Host OS login adds operating system metrics such as CPU, memory, file system usage, and disk I/O.

Related pages

• PostgreSQL monitoring
• PostgreSQL permissions
• PostgreSQL queries
• PostgreSQL extensions
• PostgreSQL log
• Connect database servers
• Troubleshoot database connectivity
• Connectivity Log