Secure vRealize Automation PostgreSQL database backups

So a few weeks back i wrote an article about how you can backup your Realize Automation (vRA) formerly known as vCloud Automation Center (vCAC) embedded PostgreSQL database. You can read the blog post here.
I have received a few questions regarding the blog post and most of they has been around securing the PostgreSQL database backup to a different location e.g. a backup system using. Since it is a virtual appliance (VA) running the PostgreSQL database it is usually not a good idea/recommended/supported to install third-party software such as a backup agent.

So what i normally do is fetching the vRA VA PostgreSQL database backups to one of the vRA IaaS servers which are Windows operating system based VMs where you can install a backup agent if the customer requires.

Below is the script i used during one of last years vRA/vCAC implementations and this customer used a backup agent in the VM that conducted daily differential backups. The pscp.exe file is required for this script and the steps included in the backup script are:

1. Remove old local backups on the Windows VM. Make sure you change the path to where you store the backups on the Windows VM.
2. Echo log file start message, date & time to the backup script log file. Make sure you change the path to the log file.
3. Get the PostgreSQL backups located on the vRA VA. Make sure you change the following parameters:
   1. User name
   2. User password
   3. vRA VA FQDN
   4. Directory where the PostgreSQL backups are located
   5. Path to where you store the pscp.exe file on the Windows VM.
4. Echo date, time and end message to the log fil.

echo off
REM ===============================
REM Author: Magnus Andersson
REM VCDX56
REM Get the vRA PostgreSQL database daily backup and keep 7 backups
REM
REM Delete old backups
del d:\vRA-VA-PostgreSQL-backups\PostgreSQL_DB_Backup*.*
REM
set logfile=d:\vRA-VA-PostgreSQL-backups\get-backup.log
echo “”Get vRA PostgreSQL backups starting”” >> %logfile%
date /t >> %logfile%
time /t >> %logfile%
REM
REM Get vRA PostgreSQL backups from vRA VA VM
cd /d d:\vRA-VA-PostgreSQL-backups\
c:\vRA-VA-PostgreSQL-backups\pscp.exe -pw password user@vravm.domain.com:/root/postgresql_backup/PostgreSQL*.sql . >> %logfile%
REM
date /t >> %logfile%
time /t >> %logfile%
echo “Get vRA PostgreSQL backups finished”” >> %logfile%”

As always, there are a lot of different ways to do the same thing but this has worked fine for me at least.