Automated Backups with PostgreSQL

Prerequisites:

• Requires Admin to allow PGAdmin and PG Agent to write to a folder
• Decide on if you want to have a single file that is updated each day (for you to copy to another location for preservation) or if you want a file with a date stamp (for you to clean up old copies periodically)

1. Create the backup command file

Create a file called "OASISPG_BACKUP.cmd" or “OASISPG_BACKUP.bat” in the "C:\Program Files\PostgreSQL" directory that contains the following. Make sure to update the (DESIRED BACKUP DIRECTORY) to reflect your desired backup directory. 

@echo off
setlocal
set "PGPASSWORD=oasispg"

for /f %%i in ('powershell -NoProfile -Command "Get-Date -Format yyyyMMdd_HHmmss"') do set "STAMP=%%i"

"C:\Program Files\PostgreSQL\18\bin\pg_dump.exe" -h "localhost" -p "6543" -U "backup" -F c -Z 6 -f "(DESIRED BACKUP DIRECTORY)\OASIS_%STAMP%.backup" -v "OASIS"

set "PGPASSWORD="

Note: If you prefer to have a single file that you copy to another location , then use this:

@echo off
setlocal
set "PGPASSWORD=oasispg"

"C:\Program Files\PostgreSQL\18\bin\pg_dump.exe" -h "localhost" -p "6543" -U "backup" -F c -Z 6 -f "(DESIRED BACKUP DIRECTORY)\OASIS.backup" -v "OASIS"

set "PGPASSWORD="

2. Run the batch file to test it.  

Confirm that it works properly and a backup file shows in the desired folder.

3. Create the scheduled task:

You have a couple of options.  

• Via pgAgent
  1. Create the Job in pgAgent and name it
  2. Make the Kind: Batch, On Error: Fail, Code: point it to your bat file
  3. Add the desired schedule
• Via Windows Scheduled Tasks
  1. From Powershell or a command prompt window and enter the following

schtasks /CREATE /SC DAILY /ST 20:00 /TN OASISPG_BACKUP /TR "C:\Program Files\PostgreSQL\OASISPG_BACKUP.cmd"

This will create a scheduled task that will run the OASISPG_BACKUP file every night at 8 pm.

2. Click the Windows button and type Windows Task Scheduler.  Then find the task created from the script above.  
3. Check the radio button for "Run whether user is logged on or not". This will require an administrative user for the customer to enter the admin password. 
4. On the Settings tab, check the box for "stop the task if it runs longer than" and set it to 4 hours. This may need further refinement but this is a good place to start.

Other Factors

Compression is controlled with the -Z command in the bat file.  Remove the -Z 6 if you want no compression.  You can also change the number from 1 - 9.  Higher compression results in a smaller backup file but takes longer to complete.  Lower (or no compression) results in a larger file and will take less time to complete.