PostgreSQL Backup Software Tool from Bacula Systems

Simplify and speed up the PostgreSQL backup with Bacula. 

This automated PostgreSQL backup tool makes the process as fast and simple as the PostgreSQL database backup administrator doesn’t need to learn about internals of DB backup and restore techniques or write complex scripts. The PostgreSQL backup software will automatically take care for you in backing up essential information such as configuration, users definition or tables. The tool supports both dump and Point In Time Recovery (PITR) backup and restore techniques.

Download TrialDownload PostgreSQL Whitepaper

PostgreSQL Backup Types

There are two main methods to backup PostgreSQL database with our tool – it’s either filesystem-level snapshots (physical) or SQL dumps (logical).

Physical Type

Filesystem-level backups (or physical backups) are essentially just snapshots of all the files in the database. But it’s not as easy as it might seem because the files inside a database are usually going through constant rewrites and changes. PostgreSQL database backup is about two key methods: continuity in archiving and point-in-time recovery. Both of those are meant to complement each other – that’s why it’s important to know how they both work.

For consistency’s sake, backups need to have some way of knowing for sure that the backup process is either copying the entire database or changing nothing and leaving the database as it is. That’s why PostgreSQL has a write-ahead logging technology – write-ahead log (WAL) segments are the exact ones that are being backed up during the ongoing archivation process. Information that’s stored in those files allows for both easier after-crash recovery and better data consistency.

It’s not uncommon for databases to undergo some changes in the process of a filesystem backup, but some of those changes might damage some parts of a backup or render it irreparable as a whole. To prevent such dire consequences PostgreSQL has a low-level API for the physical backup process. Using both “pg_start_backups()” and “pg_stop_backup()” before and after the process respectively makes sure that no dangerous changes are done to the database during the backup process. At the same time you’ll still need to generate all of the WAL segments between those two commands.

The common name for the filesystem-level snapshot like that (and all of the WAL segments needed to restore it, as well) has the name – a “base backup”.

Logical Type

Something partially different than a physical backup is the SQL dump (or logical backup). As the name suggests, this backup is all about using PostgreSQL backup commands to create database’s basic structure and then fill it to the brim with data. An SQL dump consistently represents a calm state of the database at any given moment (since the “dumping” process is almost the same as any other database session).

The process goes like this: the software goes through all of the tables available and fetches all of the rows. It’s not really complicated, but it is smart enough to keep with the order of things to restore everything as it was backed up, with all the connections and such.

Using SQL dumps means that you’ll have to get used to the data from various tables being spread over the timeline. It means that one table can have a timestamp A, and the other one might be done at the timestamp B. This is useful to keep in mind in case there are any kinds of rules in the database about how rows and tables should interact with each other. 

Download TrialDownload PostgreSQL Whitepaper

• Bacula Enterprise supports many operating systems where you can run PostgreSQL DB. See our most often Windows server backup and Linux backup.
• Need virtual machine backup for all hypervisors in your infrastructure? Take a look at our virtual machine backup.
• View Bacula Enterprise’s top backup and recovery software features.