Is there a table that with the log file information in postgres? I wish to create a view/report of the information in the postgres logs. I'd like to do something like:
SELECT *
FROM log_table;
Postgresql – Postgres Log Table
postgresql
Related Question
- Postgresql – Postgres log files not rotating
- PostgreSQL 9.6 – Log integrity
- PostgreSQL – How to Find Matching Pairs Inside Table
- PostgreSQL – Create Materialized View from Table with JSONB Columns
- Postgresql – Explore postgres recovery log records
- PostgreSQL Logs – Why Raise NOTICE and INFO Are Not Saved
Best Answer
Fortunately PostgreSQL does not log to a table, because the performance penalty would be forbidding on an active database — logging to a file is much cheaper.
You can still get what you want:
Configure
log_destination = csvlog(see the documentation for a description of the format and how it matches a database table layout).You can either load the log file into a database table with
COPYor use file_fdw to define an “external table” for it (the latter has the advantage of using no space in the database, but it only allows sequential scans).Either way, you can use table partitioning to unify the individual log files to a single partitioned log table that you can query (optional of course).