This document guides you on how to back up a PostgreSQL standalone database hosted on VMware using the Enterprise Workloads data protection solution. You can leverage VMware Tools utility to run custom pre-post backup scripts, ensuring database consistent snapshots.
📝 Note
Ensure VMware Tools are installed on the PostgreSQL Server VM.
Procedure
Create the backupScripts.d folder under the /etc/vmware-tools on the PostgreSQL Server VM. For more information, refer to the VMware KB article.
SSH into the PostgreSQL Server VM as a non-root sudo user.
Run the following command:
sudo mkdir /etc/vmware-tools/backupScripts.d
Create pre and post script files in the /etc/vmware-tools/backupScripts.d folder and you must name them starting with a number to ensure sequential execution of IO freeze and IO thaw tasks by VMware tools, where lower number has higher priority during script execution. In our example, the 10-pre-freeze-script.sh script will be executed prior to the 20-post-thaw-script.sh script.
Pre-backup script: 10-pre-freeze-script.sh
Post-backup script: 20-post-thaw-script.sh
Ensure scripts are executable with appropriate privileges.
SSH into the PostgreSQL Server VM.
Run the following commands:
sudo chmod +x /etc/vmware-tools/backupScripts.d/10-pre-freeze-script.sh
sudo chmod +x /etc/vmware-tools/backupScripts.d/20-post-thaw-script.sh
sudo chown root:root /etc/vmware-tools/backupScripts.d/10-pre-freeze-script.sh
sudo chown root:root /etc/vmware-tools/backupScripts.d/20-post-thaw-script.sh
Deploy a backup proxy to back up your VM.
Download the VMware Proxy Deployer on your local system and deploy the backup proxy using steps listed in Deploy VMware proxy using VMware Proxy Deployer.
Configure VM for backup by creating a backup policy on the Enterprise Workloads Management Console.
Enable Changed Block Tracking (CBT) and VMware tools quiescing in the backup policy configuration.
Perform a manual VM backup on the Enterprise Workloads Management Console for a quick validation.
Sample Pre-script: 10-pre-freeze-script.sh
#!/bin/sh
# set log directory
log="/var/log/vpostgres_backup.log"
# set and log start date
today=`date +%Y\/%m\/%d\ %H:%M:%S`
echo "${today}: Start of creation consistent state" >> ${log}
# execute freeze command.
# This command can be modified as per the database command
cmd="echo \"SELECT pg_start_backup('${today}', true);\" | sudo -i -u postgres psql >> ${log} 2>&1"
eval ${cmd}
# set and log end date
today=`date +%Y\/%m\/%d\ %H:%M:%S`
echo "${today}: Finished freeze script" >> ${log}
Sample Post-script: 20-post-thaw-script.sh
#!/bin/sh
echo "This section is executed when the Snapshot is removed"
log="/var/log/vpostgres_backup.log"
# set and log start date
today=`date +%Y\/%m\/%d\ %H:%M:%S`
echo "${today}: Release of backup" >> ${log}
# execute release command
cmd="echo \"SELECT pg_stop_backup();\" | sudo -i -u postgres psql >> ${log} 2>&1"
eval ${cmd}
# set and log end date
today=`date +%Y\/%m\/%d\ %H:%M:%S`
echo "${today}: Finished thaw script" >> ${log}