Database monitoring is one of most important components of building scalable, reliable infrastructure. Not only it helps in gaining deep insight into the DB’s performance, it also helps identifying optimisation opportunities, and understanding resource utilisation in your database.

PostgreSQL exposes detailed information of all the its internals for this exact purpose.

In this article, we will see how to use Vector to extract the PostgreSQL monitoring metrics, and ship the metrics and logs to Parseable. Parseable centralises and visualise monitoring data.

We will also setup the pipeline to periodically collect metrics data from PostgreSQL and look at visualise it in Parseable.

By the end of this article, you will learn:

• How to integrate Vector with built-in PostgreSQL source for efficient metric collection.

• Key steps to configure and deploy Parseable for real-time monitoring and visualisation.

• Practical use cases and examples of visualising essential database metrics.

Prerequisites

You’ll need an active PostgreSQL database that we can use as the metrics / log data source for this tutorial.

Configure PostgreSQL

By default all logs are not enabled in PostgreSQL. To demonstrate all the possibilities, we will configure PostgreSQL configuration file to enable detailed logging.

Check the following changes in postgresql.conf file to enable logging and metrics collection of the PostgreSQL

# Enable logging
logging_collector = on                # Enables the log collector.
log_destination = 'stderr'            # Logs are sent to stderr.
log_directory = 'log'                 # Directory where logs will be stored.
log_filename = 'postgresql-%Y-%m-%d.log' # Log file naming convention.
log_rotation_age = 1d                 # Rotate logs daily.
log_rotation_size = 10MB              # Rotate logs after reaching 10MB size.

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

# Enable the statistics collector
track_activities = on                 # Track command activity.
track_counts = on                     # Collect statistics for queries, indexes, etc.
track_io_timing = on                  # Include I/O timing for detailed metrics.

# If you want detailed query logging:
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 = on              # Log statistics of SQL statement run.

Install Parseable

Parseable is designed for collecting, storing, querying, and visualising logs in real-time. Its core focus is simplicity, high performance, and compatibility with modern architectures. It supports structured and unstructured log ingestion from sources such as applications, databases, and system processes, making it ideal for database monitoring use case. Parseable can act as the log analytics layer for databases like PostgreSQL.

Here we go through the steps needed to install Parseable on Kubernetes via Helm Chart. If you’re interested in running Parseable binary or want to install on a VM using systemd - please refer to the documentation here.

Installation via Helm Chart

Open your terminal and type the command to create a new secret file:

cat << EOF > Parseable-env-secret
addr=0.0.0.0:8000
staging.dir=./staging
fs.dir=./data
username=admin
password=admin
EOF

Then create the secret in Kubernetes:

kubectl create ns parseable
kubectl create secret generic Parseable-env-secret --from-env-file=Parseable-env-secret -n parseable
helm repo add parseable https://charts.parseable.com
helm install parseable parseable/parseable -n parseable --set "parseable.local=false" --set "highAvailability.enabled=true"
kubectl port-forward svc/parseable 8000:80 -n parseable

Once you run the above command, Parseable is successfully installed and you can access it here: http://localhost:8000. The default username is admin and the password is admin. You can change these values in the secret file.

Finally, we need to create a log stream before we can send events. It is like a project that will store all your logs.For this tutorial, we'll create a log stream named "pg_vector." To create a log stream, log in to the Parseable instance and click the button on the right-hand, top side.

Vector

Vector is a log shipping agent that connect with your PostgreSQL database and automatically fetches and sends PostgreSQL stats to the sink i.e Parseable.

Configuration

Before you install Vector, we need to ensure proper configuration. So that Vector reads the PostgreSQL metrics and sends it to Parseable.

Vector has a source called postgresql_metrics - we’ll use this as our source here.

With the endpoint data/endpoint array, vector would connect to your PostgreSQL instance/instances and it will automatically start shipping metrics such as pgstat_database and pgstat_bgwriter metrics from the PostgreSQL to the configured sink.

Here is the source and sink configuration for Vector. Add this in the vector.yaml ( the Vector configuration file).

sources:
  postgresql_metrics:
    type: postgresql_metrics
    endpoints:
        # Change it to your own database endpoint detail
      - postgresql://postgres:user@localhost:5432/postgres 


sinks:
  parseable:
    type: http //Connect with parseable over http request
    method: post
    batch:
      max_bytes: 10485760
      max_events: 1000
      timeout_secs: 10
    compression: gzip
    encoding:
        codec: json
    inputs:
        - postgresql_metrics
    uri: 'https://parseable.XYZ.in/api/v1/ingest' # Enter the path to your Parseable instance
    auth:
        strategy: basic //authentication strategy is basic
        user: *** //user detail
        password: *** //password detail
    request:
      headers:
        X-P-Stream: pg_vector # Stream name in the Parseable where the logs will be shipped by vector
    healthcheck:
      enabled: true
      path: 'https://parseable.xyz.in/api/v1/liveness'  # Enter the path to your Parseable instance 
      port: 443

Once you have the configuration file ready, refer below links to install Vector on Kubernetes or an OS of your choice. Please ensure to pass the configuration file that we just created above as the input.

Verify Installation

Once Vector is successfully running with relevant configuration set up, refer to the Parseable on your browser. If you followed the steps above, you should be able to view the Parseable console on http://localhost.

If the workflow is setup correctly, you should see the stream pg_vector as defined in the vector config file. Here you’ll see the logs / explore view. You can search, query, or sort logs from here.

Build Dashboards

Next we make a dashboard in Parseable following these 4 simple steps to setup metrics:

Step 1 : Every metric, chart, table is a tile on dashboard. You can click on “add tile“ button from the dashboard to access the tile builder.

Step 2 :On clicking “Add tile“ you get a simple and intuitive query builder interface for you to write your SQL query. Click on validate query and check the visualisation preview. Here are few metrics and insights you can query to build the dashboard.

Step 3 : You can choose your visualisation type from table, pie chart, bar graphs etc. and preview the data

Step 4: Add the name for your metrics and Save it. Congratulations! You have successfully added chart on your dashboard.

You have successfully pulled database monitoring metrics from the PostgreSQL, shipped it to the Parseable, and created a dashboard. From here you can keep enhancing your dashboard with more valuable metrics as per your need.

Data Retention Policy

With continuous logging, you will accumulate large amount of data. It’s possible that after a period of time the usefulness of historical data will diminish. You can define retention rules to store only relevant data and delete the rest of it.

Retention period is not defined by default. You will have to go to the setting and define the age after which you want Parseable to delete the logs. Parseable will delete logs older than retention period and free up the storage. This comes in handy for the database monitoring logs which we may not need after say 30/45 days.

Summary

Parseable and Vector is a powerful combination for Database monitoring. It’s easy to setup, simple to use, and flexible to customise to your specific needs. You can easily build the log pipeline by configuring PostgreSQL for logging, using Vector for log shipping, and Parseable for monitoring and dashboard creation.