This article describes how to Clean-up Postgres pg_xlog in Versa-Director.

If we see the disk utilization is high or POSTGRE service stopped.

admin@Versa-Director:~$ vsh status
[sudo] password for admin:
VNMS:NCS[4.7.3.3] is RUNNING
VNMS:SPACKMGR is RUNNING
VNMS:POSTGRE[9.5.17] is NOT RUNNING
VNMS:SPRING-BOOT is RUNNING
VNMS:REDIS[5.0.5] is RUNNING
VNMS:TOMCAT[Apache Tomcat/9.0.37] is RUNNING
admin@Versa-Director:~$


check the below output from the Director shell.

admin@Versa-Director:~$  sudo du -ch /var | grep G

and check if /var/lib/postgresql director is consuming high disk or above 1.6GB, if yes use the below steps to clean up the archiving from postgres user.


  1. Become postgres user by running the command ‘sudo su – postgres’
 
  1. Execute the below command. It will display a file name.
 
postgres@Versa-Director:~$ /usr/lib/postgresql/9.5/bin/pg_controldata /var/lib/postgresql/9.5/main | grep "REDO WAL file"
 
Latest checkpoint's REDO WAL file:    000000010000000000000012
 
  1. Execute following command with above file name
pg_archivecleanup -d /var/lib/postgresql/9.5/main/pg_xlog 000000010000000000000012

Once the above steps are done, please check for the disk space, which should have normalized.

If the number of files is exceeding 100 under /var/lib/postgresql/9.5/main/pg_xlog/ directory, then we can basically disable archiving altogether.

 

Below are the steps to disable archiving altogether:

In /etc/postgresql/9.5/main/postgresql.streaming.conf and /opt/versa/vnms/etc/conf/postgre/postgresql.streaming.conf please modify the following lines.

archive_mode = on >>>>>>>> existing entry (line 35)

archive_mode = off >>>>>>>> modified entry (line 35)

archive_command = '/bin/true' >>>>>>>> existing entry (line 41)

archive_command = '' >>>>>>>> modified entry (line 41)


NOTE: [SERVICE RESTART is required, post the above changes ].


The issue is due to the below reason-

PostgreSQL basically writes all transactions to a log file called WAL (write-ahead log) and the pg_xlog directory contains these WAL files.


For replication/recovery purposes we can make use of WAL archiving, through which we can set up default checkpoints in postgres so that it can be easily restored to some previous state. However, since we have an external backup mechanism and we use streaming (slot-based) replication, we don’t rely on this WAL archiving. So, we have left the default setting for the number of archive files, which is 100 and each archive can be at a max of 16 MB. So, at no point should the total size exceed 1.6 GB [Total file size under pg_xlog].


these kinds of values are the checkpoints- 000000030000001300000016 


Whenever a transaction has been made from a database (Containing IP's, templates), it creates a WAL under pg_xlog which causes this issue.


NOTE: This is the known issue with bug#62034, which is addressed in the 21.1.2 release onwards