pg_ls_logdir()
pg_ls_logdir() is a system function for examining the contents of a PostgreSQL log directory, as defined by the configuration parameter log_directory.
pg_ls_logdir() was added in PostgreSQL 10.
Usage
pg_ls_logdir () → setof record (nametext,sizebigint,modificationtimestamp with time zone)
pg_ls_logdir() returns a set of records containing the name, size and last modification timestamp of files in log_directory.
It returns the file records unsorted, so is usually combined with ORDER BY.
Note that pg_ls_logdir() will return all files in the log directory, regardless of whether they are actually log files. However, in contrast to pg_ls_dir(), it does not return filenames beginning with a dot, directories, and other special files.
Permissions
By default pg_ls_logdir() is restricted to superusers and members of the pg_monitor default role, but other users can be granted EXECUTE permission to run this function.
Source code
pg_ls_logdir() is implemented in src/backend/utils/adt/genfile.c.
Change history
- PostgreSQL 10
- added (commit befd73c5)
Examples
Select the five most recent files in log_directory:
postgres=# SELECT * FROM pg_ls_logdir() ORDER BY modification DESC LIMIT 5;
name | size | modification
----------------------------------+-------+------------------------
postgresql-2020-12-24_000000.log | 5946 | 2020-12-24 02:27:10+01
postgresql-2020-12-23_000000.log | 48825 | 2020-12-23 23:51:25+01
postgresql-2020-12-22_000000.log | 48512 | 2020-12-22 23:51:18+01
postgresql-2020-12-21_000000.log | 48748 | 2020-12-21 23:51:27+01
postgresql-2020-12-20_000000.log | 48405 | 2020-12-20 23:51:02+01
(5 rows)
Note that the directory specified by log_directorymay not exist (this could be the case e.g. if log_destination is set to syslog, and the directory was never initialised), in which case an ERROR will be raised:
postgres=# SELECT pg_ls_logdir(); ERROR: could not open directory "log": No such file or directory
References
- PostgreSQL documentation: Generic File Access Functions