Skip to main content

PostgreSQL

Overview​

Causely provides native integration with PostgreSQL to help you identify and resolve database issues before they impact your users.

Instead of just monitoring symptoms, Causely analyzes real-time signals to surface the actual root causes of database issues.

By setting up the PostgreSQL integration, you will be able to do the following:

  • Identify root causes originating from your PostgreSQL database, including:
  • Observe the database as an entity in the Topology Graph, including it's relationships to other entities on the service map, infrastructure stack and dataflow map.
  • Get insights into the slowest SQL queries, and troubleshoot them with Ask Causely directly from the UI.

The integration supports both self-hosted PostgreSQL instances and cloud-managed services including AWS RDS PostgreSQL, Azure Database for PostgreSQL, and Google Cloud SQL for PostgreSQL.

Root Cause: DDL Excessive Lock
🔎 Root Cause: DDL Excessive Lock
Root Cause: DDL Excessive Lock Symptoms
🔎 Root Cause: DDL Excessive Lock Symptoms
Ask Causely: SQL Query Analysis
🔎 Ask Causely: SQL Query Analysis
Database Summary with slow queries
🔎 Database Summary with slow queries

Setup Guide​

Step 1: Create a user​

Create a user for your PostgreSQL database server with the following permissions:

  • pg_read_all_stats: Required to access statistics views and performance data
  • pg_stat_statements: Required to monitor query performance (if using pg_stat_statements extension)
  • SELECT: Required to query performance metrics, table information, and schema details
  • USAGE: Required to access schema information and metadata

Step 2: Enable performance monitoring features​

Enable performance monitoring features in your PostgreSQL instance to collect detailed metrics and query information. This step varies depending on your PostgreSQL deployment type, as detailed below.

Native PostgreSQL​

For native PostgreSQL installations, you need to enable the pg_stat_statements extension:

  1. Edit your PostgreSQL configuration file (typically postgresql.conf)
  2. Add or update these settings:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
  1. Restart PostgreSQL
  2. Connect to your database and run:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

AWS RDS PostgreSQL​

Steps:

  1. Go to RDS Console → Parameter Groups
  2. Edit your parameter group
  3. Set shared_preload_libraries to include pg_stat_statements
  4. Restart your instance
  5. Connect and run: CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Azure Database for PostgreSQL​

Steps:

  1. Go to Azure Portal → PostgreSQL server
  2. Select "Server parameters"
  3. Set shared_preload_libraries to include pg_stat_statements
  4. Restart the server
  5. Connect and run: CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Google Cloud SQL for PostgreSQL​

Steps:

  1. Go to Cloud Console → Cloud SQL
  2. Edit your instance
  3. Add database flag: shared_preload_libraries=pg_stat_statements
  4. Restart the instance
  5. Connect and run: CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Step 3: Create a Kubernetes secret for the user​

After creating the user create a Kubernetes secret. You have two options:

Option 1: Single Database Configuration​

kubectl create secret generic \
  --namespace causely postgres-credentials \
  --from-literal=username="..." \
  --from-literal=password='...' \
  --from-literal=host="..." \
  --from-literal=port=5432 \
  --from-literal=database="..." \
  --from-literal=sslmode="..."

Option 2: Multiple Databases Configuration​

For monitoring multiple databases within the same PostgreSQL instance, you can specify multiple databases using the databases field:

kubectl create secret generic \
  --namespace causely postgres-credentials-multidb \
  --from-literal=username="..." \
  --from-literal=password='...' \
  --from-literal=host="..." \
  --from-literal=port=5432 \
  --from-literal=databases="database1,database2,database3" \
  --from-literal=sslmode="..."

Alternatively, you can create the secret using a YAML manifest:

apiVersion: v1
kind: Secret
metadata:
  name: postgres-credentials-multidb
  namespace: causely
type: Opaque
stringData:
  username: '...'
  password: '...'
  host: '...'
  port: '5432'
  databases: 'database1,database2,database3'
  sslmode: '...'

Note: Use either the database field for single database configuration or the databases field for multiple databases. Do not use both fields in the same secret.

The host must be the FQDN of your DB, or IP address if there's no DNS entry set up. It must match the FQDN/IP Causely would discover either from the K8s Server (if it's running in your K8s Cluster) or your Cloud providers API. Examples:

  • Kubernetes: host=my-postgres.namespace.service.cluster.local
  • AWS: host=myinstance.rds.amazonaws.com

If you are leveraging a proxy to connect to your database, like it's common on GCP, host must be the FQDN/IP of your proxy and additionally host_overwrite should be the IP/FQDN of the actual database service:

  • GCP: host=localhost (assuming proxy runs on the same host / Pod). host_overwrite=1.2.3.4 (the IP of your GCP Cloud SQL instance as shown in the GCP Console)

Step 4: Update Causely Configuration​

Once the secret is created, update the Causely configuration to enable scraping for the new database. Below is an example configuration:

scrapers:
  postgresql:
    enabled: true
    instances:
      - secretName: postgres-credentials
        namespace: causely

Alternative: Enable Credentials Autodiscovery​

Causely also supports credentials autodiscovery. This feature allows you to add new scraping targets without updating the Causely configuration. Label the Kubernetes secret to enable autodiscovery for the corresponding scraper.

kubectl --namespace causely label secret postgres-credentials "causely.ai/scraper=Postgresql"

What Data is Collected​

The PostgreSQL scraper collects comprehensive metadata and performance information from your PostgreSQL databases, including:

  • Database entities with names and relationships to hosting services
  • Service-to-database mappings (which service provides which database)
  • Connection details including host, port, and SSL configuration
  • Database and table schemas with complete structural information
  • Table information including names, row counts, and sizes
  • Complete table schemas with column definitions, data types, constraints, and indexes
  • Foreign key relationships and table dependencies
  • Parent-child table mappings for dependency analysis
  • Slow query analysis using PostgreSQL's pg_stat_statements
  • Lock monitoring
  • Cache and I/O performance
  • Table performance metrics