Databases are one of the core components of a business. Well performing, well tuned databases mean faster transactions, better experience and better profits.

Most of the popular databases offer several built-in telemetry features. But as companies grow, workloads evolve, it is crucial to centralize all application and infrastructure telemetry into a single location.

In this blog post, we'll show case how to build a comprehensive observability pipeline for PostgreSQL, combining logs and metrics collection. We'll use FluentBit for log collection, OpenTelemetry Collector for metrics, and Parseable as the observability backend.

Understanding the Components

Parseable

• A cloud-native log analytics engine built in Rust

• Uses Apache Arrow and Parquet for efficient data storage

• Stores data in object storage with hot-tier caching in distributed mode

• Offers direct querying capabilities on both hot and cold storage tiers

FluentBit

• A lightweight log collection agent

• Will collect and parse PostgreSQL logs

• Integrates with Parseable through a Parseable plugin

• Enables structured logging for better query ability

PostgreSQL

• One of the most popular OLTP databases

• Generates both logs and metrics we want to capture

• Requires careful monitoring for optimal performance

OpenTelemetry Collector

• Will scrape metrics from the PostgreSQL exporter

• Forwards metrics to Parseable

• Provides a standardized way to collect and process telemetry data

What We'll Cover

1. Setting up PostgreSQL and Parseable with FluentBit.

2. Configuring FluentBit with custom parser.

3. Installing and configuring the PostgreSQL exporter

4. Setting up the OpenTelemetry Collector

5. Querying and visualizing the data in Parseable.

Install PostgreSQL on Kubernetes

We’ll use the Bitnami PostgreSQL Helm Chart to install PostgreSQL on Kubernetes. Before installation, let’s create a values override file to configure structured logging in PostgreSQL.

primary:
  configuration: |-
    # listen address
    listen_addresses = '*'                 # Enable listen connection.

    # Enable logging to stdout
    logging_collector = off               # Disable logging collector to allow stdout logging.
    log_destination = 'stderr'            # Send logs to stderr (stdout for containers).

    # Log format and verbosity
    log_line_prefix = '%m [%p] %q%u@%d '  # Adds a useful prefix for log analysis.
    log_min_messages = info               # Minimum severity of logs to capture.
    log_min_error_statement = error       # Capture error-level SQL statements.

    # Additional log settings
    log_statement = 'all'                 # Logs all SQL statements (can cause noise).
    log_duration = on                     # Logs the time taken for each query.
    log_checkpoints = on                  # Log checkpoint details.
    log_connections = on                  # Log new connections.
    log_disconnections = on               # Log disconnections.
    log_lock_waits = on                   # Log waits for lock acquisition.
    log_statement_stats = off             # Turn off detailed statement stats if not needed.

Now using helm install PostgreSQL cluster.

helm repo add bitnami https://charts.bitnami.com/bitnami
helm repo update
helm upgrade --install postgres bitnami/postgresql -f values.yaml

Installing Parseable with FluentBit

Next, let’s deploy Parseable in distributed mode with AWS S3 configured as object storage. My Kubernetes cluster is on digital ocean, make sure to configure your storage class based on your Kubernetes flavor. Note that Parseable helm chart includes FluentBit installation as well.

Create configuration secret

Create a secret file with the configuration for Parseable

cat << EOF > parseable-env-secret
s3.url=https://s3.${REGION}.amazonaws.com
s3.access.key=${ACCESS_KEY}
s3.secret.key=${SECRET_KEY}
s3.region=${REGION}
s3.bucket=${BUCKET_NAME}
addr=0.0.0.0:8000
staging.dir=./staging
fs.dir=./data
username=admin
password=admin
EOF

After this, create the secret in Kubernetes.

kubectl create ns parseable
kubectl create secret generic parseable-env-secret --from-env-file=parseable-env-secret -n parseable

Install Parseable

Before running the helm upgrade command, make sure storage class is set to your Kubernetes flavor.

helm repo add parseable https://charts.parseable.com
wget https://gist.githubusercontent.com/AdheipSingh/b266f9d96e7b42b1e27bca70e636c0e6/raw -O parseable-values.yaml
helm upgrade --install parseable parseable/parseable -n parseable -f parseable-values.yaml

Port forward the Parseable query services to access Parseable console. You should see a log stream called postgres-logs with PostgreSQL logs parsed in the regex mentioned in FluentBit.

At this point we have logs in Parseable. Now let’s configure PostgreSQL metrics in Parseable.

Installing PostgreSQL Prometheus exporter

Create a file with the name postgres-values.yaml for PostgreSQL Prometheus exporter.

config:
  datasource:
    # Replace with your database connection details
    host: "postgresql.default.svc.cluster.local"
    port: "5432"
    database: "postgres"
    user: "postgres_exporter"
    password: "your-password"
    sslmode: "disable"

Now let’s install the exporter.

helm repo add prometheus-community https://prometheus-community.github.io/helm-charts
helm repo update prometheus-community
helm upgrade --install postgres-exporter prometheus-community/prometheus-postgres-exporter -f postgres-values.yaml

Once our exporter is installed, it should start export metrics on port 9187 /metrics endpoint.

Installing OTEL collector

Now we will scrape metrics from Prometheus exporter and send them to Parseable. First, create a file with otel-values.yaml.

mode: deployment

image:
  repository: otel/opentelemetry-collector-k8s

replicaCount: 1

config:
 extensions:
  health_check: {}
  basicauth/otlp:
    client_auth:
      username: admin
      password: admin

 receivers:
  ## sample receiver based on Prometheus to scrape a postgres server
  prometheus:
    config:
      scrape_configs:
        - job_name: 'postgres'
          scrape_interval: 10s
          metrics_path: "/metrics"
          static_configs:
            - targets: 
              - 'postgres-exporter.default.svc.cluster.local:9187'
  otlp:
    protocols:
      http:
        endpoint: 0.0.0.0:4318

 exporters:
  debug: {}
  ## send all metrics to the Parseable stream `otel-metrics`
  otlphttp/parseablemetrics:
    auth:
      authenticator: basicauth/otlp
    headers:
      X-P-Log-Source: otel-metrics
      X-P-Stream: p g-metrics
      Content-Type: application/json
    encoding: json
    tls:
      insecure: true
    endpoint: http://parseable-ingestor-service.parseable.svc.cluster.local:80

 processors:
    batch:
     send_batch_max_size: 100
     send_batch_size: 10

 service:
  extensions: 
    - basicauth/otlp
    - health_check
  pipelines:
    metrics:
      receivers:
      - prometheus
      processors: 
      - batch
      exporters:
      - otlphttp/parseablemetrics

Here we have configured Parseable ingestor URL to ingest metrics. Now let’s install deploy OpenTelemetry collector.

helm repo add open-telemetry https://open-telemetry.github.io/opentelemetry-helm-charts
helm upgrade --install my-opentelemetry-collector open-telemetry/opentelemetry-collector -f otel.yaml

Once OTEL pod is up and running, go to Parseable console to verify pg-metrics is stream is available.

At this point we have our PostgreSQL logs as well as our metrics within Parseable. Now lets generate some queries in PostgreSQL and see how are logs are getting parsed within Parseable.

Analyzing PostgreSQL Logs

Here is a useful script to generate dummy data in PostgreSQL.

wget https://gist.githubusercontent.com/AdheipSingh/db17c6c9affef7556a56523e780459c6/raw/d913245b4234f01a2f76a2ef4ac9274e003fec20 -O write-posgres.sh
chmod +x write-postgres.sh
./write-postgres.sh

After couple of minutes you should an ingestion spike and be able to see our INSERTS queries within Parseable.

Select SQL from drop down where Filters is selected and write this query to see all INSERT logs.

select * from "postgres-log" where (("statement" like '%INSERT%')) LIMIT 1000

Summary

This blog post demonstrates how to build a comprehensive observability pipeline for PostgreSQL by centralizing logs and metrics collection. By using FluentBit for log collection, OpenTelemetry Collector for metrics, and Parseable as the observability backend, we integrate these components into a Kubernetes environment. The post details setting up PostgreSQL with Bitnami Helm charts, configuring FluentBit and OpenTelemetry Collector, and leveraging Parseable for data visualization. Additionally, we explore generating and analyzing PostgreSQL logs to enhance database monitoring capabilities. Future posts will discuss creating interactive dashboards for PostgreSQL logs and metrics.

In our next blog post, we will look into building interactive dashboard for PostgreSQL logs and metrics within Parseable.