Backup script for PostgreSQL with zip support 2.0 [using PowerShell]

Version 2 of PostgreSQL backup script. It uses PowerShell and json file for database settings declaration.

This post was created in cooperation with Adrian Ligiewicz.


File: backup_pg.ps1
-------------------

#Global config (always uncommented)

$PG_BIN = "C:\Program Files\PostgreSQL\9.6\bin\pg_dump.exe"
$PG_HOST=$env:COMPUTERNAME
$PG_PORT=5432
$PG_PATH="C:\inetpub\temporary_backups"
$ZIP_BIN="C:\Program Files\7-Zip\7z.exe"
#----------------------------------------
#Database config for (uncomment if manual use)

#$PG_DATABASE="Your_Database"
#$PG_USER="Your_Username"
#$env:PGPASSWORD="Your_Password"
#-----------------------------------------
#Databases json file (comment if manual use)

$DataBases = (Get-Content -Raw -Path .\databases.json | ConvertFrom-Json)
#------------------------------------------
#Loop start (comment if manual use)

foreach ($Database in $Databases){
$PG_DATABASE=($Database).database
$PG_USER=($Database).user
$env:PGPASSWORD=($Database).password
#------------------------------------------
#Core functions (always uncommented)

$PG_FILENAME=$PG_PATH + "\" + $PG_DATABASE + "_postgres.sql"
$PG_BIN_ARGs="-h " + $PG_HOST + " -p " + $PG_PORT +  " -U " + $PG_USER + " " + $PG_DATABASE# + " > " + $PG_FILENAME
Start-Process $PG_BIN $PG_BIN_ARGs -NoNewWindow -Wait -RedirectStandardOutput $PG_FILENAME
$ZIP_BIN_ARGs="a " + $PG_FILENAME.TrimEnd("sql") + "zip" + " " + $PG_FILENAME
Start-Process $ZIP_BIN $ZIP_BIN_ARGs -Wait
Remove-Item $PG_FILENAME
#------------------------------------------
#Debug section

#Write-Host "Pass"
#$PG_DATABASE
#$PG_USER
#$env:PGPASSWORD
#------------------------------------------
#End of loop (comment if manual use)

}
#------------------------------------------
#Sample json (always commented)

#[
# {"user":"Konrad","password":"StronkPassword","database":"DBKonrad"},
#   {"user":"Adrian","password":"WekPass","database":"DoNotLook"},
#      {"user":"John","password":"Test","database":"TestDB"}
#] 


File: databases.json
--------------------

[
	{"user":"db_user1","password":"random_password","database":"database_1"},
  {"user":"db_user2","password":"random_password","database":"database_2"},
  {"user":"db_user3","password":"random_password","database":"database_3"}
]

Published by

Konrad Fedorczyk

Konrad Fedorczyk

I'm interested in programming and gamedev. I especially luv HTML5 and everything connected to web technologies.

Leave a Reply

Your email address will not be published. Required fields are marked *