Postgresql log uploading service on Ubuntu Linux

Question

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?

Answer 1

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.