> For the complete documentation index, see [llms.txt](https://archivedocs.stackstate.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://archivedocs.stackstate.com/5.1/stackpacks/integrations/postgresql.md). # PostgreSQL ## Overview Get metrics from the PostgreSQL service in real time to: * Visualize and monitor PostgreSQL states * Received notifications about PostgreSQL failovers and events PostgreSQL is a [StackState curated integration](/5.1/stackpacks/integrations/about_integrations.md#stackstate-curated-integrations). ## Setup ### Installation The PostgreSQL check is included in the [Agent V2 StackPack](/5.1/stackpacks/integrations/agent.md). To start gathering your PostgreSQL metrics and logs, install the first. ### Configuration Edit the `postgres.d/conf.yaml` file, in the `conf.d/` folder at the root of your Agent's configuration directory to start collecting your PostgreSQL metrics and logs. See the sample `postgres.d/conf.yaml` for all available configuration options. #### Prepare PostgreSQL To get started with the PostgreSQL integration, create a read-only `stackstate` user with proper access to your PostgreSQL server. Start `psql` on your PostgreSQL database and run: For PostgreSQL version 10 and above: ``` create user stackstate with password ''; grant pg_monitor to stackstate; ``` For older PostgreSQL versions: ``` create user stackstate with password ''; grant SELECT ON pg_stat_database to stackstate; ``` **Note**: When generating custom metrics that require querying additional tables, you may need to grant the `CONNECT` permission on those tables to the `stackstate` user. To verify the permissions are correct, run the following command: ``` psql -h localhost -U stackstate postgres -c \ "select * from pg_stat_database LIMIT(1);" \ && echo -e "\e[0;32mPostgreSQL connection - OK\e[0m" \ || echo -e "\e[0;31mCannot connect to PostgreSQL\e[0m" ``` When it prompts for a password, enter the one used in the first command. #### Metric Collection * Edit the `postgres.d/conf.yaml` file to point to your server / port and set the masters to monitor. See the sample `postgres.d/conf.yaml` for all available configuration options. | Option | Required | Description | | ----------------------------------- | -------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **`username`** | No | The user account used to collect metrics, created in the **Installation** section above. | | **`password`** | No | The password for the user account. | | **`dbname`** | No | The name of the database you want to monitor. | | **`ssl`** | No | Defaults to `False`. Indicates whether to use an SSL connection. | | **`use_psycopg2`** | No | Defaults to `False`. Setting this option to `True` forces StackState to collect PostgreSQL metrics using `psycopg2` instead of `pg8000`. Note: `pyscopg2` doesn't support SSL connections. | | **`tags`** | No | A list of tags applied to all metrics collected. Tags may be simple strings or key-value pairs. | | **`relations`** | No | By default, all schemas are included. Add specific schemas here to collect metrics for schema relations. Each relation generates 10 metrics and an additional 10 metrics per index. | | **`collect_function_metrics`** | No | Collect metrics regarding PL/pgSQL functions from `pg_stat_user_functions`. | | **`collect_count_metrics`** | No | Collect count metrics. The default value is `True` for backward compatibility, but this might be slow. The recommended value is `False`. | | **`collect_activity_metrics`** | No | Defaults to `False`. Collect metrics regarding transactions from `pg_stat_activity`. Make sure the user has sufficient privileges to read from `pg_stat_activity` before enabling this option. | | **`collect_database_size_metrics`** | Yes | Collect database size metrics. Default value is `True` but this might be slow with large databases. | | **`collect_default_database`** | No | Defaults to `False`. Include statistics from the default database `postgres` in the check metrics. | For PostgreSQL versions 9.6 and below, run the following and create a `SECURITY DEFINER` to read from `pg_stat_activity`. ``` CREATE FUNCTION pg_stat_activity() RETURNS SETOF pg_catalog.pg_stat_activity AS $$ SELECT * from pg_catalog.pg_stat_activity; $$ LANGUAGE sql VOLATILE SECURITY DEFINER; CREATE VIEW pg_stat_activity_dd AS SELECT * FROM pg_stat_activity(); grant SELECT ON pg_stat_activity_dd to stackstate; ``` * Restart the Agent to start sending PostgreSQL metrics to StackState. #### Log Collection PostgreSQL default logging is to `stderr` and logs don't include detailed information. It's recommended to log into a file with additional details specified in the log line prefix. * Edit your PostgreSQL configuration file `/etc/postgresql//main/postgresql.conf` and uncomment the following parameter in the log section: ``` logging_collector = on log_directory = 'pg_log' # directory where log files are written, # can be absolute or relative to PGDATA log_filename = 'pg.log' #log file name, can include pattern log_statement = 'all' #log all queries log_line_prefix= '%m [%p] %d %a %u %h %c ' log_file_mode = 0644 ## For Windows #log_destination = 'eventlog' ``` * Collecting logs is disabled by default in StackState , enable it in `stackstate.yaml`: ``` logs_enabled: true ``` * Add this configuration block to your `postgres.d/conf.yaml` file to start collecting your PostgreSQL logs: ``` logs: - type: file path: /var/log/pg_log/pg.log source: postgresql sourcecategory: database service: myapp #To handle multi line that starts with yyyy-mm-dd use the following pattern #log_processing_rules: # - type: multi_line # pattern: \d{4}\-(0?[1-9]|1[012])\-(0?[1-9]|[12][0-9]|3[01]) # name: new_log_start_with_date ``` * Change the `service` and `path` parameter values to configure for your environment. See the sample `postgres.d/conf.yaml` for all available configuration options. * Restart the Agent. ### Validation Run the Agent's `status` subcommand and look for `postgres` under the Checks section. ## Data Collected Some of the metrics listed below require additional configuration, see the sample `postgres.d/conf.yaml` for all configurable options. ### Metrics See `metadata.csv` for a list of metrics provided by this integration. ### Events The PostgreSQL check doesn't include any events. ### Service Checks **postgres.can\_connect** Returns `CRITICAL` if the Agent is unable to connect to the monitored PostgreSQL instance. Returns `OK` otherwise. --- # 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 current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://archivedocs.stackstate.com/5.1/stackpacks/integrations/postgresql.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. 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.