> For the complete documentation index, see [llms.txt](https://docs.groundcover.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.groundcover.com/collect-data/data-sources/postgresql-database-monitoring.md). # PostgreSQL Database Monitoring Collect PostgreSQL health metrics and query-performance traces without running a separate exporter groundcover connects directly to a PostgreSQL instance to collect built-in health metrics and query statistics without requiring a separate Prometheus exporter. You can use the health metrics in dashboards and monitors and inspect query statistics as traces. ## Prerequisites The integration must run from a groundcover backend or monitored cluster that can reach the PostgreSQL host and port. Create a dedicated monitoring user, grant access to PostgreSQL statistics views, and enable `pg_stat_statements` in every database from which you want to collect query statistics: ```sql CREATE USER groundcover WITH PASSWORD ''; GRANT pg_monitor TO groundcover; CREATE EXTENSION IF NOT EXISTS pg_stat_statements; ``` As a database administrator, check the active preload value and whether the server has detected a change that requires a restart: ```sql SELECT name, setting AS active_value, pending_restart, source, sourcefile, sourceline FROM pg_settings WHERE name = 'shared_preload_libraries'; ``` `active_value` is the running server's value. `pending_restart = true` means the server has reread a changed setting that needs a restart; it does not show the replacement value. Changes in files that the server has not reread may not be reflected in this flag. To inspect the current configuration-file values, including entries that override earlier ones, run: ```sql SELECT sourcefile, sourceline, seqno, setting AS configured_value, applied, error FROM pg_file_settings WHERE name = 'shared_preload_libraries' ORDER BY seqno; ``` Review the entries in order and check `error`; `applied = false` can indicate an overridden entry or a setting that cannot take effect yet. This view reads the current files, not the active server state, and requires superuser access by default. If your managed service restricts access, inspect its parameter group or database flags instead. See PostgreSQL's [pg\_settings](https://www.postgresql.org/docs/current/view-pg-settings.html) and [pg\_file\_settings](https://www.postgresql.org/docs/current/view-pg-file-settings.html) references. Preserve every existing entry and append `pg_stat_statements` to the comma-separated list if it is not already present. For example, a configured value of `pgaudit` becomes `pgaudit,pg_stat_statements`. Do not replace an existing list with only `pg_stat_statements`. After applying the configuration and restarting, rerun the first query to confirm that the active value includes `pg_stat_statements` and `pending_restart` is false. Configure these settings in `postgresql.conf`: ```ini # Use this assignment only if the existing preload list is empty. # Otherwise, append pg_stat_statements to the existing list. # Changes take effect after a restart. shared_preload_libraries = 'pg_stat_statements' # Records statements inside functions and procedures, not only top-level ones. pg_stat_statements.track = all # How many distinct statements the extension retains. pg_stat_statements.max = 10000 # Enables I/O timing metrics. Off by default. track_io_timing = on ``` If you use a managed PostgreSQL service, apply server-level settings through the provider's parameter-group or database-flags interface. Preserve the existing `shared_preload_libraries` entries there and append `pg_stat_statements` if it is missing. Apply the change and restart the database as required by your provider. Managed services also expose administrative databases that the monitoring user cannot read; exclude those databases from collection. {% hint style="info" %} The integration probes the server's capabilities when it starts. A missing setting, grant, server capability, or primary/standby role can make only the affected health group unavailable while other groups continue collecting. {% endhint %} ## What it collects | Signal | What it contains | Where to use it | | ----------------------- | -------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | Built-in health metrics | PostgreSQL engine health and capacity under the `groundcover_db_pg_*` prefix | [Data Explorer](https://app.groundcover.com/explore/data-explorer), monitors, and the PostgreSQL dashboard from the [Dashboard Catalog](/investigate/dashboards-and-alerts/dashboard-catalog.md) | | Query statistics | Rows from `pg_stat_statements`, represented as traces with database semantic-convention attributes | [Traces](https://app.groundcover.com/traces) | `pg_stat_statements` collection is independent of health-metric collection. Each distinct statement becomes a standalone trace containing calls, execution time, rows, and shared-block statistics; it is not joined to the application trace that issued the query. See [Verify collection](#verify-collection) for copyable metric queries and trace filters. ### Health metric groups All health groups are enabled by default when built-in health metrics are enabled. You can disable individual groups in the app or configuration. | Group | Description | Availability and empty results | | ---------------------- | ----------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------- | | Connections | Client connections by state (active, idle, idle in transaction, waiting) and the server's max\_connections limit. | Available from standard statistics views. | | Replication | Replication lag in seconds and bytes per replica, and how much WAL each replication slot is holding back. | Replica and slot series are empty when neither is configured. Some queries apply only to a primary. | | Standby | On a replica: how far behind the primary it is, and whether the WAL receiver is still connected. | Collected only when the monitored instance is in recovery. | | Database stats | Per-database transactions, cache hit ratio, rows read and written, deadlocks, temp file spills and session totals. | Excluded databases do not produce series. | | Transaction wraparound | How much transaction ID headroom each database has left before wraparound forces the server to stop accepting writes. | Excluded databases do not produce series. | | Database size | On-disk size of each database. | Excluded databases do not produce series. | | Conflicts | Queries cancelled on a standby because WAL replay conflicted with them, broken down by reason. | Normally empty on a primary or when a standby has no recovery conflicts. | | WAL archiver | WAL segments successfully archived, and archive failures. | Can remain empty or inactive when archiving is not configured. | | Instance | Server uptime, current timeline, and the count, size and age of files in the WAL directory. | Some filesystem-level values require statistics-monitoring privileges. | | Locks | Locks held and waiting by lock mode, and how long the oldest idle-in-transaction session has been holding one. | Requires statistics-monitoring privileges; waiting-lock series are empty when there is no contention. | | Checkpoints | How often checkpoints run and how long they take, and how many buffers the checkpointer, background writer and backends each wrote. | The exact server counters used are selected automatically from available capabilities. | | WAL | WAL records and bytes generated, full-page images written, and time spent writing and syncing WAL. | Collected when the server exposes the required WAL statistics. | | I/O | Reads, writes, extends and fsyncs by backend type and context, with timing when track\_io\_timing is enabled. | Collected when the server exposes the required I/O statistics. Timing values require `track_io_timing`. | ## Set it up {% tabs %} {% tab title="In the app (recommended)" %} Open [Data Sources](https://app.groundcover.com/data-sources), select **PostgreSQL**, and complete the wizard:
PostgreSQL setup wizard showing connection and cluster-scoping settings

Configure the PostgreSQL connection in the Data Sources wizard.

1. **Prepare PostgreSQL** — run the provided user, grant, extension, and server-configuration commands. 2. **Connection** — enter a name, host, optional port and database, and TLS settings. The database is the connection database; health metrics are collected for every non-excluded database on the instance. 3. **Cluster scoping** — run the integration from the backend or from a monitored cluster that can reach the database. 4. **Authentication** — provide the monitoring user's basic-auth credentials and enable TLS under **Connection**. Select no authentication only when the database is deliberately configured for passwordless access and network controls restrict access to trusted clients, including the integration's execution location. Never expose an unauthenticated database endpoint to the public internet. 5. **Collection** — choose query-statistics collection, INSERT filtering, the maximum statements per scrape, the shared scrape interval, and health-metric groups. 6. **Database filtering** — list administrative or application databases to exclude. Every other database on the instance is monitored. 7. **Ingestion settings** — add labels such as `environment` or `team` to every collected metric. Save the integration, then use [Verify collection](#verify-collection) to confirm both metrics and query traces are arriving. {% endtab %} {% tab title="Terraform" %} Use the [canonical Terraform examples](https://github.com/groundcover-com/terraform-provider-groundcover/blob/main/examples/resources/groundcover_dataintegration/resource.tf), selecting the `postgresql_dbm` resource with `type = "postgresqldbm"`. Follow the [provider configuration guide](https://registry.terraform.io/providers/groundcover-com/groundcover/latest/docs) and [`groundcover_dataintegration` reference](https://registry.terraform.io/providers/groundcover-com/groundcover/latest/docs/resources/dataintegration). Replace the example endpoint, database, username, and integration name with your values. Configure the execution cluster and password reference as described in [Credentials and execution location](#credentials-and-execution-location), then review `terraform plan` before applying. {% endtab %} {% tab title="Crossplane" %} Use the [canonical PostgreSQL `DataIntegration` manifest](https://github.com/groundcover-com/crossplane-provider-groundcover/blob/main/examples/dataintegration-postgresql.yaml). Follow the [provider setup instructions](https://github.com/groundcover-com/crossplane-provider-groundcover#readme) to install the provider and configure its credentials. Replace the example endpoint, database, username, integration name, and `providerConfigRef` with your values. Configure the execution cluster and password reference as described in [Credentials and execution location](#credentials-and-execution-location), then apply the manifest to the Kubernetes cluster running Crossplane. {% endtab %} {% endtabs %} ## Configuration notes ### Credentials and execution location Choose an execution location with network access to the PostgreSQL endpoint: * A cluster-run integration uses the integrations agent in the selected monitored cluster. Use `secretRef::k8s::::::` to read an existing Kubernetes Secret from that cluster. * A backend-run integration uses the integrations agent on the groundcover backend. Store the password through the app or use `secretRef::store::` in API-managed configuration. #### Create a Kubernetes Secret For a cluster-run integration, store the monitoring user's database password in a local file using your secret-management workflow. Restrict access to the file and ensure it contains only the password, with no trailing newline. Create the Secret in the cluster selected in **Cluster scoping**: ```bash kubectl --context "" --namespace groundcover \ create secret generic groundcover-postgresql \ --from-file=admin-password=/path/to/password-file ``` The namespace must already exist. This creates a Secret named `groundcover-postgresql` with a data key named `admin-password`. Reference it in the integration's `authentication.basicAuth.password` field: ``` secretRef::k8s::groundcover::groundcover-postgresql::admin-password ``` Replace the context, namespace, Secret name, and key to match your environment. Use the dedicated monitoring username created in [Prerequisites](#prerequisites). #### Backend-run credentials For a backend-run integration, select **Run the integration from the backend** and enter the monitoring username and password under **Authentication** in the app wizard. Saving the integration stores the password as a secret. A `secretRef::store::` reference identifies a secret in that store; use an existing secret's ID for IaC configuration. Omit `cluster` for backend execution. Never put a plaintext production password in Terraform or Crossplane configuration. ### TLS Enable TLS for encrypted database connections. Keep certificate verification enabled whenever the PostgreSQL endpoint presents a certificate trusted by the integration's runtime. Use the skip-verification option only when you understand and accept the risk of an internal or self-signed certificate chain. ### Query-statistics options `maxQueries` limits each scrape to the statements with the highest cumulative execution time. `filterInserts` excludes all `INSERT` statements, which can prevent high-volume writes from crowding other statements out of the result. ## Verify collection 1. Open the integration from [Data Sources](https://app.groundcover.com/data-sources). 2. Use **Stats** to confirm that PostgreSQL metrics are arriving and **Activity Traces** to inspect collection attempts and errors. 3. In [Data Explorer](https://app.groundcover.com/explore/data-explorer), query the built-in metrics for this integration: ```promql {__name__=~"groundcover_db_pg_.*", gc_integration_name=""} ``` 4. Install the PostgreSQL dashboard from the [Dashboard Catalog](/investigate/dashboards-and-alerts/dashboard-catalog.md) and select the integration or server endpoint. 5. In [Traces](https://app.groundcover.com/traces), choose a time range covering recent database activity. Filter by `gc_integration_name` using the **Name** you entered in the wizard (or `config.name` in IaC), and by `source.table` to find the collected database statements: ``` gc_integration_name:"" source.table:"pg_stat_statements" ``` Open a matching trace to inspect its SQL statement and database statistics. These are the collected query-statistics traces; **Activity Traces** in the data-source drawer show the integration's collection attempts. ## Troubleshooting ### The integration cannot connect Confirm that the selected execution location can resolve and reach the host and port. Then verify the username, secret reference, TLS mode, and certificate trust. Use the most recent **Activity Traces** entry to inspect reported network, authentication, and query failures. ### Health metrics are missing * Edit the integration and confirm that **Collect health metrics** and the expected groups are enabled. * Confirm that the user still has `pg_monitor` and can read PostgreSQL statistics views. * Check whether the group applies to the server's current capabilities and primary/standby role. Replication, standby, conflict, lock-wait, and archiver groups can legitimately return no rows. * For I/O timing values, confirm that `track_io_timing` is enabled. * Check **Stats**, **Activity Traces**, and the MetricsQL selector above. The product does not currently expose a persisted per-group status view. ### Query traces are missing Confirm that query-statistics collection is enabled, `pg_stat_statements` is loaded, and the extension exists in the connection database. Check that statements have executed since statistics were last reset and that INSERT filtering is not excluding the workload you expect. ### A database produces permission errors Add managed-service administrative databases and any intentionally inaccessible databases to **Ignored databases**. The list is exclusion-based: newly created databases are monitored automatically unless you add them to the list. For general integration diagnostics, see [Monitor Data Sources Integrations](/collect-data/monitor-data-sources-integrations.md). ## Related documentation * [ClickHouse Database Monitoring](/collect-data/data-sources/clickhouse-database-monitoring.md) * [Monitor Data Sources Integrations](/collect-data/monitor-data-sources-integrations.md) * [Dashboard Catalog](/investigate/dashboards-and-alerts/dashboard-catalog.md) * [groundcover Terraform Provider](/reference/groundcover-terraform-provider.md) --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters: ``` GET https://docs.groundcover.com/collect-data/data-sources/postgresql-database-monitoring.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `build a script that syncs our docs to a CMS` lets GitBook tailor the answer to that use case. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.