Automate PostgreSQL database backup

Minimal Examples

A minimal example on how to use pg_dump in a .bat script to automate PostgreSQL database backups on Windows operating system.

@echo off

SET PGPASSWORD='mypassword1234'

for /f "tokens=2 delims==" %%a in ('wmic OS Get localdatetime /value') do set "dt=%%a"
set "YY=%dt:~2,2%" & set "YYYY=%dt:~0,4%" & set "MM=%dt:~4,2%" & set "DD=%dt:~6,2%"
set "HH=%dt:~8,2%" & set "Min=%dt:~10,2%" & set "Sec=%dt:~12,2%"
set "fullstamp=%YYYY%%MM%%DD%%HH%%Min%%Sec%"

pg_dump -h localhost -U postgres -d MY_DATABASE -Fc > "C:\Backups\MY_DATABASE_%fullstamp%.backup"

Replace mypassword1234 with the value of postgres user password. It is recommended to use the highest privileged user to run backups.

The above .bat script can be configured to run periodically through Task Scheduler.