SQL Queries - Common SQL Queries - How to get postgres log files

Purpose of Query

This page will help you to get the content of PG log files, when you don’t have access to the machine hosting the server.

Applicable CAST Version

Applicable RDBMS

Query for CSS

• For all versions of CSS:

with LogDirectory(DirectoryPath) as ( select setting from pg_settings where name = ’log_directory' ), LogRelativePath(RelativePath) as ( select ld.DirectoryPath || ‘/’ || pg_ls_dir(ld.DirectoryPath) from LogDirectory ld ), ServerLogs(RelativePath, ModificationDate, Size) as ( select lrp.RelativePath, (pg_stat_file(lrp.RelativePath)).modification, (pg_stat_file(lrp.RelativePath)).size from LogRelativePath lrp where (pg_stat_file(lrp.RelativePath)).size > 0 order by 2 desc limit 5 ) select RelativePath, ModificationDate, pg_size_pretty(Size), convert_from(pg_read_binary_file(RelativePath), ‘UTF8’) from ServerLogs;

- CSS4:

  ```sql
with ServerLogs(RelativePath, ModificationDate, Size)
as
(
select 'log/' || pg_ls_dir,
       (pg_stat_file('log/' || pg_ls_dir)).modification,
       (pg_stat_file('log/' || pg_ls_dir)).size
  from pg_ls_dir('log')
where (pg_stat_file('log/' || pg_ls_dir)).size > 0
order by (pg_stat_file('log/' || pg_ls_dir)).modification desc
limit 1 -- number of log files to get starting from the recent one
)
select RelativePath,
       ModificationDate,
       pg_size_pretty(Size),
       convert_from(pg_read_binary_file(RelativePath), 'UTF8')
  from ServerLogs;

• CSS2 and CSS3:

with ServerLogs(RelativePath, ModificationDate, Size) as ( select ‘pg_log/’ || pg_ls_dir, (pg_stat_file(‘pg_log/’ || pg_ls_dir)).modification, (pg_stat_file(‘pg_log/’ || pg_ls_dir)).size from pg_ls_dir(‘pg_log’) where (pg_stat_file(‘pg_log/’ || pg_ls_dir)).size > 0 order by (pg_stat_file(‘pg_log/’ || pg_ls_dir)).modification desc limit – number of log files to get starting from the recent one ) select RelativePath, ModificationDate, pg_size_pretty(Size), convert_from(pg_read_binary_file(RelativePath), ‘UTF8’) from ServerLogs;

If you replace <Enter the Limit> by 1 you will get the last generated log file-

**Query result example**

![](https://doc-data.castsoftware.com/TG/attachments/566526548/566526547.png)

**Query result interpretation**

The query returns the log path, last modification date, log size and log content

**Notes/comments**

**Related Pages**