How to find alerts in PostgreSQL through Plain Text Logs with a query

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How to find alerts in PostgreSQL through Plain Text Logs with a query - 7 September 2026
• How to see how much Indexes are used in PostgreSQL - 4 September 2026
• How to find the size of tables in a PostgreSQL database - 2 September 2026

As Database Administrators, we are used to reading alerts like regular tables from environments like Oracle. v$diag_alert_ext with simple SQL queries. In PostgreSQL, things are a little different, by default its logs are plain text text files and not in an internal view.

If we want to check the latest errors directly from our database using SQL, we can leverage Postgres' file reading function. pg_read_fileInstead of looking up what day it is or what the exact name of the file is, pg_ls_logdir() scans the folder and automatically selects the most recently modified log file.

The Query

The following script reads directly the current plain text log file and returns us a clean list of serious errors ERROR, FATAL, PANIC sorted chronologically:

WITH latest_log AS (
    SELECT 'log/' || name AS log_file 
    FROM pg_catalog.pg_ls_logdir() 
    ORDER BY modification DESC 
    LIMIT 1
),
raw_lines AS (
    SELECT 
        line_number,
        log_line,
        COUNT(CASE WHEN log_line ~ '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}' THEN 1 END) OVER (ORDER BY line_number) AS event_id
    FROM (
        SELECT line_number, log_line
        FROM unnest(string_to_array(pg_read_file((SELECT log_file FROM latest_log)), e'\n')) WITH ORDINALITY AS t(log_line, line_number)
    ) s
),
grouped_events AS (
    SELECT 
        event_id,
        MIN(log_line) AS header_line,
        string_agg(log_line, e'\n' ORDER BY line_number) AS full_multiline_block
    FROM raw_lines
    WHERE event_id > 0
    GROUP BY event_id
)
SELECT 
    substring(header_line from '^([0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2})') AS log_time,
    
    CASE 
        WHEN full_multiline_block LIKE '%FATAL%' THEN 'FATAL'
        WHEN full_multiline_block LIKE '%PANIC%' THEN 'PANIC'
        ELSE 'ERROR'
    END AS severity,
    
    REGEXP_REPLACE(full_multiline_block, '^.*?(ERROR|FATAL|PANIC):\s*', '\1: ', 's') AS error_message

FROM grouped_events
WHERE full_multiline_block LIKE '%ERROR%' 
   OR full_multiline_block LIKE '%FATAL%' 
   OR full_multiline_block LIKE '%PANIC%'
  AND full_multiline_block NOT LIKE '%canceling statement due to user request%'
ORDER BY log_time DESC;

Sources:

• PostgreSQL 18.4 Documentation