2

I installed postgresql from source on Ubuntu 14.04 (I also installed from repositories too before, I just want to get a feel for both of them right now, as I will heavily rely on this database). So when I installed from source, in my postgresql.conf, I specified I want all standard error from the postmaster executable to go to /var/log:

log_destination = 'stderr' 
logging_collector = on
log_directory = '/var/log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

I know that only root can write to /var/log (and that is the defaults on Ubuntu), so how are people doing this? How can you make that log file writable before it even exists? Or are people manually creating the log file first and then changing its permissions?

Unfortunately, my temporary solution is to hard-code a filename e.g. postgresql-9.4.2-main.log for log_filename and then as root create that file in /etc/log and then change its ownership to postgres, and then when I start the server it works fine. But I want the log file to be dynamic. After it reaches a specified size, I want the old one to be zipped up, and a new one created with the current date and time.

  • @ErwinBrandstetter how is that going to make the log dynamic? – Donato May 27 '15 at 17:53
7

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/

| improve this answer | |
  • 1
    I stumbled into this answer and it took me a while to realize that the chmod needs to be 0700, otherwise postgres will not have enough permissions to create new log files. – YuppieNetworking Oct 17 '19 at 18:05
  • @YuppieNetworking: Yes, thanks for pointing out. I fixed accordingly. – Erwin Brandstetter Oct 17 '19 at 21:39

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.