LogsDB
PostgreSQL logo

PostgreSQL Server Log

PostgreSQL server logs including queries, errors, connections, and performance data

Edit this page

Quick Facts

Default Path (Linux)
/var/log/postgresql/postgresql-17-main.log
Docker
stderr
Default Format
stderr (text)
JSON Native
Yes (since 15)
Rotation
log_rotation_age/size or logrotate

Log Example

Default format: Standard Error Format

Example Log Entrylog
2025-12-20 14:32:18.123 UTC [1234] postgres@mydb LOG:  statement: SELECT * FROM users WHERE id = 1

Structure:

timestamp [pid] user@database level: message

Paths by Platform

Debian / Ubuntu
/var/log/postgresql/postgresql-17-main.log
RHEL / CentOS
/var/lib/pgsql/data/log/

Available Formats

Standard Error Format

Default

Example:

2025-12-20 14:32:18.123 UTC [1234] postgres@mydb LOG:  statement: SELECT * FROM users WHERE id = 1

Structure:

timestamp [pid] user@database level: message

CSV Log Format

Config Required

Example:

2025-12-20 14:32:18.123 UTC,"postgres","mydb",1234,"192.168.1.100:54321",12345678.1234,1,"SELECT",2025-12-20 14:30:00 UTC,1/1,0,LOG,00000,"statement: SELECT * FROM users WHERE id = 1",,,,,,"psql"

Structure:

timestamp,user,database,pid,client,session_id,line_num,command_tag,session_start,vxid,txid,level,code,message,detail,hint,query,pos,context,location,app

JSON Log Format

Native
Config Required

Available since version 15

Example:

{"timestamp":"2025-12-20 14:32:18.123 UTC","pid":1234,"user":"postgres","dbname":"mydb","error_severity":"LOG","message":"statement: SELECT * FROM users WHERE id = 1"}

Structure:

JSON object with structured fields

Fields Reference

FieldTypeDescriptionExample
timestamp
datetime
When the log entry was created2025-12-20 14:32:18.123 UTC
pid
integer
Process ID of the backend1234
user
string
Database user namepostgres
dbname
string
Database namemydb
client_addr
ip
Client IP address192.168.1.100
error_severity
string
Log levelLOG
message
string
Log message contentstatement: SELECT * FROM users WHERE id = 1
duration
float
(milliseconds)		Query duration (when log_duration or log_min_duration_statement enabled)12.345
sql_state_code
string
SQL state error code42P01
application_name
string
Application name from connectionpsql

Parsing Patterns

Grok Patterns

stderr:

%{TIMESTAMP_ISO8601:timestamp} %{TZ:timezone} \[%{NUMBER:pid}\] %{DATA:user}@%{DATA:database} %{WORD:level}:  %{GREEDYDATA:message}

Collector Configurations

logstashruby
1filter {
2  grok {
3    match => { "message" => "%{TIMESTAMP_ISO8601:timestamp} %{TZ:timezone} \[%{NUMBER:pid}\] %{DATA:user}@%{DATA:database} %{WORD:level}:  %{GREEDYDATA:pg_message}" }
4  }
5  if "duration:" in [pg_message] {
6    grok {
7      match => { "pg_message" => "duration: %{NUMBER:duration:float} ms" }
8    }
9  }
10}

Configuration

Enable Logging

Enable detailed logging in postgresql.conf

postgresql.conf
# Logging destination
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

# What to log
log_statement = 'all'           # none, ddl, mod, all
log_duration = on
log_min_duration_statement = 1000  # Log queries > 1 second

# Connection logging
log_connections = on
log_disconnections = on

# Error logging
log_error_verbosity = default   # terse, default, verbose

Enable Json

Recommended

Enable JSON logging format (PostgreSQL 15+)

# In postgresql.conf
log_destination = 'jsonlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql.json'

Log Rotation

Tool: PostgreSQL native or logrotate | Config: postgresql.conf

postgresql.conf
# Native rotation
log_rotation_age = 1d           # Rotate daily
log_rotation_size = 100MB       # Or when reaching 100MB
log_truncate_on_rotation = on

# With logrotate (if logging_collector = off)
/var/log/postgresql/*.log {
    daily
    rotate 7
    compress
    missingok
    notifempty
    postrotate
        /usr/bin/pg_ctlcluster 17 main reload
    endscript
}

Use Cases

Slow query analysis

Find queries exceeding duration threshold

duration
message
duration > 1000

Connection monitoring

Track connection patterns

user
client_addr
message

Error rate

Monitor ERROR and FATAL events

error_severity

Troubleshooting

All Available Variables

log_line_prefix

%aApplication name
%uUser name
%dDatabase name
%rRemote host and port
%hRemote host
%pProcess ID
%tTimestamp without milliseconds
%mTimestamp with milliseconds
%iCommand tag
%eSQL state error code
%cSession ID
%lSession line number
%sSession start timestamp
%vVirtual transaction ID
%xTransaction ID
%qStop here in non-session processes

Tested On

v17.0 on Ubuntu 24.04
db_expert - 2025-12-12
v16.4 on Debian 12
admin - 2025-11-01
Last updated: 2025-12-12 by db_expert
3 contributors187 upvotes
Validated

Community Discussions

Help improve this documentation

Found an error or want to add more examples? Contributions are welcome!

EditReport Issue