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'

You can also configure PostgreSQL to log queries with duration:

log_min_duration_statement = 0

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/

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 https://api.uptrace.dev/api/v1/vector/logs.

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:

sources.pg]
type = "file"
read_from = "beginning"
include = ["/var/lib/postgresql/15/main/log/*.json"]

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

. = merge!(., kvs)

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

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

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

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

vector --config vector.toml

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

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):

alerting:
  rules:
    - name: Number of ERROR logs
      metrics:
        - uptrace.tracing.events as $events
      query:
        - $events > 100
        - where span.system = 'log:error'
      for: 5m

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.

• DataDog competitors
• DataDog vs Prometheus