Overview

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

The following components are required: 

• pgAdmin tool
• TF_Backup-PostgreSQL-DB.ps1

Details

Download And Install 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 with 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 = "Triofox 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 .\TF_Backup-PostgreSQL-DB.ps1
 .\TF_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

Reference:

Manually Backup PostgreSQL using pgAdmin GUI 

Manually Restore PostgreSQL using pgAdmin GUI