Automated Backups with PostgreSQL

Prerequisites:

• 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_basebackup.exe" -h localhost -p 5432 -U postgres -D "(DESIRED BACKUP DIRECTORY)\OASIS_%STAMP%_backup" -F p -X stream -P -v

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_basebackup.exe" -h localhost -p 5432 -U postgres -D "(DESIRED BACKUP DIRECTORY)\OASIS_backup" -F p -X stream -P -v

set "PGPASSWORD="

Note:  If you run this in PowerShell you can include a "&" at the beginning on the command line.

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.

TO RESTORE

1. Stop the postgres service
2. Rename the original postgres data folder to something else in my case PostgresData_Original
3. Name the newly copied Postgres data folder to what the original was 
4. Right click the new data folder and go to security. Here add the Network Service account and give it full control.
5. Restart the service
6. Restart PGAdmin and confirm you are able to connect