PostgreSQL – Get Exact Log File Name with SQL

logspostgresql

I get current log file by using SQL without getting in server via ssh. So I use a query like the one below;

SELECT pg_read_binary_file(
  concat_ws('/', 
    current_setting('log_directory'),
    'postgresql-Fri.log'
  )
);

Log files are not too big so i am fine with that. The thing is, I cannot use this query in a generic way. Is there a way to get 'postgresql-Fri.log' from my log_filename setting which is currently postgresql-%a.log but the format can differ?

Best Answer

There is now a pg_current_logfile() function returning this information:

Primary log file name, or log in the requested format, currently in use by the logging collector.

but it's a new feature of PostgreSQL 10, which you probably don't run since it hasn't yet got out as a General Availability release (it's still Release Candidate at the moment).

With previous versions, you're pretty much on your own to figure out the current name, either by interpreting log_filename, or by finding the latest modified, for instance with a combination of pg_ls_dir and pg_stat_file:

SELECT file, -- should be the latest logfile
  (pg_stat_file(current_setting('log_directory')||'/'||file)).modification
  FROM  pg_ls_dir(current_setting('log_directory')||'/') as list(file)
  ORDER BY 2 DESC
  LIMIT 1;

This can fail if the server's clock goes backward or if non-log files are dumped into the same directory as the log files.

Related Solutions

MySQL – How to Log DB Changing Queries and Users

This sounds like a perfect use of TRIGGERS to me. You say you have transactions, so you must already be using InnoDB. Attach triggers on INSERT, UPDATE, and DELETE on the tables you care about, each one triggering an INSERT into a log table AFTER the table event. There's your SELECT filter, done. You also have total control over what you log and how.

This is a higher level solution, closer to the application layer, not a low-level system log solution where you have an exact copy of the complete SQL query. But I think it does address some of your concerns.

InnoDB Triggers have access to stored procedures and all the MySQL functions, so I presume you can find the user's login name in there somewhere. The log time is just NOW().

You may end up with a lot of triggers to manage, depending on your number of tables, but it's just a copy/paste job for each one. I maintain the code for all triggers in a single reference SQL file along with the table definitions and views for that database.

I've never tested triggers in a transaction-rich environment to make sure the trigger only fired after the transaction was committed. The docs are thin on this subject, but they do say, "An AFTER trigger is executed only if the BEFORE trigger (if any) and the row operation both execute successfully." Sounds conclusive to me. Now you also know you're not logging things that didn't actually work.
OR set up replication, even to another mysqld instance on the same box if necessary. Turn on your mysqld full query logging on the slave instance. Failed transactions don't get replicated, and neither do SELECTs.

Postgresql – could not open log file “/var/log/postgresql-2015-05-24_181212.log”: Permission denied

I know that only root can write to /var/log ...

You can change that. As root user (or with sudo) in the shell:

mkdir /var/log/postgresql
chmod 0700 /var/log/postgresql
chown postgres:postgres /var/log/postgresql

And in postgresql.conf:

log_destination = 'stderr'      
logging_collector = on      
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'     
log_rotation_age = '1d'          # That's what makes it "dynamic"

Depesz has a detailed article on the topic:
https://www.depesz.com/2011/05/06/understanding-postgresql-conf-log/

Best Answer

Related Solutions

MySQL – How to Log DB Changing Queries and Users

Postgresql – could not open log file “/var/log/postgresql-2015-05-24_181212.log”: Permission denied

Related Question