PostgreSQL Backup with Veeam

Related Articles

Reflecting on 4 years as a Veeam Vanguard

7. March 2024

Supercharge Excel with ChatGPT

17. July 2023

Veeam vSphere Tagging Excel Tool

23. June 2023

Introduction

Recently, one of my customers had a request to perform a PostgreSQL backup with Veeam, consistently . From my experience, I have rarely had requests for this specific topic in the past. Most database administrators in the past had their own backup routines for PostgreSQL and wanted to stick with it. So usually regular dumps are written away via the native database tools.

In the current situation, however, the customer wanted to ensure a consistent backup of the database via Veeam. We discussed what options we have. Basically, Veeam has been offering PostgreSQL integration with the Agent for Linux for quite some time. But the problem is that we have to work with an agent in the virtual machine. Consequently, no SureBackup functionalities are available. Basically the agent also only creates a write-ahead checkpoint of the database. This makes a granular db restore difficult afterwards. In principle, it would be possible to restore the entire system each time. If only one database is affected by a failure this can be very cumbersome.

The Goal

Our goal was to keep a consistent dump of the PostgreSQL databases on the same system during the Veeam backup, so that it backed up during the image-level backup. For such a project, the pre- and post-thaw script routine in Veeam is ideally suited. This is easy to configure for any VM.

The Script

First, we looked at what the command for a PostgresSQL dump looks like and created a simple shell script on top of it.

sudo -u postgres /usr/pgsql-12/bin/pg_dumpall –exclude-database=”postgres” > /tmp/postgresqlbackup.sql

This command creates a dump of all databases into the tmp directory of the PostgreSQL system. The configuration database is excluded. To avoid that the storage usage on the PosgreSQL server grows too much we added a line to delete the previous dump. Additionally we added a loop of 20 seconds to ensure that the created dump is 100% included in the VMware snapshot. The final script looks like this:

rm -f /tmp/postgresqlbackup.sql
sudo -u postgres /usr/pgsql-12/bin/pg_dumpall –exclude-database=”postgres” > /tmp/postgresqlbackup.sql
sleep 20

This shell script was saved on the Veeam backup server und “D:\Veeam\Scripts\VM-name\pre_script_vm_name.sh”

The Doing

With the created script, only the backup job has to be configured now. Application processing must be activated for the PostgreSQL VM. Additionally, a Linux account with root privileges is mandatory for this VM. This account must be able to execute sudo without password entry.

Within the job configuration you just have to link to the specific script inside the application processing menu for this specific VM. In our case we pointed to the path on the VBR server “D:\Veeam\Scripts\VM-name\pre_script_vm_name.sh” where our script is stored.

After that, we were able to run the job and as you can see, the script executed successfully. It was just a small PostgreSQL instance so the actual runtime was about 1 second plus 20 seconds for the sleep time out.

The Restore of PostgreSQL Backup with Veeam

Now we have a consistent dump for the databases that align exactly to the point in time when the image level backup ran. To run a restore out of this dump we need to recover the database dump back to the production system by using Veeam file level recovery.

Inside the FLR explorer we now can see the created database dump named postgresqlbackup.sql. The next step is to restore this dump file to the original database system. We will use the keep option to get this file back with the suffix “Restored”

The restore completed successfully back to the original system in the /tmp/ folder

To simulate a restore operation we dropped the database “Test_BackupRestore” using PGadmin console.

With the database dump back in place we just need to run one specific PostgreSQL command to recover the database out of the dump file. For this operation, we need ssh access to our PostgreSQL server. Once you are logged in you can double check that the restored dump fill will show up in the /tmp folder.

With the following command, you are able to recover the databases by using the dump file.

sudo –u postgres /usr/pgsql-12/bin/psql –a –d postgres –f postgresqlbackup.sql.RESTORED-2021012813717

Note: The timestamp will definitively differ to your file name. So please make sure you are using the right file name.

After the restore completes you will see the database back online again in the PGadmin console.

The conclusion of PostgreSQL Backup with Veeam

I sincerely hope that this short article will help you to overcome the hurdle of backing up PostgreSQL databases with Veeam. If you have any other ideas on how to approach this topic, please feel free to leave feedback.

Florian