DEV Community

Cover image for Monitoring PostgreSQL 15 logs with Vector and Uptrace
Vladimir Mihailenco for Uptrace

Posted on • Updated on • Originally published at

Monitoring PostgreSQL 15 logs with Vector and Uptrace

This tutorial will teach you how to collect PostgreSQL logs with Vector and then monitor collected logs with Uptrace.

What is Vector? Vector is a lightweight, ultra-fast tool for building observability pipelines, for example, collecting and parsing logs.

What is Uptrace? Uptrace is an OpenTelemetry APM tool that supports distributed tracing, metrics, and logs. You can use it to monitor applications and set up automatic alerts to receive notifications via email, Slack, Telegram, and more.

PostgreSQL JSON log format

Starting from PostgreSQL 15, you can configure PostgreSQL to write logs in JSON format for easier consumption.

To start using JSON format, add the following settings to postgresql.conf:

logging_collector = on
log_destination = 'jsonlog'
Enter fullscreen mode Exit fullscreen mode

You can also configure PostgreSQL to log queries with duration:

log_min_duration_statement = 0
Enter fullscreen mode Exit fullscreen mode

After you restart PostgreSQL, it will start writing logs to /var/lib/postgresql/15/main/log directory:

sudo systemctl restart postgresql
sudo ls -la /var/lib/postgresql/15/main/log/
Enter fullscreen mode Exit fullscreen mode

Starting Uptrace

You can quickly start Uptrace locally using the official Docker example on GitHub.

Once Uptrace is started, you can send Vector logs to http://localhost:14318/vector/logs endpoint. If you are using the cloud version, the endpoint is

Collecting logs with Vector

To collect PostgreSQL logs with Vector, you can use the following config which uses Vector remap transformation to parse JSON logs and query duration:]
type = "file"
read_from = "beginning"
include = ["/var/lib/postgresql/15/main/log/*.json"]

type = "remap"
inputs = ["pg"]
source = '''
kvs = parse_json!(.message)
if kvs == null {

. = merge!(., kvs)

msg = parse_regex!(.message, r'^duration: (?P<duration>\d+(\.\d+)?\s\w+)\s+statement: (?P<query>.+)$')
if msg != null {

  .span_event_name = "span"
  .span_kind = "server"
  .span_duration = parse_duration!(replace(msg.duration, " ", ""), "ns")
  .db_system = "postgresql"
  .db_statement = msg.query

type = "http"
inputs = ["pg_json"]
encoding.codec = "json"
framing.method = "newline_delimited"
compression = "gzip"
uri = "http://localhost:14318/api/v1/vector/logs"
#uri = ""
request.headers.uptrace-dsn = "http://project2_secret_token@localhost:14317/2"
Enter fullscreen mode Exit fullscreen mode

Save the config to vector.toml file and then start Vector:

vector --config vector.toml
Enter fullscreen mode Exit fullscreen mode

If everything is working correctly, you should see logs at http://localhost:19876/events/2:

Uptrace logs

Monitoring and alerts

You can also use Uptrace to monitor number of logs (events) and send a notification via AlertManager.

To monitor number of logs, add the following alerting rule to your Uptrace config (uptrace.yml):

    - name: Number of ERROR logs
        - as $events
        - $events > 100
        - where span.system = 'log:error'
      for: 5m
Enter fullscreen mode Exit fullscreen mode

You can also create more specific alerting rules by using more granular span metrics.

What's next?

Next, you can learn more about Vector remap language and how you can use Uptrace and OpenTelemetry tracing to monitor app performance.

Top comments (0)