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 do I convert the database from MSSQL to PGSQL ?
Kindly plan to perform this process ONLY during Non Operational hours. You can do this process in the same server that you have SDP MSP installed. Step 1: Stop the application and Take a backup of the existing data in the application. Navigate to ...Steps to take PGSQL Dump Backup and Restore
PGSQL – DUMP – Backup Procedure : Login to the MSP application server and perform the below steps, 1. Start the application To take Postgres (Psql) Dump : 3. Open a command prompt and navigate into <ServiceDeskPlus-MSP>/pgsql/bin directory. 4. ...Unable to start the application due to Pgsql privilege issue
ISSUE: Application does not start via both services and command prompt. ERROR TRACE: Serverout: [16:26:36:443]|[05-10-2023]|[com.adventnet.db.adapter.postgres.PostgresDBAdapter]|[SEVERE]|[27]: Exception occurred while obtaining mode of the database| ...Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...