Environment 

Aqua CSP. Specifically related to the aqua-db component. 


Deployment Task 

This article provides two ways to activate PostgreSQL 9.5 Aqua DB (aqua-db) logging. It was tested on a containerized instance of aqua-db Docker Only Aqua Deployment 


Warning:  Enabling extra logging, especially if PostgreSQL is set to log every statement, will cause a much higher disk space consumption on the host Keep in mind that when following the “To stderr” step, and you do not set it differently, the Docker logging drivers (5*are left as the default.  


Deployment Steps 

To stderr 


This method provides a quick way of enabling PostgreSQL statements logging. (4*) 

1. Enter the aqua-db container 

   [sudo] docker container exec -it aqua-db /bin/bash 

2. Change the user to postgres 

   su – postgres 

3. Make a backup copy of postgresql.conf 

   cp data/postgresql.conf data/postgresql.conf.bak 

4. Edit the PostgreSQL configuration file 

   vi data/postgresql.conf 

  

At the end of the file, append the following lines and change the log_min_duration_statement so it addresses your needs. (1*) 

  

# Aqua Settings 

#log_destination = 'stderr' 

#logging_collector = on # Requires DB Restart 

#log_directory = 'pg_log' # Only when log_collector is on 

#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Only when log_collector is on 

#log_rotation_age = 1d # Only when log_collector is on 

log_min_duration_statement = 1000 # In milliseconds e.g. 1000ms = 1s 

 

5. Reload postgresql 

   /usr/local/bin/pg_ctl reload -D /var/lib/postgresql/data/ 

6. Exit the container and check the logs 

   [sudo ]docker container logs -f aqua-db 

7. Stop log collection [2*] by entering the aqua-db container (step 1), comment-out the log_min_duration_statement line and reloading PostgreSQL (step 5). 

   #log_min_duration_statement = 1000 # In milliseconds e.g. 1000ms = 1s 

 

To log file 

This method will retain the log files inside the container as the per the rotation policy. It is useful if you need to capture logs entries for a longer period of time. Since this option requires a Database restart, it might not be possible, or easy, to apply it in a running production environment without proper planning. 


1. Enter the aqua-db container 

   [sudo] docker container exec -it aqua-db /bin/bash 


2. Change user to postgres 

   su – postgres 


3. Make a backup copy of postgresql.conf 

   cp data/postgresql.conf data/postgresql.conf.bak 


4. Edit the PostgreSQL configuration file 

   vi data/postgresql.conf 

  

At the end of the file, append the following lines and change the log_min_duration_statement so it addresses your needs. 

  

# Aqua Settings 

log_destination = 'stderr' 

logging_collector = on # Requires DB Restart 

log_directory = 'pg_log' # Only when log_collector is on 

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Only when log_collector is on 

log_rotation_age = 1d # Only when log_collector is on 

log_min_duration_statement = 1000 # In milliseconds e.g. 1000ms = 1s 

  

5. Restart the aqua-db container [3*] 

   [sudo] docker container restart aqua-db 


6. Enter the aqua-db container 

   [sudo] docker container exec -it aqua-db /bin/bash 


7. Check the log (your log will have a different timestamp than the example below) 

   cd /var/lib/postgresql/data/pg_log/ 

   less postgresql-2019-10-23_123430.log 

   or 

   tail -f postgresql-2019-10-23_123430.log 

  It is also possible to copy the log file from the container to the host for further analysis and/or for reference at a later time. 

  [sudo] docker cp aqua-db://var/lib/postgresql/data/pg_log/postgresql-2019-11-20_155903.log /tmp/ 

  n.b. files inside the /tmp directory will be deleted at server re-boot. If you need to keep the log permanently, use another locationsuch as, for example, in your /home/my-home-directory.  


8. Stop log collection by commenting-out the log_destination, the log_min_duration_statement lines. 

   #log_destination = 'stderr' 
   #log_min_duration_statement = 1000 # In milliseconds e.g. 1000ms = 1s 


9. Reload Postgres 

/usr/local/bin/pg_ctl reload -D /var/lib/postgresql/data/ 


At this point, reloading Postgres should be adequate to stop the log collection. This will give you the option to collect the logs to file again if necessary. 


If you want, you can completely reverse to the default comment-out all the lines under Aqua Settings in the postgresql.conf file and re-start the aqua-db container. 


Related information 

[2*] You can also stop logging by commenting-out (pre-append with an #) the log_destination and by setting the log_min_duration_statement = -1. 

If you need to, you can also set log_min_duration_statement = 0. 
However, this action will result in a very verbose output. 


[3*] Executing /usr/local/bin/pg_ctl restart -D /var/lib/postgresql/data/ will cause the container to stop and [Exit 0]. 


[4*] Although the container will log PostgreSQL standard errors, e.g., a failed INSERT for an already existing user, it does not log successful statements without the log_min_duration_statement set to catch them, hence missing some potentially important insight. 

To find out the aqua-db Docker container’s log run: 

[sudo] docker inspect --format='{{.LogPath}}' aqua-db