PostgreSQL

Integrating PostgreSQL with vScope lets you inventory and monitor your PostgreSQL environment. vScope discovers the PostgreSQL instance, the databases it hosts, and the schemas within each database. This gives you insight into configuration, sizing, users and roles, and replication.

Note

These permissions allow vScope to read data only. vScope does not alter or write any information to your PostgreSQL databases.

Required Permissions

vScope connects with a dedicated login role. This role should be granted the following two built-in roles:

• pg_monitor gives read access to the monitoring views (pg_stat_activity, pg_stat_replication, pg_stat_wal_receiver, pg_stat_archiver and related pg_stat_* views). vScope uses these to collect activity and replication data.
• pg_read_server_files is needed to detect replication relationships, so vScope can match a primary instance with its standby replicas.

We recommend granting both roles. pg_monitor is the minimum needed for discovery. pg_read_server_files also enables detection of replication relationships between instances.

Creating a User for vScope

Connect to your PostgreSQL instance as a superuser (for example with psql) and run:

-- Replace 'vscopereader' and 'password' with your preferred username and a secure password
CREATE ROLE vscopereader WITH LOGIN PASSWORD 'password';

-- Grant the required built-in roles
GRANT pg_monitor TO vscopereader;
GRANT pg_read_server_files TO vscopereader;

Once the role is created, configure this credential in vScope’s Discovery to allow PostgreSQL data collection.

Managed PostgreSQL services

Managed PostgreSQL services such as Amazon RDS, Azure Database for PostgreSQL and Google Cloud SQL typically do not allow customers to grant pg_read_server_files. vScope can still inventory these instances with pg_monitor alone, but replication relationships between them will not be detected.

Connecting vScope to PostgreSQL

vScope connects to PostgreSQL over a standard database connection using the IP address or hostname of the discovery target.

Requirements:

• Credential with the required permissions (see Required Permissions).
• Target (IP or hostname) of the PostgreSQL server.

Note

vScope authenticates with a username and password. Both scram-sha-256 and md5 are supported. Client-certificate and GSSAPI/Kerberos authentication are not currently supported.

1. In vScope, navigate to Discovery > Credentials and create a new credential.
2. Select PostgreSQL.
3. Enter the username and password of the role you created above.
4. Adjust the Port and encryption settings if needed (see Settings).
5. Add the Target (IP or hostname) of the PostgreSQL server.

Settings

PostgreSQL comes with default values that work in most environments, but you may change them to suit your setup.

• Port - The port vScope connects to. Defaults to 5432.
• Use Encryption - If you require an encrypted (SSL) connection, enable this. Your PostgreSQL server must be configured to accept SSL connections, otherwise the connection will fail.

Supported Versions

vScope supports the currently supported PostgreSQL versions (14 through 18). The core data collected by vScope is stable across these versions.

Note

Older PostgreSQL versions that have reached end of life may still be discovered, but they are not officially supported.

Common Errors

Error	What happened?	Suggested action
Connection refused: connect	vScope could not reach the database.	Ensure that the vScope server can communicate with the PostgreSQL server and that the correct port is used. Check that the server’s listen_addresses setting allows remote connections and that any firewall permits the port.
Invalid password / authentication failed	vScope could not log in.	Verify that the username and password of the vScope role are correct and that the account is not locked or expired.
Database does not exist	The database vScope tried to connect to was not found.	Ensure that the instance is reachable and that the default postgres database exists.
Missing data after discovery	The role is missing one of the recommended roles.	Confirm that the credential has been granted pg_monitor and pg_read_server_files (see Required Permissions).