PostgreSQL Backup with Veeam

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.

PostgreSQL Backup with Veeam

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.

PostgreSQL Backup with Veeam

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”

PostgreSQL Backup with Veeam

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

PostgreSQL Backup with Veeam

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

PostgreSQL Backup with Veeam

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.

Restore PostgreSQL Command

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

Review Restored DB

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

 

 

About Florian Raack

Florian is a consultant and trainer working for SVA GmbH Germany. He has a strong focus in virtualization and backup infrastructures. VCP-DCV, VCP-DT, VMCE, VMCT, VMCA.

Check Also

vSphere Tagging Tool HTML5

Supercharge Excel with ChatGPT

Introduction and why I Supercharge Excel with ChatGPT When you read “Supercharge Excel with ChatGPT”, …

6 comments

  1. Hi, is it possible to backup and restore individual tables?

    • Hi, you could restore the database dump by using the Veeam file level explorer. If this task is done you can use native PostgreSQL tools to recover the database and then for sure you could grab individual tables out of it.
      But in general there is no dedicated Veeam Explorer available for PostgreSQL like it is for MS SQL or Oracle.

  2. ElsayedElmazoun

    Hi,

    Thanks for the great article, I have a question, would it be better if we perform the backup using veeam agent for Linux instead of image-level backup?

    The second question, how can we backup and restore postgres cluster using Veeam, let’s suppose the cluster contains 3nodes? Do I have to install agent on all machines? How can I perform the restore from the cluster?

    Thank you

  3. Hello,

    Is it possible to use this approach and then pull the backup file using powershell? I’m looking to automate some nightly restores of several databases but the documentation from veeam on exactly how to do this lacking. Appreciate any insight you may have!

    • Falko Banaszak

      I did not look into this to be honest, but in the meantime Veeam should be aware of PostgreSQL even more. The blog I provided on this topic is very old to be fair and Veeam really developed much around this. So sorry to disappoint you there !

Leave a Reply

Your email address will not be published. Required fields are marked *