Maintenance of PostgreSQL Database and Automation

Performing database maintenance regularly will help to improve the performance of your projects.

Maintenance can be performed either manually or automatically.

If you have the maintenance configured and the performance is not improving, it is recommended to restart the Visure Server Service once a week (it can be automated too).

Manually Performing Maintenance

From pgAdmin, right-click on your database and select Maintenance.

From the maintenance window, perform the following actions:

— Right-click on top of the database and select Maintenance.

— Select VACUUM, turn ANALYZE on, and click on OK

 

 

This should be completed in a couple of minutes.

Once finished, you are done with your database maintenance.

 

Automating database maintenance

To perform the maintenance automatically, you can use a script that will be run regularly at a given time.

1. Environment Variable

Before starting with the script, an environment variable is needed to perform the command from the script.

In order to configure the “Environment Variables”, the path of the Postgres installation is needed; this path usually is “C:\Program Files\PostgreSQL\16\bin” (without the quotes), make sure that your path is correct. Then type “Environment Variables” on the “Windows Search”:

Once the panel is open, almost in the bottom right corner there is an option called “Environment Variables”:

Inside that option are two sections, user variables and system variables. In the system variables, look for PATH and click on edit:

Now, click on New, paste the path of the Postgres installation, and click on OK:

Click on OK again for the next window, and test if the new path is working. The path can be tested by opening a Command Prompt or PowerShell, then typing “psql --version” (without the quotes). This will show a message with the version that you installed before. If the command works like the next example, the path is configured correctly.

 

2. Maintenance Script

Now that the environment variable is configured, the script can be executed. The script must be saved as a batch (.bat) file and can be done so in any text editor program.

Here is a script example: (Before using the script, change the values for all the set variables to fit the script to your environment.)

@echo off
setlocal

set "DB_NAME=VisureDB"
set "DB_USER=postgres"
set "DB_PASS=PASSWORD"
set "DB_HOST=localhost"
set "DB_PORT=5432"
set "DB_VERSION=17"


cd /d "C:\Program Files\PostgreSQL\%DB_VERSION%\bin" || (
    echo Failed to change directory to PostgreSQL bin.
    exit /b 1
)

echo Starting VACUUM ANALYZE on main VisureDB_Schema tables...

rem === IMPORTANT: keep the next line as ONE SINGLE LINE (no breaks) ===
psql -h "%DB_HOST%" -p "%DB_PORT%" -U "%DB_USER%" -d "%DB_NAME%" -c "VACUUM (ANALYZE);"

rem === Capture exit code from psql ===
set "ERR=%ERRORLEVEL%"

if not "%ERR%"=="0" goto :error

echo VACUUM_ANALYZE_completed successfully.
goto :eof

:error
echo VACUUM_ANALYZE_failed with errorlevel %ERR%.
exit /b %ERR%

:eof

set "DB_PASS="

endlocal

At the end of this article you can download this in a TXT file.

Parameters:

• DB_NAME: This is the name of the Postgres database (VisureDB as an example).
• DB_USER: This is the user who has rights to perform the actions on the database (postgres by default).
• DB_PASS: This is the password for the admin user (Postgres doesn't support some special characters in the password)*.
• DB_HOST: This is the server that contains the database (localhost by default).
• DB_PORT: This is the port that Postgres uses for communication (5432 by default).
• DB_VERSION: This is the version of the database; we recommend 16.

*: The passwords in Postgres sometimes don't work if they contain some special characters like +, -, &, $, %, etc. 

The script must be saved as a .bat file also needs to be saved in a folder where the task scheduler will reach it (next step).

3. Running the script automatically

I) Launch Windows Task Scheduler (Control Panel/Administrative Tools/Task Scheduler):

II) Create a new task and name it:

The checkbox called Hidden is recommended to avoid misinteraction when a user is using the server. It is recommended to select “Run whether user is logged on or not”.

III) Go to the Actions tab, click New, then Browse to the .bat script file we just created:

IV) Click OK to validate; the result should look like this:

V) From the Trigger tab, we will select how often the maintenance will be performed and at what time by clicking on New:

It is recommended a daily maintenance.

VI) Check the Task Scheduler Library folder to ensure the new task is there:

With that, the Automated Maintenance is done. It is always good to ensure that the task runs without issue, and the script by itself should run without issue.

If you experience any issue configuring this, please reach to our support team by emailing to [e-mail].