How to back up PostgreSQL on Windows automatically

The reliable way to back up PostgreSQL on Windows takes three parts. pg_dump makes the backup file, a small PowerShell script handles naming, logging and deleting old copies, and Windows Task Scheduler runs that script every night. This guide builds all three, then shows how to prove the backups actually restore. Skip that last step and you don't really have backups.

It works for a PostgreSQL server on the same PC, one elsewhere on your network, or a hosted one (Supabase, Neon, Amazon RDS, Azure and so on). Only the connection details change. The provider guides cover the quirks of each.

What you need

• The PostgreSQL client tools: pg_dump to make backups and pg_restore to read them back. You don't need a PostgreSQL server on your PC, just the tools. See how to install the PostgreSQL command-line tools on Windows.
• A pg_dump at least as new as your server. pg_dump can back up servers older than itself (back to 9.2) but refuses newer ones with a server version mismatch. If your server runs PostgreSQL 17, use pg_dump 17 or 18.
• A login that can read everything in the database: usually the database owner or the admin user your host gave you.
• A folder for the backups, ideally on a different disk from the database, and a copy somewhere off the machine (more on that below).

Step 1: Make one backup by hand

Before automating anything, run a backup yourself so you know the connection works. Open PowerShell and run (adjust the version folder, host, user and database):

& 'C:\Program Files\PostgreSQL\18\bin\pg_dump.exe' -h localhost -p 5432 -U postgres -F c -f "$HOME\Desktop\shop-test.dump" shop

pg_dump asks for the password, then writes shop-test.dump. The options that matter:

• -F c writes the custom format: compressed, and restorable table by table with pg_restore. It's the right default for backups (custom vs. plain SQL).
• -f names the output file. Don't use > redirection in Windows PowerShell 5.1: it re-encodes the output and corrupts a binary dump.
• The last argument is the database name. pg_dump backs up one database per run.

If it fails, the message is usually clear: wrong password, could not connect (firewall, wrong host or port), or SSL connection is required (add $env:PGSSLMODE = 'require' first; most hosted databases need it).

Step 2: Store the password where pg_dump can find it

A scheduled task can't type a password, and putting one on the command line exposes it to anything that can list processes. PostgreSQL's answer is a password file. Create a folder only you, Administrators and the system account can read, and put the file in it:

New-Item -ItemType Directory -Force 'C:\ProgramData\pg-backup' | Out-Null
Set-Content -Path 'C:\ProgramData\pg-backup\pgpass.conf' -Encoding Ascii `
    -Value 'localhost:5432:shop:postgres:YOUR_PASSWORD'
icacls 'C:\ProgramData\pg-backup' /inheritance:r /grant:r '*S-1-5-18:(OI)(CI)F' '*S-1-5-32-544:(OI)(CI)F' "${env:USERNAME}:(OI)(CI)F"

Each line is host:port:database:username:password; * matches anything. -Encoding Ascii matters: Windows PowerShell's UTF-8 adds a byte-order mark that pg_dump reads as part of the host name, and the line silently never matches. The icacls line removes inherited permissions and grants access to SYSTEM, Administrators and you, by security ID so it works on any Windows language. The details, and what to do with a : in your password, are in how pg_dump finds your password on Windows.

Step 3: The backup script

Save this as C:\ProgramData\pg-backup\Backup-Postgres.ps1 and change the six settings at the top. It finds the newest pg_dump, writes a timestamped backup, logs the result, deletes half-written files when something fails, and removes this database's backups older than $KeepDays days:

# Backup-Postgres.ps1: back up one PostgreSQL database with pg_dump,
# keep $KeepDays days of backups, and log every run.

$Database  = 'shop'
$PgHost    = 'localhost'
$Port      = 5432
$User      = 'postgres'
$BackupDir = 'D:\PostgresBackups'
$KeepDays  = 14

# Password and SSL come from the environment, never the command line.
$env:PGPASSFILE = 'C:\ProgramData\pg-backup\pgpass.conf'
$env:PGSSLMODE  = 'prefer'   # 'require' for most hosted databases

$LogFile = Join-Path $BackupDir 'backup-log.txt'
function Write-Log([string]$Message) {
    $line = '{0:yyyy-MM-dd HH:mm:ss}  {1}' -f (Get-Date), $Message
    Add-Content -Path $LogFile -Value $line
    Write-Output $line
}

New-Item -ItemType Directory -Force -Path $BackupDir | Out-Null

# The newest pg_dump under Program Files\PostgreSQL, else the one on PATH.
$PgDump = Get-ChildItem "$env:ProgramFiles\PostgreSQL\*\bin\pg_dump.exe" -ErrorAction SilentlyContinue |
    Sort-Object { try { [version]$_.VersionInfo.ProductVersion } catch { [version]'0.0' } } -Descending |
    Select-Object -First 1 -ExpandProperty FullName
if (-not $PgDump) { $PgDump = (Get-Command pg_dump.exe -ErrorAction SilentlyContinue).Source }
if (-not $PgDump) { Write-Log 'FAILED: pg_dump.exe not found'; exit 2 }

$File = Join-Path $BackupDir ('{0}-{1:yyyyMMdd-HHmmss}.dump' -f $Database, (Get-Date))
Write-Log "Backing up $Database from ${PgHost}:$Port with $PgDump"
$output = & $PgDump -h $PgHost -p $Port -U $User -w -F c -f $File $Database 2>&1
$exitCode = $LASTEXITCODE
$output | ForEach-Object { Write-Log "  $_" }

if ($exitCode -ne 0) {
    Remove-Item $File -ErrorAction SilentlyContinue
    Write-Log "FAILED: pg_dump exited with code $exitCode"
    exit 1
}
Write-Log ('OK: {0} ({1:N1} MB)' -f $File, ((Get-Item $File).Length / 1MB))

# Delete this database's backups older than $KeepDays days (only files this script named).
$pattern = '^' + [regex]::Escape($Database) + '-\d{8}-\d{6}\.dump$'
Get-ChildItem $BackupDir -File |
    Where-Object { $_.Name -match $pattern -and $_.LastWriteTime -lt (Get-Date).AddDays(-$KeepDays) } |
    ForEach-Object { Remove-Item $_.FullName; Write-Log "Deleted old backup $($_.Name)" }
exit 0

A few deliberate choices:

• -w (no password prompt) makes pg_dump fail immediately instead of waiting forever for a password nobody will type.
• Old files are only deleted after a successful backup, so a broken script never prunes your last good copy.
• The name pattern means the cleanup never touches another database's files in the same folder.
• exit 1 on failure shows up as the task's "Last Run Result" in Task Scheduler.

Run it once from PowerShell (& 'C:\ProgramData\pg-backup\Backup-Postgres.ps1') and check the log. You can also generate this script with your own settings instead of editing it.

Step 4: Schedule it with Task Scheduler

Run this in PowerShell as Administrator. It creates a task that runs every night at 02:00 as the SYSTEM account, so it runs even when nobody is signed in:

$action    = New-ScheduledTaskAction -Execute 'powershell.exe' `
    -Argument '-NoProfile -ExecutionPolicy Bypass -File "C:\ProgramData\pg-backup\Backup-Postgres.ps1"'
$trigger   = New-ScheduledTaskTrigger -Daily -At '02:00'
$principal = New-ScheduledTaskPrincipal -UserId 'SYSTEM' -LogonType ServiceAccount -RunLevel Highest
$settings  = New-ScheduledTaskSettingsSet -StartWhenAvailable -ExecutionTimeLimit (New-TimeSpan -Hours 6)
Register-ScheduledTask -TaskName 'PostgreSQL backup (shop)' -Action $action -Trigger $trigger `
    -Principal $principal -Settings $settings -Force

-StartWhenAvailable is important on laptops and PCs that sleep: if the PC was off at 02:00, the backup runs as soon as it's back instead of skipping a day. Because the task runs as SYSTEM, the script sets PGPASSFILE explicitly. SYSTEM has its own profile and would never find a password file in yours.

Test it right away:

Start-ScheduledTask -TaskName 'PostgreSQL backup (shop)'
Start-Sleep -Seconds 20
Get-ScheduledTaskInfo -TaskName 'PostgreSQL backup (shop)' | Select-Object LastRunTime, LastTaskResult

LastTaskResult is 0 when the backup succeeded and 1 when pg_dump failed; the log says why.

Step 5: Prove the backup restores

A backup you've never restored is a hope, not a backup. Restore into a scratch database and look at the data:

$bin = 'C:\Program Files\PostgreSQL\18\bin'
& "$bin\createdb.exe" -h localhost -U postgres shop_restore_test
& "$bin\pg_restore.exe" -h localhost -U postgres -d shop_restore_test --no-owner --no-privileges 'D:\PostgresBackups\shop-20261014-020000.dump'
& "$bin\psql.exe" -h localhost -U postgres -d shop_restore_test -c 'SELECT count(*) FROM orders;'

Compare a few row counts with the live database, then drop the test database. Do this after setting up and again every few months. Restoring a .dump file on Windows covers restoring over an existing database and plain .sql files.

Step 6: Decide how many copies to keep

Fourteen daily files is a sensible start, but think about when you'd notice a problem. If someone deletes a table and nobody notices for three weeks, fourteen days of backups won't help. A common answer is grandfather-father-son retention: the last 7 daily, 4 weekly and 12 monthly copies. That covers a year in 23 files. Backup retention explained has a script for it.

Step 7: Keep a copy somewhere else

A backup on the same disk as the database dies with that disk. The cheapest off-site copy on Windows is pointing $BackupDir at a folder that OneDrive, Dropbox or Google Drive already syncs, or adding a line that copies each new file to a network share. Read off-site PostgreSQL backups with OneDrive first: synced folders and partially written files need care, and backups contain your data in readable form.

What this setup doesn't cover

• Nobody is told when it fails. The log and LastTaskResult record failures, but you have to look. Add a weekly reminder, or have the script send an email on failure.
• Roles and passwords. pg_dump saves one database: tables, data, functions and permissions, but not the server's login roles. If you'd rebuild the whole server from backups, also run pg_dumpall --globals-only occasionally.
• Point-in-time recovery. A nightly dump can take you back to last night, not to 14:32 yesterday. For that you need WAL archiving (pgBackRest, Barman) or your host's PITR feature. For most small and medium databases, nightly (or hourly) dumps are the right trade-off.
• Very large databases. Past a few hundred gigabytes, dumps get slow; physical backups suit better.

The shortcut: what PostgresBackup does instead

Everything above is a fine setup that you now own and maintain. PostgresBackup is the same idea packaged as a Windows app:

Clones, restores and manual backups are free. Scheduled backups are free for 14 days, then $9.99 once (launch price; $24 from January 1). The files are ordinary pg_dump custom-format dumps either way, so you can always restore them with the commands on this page.

Common questions

Can I back up all databases on a server at once?

pg_dumpall writes every database into one plain SQL file, but it's slow to restore and all-or-nothing. Most people run pg_dump once per database (a loop in the script, or one task per database) plus pg_dumpall --globals-only for roles.

Does pg_dump lock the database?

No. It reads a consistent snapshot while your application keeps working. It does block schema changes (ALTER TABLE, DROP) on the tables it's reading until it finishes.

How long does a backup take?

Roughly as long as reading the data once. A few GB usually takes a few minutes. Custom format compresses as it writes; -Z 1 trades a slightly larger file for a much faster dump.