#8 Customise logging, POSTGRESQL install configurations(PART-II)

Good day everyone, welcome to yet another article and this is the extension of the last article on POSTGRES installation and configuration. Assuming that you are either familiar with the installation or have already gone through my last article on the installation of postgresql, we are going to cover some very basic but important aspects of customising log files for postgres. If you want to revisit the article please see the link:

Now log files play a very important role when it comes to troubleshooting issues or doing audits, automating certain security compliances for organisation(s) etc. Firstly whenever you install postgres(PG) there is going to be a default login behaviour on different platforms and different flavours of PG. Now I have already covered how to find different directories and files after the installation of PG.

I will be covering specifically PG11 but its should be valid for the older versions of PG as well. Lets take a look at the default behaviour of the login done in postgres. I have just initialised the database cluster at directory /usr/var/local/postgres/data in the below mentioned:

Rajs-MacBook-Pro:postgres rajverma$ pg_ctl initdb -D /usr/local/var/postgres/data
The files belonging to this database system will be owned by user "rajverma".
This user must also own the server process.

The database cluster will be initialized with locales
  COLLATE:  C
  CTYPE:    UTF-8
  MESSAGES: C
  MONETARY: C
  NUMERIC:  C
  TIME:     C
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

creating directory /usr/local/var/postgres/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/Cellar/postgresql/11.2/bin/pg_ctl -D /usr/local/var/postgres/data -l logfile start

Now if I go ahead and list all the files inside the data directory which i have mentioned while initialising the cluster then I see this:

Rajs-MacBook-Pro:data rajverma$ ll
total 88
-rw-------   1 rajverma  admin      3 Apr  7 01:24 PG_VERSION
drwx------   5 rajverma  admin    170 Apr  7 01:24 base
drwx------  60 rajverma  admin   2040 Apr  7 01:24 global
drwx------   2 rajverma  admin     68 Apr  7 01:24 pg_commit_ts
drwx------   2 rajverma  admin     68 Apr  7 01:24 pg_dynshmem
-rw-------   1 rajverma  admin   4513 Apr  7 01:24 pg_hba.conf
-rw-------   1 rajverma  admin   1636 Apr  7 01:24 pg_ident.conf
drwx------   5 rajverma  admin    170 Apr  7 01:24 pg_logical
drwx------   4 rajverma  admin    136 Apr  7 01:24 pg_multixact
drwx------   3 rajverma  admin    102 Apr  7 01:24 pg_notify
drwx------   2 rajverma  admin     68 Apr  7 01:24 pg_replslot
drwx------   2 rajverma  admin     68 Apr  7 01:24 pg_serial
drwx------   2 rajverma  admin     68 Apr  7 01:24 pg_snapshots
drwx------   2 rajverma  admin     68 Apr  7 01:24 pg_stat
drwx------   2 rajverma  admin     68 Apr  7 01:24 pg_stat_tmp
drwx------   3 rajverma  admin    102 Apr  7 01:24 pg_subtrans
drwx------   2 rajverma  admin     68 Apr  7 01:24 pg_tblspc
drwx------   2 rajverma  admin     68 Apr  7 01:24 pg_twophase
drwx------   4 rajverma  admin    136 Apr  7 01:24 pg_wal
drwx------   3 rajverma  admin    102 Apr  7 01:24 pg_xact
-rw-------   1 rajverma  admin     88 Apr  7 01:24 postgresql.auto.conf
-rw-------   1 rajverma  admin  23817 Apr  7 01:24 postgresql.conf

t's take a look at the section in postgresql.conf file for logging information. By default it looks like this:

Now if you see by default the logging collector is set to 'OFF'. That is the prime reason for you to not see any logs in any of the PG directories. So to enable logging for postgres all you need to do is the below mentioned settings:

# - Where to Log -

log_destination = 'stderr'              
logging_collector = on          
log_directory = 'pg_log'                   
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' 
log_truncate_on_rotation = on                                   
log_rotation_age = 1d                   

Let me go through each one of those for you:

1. log_destination >> this is default value which logs the messages to system error logs.
2. logging_collector >> This parameter enables the logging collector, which is a background process that captures log messages sent to stderr and redirects them into log files.
3. log_directory >> This is the location of the new folder where you want to save your postgresql log file after the log collector process fetches them from stderr.
4. log_filename >> This can be customised in several different ways(naming your log file days wise, month wise etc.). Its really helpful in looking at the right error log when looking for info so for e.g when its Monday you only have to look at Monday's logs and nothing else. Will cover this in detail in another article.
5. log_truncate_on_rotation >> Basically what this means is, it will cause PostgreSQL to truncate (overwrite), rather than append to, any existing log file of the same name. However, truncation will occur only when a new file is being opened due to time-based rotation, not during server startup or size-based rotation. When off, pre-existing files will be appended to in all cases. Example mentioned below:

"To keep 7 days of logs, one log file per day named server_log.Mon, 
server_log.Tue, etc, and automatically overwrite last week's log with this week's log, set log_filename to server_log.%a, log_truncate_on_rotation to on, and log_rotation_age to 1440 or 1d."

The number 1440 is due to the fact that there are 24 hrs in a day and 60 minutes in an hours so a day=1440 minutes.

Well once you have all those settings done, save them using ":wq" or whatever editor you are using. Once this is saved it is of utmost importance that your reload the services. I will have an articles or reload vs restart the PG service in future.

Rajs-MacBook-Pro:data rajverma$ pg_ctl start -D /usr/local/var/postgres/data/
waiting for server to start....2019-04-07 01:56:56.595 PDT [17122] LOG:  listening on IPv6 address "::1", port 5432
2019-04-07 01:56:56.595 PDT [17122] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-04-07 01:56:56.596 PDT [17122] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-04-07 01:56:56.603 PDT [17122] LOG:  redirecting log output to logging collector process
2019-04-07 01:56:56.603 PDT [17122] HINT:  Future log output will appear in directory "pg_log".
 done
server started

You see the last line says HINT: Future log output will appear in directory "pg_log". This means your login is now enabled and all the messages will be logged inside the data directory plus pg_log directory. You can call it whatever you want. So now this is how your directory structure will look like:

Rajs-MacBook-Pro:data rajverma$ ll
total 112
-rw-------   1 rajverma  admin      3 Apr  7 01:24 PG_VERSION
drwx------   5 rajverma  admin    170 Apr  7 01:24 base
-rw-------   1 rajverma  admin     47 Apr  7 01:56 current_logfiles
drwx------  60 rajverma  admin   2040 Apr  7 01:24 global
drwx------   2 rajverma  admin     68 Apr  7 01:24 pg_commit_ts
drwx------   2 rajverma  admin     68 Apr  7 01:24 pg_dynshmem
-rw-------   1 rajverma  admin   4513 Apr  7 01:24 pg_hba.conf
-rw-------   1 rajverma  admin   1636 Apr  7 01:24 pg_ident.conf
drwx------   3 rajverma  admin    102 Apr  7 01:56 pg_log
drwx------   5 rajverma  admin    170 Apr  7 01:24 pg_logical
drwx------   4 rajverma  admin    136 Apr  7 01:24 pg_multixact
drwx------   3 rajverma  admin    102 Apr  7 01:56 pg_notify
drwx------   2 rajverma  admin     68 Apr  7 01:24 pg_replslot
drwx------   2 rajverma  admin     68 Apr  7 01:24 pg_serial
drwx------   2 rajverma  admin     68 Apr  7 01:24 pg_snapshots
drwx------   2 rajverma  admin     68 Apr  7 01:24 pg_stat
drwx------   3 rajverma  admin    102 Apr  7 01:56 pg_stat_tmp
drwx------   3 rajverma  admin    102 Apr  7 01:24 pg_subtrans
drwx------   2 rajverma  admin     68 Apr  7 01:24 pg_tblspc
drwx------   2 rajverma  admin     68 Apr  7 01:24 pg_twophase
drwx------   4 rajverma  admin    136 Apr  7 01:24 pg_wal
drwx------   3 rajverma  admin    102 Apr  7 01:24 pg_xact
-rw-------   1 rajverma  admin     88 Apr  7 01:24 postgresql.auto.conf
-rw-------   1 rajverma  admin  23812 Apr  7 01:56 postgresql.conf
-rw-------   1 rajverma  admin     83 Apr  7 01:56 postmaster.opts
-rw-------   1 rajverma  admin     95 Apr  7 01:56 postmaster.pid

Looking inside the pg_log directory, it looks like this:

Rajs-MacBook-Pro:pg_log rajverma$ pwd
/usr/local/var/postgres/data/pg_log
Rajs-MacBook-Pro:pg_log rajverma$ ll
total 8
-rw-------  1 rajverma  admin  188 Apr  7 01:56 postgresql-2019-04-07_015656.log

This means there is a file created with the format we specified in the log_filename:

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

If you want to change it and have the name of the day just mention %a instead of %d.

Thanks for coming and reading the article. Feel free to post your questions if any and I will be more than happy to answer those for you. Happy learning!!