3

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;
0
3

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 COPY or 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).

0

I find this to be a good question and I am not satisfied with the answer. The reason is that the database is the home of all data and all analysis you want to do on the logs, you really want to be able to combine with your data.

The question is what the extra load on the database is for log recording. Perhaps the real issue is whether logging might perhaps require less ACIDity and therefore could use certain less expensive operations. Having to save log data to the WAL and then apply it to the table files, perhaps that's unnecessary.

Logging to CSV and then mount as a foreign table seems like a decent approach. But what if you have a distributed system of many instances (we have that in the cloud for example) you end up wanting one place to have all your logs, including Web server logs, application server logs. All this writing to /var/log/ files and then parsing the various text formats to get something useful for analysis seems like a wrong-headed waste. There are log analyzing software packages out there, to me that's crazy. We log everything to the database, even syslog from NGINX web servers go into the database.

Perhaps one might say not "the" database but "a" database. If I found that the log activity competes too much with the main database server resources, I would put the logging database separately, with some nifty distribution, perhaps using PgPool-II to accomplish that. But so far, I think its simpler to just log to the database.

But now, there is also the question how much logging you will actually ever use? I see this as the biggest bang for the buck to reduce logging activity where you won't ever look at it anyway. Especially if all that's happening is IO to log tables and then rotated them and throw them away.

You might not even need that log file if you just save pg_stat_activity into a historical table. Here is an article about an approach like that:

https://bdrouvot.wordpress.com/2018/07/07/postgresql-active-session-history-ash-welcome-to-the-pg_active_session_history-view-part-of-the-pgsentinel-extension/

This one doesn't even write the data out, it is an extension that keeps the history in memory. I don't think that's a great idea when we have a busy database and want to find out the statistics of where we spend our resources during days and weeks of activity.

You could run a sampling of the pg_stat_activity yourself by essentially

INSERT INTO pg_stat_activity_history SELECT FROM pg_stat_activity;

Perhaps by sampling every minute or so you have less bulk and you might still get something out of it. And then there is all the other monitoring statistics you can get:

https://www.postgresql.org/docs/13/monitoring-stats.html

My personal interest is deep logging of all system resources and understanding the cost of everything traced back to the end-user activity. So I need even more. But to me database is the point where I do this, and it just becomes a question of how best to partition the log activity to make it not have undue adverse effect of the payload activity of the system.

0

You can use the file_fdw extension to load the log into a table for querying. You have to deal with log file rotation manually though. If you are on Amazon RDS, there is the log_fdw extension supported which will do this, and it handles log rotation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.