Monitoring PostgreSQL 15 logs with Vector and Uptrace
Tags: postgres, monitoring, devops
Published: 2023-01-20 11:01:14

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](https://uptrace.dev/get/opentelemetry-apm.html) 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:

```shell
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](https://github.com/uptrace/uptrace/tree/master/example/docker) 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](https://uptrace.dev/), 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:

```toml
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:

```shell
vector --config vector.toml
```

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

![Uptrace logs](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/15tb9l2tr6p4fl2drfq8.png)

## Monitoring and alerts

You can also use Uptrace to monitor number of logs (events) and [send a notification](https://uptrace.dev/get/alerting.html) via AlertManager.

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

```yaml
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](https://uptrace.dev/get/span-metrics.html).

## What's next?

Next, you can learn more about [Vector remap language](https://vector.dev/docs/reference/vrl/) and how you can use Uptrace and [OpenTelemetry tracing](https://uptrace.dev/opentelemetry/distributed-tracing.html) to monitor app performance.

---

- [DataDog competitors](https://uptrace.dev/blog/datadog-competitors.html)
- [DataDog vs Prometheus](https://uptrace.dev/blog/datadog-vs-prometheus.html)