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
Release | Yes/No |
---|---|
8.3.x |
Applicable RDBMS
RDBMS | Yes/No |
---|---|
CSS |
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:
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
Query result interpretation
The query returns the log path, last modification date, log size and log content
Notes/comments
Related Pages