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

 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.

• 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:

  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 <Enter the 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