How to get complete postgres logs

This is part of the Semicolon&Sons Code Diary - consisting of lessons learned on the job. You're in the databases category.

Last Updated: 2022-12-01

Web apps that interact with postgres (or other DBs) often have the ability to log their interactions with whatever database they use. But sometimes this isn't sufficient (or you might distrust its perspective) and you want to check how the picture appears from the database's point of view.

How can one do this, given that the database is usually a daemon?

Option 1: Tune into existing logs

On macos:

tail -f /usr/local/var/log/postgres.log

Note that you probably will not see the queries in the logs at first. This is because you need to change log_statement = 'all' in the postgres.conf file, which is located in the same directory as I gave for the data argument (-D) above.

The default for log_statement is none which means it won't log many of your SQL statements.

Option 2: Run postgres in the foreground

postgres -D /usr/local/var/postgres runs postgres in the foreground. This can be useful is you're using something like foreman to run all processes related to your system via one parent process with unified logging.

Option 3: Leave it running as a daemon service, but tune in with system-specific logging tools

This provides the advantages of option 2, but without the need to mess with your postgres daemon (e.g. switching it off) and potentially intefering with other programs on your machine.

On macos:

log_level=info
process_name=postgres
log stream --$log_level --predicate "process == '$process_name'"