Management of wal_archive Directory in pgsql
In PostgreSQL, the Write-Ahead Log (WAL) files are critical for ensuring data integrity and durability. However, they can also take up a lot of disk space, especially if your database is large or your server is busy. If your wal_archive files are taking up too much space and causing your server to run out of disk space, you may delete that folder manually after taking a proper backup of application or can use a script to clear out old wal_archive files.
The wal_archive folder will be cleaned up once a backup is taken successfully. This feature had a framework issue, which is fixed in build 14202 (Ref Issue Id : SD-109229).
In this article, we will introduce a script that clears out wal_archive files that
are older than a particular date after taking a backup thereby solving the above problem
Working of script
- As wal_archive folder used to retrieve data if there is no backup is found. Hence at the first step to prevent data loss, we are triggering the manual full backup script. It deletes the wal_archive files, only if two new backup files are created after the script invocation time.
Warning
- Do not use this script when the setup has inconsistencies in the database, say for example a upgrade failed setup. Use this script when the application is running successfully without any inconsistencies.
How to Use?
- Download the attached zip, extract the scripts and place the file directly in <servicedeskplus_home>\bin directory.
- Invoke the script file with priviliges to read/write all the directories of ServiceDeskPlus. If possible with administrator privileges.
- "bash <script_name>.sh" for linux
- "<script_name>.bat" for windows
After invoking the script, check whether the size of your wal_archive is reduced.
This script will usually clears the wal_archive files older than 30 days. If you want to modify that time, change the value as number of days to "DAYS_TO_KEEP" in that script.
New to ADSelfService Plus?
Related Articles
How to connect PGSQL from a remote computer?
To remotely access a PostgreSQL database, you must set the two main PostgreSQL configuration files: postgresql.conf pg_hba.conf First of all, configure PostgreSQL service to listen on port 65432 on all network interfaces in Windows 7 machine: open ...How to proactively handle problem management in your IT environment.
This post describes the use of a python script to analyze the nature of the requests and alarms the technicians about frequently raised requests' category, and sub-category. This may help the technicians to proactively start their investigation in ...How to change the database from MySQL/PostgreSQL to MSSQL
The steps mentioned below are applicable only if you migrate the DB from PgSQL to MSSQL. This means that the application server is going to remain the same. The data alone is going to be transferred to MSSQL DB from the existing PgSQL DB. For ...Unable to Connect the bundled PGSQL from outside the server after upgrading to 14300
Inorder to connect our bundled postgres from External machine, we need to configure the postgres in such a way that is accessible from outside the server. By default, it will be accessible only within the server. Such configurations are to be made in ...Upgrade Failure due to the presence of CtrlZ Failure
This issue (SD-118352) in fixed in version 14710. Post this release, these characters are handled in the migration itself. If you are planning to migrate from version less than 14610 to 14610, then you can directly migrate to 14710, to handle this ...