Setting Up Database Monitoring for Amazon RDS managed Postgres
This page is not yet available in Spanish. We are working on its translation.
If you have any questions or feedback about our current translation project,
feel free to reach out to us!Database Monitoring provides deep visibility into your Postgres databases by exposing query metrics, query samples, explain plans, database states, failovers, and events.
The Agent collects telemetry directly from the database by logging in as a read-only user. Do the following setup to enable Database Monitoring with your Postgres database:
- Configure the AWS integration
- Configure database parameters
- Grant the Agent access to the database
- Install and configure the Agent
- Install the RDS integration
Interested in a more streamlined setup where Datadog automatically deploys the Agent to monitor your RDS?
Fill out this form to share your interest.
Before you begin
- Supported PostgreSQL versions
- 9.6, 10, 11, 12, 13, 14, 15, 16
- Supported Agent versions
- 7.36.1+
- Performance impact
- The default Agent configuration for Database Monitoring is conservative, but you can adjust settings such as the collection interval and query sampling rate to better suit your needs. For most workloads, the Agent represents less than one percent of query execution time on the database and less than one percent of CPU.
Database Monitoring runs as an integration on top of the base Agent (see benchmarks). - Proxies, load balancers, and connection poolers
- The Datadog Agent must connect directly to the host being monitored. For self-hosted databases,
127.0.0.1
or the socket is preferred. The Agent should not connect to the database through a proxy, load balancer, or connection pooler such as pgbouncer
. If the Agent connects to different hosts while it is running (as in the case of failover, load balancing, and so on), the Agent calculates the difference in statistics between two hosts, producing inaccurate metrics. - Data security considerations
- See Sensitive information for information about what data the Agent collects from your databases and how to ensure it is secure.
Enable Standard Collection in the Resource Collection section of your Amazon Web Services integration tile.
Configure Postgres settings
Configure the following parameters in the DB parameter group and then restart the server for the settings to take effect. For more information about these parameters, see the Postgres documentation.
Parameter | Value | Description |
---|
shared_preload_libraries | pg_stat_statements | Required for postgresql.queries.* metrics. Enables collection of query metrics using the pg_stat_statements extension. |
track_activity_query_size | 4096 | Required for collection of larger queries. Increases the size of SQL text in pg_stat_activity . If left at the default value then queries longer than 1024 characters will not be collected. |
pg_stat_statements.track | ALL | Optional. Enables tracking of statements within stored procedures and functions. |
pg_stat_statements.max | 10000 | Optional. Increases the number of normalized queries tracked in pg_stat_statements . This setting is recommended for high-volume databases that see many different types of queries from many different clients. |
pg_stat_statements.track_utility | off | Optional. Disables utility commands like PREPARE and EXPLAIN. Setting this value to off means only queries like SELECT, UPDATE, and DELETE are tracked. |
track_io_timing | on | Optional. Enables collection of block read and write times for queries. |
Grant the Agent access
The Datadog Agent requires read-only access to the database server in order to collect statistics and queries.
The following SQL commands should be executed on the primary database server (the writer) in the cluster if Postgres is replicated. Choose a PostgreSQL database on the server for the Agent to connect to. The Agent can collect telemetry from all databases on the database server regardless of which one it connects to, so a good option is to use the default postgres
database. Choose a different database only if you need the Agent to run custom queries against data unique to that database.
Connect to the chosen database as a superuser (or another user with sufficient permissions). For example, if your chosen database is postgres
, connect as the postgres
user using psql by running:
psql -h mydb.example.com -d postgres -U postgres
Create the datadog
user:
CREATE USER datadog WITH password '<PASSWORD>';
Note: IAM authentication is also supported. Please see the guide on how to configure this for your RDS instance.
Give the datadog
user permission to relevant tables:
ALTER ROLE datadog INHERIT;
Create the following schema in every database:
CREATE SCHEMA datadog;
GRANT USAGE ON SCHEMA datadog TO datadog;
GRANT USAGE ON SCHEMA public TO datadog;
GRANT pg_monitor TO datadog;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements schema public;
Create the following schema in every database:
CREATE SCHEMA datadog;
GRANT USAGE ON SCHEMA datadog TO datadog;
GRANT USAGE ON SCHEMA public TO datadog;
GRANT pg_monitor TO datadog;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements schema public;
Create the following schema in every database:
CREATE SCHEMA datadog;
GRANT USAGE ON SCHEMA datadog TO datadog;
GRANT USAGE ON SCHEMA public TO datadog;
GRANT SELECT ON pg_stat_database TO datadog;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
Create functions in every database to enable the Agent to read the full contents of pg_stat_activity
and pg_stat_statements
:
CREATE OR REPLACE FUNCTION datadog.pg_stat_activity() RETURNS SETOF pg_stat_activity AS
$$ SELECT * FROM pg_catalog.pg_stat_activity; $$
LANGUAGE sql
SECURITY DEFINER;
CREATE OR REPLACE FUNCTION datadog.pg_stat_statements() RETURNS SETOF pg_stat_statements AS
$$ SELECT * FROM pg_stat_statements; $$
LANGUAGE sql
SECURITY DEFINER;
For data collection or custom metrics that require querying additional tables, you may need to grant the
SELECT
permission on those tables to the
datadog
user. Example:
grant SELECT on <TABLE_NAME> to datadog;
. See
PostgreSQL custom metric collection for more information.
Create the function in every database to enable the Agent to collect explain plans.
CREATE OR REPLACE FUNCTION datadog.explain_statement(
l_query TEXT,
OUT explain JSON
)
RETURNS SETOF JSON AS
$$
DECLARE
curs REFCURSOR;
plan JSON;
BEGIN
OPEN curs FOR EXECUTE pg_catalog.concat('EXPLAIN (FORMAT JSON) ', l_query);
FETCH curs INTO plan;
CLOSE curs;
RETURN QUERY SELECT plan;
END;
$$
LANGUAGE 'plpgsql'
RETURNS NULL ON NULL INPUT
SECURITY DEFINER;
Securely store your password
Store your password using secret management software such as Vault. You can then reference this password as ENC[<SECRET_NAME>]
in your Agent configuration files: for example, ENC[datadog_user_database_password]
. See Secrets Management for more information.
The examples on this page use datadog_user_database_password
to refer to the name of the secret where your password is stored. It is possible to reference your password in plain text, but this is not recommended.
Verify
To verify the permissions are correct, run the following commands to confirm the Agent user is able to connect to the database and read the core tables:
psql -h localhost -U datadog postgres -A \
-c "select * from pg_stat_database limit 1;" \
&& echo -e "\e[0;32mPostgres connection - OK\e[0m" \
|| echo -e "\e[0;31mCannot connect to Postgres\e[0m"
psql -h localhost -U datadog postgres -A \
-c "select * from pg_stat_activity limit 1;" \
&& echo -e "\e[0;32mPostgres pg_stat_activity read OK\e[0m" \
|| echo -e "\e[0;31mCannot read from pg_stat_activity\e[0m"
psql -h localhost -U datadog postgres -A \
-c "select * from pg_stat_statements limit 1;" \
&& echo -e "\e[0;32mPostgres pg_stat_statements read OK\e[0m" \
|| echo -e "\e[0;31mCannot read from pg_stat_statements\e[0m"
psql -h localhost -U datadog postgres -A \
-c "select * from pg_stat_database limit 1;" \
&& echo -e "\e[0;32mPostgres connection - OK\e[0m" \
|| echo -e "\e[0;31mCannot connect to Postgres\e[0m"
psql -h localhost -U datadog postgres -A \
-c "select * from pg_stat_activity limit 1;" \
&& echo -e "\e[0;32mPostgres pg_stat_activity read OK\e[0m" \
|| echo -e "\e[0;31mCannot read from pg_stat_activity\e[0m"
psql -h localhost -U datadog postgres -A \
-c "select * from pg_stat_statements limit 1;" \
&& echo -e "\e[0;32mPostgres pg_stat_statements read OK\e[0m" \
|| echo -e "\e[0;31mCannot read from pg_stat_statements\e[0m"
When it prompts for a password, use the password you entered when you created the datadog
user.
To monitor RDS hosts, install the Datadog Agent in your infrastructure and configure it to connect to each instance endpoint remotely. The Agent does not need to run on the database, it only needs to connect to it. For additional Agent installation methods not mentioned here, see the Agent installation instructions.
To configure collecting Database Monitoring metrics for an Agent running on a host, for example when you provision a small EC2 instance for the Agent to collect from an RDS database:
Edit the postgres.d/conf.yaml
file to point to your host
/ port
and set the masters to monitor. See the sample postgres.d/conf.yaml for all available configuration options.
init_config:
instances:
- dbm: true
host: '<AWS_INSTANCE_ENDPOINT>'
port: 5432
username: datadog
password: 'ENC[datadog_user_database_password]'
tags:
- "dbinstanceidentifier:<DB_INSTANCE_NAME>"
## Required for Postgres 9.6: Uncomment these lines to use the functions created in the setup
# pg_stat_statements_view: datadog.pg_stat_statements()
# pg_stat_activity_view: datadog.pg_stat_activity()
## Optional: Connect to a different database if needed for `custom_queries`
# dbname: '<DB_NAME>'
For Agent versions ≤ 7.49
, add the following setting to the instance config where host
and port
are specified:
If you want to authenticate with IAM, specify the region
and instance_endpoint
parameters, and set managed_authentication.enabled
to true
.
Note: only enable managed_authentication
if you want to use IAM authentication. IAM authentication takes precedence over the password
field.
init_config:
instances:
- dbm: true
host: '<AWS_INSTANCE_ENDPOINT>'
port: 5432
username: datadog
aws:
instance_endpoint: '<AWS_INSTANCE_ENDPOINT>'
region: '<REGION>'
managed_authentication:
enabled: true
tags:
- "dbinstanceidentifier:<DB_INSTANCE_NAME>"
## Required for Postgres 9.6: Uncomment these lines to use the functions created in the setup
# pg_stat_statements_view: datadog.pg_stat_statements()
# pg_stat_activity_view: datadog.pg_stat_activity()
## Optional: Connect to a different database if needed for `custom_queries`
# dbname: '<DB_NAME>'
For information on configuring IAM authentication on your RDS instance, see Connecting with Managed Authentication.
Restart the Agent.
To configure the Database Monitoring Agent running in a Docker container such as in ECS or Fargate, you can set the Autodiscovery Integration Templates as Docker labels on your agent container.
Note: The Agent must have read permission on the Docker socket for Autodiscovery of labels to work.
Command line
Get up and running quickly by executing the following command to run the agent from your command line. Replace the values to match your account and environment:
export DD_API_KEY=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
export DD_AGENT_VERSION=7.36.1
docker run -e "DD_API_KEY=${DD_API_KEY}" \
-v /var/run/docker.sock:/var/run/docker.sock:ro \
-l com.datadoghq.ad.checks='{"postgres": {
"init_config": {},
"instances": [{
"dbm": true,
"host": "<AWS_INSTANCE_ENDPOINT>",
"port": 5432,
"username": "datadog",
"password": "<UNIQUEPASSWORD>",
"tags": ["dbinstanceidentifier:<DB_INSTANCE_NAME>"]
}]
}}' \
gcr.io/datadoghq/agent:${DD_AGENT_VERSION}
For Postgres 9.6, add the following settings to the instance config where host and port are specified:
pg_stat_statements_view: datadog.pg_stat_statements()
pg_stat_activity_view: datadog.pg_stat_activity()
Dockerfile
Labels can also be specified in a Dockerfile
, so you can build and deploy a custom agent without changing any infrastructure configuration:
FROM gcr.io/datadoghq/agent:7.36.1
LABEL "com.datadoghq.ad.check_names"='["postgres"]'
LABEL "com.datadoghq.ad.init_configs"='[{}]'
LABEL "com.datadoghq.ad.instances"='[{"dbm": true, "host": "<AWS_INSTANCE_ENDPOINT>", "port": 5432,"username": "datadog","password": "ENC[datadog_user_database_password]","tags": ["dbinstanceidentifier:<DB_INSTANCE_NAME>"]}]'
For Postgres 9.6, add the following settings to the instance config where host and port are specified:
pg_stat_statements_view: datadog.pg_stat_statements()
pg_stat_activity_view: datadog.pg_stat_activity()
To avoid exposing the datadog
user’s password in plain text, use the Agent’s secret management package and declare the password using the ENC[]
syntax, or see the Autodiscovery template variables documentation to learn how to pass the password as an environment variable.
If you have a Kubernetes cluster, use the Datadog Cluster Agent for Database Monitoring.
Follow the instructions to enable the cluster checks if not already enabled in your Kubernetes cluster. You can declare the Postgres configuration either with static files mounted in the Cluster Agent container or using service annotations:
Helm
Complete the following steps to install the Datadog Cluster Agent on your Kubernetes cluster. Replace the values to match your account and environment.
Complete the Datadog Agent installation instructions for Helm.
Update your YAML configuration file (datadog-values.yaml
in the Cluster Agent installation instructions) to include the following:
clusterAgent:
confd:
postgres.yaml: |-
cluster_check: true
init_config:
instances:
- dbm: true
host: <INSTANCE_ADDRESS>
port: 5432
username: datadog
password: 'ENC[datadog_user_database_password]'
tags:
- 'dbinstanceidentifier:<DB_INSTANCE_NAME>'
clusterChecksRunner:
enabled: true
For Postgres 9.6, add the following settings to the instance config where host and port are specified:
pg_stat_statements_view: datadog.pg_stat_statements()
pg_stat_activity_view: datadog.pg_stat_activity()
Deploy the Agent with the above configuration file from the command line:
helm install datadog-agent -f datadog-values.yaml datadog/datadog
For Windows, append --set targetSystem=windows
to the helm install
command.
To configure a cluster check with a mounted configuration file, mount the configuration file in the Cluster Agent container on the path: /conf.d/postgres.yaml
:
cluster_check: true # Make sure to include this flag
init_config:
instances:
- dbm: true
host: '<AWS_INSTANCE_ENDPOINT>'
port: 5432
username: datadog
password: 'ENC[datadog_user_database_password]'
tags:
- dbinstanceidentifier:<DB_INSTANCE_NAME>
## Required: For Postgres 9.6, uncomment these lines to use the functions created in the setup
# pg_stat_statements_view: datadog.pg_stat_statements()
# pg_stat_activity_view: datadog.pg_stat_activity()
Rather than mounting a file, you can declare the instance configuration as a Kubernetes Service. To configure this check for an Agent running on Kubernetes, create a Service in the same namespace as the Datadog Cluster Agent:
apiVersion: v1
kind: Service
metadata:
name: postgres
labels:
tags.datadoghq.com/env: '<ENV>'
tags.datadoghq.com/service: '<SERVICE>'
annotations:
ad.datadoghq.com/service.check_names: '["postgres"]'
ad.datadoghq.com/service.init_configs: '[{}]'
ad.datadoghq.com/service.instances: |
[
{
"dbm": true,
"host": "<AWS_INSTANCE_ENDPOINT>",
"port": 5432,
"username": "datadog",
"password": "ENC[datadog_user_database_password]",
"tags": [
"dbinstanceidentifier:<DB_INSTANCE_NAME>"
]
}
]
spec:
ports:
- port: 5432
protocol: TCP
targetPort: 5432
name: postgres
For Postgres 9.6, add the following settings to the instance config where host and port are specified:
pg_stat_statements_view: datadog.pg_stat_statements()
pg_stat_activity_view: datadog.pg_stat_activity()
The Cluster Agent automatically registers this configuration and begin running the Postgres check.
To avoid exposing the datadog
user’s password in plain text, use the Agent’s secret management package and declare the password using the ENC[]
syntax.
Validate
Run the Agent’s status subcommand and look for postgres
under the Checks section. Or visit the Databases page to get started!
Example Agent Configurations
One agent connecting to multiple hosts
It is common to configure a single Agent host to connect to multiple remote database instances (see Agent installation architectures for DBM). To connect to multiple hosts, create an entry for each host in the Postgres integration config.
In these cases, Datadog recommends limiting the number of instances per Agent to a maximum of 10 database instances to guarantee reliable performance.
init_config:
instances:
- dbm: true
host: example-service-primary.example-host.com
port: 5432
username: datadog
password: 'ENC[datadog_user_database_password]'
tags:
- 'env:prod'
- 'team:team-discovery'
- 'service:example-service'
- dbm: true
host: example-service–replica-1.example-host.com
port: 5432
username: datadog
password: 'ENC[datadog_user_database_password]'
tags:
- 'env:prod'
- 'team:team-discovery'
- 'service:example-service'
- dbm: true
host: example-service–replica-2.example-host.com
port: 5432
username: datadog
password: 'ENC[datadog_user_database_password]'
tags:
- 'env:prod'
- 'team:team-discovery'
- 'service:example-service'
[...]
Monitoring multiple databases on a database host
Use the database_autodiscovery
option to permit the Agent to discover all databases on your host to monitor. You can specify include
or exclude
fields to narrow the scope of databases discovered. See the sample postgres.d/conf.yaml for more details.
init_config:
instances:
- dbm: true
host: example-service-primary.example-host.com
port: 5432
username: datadog
password: 'ENC[datadog_user_database_password]'
database_autodiscovery:
enabled: true
# Optionally, set the include field to specify
# a set of databases you are interested in discovering
include:
- mydb.*
- example.*
tags:
- 'env:prod'
- 'team:team-discovery'
- 'service:example-service'
Running custom queries
To collect custom metrics, use the custom_queries
option. See the sample postgres.d/conf.yaml for more details.
init_config:
instances:
- dbm: true
host: localhost
port: 5432
username: datadog
password: 'ENC[datadog_user_database_password]'
custom_queries:
- metric_prefix: employee
query: SELECT age, salary, hours_worked, name FROM hr.employees;
columns:
- name: custom.employee_age
type: gauge
- name: custom.employee_salary
type: gauge
- name: custom.employee_hours
type: count
- name: name
type: tag
tags:
- 'table:employees'
Monitoring relation metrics for multiple databases
In order to collect relation metrics (such as postgresql.seq_scans
, postgresql.dead_rows
, postgresql.index_rows_read
, and postgresql.table_size
), the Agent must be configured to connect to each database (by default, the Agent only connects to the postgres
database).
Specify a single “DBM” instance to collect DBM telemetry from all databases. Use the database_autodiscovery
option to avoid specifying each database name.
init_config:
instances:
# This instance is the "DBM" instance. It will connect to the
# all logical databases, and send DBM telemetry from all databases
- dbm: true
host: example-service-primary.example-host.com
port: 5432
username: datadog
password: 'ENC[datadog_user_database_password]'
database_autodiscovery:
enabled: true
exclude:
- ^users$
- ^inventory$
relations:
- relation_regex: .*
# This instance only collects data from the `users` database
# and collects relation metrics from tables prefixed by "2022_"
- host: example-service-primary.example-host.com
port: 5432
username: datadog
password: 'ENC[datadog_user_database_password]'
dbname: users
dbstrict: true
relations:
- relation_regex: 2022_.*
relkind:
- r
- i
# This instance only collects data from the `inventory` database
# and collects relation metrics only from the specified tables
- host: example-service-primary.example-host.com
port: 5432
username: datadog
password: 'ENC[datadog_user_database_password]'
dbname: inventory
dbstrict: true
relations:
- relation_name: products
- relation_name: external_seller_products
Collecting schemas
To enable this feature, use the collect_schemas
option. You must also configure the Agent to connect to each logical database.
Use the database_autodiscovery
option to avoid specifying each logical database. See the sample postgres.d/conf.yaml for more details.
init_config:
# This instance only collects data from the `users` database
# and collects relation metrics only from the specified tables
instances:
- dbm: true
host: example-service-primary.example-host.com
port: 5432
username: datadog
password: 'ENC[datadog_user_database_password]'
dbname: users
dbstrict: true
collect_schemas:
enabled: true
relations:
- products
- external_seller_products
# This instance detects every logical database automatically
# and collects relation metrics from every table
- dbm: true
host: example-service–replica-1.example-host.com
port: 5432
username: datadog
password: 'ENC[datadog_user_database_password]'
database_autodiscovery:
enabled: true
collect_schemas:
enabled: true
relations:
- relation_regex: .*
Working with hosts through a proxy
If the Agent must connect through a proxy such as the Cloud SQL Auth proxy, all telemetry is tagged with the hostname of the proxy rather than the database instance. Use the reported_hostname
option to set a custom override of the hostname detected by the Agent.
init_config:
instances:
- dbm: true
host: localhost
port: 5000
username: datadog
password: 'ENC[datadog_user_database_password]'
reported_hostname: example-service-primary
- dbm: true
host: localhost
port: 5001
username: datadog
password: 'ENC[datadog_user_database_password]'
reported_hostname: example-service-replica-1
Install the RDS Integration
To see infrastructure metrics from AWS, such as CPU, alongside the database telemetry in DBM, install the RDS integration (optional).
Troubleshooting
If you have installed and configured the integrations and Agent as described and it is not working as expected, see Troubleshooting
Further reading
Más enlaces, artículos y documentación útiles: