Logit.io
PostgreSQL logs: slow queries, errors and connections
← Back to blog
11/19/2026 · 31 min read

PostgreSQL logs: slow queries, errors and connections

Logit.io Team
Logit.io Team
Technical Content Team

Last updated 11/19/2026

Log ManagementHow To GuidesOpensearch

To get PostgreSQL slow queries, errors and connections into OpenSearch on Logit.io, turn on log_min_duration_statement, log_connections, log_disconnections and log_lock_waits, keep log_line_prefix in the shape the parser expects ('%m [%p-%l] %q%u@%d ') and ship the log file with the Filebeat postgresql module. Your stack's Logstash routes each event to a managed PostgreSQL ingest pipeline, which writes event.duration in nanoseconds, log.level, user.name, postgresql.log.database and postgresql.log.query into daily filebeat-* indices. There's no PostgreSQL pack in the Dashboard Library. Install Dashboards in your stack settings adds the stock Filebeat PostgreSQL dashboards for log levels and query durations, and this guide gives you the saved searches, visualisations and alerts for errors, connections and lock waits that they don't cover.

Most PostgreSQL servers already write a log. The trouble is that the defaults record almost nothing you'd want during an incident: no durations, no connections, no lock waits. The settings below fix that without logging every statement, and the rest of the guide is about what the parsed events look like on your stack, so the queries you save on a quiet Tuesday still work when the application is slow and someone asks whether it's the database.

Contents

What reaches OpenSearch from one PostgreSQL log line

Four pieces sit between postgresql.conf and a search in OpenSearch Dashboards, and every problem later in this guide comes from one of them:

PostgreSQL writes its log file using the line prefix you set, the Filebeat postgresql module reads it and joins continuation lines, your stack's Logstash routes the events to the managed PostgreSQL ingest pipeline and the parsed events land in daily filebeat indices that you search in OpenSearch Dashboards
  1. PostgreSQL writes each message with the prefix from log_line_prefix. The prefix is what the parser reads user, database and process ID from, so its shape matters more than any other setting.
  2. Filebeat's postgresql module reads the file. Its log fileset starts a new event at any line that begins with a date and appends everything else to the line above, which is how a multi-line query or an auto_explain plan stays in one event.
  3. Logstash on your Logs stack receives the events over Beats with TLS. Events from the module carry a pipeline marker, and Logstash sends those to the managed PostgreSQL ingest pipeline rather than indexing them raw.
  4. The ingest pipeline reads the timestamp, then looks at the next character. A space means the plain text log, a comma means csvlog, and each format goes to its own parser. Parsed events are stored in daily filebeat-* indices with @timestamp set from the PostgreSQL timestamp, not from when Filebeat read the line.

Here is what that does to a slow statement. This line, from a server with the prefix above:

2026-11-19 09:14:03.118 UTC [48213-3] app@shop LOG:  duration: 2412.337 ms  execute <unnamed>: SELECT o.id, o.total FROM orders o WHERE o.customer_id = $1 ORDER BY o.created_at DESC

becomes a document with these fields (trimmed, illustrative values):

{
  "@timestamp": "2026-11-19T09:14:03.118Z",
  "event.module": "postgresql",
  "event.dataset": "postgresql.log",
  "event.duration": 2412337000,
  "event.timezone": "UTC",
  "event.type": ["info"],
  "event.category": ["database"],
  "log.level": "LOG",
  "process.pid": 48213,
  "postgresql.log.session_line_number": 3,
  "user.name": "app",
  "postgresql.log.database": "shop",
  "postgresql.log.query_step": "execute",
  "postgresql.log.query": "SELECT o.id, o.total FROM orders o WHERE o.customer_id = $1 ORDER BY o.created_at DESC",
  "host.name": "db01"
}

Two things in there catch people out. event.duration is in nanoseconds, so 2.4 seconds is 2412337000. And event.type says info on every line unless PostgreSQL wrote a SQLSTATE code the parser could read, which in practice means csvlog. That's covered in the settings below.

Set postgresql.conf for logs worth parsing

This block is a sensible starting point for a production primary on PostgreSQL 13 to 17. Put it in postgresql.conf or a file under conf.d (which only works when postgresql.conf has an include_dir line for it, as Debian and Ubuntu packages do by default), or set each value with ALTER SYSTEM:

# Where and how
log_destination = 'stderr'
log_line_prefix = '%m [%p-%l] %q%u@%d '
log_timezone = 'UTC'

# Slow statements and the SQL behind errors
log_min_duration_statement = 500ms
log_min_error_statement = error
log_parameter_max_length = 0

# Connections, locks and spills
log_connections = on
log_disconnections = on
log_lock_waits = on
deadlock_timeout = 1s
log_temp_files = 10MB

# Background work worth seeing
log_checkpoints = on
log_autovacuum_min_duration = 10s

Every setting here takes effect on a reload, with no restart:

SELECT pg_reload_conf();

SELECT name, setting, unit, pending_restart
FROM pg_settings
WHERE name IN ('log_line_prefix', 'log_min_duration_statement', 'log_connections',
               'log_disconnections', 'log_lock_waits', 'log_timezone');

log_connections and log_disconnections apply to sessions that start after the reload, so long-lived pooled connections won't show up until the pool recycles them.

The line prefix the parser expects

The text parser reads a fixed shape: timestamp with time zone, the process ID in square brackets with an optional line number after a hyphen, then optionally user@database, then the severity. In log_line_prefix terms that's:

  • %m: timestamp with milliseconds and zone, such as 2026-11-19 09:14:03.118 UTC.
  • [%p-%l]: process ID and the per-session line number. The parser stores them as process.pid and postgresql.log.session_line_number. Plain [%p] also parses.
  • %q: stop here for background processes, so the checkpointer and autovacuum lines don't print an empty @.
  • %u@%d: user and database, stored as user.name and postgresql.log.database.

Debian and Ubuntu packages ship '%m [%p] %q%u@%d ', which parses as it is. PostgreSQL's own default since version 10 is '%m [%p] ', which also parses but leaves you with no user or database fields, so change it. What you can't do is add anything else. A prefix with %a for the application name, %h for the client host or %e for the SQLSTATE no longer matches, and every event arrives with an error.message and the raw line in message. If you need those values as fields, use csvlog, which carries them as columns.

Set log_timezone = 'UTC' too. The parser expects a zone abbreviation made of letters. Zones that PostgreSQL prints as a numeric offset, such as +03, fail to parse, and abbreviations like BST or IST mean different things in different places. UTC removes both problems, and OpenSearch Dashboards converts to your browser's time zone for display anyway.

Slow statements without logging everything

log_min_duration_statement logs every completed statement that took at least that long, with its duration and text, in one line. It is the setting you want. Two others look similar and aren't:

  • log_statement = 'all' logs every statement before it runs, with no duration. On a busy server it multiplies your log volume and tells you nothing about speed. Leave it at none, or ddl if you want schema changes recorded.
  • log_duration = on logs the duration of every statement with no text. You can't tell which query was slow.

Pick the threshold from what your users notice. 500 ms suits most web applications; 100 ms suits a latency-sensitive API; a reporting database might want 5 seconds. You can override it per database or per role, which is better than one compromise number:

ALTER DATABASE reporting SET log_min_duration_statement = '5s';
ALTER ROLE batch_etl SET log_min_duration_statement = '30s';

If even 500 ms produces thousands of lines an hour, PostgreSQL 13 and later can sample instead. log_min_duration_sample = '100ms' with log_statement_sample_rate = 0.1 logs one in ten statements above 100 ms, while log_min_duration_statement still logs everything above its own, higher threshold.

log_parameter_max_length = 0 is in the block for privacy. Applications using prepared statements log $1 placeholders in the statement and then a separate DETAIL: parameters: $1 = '81723' line with the bound values, which can be email addresses or anything else in a WHERE clause. Zero turns that line off for slow-statement logging. Set it to -1 on a staging server if you want the values while you debug. Literal values written inline in the SQL are still logged, so treat the slow log like the data it touches.

Plans for the worst queries with auto_explain

The slow-statement line tells you which query was slow, not why. auto_explain, which ships with PostgreSQL, logs the plan for statements over its own threshold:

session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '2s'
auto_explain.log_analyze = off
auto_explain.log_format = 'text'
# PostgreSQL 16 and later only: keeps bind values out of plans
auto_explain.log_parameter_max_length = 0

session_preload_libraries loads it into each new session without a restart. Set the threshold above log_min_duration_statement so you get plans for the worst statements only. Keep log_analyze off on a busy primary: with it on, PostgreSQL times every plan node of every statement, not just the slow ones, which costs real CPU. On PostgreSQL 16 and later, keep auto_explain.log_parameter_max_length = 0 as well. Its default of -1 writes bind parameter values into every plan in full, whatever log_parameter_max_length is set to. Leave that line out on 13 to 15, which don't have the setting.

The plan arrives as one multi-line event that starts LOG: duration: 3184.902 ms plan:. Filebeat keeps the indented plan lines with it, the pipeline sets event.duration, and the whole plan, including the Query Text: line, stays in message, where you can read it in Discover and search it as text. It has no postgresql.log.query_step, which is how you tell plans apart from slow-statement lines in a query.

Connections, lock waits and the rest

log_connections writes a connection received line when a client connects and a connection authorized line once it has authenticated, with the user, database and application_name. log_disconnections writes the session length when it ends. Failed logins are logged at FATAL whatever these settings are, followed by a DETAIL line naming the pg_hba.conf line that matched:

2026-11-19 09:15:40.502 UTC [48390-1] [unknown]@[unknown] LOG:  connection received: host=203.0.113.24 port=51544
2026-11-19 09:15:40.511 UTC [48390-2] app@shop LOG:  connection authorized: user=app database=shop application_name=checkout-api SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256)
2026-11-19 09:16:21.718 UTC [48390-3] app@shop LOG:  disconnection: session time: 0:00:41.207 user=app database=shop host=203.0.113.24 port=51544
2026-11-19 09:17:02.330 UTC [48455-2] app@shop FATAL:  password authentication failed for user "app"
2026-11-19 09:18:11.904 UTC [48501-2] app@shop FATAL:  sorry, too many clients already

Note the first line. Before authentication PostgreSQL doesn't know the user or database, prints [unknown]@[unknown], and the parser stores unknown in both fields. Filter on the message text for connection counts, not on user.name.

On PostgreSQL 18, log_connections takes a list instead of a boolean. on still works and means receipt, authentication and authorisation; add setup_durations if you also want the time spent setting each connection up.

log_lock_waits logs any session that has waited longer than deadlock_timeout for a lock, with the process holding it in a DETAIL line. It's the single most useful setting for "the query is normally fast but sometimes takes 30 seconds", because it shows the blocker. log_temp_files = 10MB logs sorts and hashes that spilled more than 10 MB to disk, usually a sign that work_mem is too small for that query. log_checkpoints has been on by default since PostgreSQL 15, and log_autovacuum_min_duration = 10s records autovacuum runs long enough to matter.

Text log or csvlog

The pipeline parses both formats. Most servers should stay on the text log, because it's the default, it's what package installs write to /var/log/postgresql and the module finds it without extra paths. csvlog is worth the switch when you want SQLSTATE codes, client addresses and application names as fields on every event:

What you getText logcsvlog
User, database, PIDFrom the prefixFrom columns
SQLSTATE in postgresql.log.sql_state_codeNoAlways
event.type of error on real errorsNoAlways
Client address and portOnly inside the connection message textpostgresql.log.client_addr, postgresql.log.client_port
Application name, backend typeNopostgresql.log.application_name, postgresql.log.backend_type
Detail, hint, failing statementSeparate eventsFields on the same event
SQL of a slow statementpostgresql.log.querymessage
event.duration on auto_explain plansYesNo

Two rows in that table are worth reading twice. In the text log, an error's DETAIL, HINT and STATEMENT lines each become their own event, with log.level set to that word and a session line number one higher than the line before. In csvlog they're columns on one event. And the SQL of a slow statement lands in different fields, so a saved search written for one format misses the other. Pick one format per fleet.

Don't turn on log_error_verbosity = verbose to get SQLSTATE codes in the text log. Verbose mode puts a code in front of every message, including LOG: 00000: duration: …, and the parser then stores that line as plain message text with no event.duration or postgresql.log.query, so every slow-statement search in this guide comes back empty. It also adds a LOCATION line after every message, not just errors. If you want SQLSTATE codes as a field, use csvlog. It needs PostgreSQL's own log collector, which only takes effect after a restart:

logging_collector = on
log_destination = 'csvlog'
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d

That writes files such as postgresql-2026-11-19.csv, which match the module's default pattern on Linux. Test one server before changing the rest. On PostgreSQL 14 and later, csvlog adds leader_pid and query_id columns after backend_type. The pipeline ignores columns past the ones it names, so they're dropped rather than breaking the parse. The JSON log format added in PostgreSQL 15, jsonlog, isn't one the pipeline parses, so don't choose it for logs you send this way.

Ship the log with the Filebeat postgresql module

Follow the PostgreSQL integration guide while signed in to Logit.io. Choose Install Integration on that page for the stack you're sending to first, then install Filebeat on the database server. The examples here use Filebeat 8.x on Linux, matching the guide. Enable the module:

sudo filebeat modules enable postgresql

Then replace the contents of /etc/filebeat/modules.d/postgresql.yml. The fileset is disabled until you set enabled: true:

- module: postgresql
  log:
    enabled: true
    # Leave var.paths out on Debian and Ubuntu package installs.
    # Set it anywhere else, using the path PostgreSQL reports.
    #var.paths:
    #  - /var/lib/pgsql/17/data/log/*.log

Without var.paths, the module reads /var/log/postgresql/postgresql-*-*.log* and /var/log/postgresql/postgresql-*-*.csv* on Linux. That suits the Debian and Ubuntu layout, where the file is called something like postgresql-17-main.log. It doesn't suit everything else:

InstallTypical log fileDefault path finds it?
Debian or Ubuntu package/var/log/postgresql/postgresql-17-main.logYes
PGDG packages on RHEL, Rocky or Alma/var/lib/pgsql/17/data/log/postgresql-Thu.logNo, set var.paths
Built from source or a custom data directorylog under the data directoryNo, set var.paths
csvlog with the settings above/var/log/postgresql/postgresql-2026-11-19.csvYes

Ask the server rather than guessing. With the log collector on, SELECT pg_current_logfile(); returns the file it's writing now. Without the collector, the Debian path above is set by the package's cluster wrapper, and ls -l /var/log/postgresql shows it.

Now set the output in /etc/filebeat/filebeat.yml. Copy the block from the integration guide, because it fills in your stack's Logstash host and Beats SSL port:

filebeat.config.modules:
  path: ${path.config}/modules.d/*.yml
  reload.enabled: false

output.logstash:
  hosts: ["your-logstash-host:your-ssl-port"]
  loadbalance: true
  ssl.enabled: true

processors:
  - add_host_metadata:
      when.not.contains.tags: forwarded

add_host_metadata sets host.name, which is how you'll tell a primary from its replicas in every search below. Test, start and watch Filebeat's own log:

sudo filebeat test config
sudo filebeat test output
sudo systemctl enable --now filebeat
sudo journalctl -u filebeat -f

A healthy test output ends like this (illustrative output, placeholder host):

logstash: your-logstash-host:your-ssl-port...
  connection...
    parse host... OK
    dns lookup... OK
    addresses: 192.0.2.10
    dial up... OK
  TLS...
    security: server's certificate chain verification is enabled
    handshake... OK
    TLS version: TLSv1.3
    dial up... OK
  talk to server... OK

Keep the module rather than pointing a plain filestream input at the same file. A plain input sends no pipeline marker, so the lines are stored unparsed in message, and multi-line queries and plans are split into one event per line. If you've got both, you'll see each line twice, once parsed and once not. The Filebeat filestream mistakes guide covers the duplicate-input problem in more detail.

Beats events sent through your stack's Logstash land in daily filebeat-* indices. If you've changed the index name in Logstash, use your own pattern in place of filebeat-* everywhere below. Logstash on Logs stacks also has a dead letter queue for events OpenSearch rejects, described in the Logstash dead letter queue guide; it is not a buffer for when Filebeat can't connect. Filebeat's own registry is what lets it resume from the right place in the file after an outage.

PostgreSQL in a container usually logs to stderr rather than a file. The container's log then reaches your stack as a container log, without the module's parsing, unless you route it through the module. On Kubernetes, Filebeat autodiscover hints can do that with the annotation co.elastic.logs/module: postgresql; the Kubernetes container logs guide covers the Filebeat side.

Make PostgreSQL write one of each, then find them

Ten minutes here proves the whole path before you build anything on it. From a new psql session, so it picks up the reloaded settings, write a slow statement and an error:

SELECT pg_sleep(1.5);
SELECT * FROM table_that_does_not_exist;

From another machine, fail a login on purpose with a wrong password:

psql "host=db01.example.internal dbname=shop user=app"

And for a lock wait, open two sessions on a scratch table. In the first, take a row lock and leave the transaction open:

CREATE TABLE lock_test (id int PRIMARY KEY, n int);
INSERT INTO lock_test VALUES (1, 0);
BEGIN;
UPDATE lock_test SET n = n + 1 WHERE id = 1;

In the second, run the same UPDATE. It blocks. Wait two seconds, then ROLLBACK; in the first session and drop the table. On the server, the log should now hold lines like these (illustrative):

2026-11-19 10:02:11.420 UTC [51007-4] app@shop LOG:  duration: 1501.832 ms  statement: SELECT pg_sleep(1.5);
2026-11-19 10:02:15.006 UTC [51007-5] app@shop ERROR:  relation "table_that_does_not_exist" does not exist at character 15
2026-11-19 10:02:15.006 UTC [51007-6] app@shop STATEMENT:  SELECT * FROM table_that_does_not_exist;
2026-11-19 10:03:40.118 UTC [51122-2] app@shop FATAL:  password authentication failed for user "app"
2026-11-19 10:05:02.741 UTC [51140-3] app@shop LOG:  process 51140 still waiting for ShareLock on transaction 9917321 after 1000.087 ms
2026-11-19 10:05:02.741 UTC [51140-4] app@shop DETAIL:  Process holding the lock: 51007. Wait queue: 51140.

If they're not in the file, the problem is in PostgreSQL, not the shipping: check the settings with the pg_settings query above. If they are, open Discover in OpenSearch Dashboards, choose filebeat-*, set the time picker to the last 15 minutes and search:

event.dataset:postgresql.log

Open the pg_sleep event and check four things. event.duration is about 1501832000. postgresql.log.query holds the statement and postgresql.log.query_step says statement. user.name and postgresql.log.database are the account and database you used. And there's no error.message field. An error.message on a PostgreSQL event means the pipeline couldn't parse the line, and almost always points at the prefix or the time zone.

Then check the field types. In Dashboards Management, open the filebeat-* index pattern and search for postgresql, or ask the OpenSearch API on your stack. The endpoint and stack API key are on the Endpoints page in your stack settings. This request is illustrative, with placeholders:

curl -s "https://<stack-id>-es.logit.io/filebeat-*/_field_caps?fields=event.duration,log.level,user.name,postgresql.log.database,postgresql.log.query,postgresql.log.sql_state_code&apikey=<stack-api-key>&pretty"

You want event.duration as long and log.level and user.name as keyword. On most stacks the Beats template maps the postgresql.log.* strings as keyword as well, with a 1,024 character limit. If yours shows them as text with a .keyword sub-field, use postgresql.log.database.keyword and the like wherever this guide aggregates or sorts on them. Index templates on Logs stacks are managed, which you can read but not change, and the managed stack limitations page lists what else the API keeps to itself.

The fields you'll query most

These are the fields the managed pipeline writes for the text log, in the order you'll reach for them:

FieldWhat it holdsWatch out for
event.datasetpostgresql.log on every event from the moduleThe cheapest filter to start every search with
event.durationStatement or plan timeNanoseconds; only on duration lines
log.levelLOG, WARNING, ERROR, FATAL, PANIC, and in the text log also DETAIL, HINT, STATEMENT, CONTEXTUpper case, exactly as PostgreSQL writes it
postgresql.log.querySQL of a slow statementKeyword up to 1,024 characters for search
postgresql.log.query_stepThe protocol step, such as statement or executeMissing on auto_explain plans
postgresql.log.databaseDatabase from %dunknown before authentication
user.nameRole from %uunknown before authentication
process.pidBackend process IDTies an error to its STATEMENT event
postgresql.log.sql_state_codeSQLSTATE, such as 40P01csvlog only
messageThe message text, or the whole line for duration events and plansFull-text field: search it, don't aggregate on it
host.nameThe server Filebeat runs onSet by add_host_metadata

Saved searches for slow queries, errors and connections

Each of these is a DQL query for Discover in OpenSearch Dashboards on filebeat-*. Run it, add the columns listed, then save it with the name given so the rest of your team finds it. Start with slow statements over one second, with the plans excluded:

event.dataset:postgresql.log and event.duration >= 1000000000 and postgresql.log.query_step:*

Columns: host.name, postgresql.log.database, user.name, event.duration, postgresql.log.query. Save as PostgreSQL slow statements. For the plans that auto_explain wrote, search the plan header instead:

event.dataset:postgresql.log and message:"Query Text" and not postgresql.log.query_step:*

Errors next. Filter on severity, not on event.type, because event.type is info for every text-log line:

event.dataset:postgresql.log and log.level:(ERROR or FATAL or PANIC)

Save as PostgreSQL errors with host.name, log.level, user.name, postgresql.log.database and message. In the text log, the SQL that failed is in the next event, the one with log.level:STATEMENT and the same process.pid. From any error, choose View surrounding documents in Discover and the statement is right below it. For the statement itself:

event.dataset:postgresql.log and log.level:STATEMENT and process.pid:51007

Connection problems split into three questions with three queries. Failed logins, which you'd want to see grouped by user and host:

event.dataset:postgresql.log and log.level:FATAL and message:"authentication failed"

Clients refused because the server is full, which is the log's view of max_connections running out, and the reserved-slot variant PostgreSQL writes when only the superuser slots (and, on 16 and later, the pg_use_reserved_connections slots) are left:

event.dataset:postgresql.log and log.level:FATAL and (message:"too many clients" or message:"remaining connection slots are reserved")

And connections without a matching pg_hba.conf rule, which is usually a new application server or a changed subnet:

event.dataset:postgresql.log and message:"no pg_hba.conf entry"

Lock trouble, which is where most "it's only slow sometimes" tickets end up:

event.dataset:postgresql.log and (message:"still waiting for" or message:"deadlock detected" or message:"canceling statement due to lock timeout")

And statements killed by a timeout you set, so you can tell a cancelled query from a slow one:

event.dataset:postgresql.log and message:"canceling statement due to statement timeout"

Finally, anything that wasn't parsed. Keep this one and look at it after every PostgreSQL upgrade or config change:

event.dataset:postgresql.log and error.message:*

Build the PostgreSQL views yourself

There's no PostgreSQL pack in the Dashboard Library, but Install Dashboards in your stack settings does offer PostgreSQL. It adds the stock Filebeat module dashboards, [Filebeat PostgreSQL] Overview ECS and Query Duration Overview ECS, on filebeat-*. They chart log levels and query durations, and their Slow Queries search uses a fixed 30 ms cut-off rather than your threshold. None of them covers failed logins, connection churn or lock waits, and they don't break errors down by SQLSTATE, so build the views below as well. That's less work than it sounds, because the saved searches above do the filtering. First, make durations readable. In Dashboards Management (Stack Management on older versions), open the filebeat-* index pattern, edit event.duration, set the format to Duration with input in nanoseconds and output in milliseconds or a human-readable value. Every table and chart then shows 2,412 ms rather than 2412337000. This changes the index pattern, a saved object, not the mapping.

Then create these in Visualize, each on filebeat-* with the query shown:

  1. Slow statement time by database. Line chart. Query: the slow statements search. Y-axis: Percentiles of event.duration at 50 and 95. X-axis: date histogram on @timestamp. Split series: Terms on postgresql.log.database, top 5. Read it as "how slow the slow ones are", not overall latency, because fast statements never reach the log.
  2. Slowest statements. Data table. Query: the slow statements search. Split rows: Terms on postgresql.log.query, top 20, ordered by Max event.duration. Metrics: Count, Max and Sum of event.duration. Sum is the column to sort by when deciding what to fix first: a 600 ms query that runs ten thousand times a day costs more than one 40-second report.
  3. Errors by severity. Vertical bar chart. Query: the errors search. X-axis: date histogram. Split series: Terms on log.level. A PANIC bar of any height is an incident.
  4. Failed logins by user and host. Data table. Query: the failed logins search. Split rows: Terms on user.name, then Terms on host.name.
  5. Connection churn. Line chart. X-axis: date histogram. Split series: Filters, with one filter message:"connection authorized" and one message:"disconnection". Two lines that track each other at a high rate mean an application opening a connection per request, which is a pooler problem rather than a database one.
  6. Lock waits and deadlocks. Metric or bar chart on the lock trouble search, split by host.name.

Put them on one dashboard with the three saved searches underneath as panels, and set the dashboard's default time range to the last 24 hours. When someone says the database is slow, one URL then answers whether slow statements, errors or connection refusals rose, and when.

PPL for the summaries DQL can't produce

DQL filters; it doesn't group or compute. For "which database spent the most time in slow statements in the last 24 hours", use PPL in Query Workbench, which the guide to querying with Query Workbench shows you how to open. Choose the PPL tab and run:

source = filebeat-*
| where `event.dataset` = 'postgresql.log'
    and `event.duration` >= 1000000000
    and isnotnull(`postgresql.log.query_step`)
    and `@timestamp` >= DATE_SUB(NOW(), INTERVAL 1 DAY)
| stats count() as slow, sum(`event.duration`) as total_ns, max(`event.duration`) as worst_ns
    by `host.name`, `postgresql.log.database`
| eval total_s = total_ns / 1000000000.0, worst_ms = worst_ns / 1000000.0
| sort - total_ns
| head 10

One database carrying most of the total points at its queries or its indexes. An even spread across databases on one host points at the host: I/O, memory or a vacuum that's fallen behind. Backticks are required around @timestamp and harmless around any dotted field name, and Query Workbench has no time picker, so the time filter goes in the query.

The text log has no client address field, but the connection received message does have one. PPL's parse command pulls it out at query time, which answers "who's opening all these connections?":

source = filebeat-*
| where `event.dataset` = 'postgresql.log'
    and `@timestamp` >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
| parse message 'connection received: host=(?<client>\S+) port=\d+'
| where client != ''
| stats count() as connections by client, `host.name`

Lines that don't match the pattern get an empty client, which the second where drops. Parsing happens on every matching document at query time, so keep the time range short on a busy server. If you need client addresses routinely, csvlog gives you postgresql.log.client_addr as a real field.

The same questions work in plain English from an AI assistant through Hosted OpenSearch MCP, a standard MCP server that any MCP-compatible client can use. "Which PostgreSQL statements over one second ran most often on db01 today?" turns into a search on exactly these fields.

Alerts for failed logins, full servers and slow spikes

Logs stacks include alerting with ElastAlert 2 rules, which you enable from Alerting & Notifications in your stack settings; the guide to creating alerting rules walks through it. Three rules cover most PostgreSQL servers. First, a burst of failed logins against one server, using the frequency rule type. One or two a day is a person with a typo; twenty in five minutes is a broken deploy with an old password or someone guessing:

name: PostgreSQL failed logins
type: frequency
index: "filebeat-*"
num_events: 20
timeframe:
  minutes: 5
query_key: host.name
filter:
  - query:
      query_string:
        query: 'event.dataset:"postgresql.log" AND log.level:FATAL AND message:"authentication failed"'
realert:
  minutes: 30
alert:
  - "email"
email:
  - "[email protected]"

Second, the server refusing connections or crashing. One event is enough, so num_events is 1:

name: PostgreSQL refusing connections or panicking
type: frequency
index: "filebeat-*"
num_events: 1
timeframe:
  minutes: 5
query_key: host.name
filter:
  - query:
      query_string:
        query: 'event.dataset:"postgresql.log" AND (log.level:PANIC OR (log.level:FATAL AND (message:"too many clients" OR message:"remaining connection slots are reserved")))'
realert:
  minutes: 15
alert:
  - "email"
email:
  - "[email protected]"

Third, slow statements rising well above normal, with the spike rule type. It compares the last 15 minutes with the 15 before, so it adapts to a server that's always a little slow and still fires when a deploy adds a missing-index query:

name: PostgreSQL slow statements spiking
type: spike
index: "filebeat-*"
spike_height: 3
spike_type: up
threshold_cur: 20
timeframe:
  minutes: 15
query_key: host.name
filter:
  - query:
      query_string:
        query: 'event.dataset:"postgresql.log" AND event.duration:>=1000000000 AND postgresql.log.query_step:*'
alert:
  - "email"
email:
  - "[email protected]"

threshold_cur: 20 stops a jump from one slow statement to three waking anyone up. Run each rule with Test only before enabling it, to see how often it would have fired over recent data. A flatline rule on event.dataset:"postgresql.log" per host catches a stopped Filebeat or a moved log file, but a quiet replica can go an hour without a line, so give it a window longer than your quietest hour or point it only at busy primaries.

When PostgreSQL events arrive wrong

Nothing from PostgreSQL at all. Search event.module:postgresql over the last hour. If that's empty, look at Filebeat before anything else: sudo journalctl -u filebeat shows permission errors on the log directory and TLS errors to Logstash. Then check the module is enabled with sudo filebeat modules list and that enabled: true is set in the fileset, because the module ships with it off.

Events arrive with error.message and no PostgreSQL fields. The line didn't match the parser. Compare one raw message with the prefix rules above: an extra escape such as %a or %h, a missing space before the severity, or a numeric time zone are the usual causes. A database name with a hyphen in it, such as shop-prod, fails too, because the text parser only accepts letters, digits, underscores and $ in the database name. If the prefix is right, check that you chose Install Integration on the PostgreSQL integration page for this stack. The grok parse failures guide shows how to test a pattern against a sample line.

Events arrive as raw lines with no error.message either. They didn't go through the PostgreSQL pipeline. A plain filestream or log input is reading the file, or the events came from another shipper. Use the module.

One query appears as several events. Same cause: a plain input reading the file without the module's multi-line rule. With the module, only lines beginning with a date start a new event.

Times are off by hours. @timestamp comes from the PostgreSQL timestamp and its zone. A wrong log_timezone, or an ambiguous abbreviation, shifts every event. Set log_timezone = 'UTC' and reload.

Slow statements are logged on the server but the slow search is empty. Check the threshold units. event.duration >= 1000000000 is one second; >= 1000 is one microsecond and matches everything, while >= 1000000000000 is over 16 minutes and matches nothing. Also check the session: a connection opened before you changed log_min_duration_statement in a per-role setting keeps its old value.

Errors don't show in an event.type:error search. Expected with the text log, as described above. Search on log.level.

A long query can't be found by its SQL. postgresql.log.query is a keyword with a 1,024 character limit on most stacks. Longer statements are kept in the document and shown in Discover, but they aren't indexed, so a search or Terms split on that field skips them. Search message for a distinctive table name instead.

Events reach Logstash but not OpenSearch. An event that OpenSearch rejects, usually for a field type conflict with a field from another source in the same daily index, can end up in the Logstash dead letter queue. The field type conflicts guide explains how to find which field.

What PostgreSQL logs can't tell you

  • It's a sample, not a census. The log only holds statements over your threshold. A 20 ms query that runs a million times an hour never appears, even when together it costs more than every slow query combined. For totals across all statements, pg_stat_statements on the server is the tool, and the log tells you when and with what parameters.
  • No query fingerprinting. The same statement with different literal values is a different postgresql.log.query, so the Slowest statements table splits one problem into many rows. Applications that use bind parameters log $1 placeholders and group well; ORMs that inline values don't.
  • Metrics are a separate signal. Connection counts, replication lag, cache hit ratio, transaction rates and table bloat come from PostgreSQL's statistics views, not the log. The PostgreSQL integration guide also covers sending those with Telegraf to the metrics side of your stack.
  • Managed PostgreSQL is a different path. Amazon RDS and Aurora, Cloud SQL and Azure Database for PostgreSQL don't give Filebeat access to the log files, and some fix the line prefix to a format this parser doesn't read. Their logs come out through the provider's own logging, which is a separate setup and lands without this pipeline's fields. For Cloud SQL metrics there's a Google Cloud PostgreSQL integration guide.
  • The parser is fixed. Ingest pipelines on Logs stacks are managed, so a custom prefix or jsonlog won't be parsed into these fields. Staying with the prefix shape above is the fix, and anything you need beyond it belongs in csvlog.
  • Volume grows with the settings. log_connections on a server behind an application that doesn't pool can write more lines than everything else combined. Check your daily volume the day after you turn it on, and put a pooler such as PgBouncer in front if connection lines dominate. The PgBouncer integration guide covers sending the pooler's own metrics.
  • Logs carry data. SQL with literal values, failed login user names and client addresses are all in there. Keep log_parameter_max_length = 0, and on PostgreSQL 16 and later auto_explain.log_parameter_max_length = 0 too, because its default logs bind values in full in every plan. Check who can view filebeat-* on your stack and keep retention to what you need.

Start Free Trial

Unlock complete visibility with hosted ELK, Grafana, and Prometheus-backed Observability

Start Free Trial

FAQ

What should log_min_duration_statement be set to?

Start at 500 ms for a web application's primary and look at the PostgreSQL slow statements search after a day. If it's nearly empty while users still complain, lower it to 200 or 100 ms. If it's thousands an hour, fix the top rows of the Slowest statements table before raising the threshold, and use per-database or per-role overrides for reporting and batch work rather than one number for everything.

Why do my ERROR events have event.type info?

The text log only writes the SQLSTATE code in verbose mode, which stops the parser reading durations, so on the text log every line is info. Search on log.level:(ERROR or FATAL or PANIC), or switch to csvlog if you want codes such as 40P01 and 53300 as a field.

Does this work with Amazon RDS, Aurora or Cloud SQL?

Not as described here. This path needs Filebeat reading the log file on the database host, which managed services don't allow, and their fixed line prefixes don't match the parser. Export their logs through the provider's own logging instead, and expect to search them as plain messages rather than with the postgresql.log.* fields.

Can I use jsonlog on PostgreSQL 15 or later?

Not with the PostgreSQL pipeline on Logs stacks, which parses the text log and csvlog only. A jsonlog line arrives with an error.message and the whole JSON object in message. If you want structured columns, csvlog gives you nearly all of the same information as parsed fields.

Why are the events in filebeat-* rather than logstash-*?

Events from Beats sent through your stack's Logstash are written to daily filebeat-* indices, which use the Beats mapping: event.duration and process.pid as numbers and the ECS strings as keywords. Data sent to Logstash without a Beat, such as syslog, goes to logstash-*, where strings are text with .keyword sub-fields. Search PostgreSQL module events on filebeat-*.

Can I try this on the free trial?

Yes. The free trial includes every feature, including alerting, Query Workbench and Hosted OpenSearch MCP. Point Filebeat at one PostgreSQL server, run the four test statements from this guide and build the first saved search.

Next steps

Set the prefix, threshold, connection and lock settings, reload, enable the Filebeat postgresql module and make PostgreSQL write one slow statement, one error, one failed login and one lock wait. When all four are in Discover with their fields, save the searches, build the six visualisations and turn on the three alert rules. If you're running your own OpenSearch cluster for database logs, a Logit.io Logs stack gives you managed Logstash, the PostgreSQL pipeline, OpenSearch and OpenSearch Dashboards without the upkeep. For a dedicated cluster, see Hosted OpenSearch and OpenSearch pricing. To ask questions of the same PostgreSQL logs from any MCP-compatible AI assistant, see Hosted OpenSearch MCP. And if you also run MySQL, the MySQL slow query and error log guide covers the equivalent path, with a ready-made dashboard. For the cache in front of the database, the Redis logs and slow commands guide does the same for Redis.

Get the latest Elastic Stack & logging resources when you subscribe

Want to see this in action?
Start a free trial and connect logs to your alert workflows.