0

I am trying to create a systemctl service to collect + upload logs for Postgresql 12 on Ubuntu Linux. However, I am expert at none of the two.

While I have set up the server modifying /etc/postgresql/12/main/postgresql.conf (I am able to connect and run my DDLs on the server from local machine), I am confused about logs:

  • There are so many log options in the file. Once I set them, I could see logfiles inside /var/lib/postgresql/12/main/log folder.

However, since I accidentally deleted them, I can no longer see any logs created even as I perform operations (errors + normal both). I have following settings:

log_destination = 'stderr,csvlog'
logging_collector = on
log_directory = 'log'
log_min_duration_statement = 1000
#log_min_error_statement = error
#log_min_messages = warning

(notice that last 2 are left as defaults as I suppose they should be)

How to get the logs getting created again?

  • Again, I am not sure if those are everything. There is this /var/log/postgresql/postgresql-12-main.log also, which has connection and disconnection info.

Is this everything? Or is there some crucial file places I am missing?

Also, I assume there will be more than 1 log files (based on rotation and all) - Is there a recommended strategy to have only relevant info uploaded to some place? Optionally, is there a library/readymade service which does this readily?

3
  • Restart postgres to get the logs written again, I assume the server still has the now-deleted file handles open. Ask about the log rotation on the main stackoverflow or on superuser - log file rotation is an OS-level thing, not a database thing.
    – AdamKG
    Sep 1, 2021 at 0:35
  • Usually a reload of the service is all that’s needed to signal to it to create new log files. This is what many logrotate rules do. Sep 1, 2021 at 4:37
  • Why are you rolling your own? Why not setup a syslog server for this? Sep 1, 2021 at 4:39

1 Answer 1

2

You probably installed PostgreSQL with binary packages rather than from source.

The packager for Ubuntu decided not to use PostgreSQL's features for writing and rotating the log file, but to redirect standard error into a file in /var/log/postgresql and use operating system tools for log rotation.

So yes, the log you see there is all there is. You can adjust the PostgreSQL parameters to change what ends up in the log.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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