The script can be download from TechNet Gallery or from GitHub Gist

Configure PostgreSQL server

postgres

Log off from Windows and log in as postgres , navigate to C:\Users\postgres\AppData\Roaming\ and create a folder named postgresql . Inside postgresql create a file named pgpass.conf with the following content:





localhost:5432:*:pgbackup:pgbackup-pass

The pg_basebackup tool will look for this file to fetch the password.

Backup-Postgres.ps1 and modify the following variables to match your configuration:



Openand modify the following variables to match your configuration:

# path settings $BackupRoot = 'C:\Database\Backup'; $BackupLabel = (Get-Date -Format 'yyyy-MM-dd_HHmmss'); # pg_basebackup settings $PgBackupExe = 'C:\Program Files\PostgreSQL\9.3\bin\pg_basebackup.exe'; $PgUser = 'pgbackup'; # purge settings $ExpireDate = (Get-Date).AddDays(-7);

Now it's time to schedule the backup, open Windows Task Scheduler and create a new task. Setup the task to run whatever the user is logged on or not with highest privileges, use the postgres user for this. Add a recursive trigger, I've set mine to repeat every day indefinitely. You should carefully chose the best time to start the backup and that's when the server is less used. You should specify in the settings tab the rule Do not start a new instance if the task is already running, this will prevent running multiple backups in parallel.

Go to the Actions tab and add a new action:





powershell -ExecutionPolicy Bypass -File "C:\Jobs\Backup-Postgres.ps1"

Restore cluster from base backup

In order to restore a base backup with multiple table spaces, you'll have to extract each table space archive to it's original path. Since Windows doesn't have native support for tar.gz you can use the 7zip command line.

With 7zip you can extract a tar.gz archive without storing the intermediate tar file, 7zip can write to stdout and read from stdin using the following command:





7z x "base.tar.gz" -so | 7z x -aoa -si -ttar -o "C:\Program Files\PostgreSQL\9.3\data"

data

recovery.conf

data

postgres

standby_mode = 'on' primary_conninfo = 'host=localhost port=5432 user=postgres password=PG-PASS'

pg_hba.conf

recovery.conf

recovery.done

pg_hba.conf

Create a local Windows user named. It doesn't need to have administrator rights, but it should haveto the backup folder.1) Stop Postgres server2) Delete thefolder content and all table spaces content (if you have enough free space, you should make a backup copy of the current data and table spaces)3) Run the 7zip command and extract each archive to its corresponding folder4) Create afile infolder with the following content, specifying thepassword:5) Openfile and comment all existing rules, this will prevent external clients from accessing the server while in recovery.6) Start Postgres server. When Postgres starts it will process all WAL files and once recovery is finished thefile gets renamed to7) Restoreto its original state and restart Postgres.After getting used to the restore process you could automate it with PowerShell.