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 from pg_stat_statements.
• Database inventory and PostgreSQL server settings.
• Memory and buffer cache information from pg_buffercache.
• Table scan and index usage diagnostics.
• 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.
• pg_buffercache extension: required for buffer cache and memory inspection.
• file_fdw extension: required for querying PostgreSQL log files through a foreign data wrapper.

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 pg_buffercache;
CREATE EXTENSION IF NOT EXISTS file_fdw;

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.

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 buffer cache information is missing, enable pg_buffercache.
• 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, and pg_buffercache improves memory and cache analysis.

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 log
• Connect database servers
• Troubleshoot database connectivity
• Connectivity Log