Overview

The Centrestack database contains metadata of your Centrestack instance and log data. This database must be backed up regularly (at least daily backup). If you use PostgreSQL/All-in-One PostgreSQL database, you can use this PowerShell script to back up your Centrestack database.

The following components are required: 

• pgAdmin tool
• Backup PowerShell Script

Details

Download And Install the pgAdmin Tool

The pgAdmin tool is the leading Open Source GUI management tool for Postgres. You can use the most recent version. After you install the pgAdmin Tool, please make sure the runtime folder exists. 

i.e. C:\Program Files\pgAdmin 4\v6\runtime\

Prepare the PowerShell Backup Script

Please modify the backup script using your environment settings.

If you use default All-in-One PostgreSQL, you don't need to change those settings

$DBhost = "localhost"
$port = "5679"
$username = "postgres"
$role = "postgres"

$DBnamesarray = @('csmain')

#Please make sure you use the correct backup location

$dumpFilePath = "C:\\mypostgreSQL_backup\\csmain\\"

#Please make sure to use your SMTP server and email account information for Backup Notification
$SmtpServer = "<your SMTP server>"
$mailFrom = "<your FROM email>"
$mailFromPWD = ConvertTo-SecureString -String "<your email paswwword>" -AsPlainText -Force

$mailTo = "<your TO email address>"
$mailSubject = "Centrestack PostgreSQL Backup Notification"
$mailBody = "The backup log was atteched. "

# pg_dump path. Update below if you installed PGAdmin in a different path
cd "C:\Program Files\pgAdmin 4\v6\runtime\"

Execute the PowerShell Backup Script

Launch Powershell with "Run As Administrator" and run the following PowerShell commands

cd <your script folder>
Unblock-File .\CS_Backup-PostgreSQL-DB.ps1
 .\CS_Backup-PostgreSQL-DB.ps1

Please Go to your email account to verify the backup notification.

To Automate your backup task, you can create a Windows scheduler task and define your backup schedule.

Use PowerShell to Create Scheduled Tasks

Ref:

Manually Backup PostgreSQL using pgAdmin GUI 

Manually Restore PostgreSQL using pgAdmin GUI