Postgres Backup and Recovery Just Got a Whole Lot Easier

October 20, 2014

Postgres has transformed in recent years with advancing capabilities and new tools. While features and capabilities have encouraged more and larger enterprises to deploy Postgres for a wider range of workloads, the tools have helped DBAs manage and expand their databases. A remaining challenge for DBAs, however, was the complex process of setting up backup and recovery processes for Postgres.

Fortunately, if you’re a DBA or System Administrator working with Postgres, your life is about to get a lot easier. EnterpriseDB (EDB) has spent the last few weeks working with some customers and partners, like K.K Ashisuto in Japan, to review our new EDB Backup and Recovery Tool (BART), and I am excited to say we have released the first version of the tool as Generally Available earlier this week. EDB BART greatly simplifies what has until now been a time-consuming process and one that was overly dependent on the capabilities and experience of individuals. EDB BART supports both community PostgreSQL and EDB’s Postgres Plus Advanced Server.

EDB BART provides a system wide catalog and command line interface that makes the management of backup and recovery across local and remote Postgres servers much more manageable. Online physical backups via pg_basebackup eliminate downtime, support for tablespaces on different paths during restoration allow for more flexibility, and auto-compression and MD5 checksum verification of backups save on disk storage and ensure files are valid.

No More Incremental Backup 

In conjunction with transaction log archiving (wal_level=archive), DBAs will be able to “roll forward” from the point of last base backup. Users may ask “What about ‘incremental backup’? For databases that are smaller than a few hundred GB in size, Postgres’ point-in-time-recovery (PITR) features are usually more than sufficient.

EDB Database Architect and PostgreSQL Contributor and Committer Kevin Grittner has a great way of explaining the significance. As he puts it:, “If you are archiving WAL and set archive_timeout = 1h, you have all the recovery options that an hourly incremental backup gives you, and then some. Basically, incremental backup as described by most people would only have one advantage over this—the incremental backups would essentially be a summary of the WAL for each hour, taking less space at the cost of not being able to pick your recovery point at transaction granularity.”

We do recognize that for larger databases, an incremental backup facility may be useful. To address this, we are exploring a few options: one is implementing a file-based incremental backup technique in the BART tool, where we could manage the backup of database files that have changed since the last base backup; another is working with the PostgreSQL community to provide a new INCREMENTAL option in the core pg_basebackup utility. More details will become available in early 2015 as we continue design and choose a release vehicle.

Looking Ahead

“EDB BART has simplified very complicated processes and distinguishes itself in the market,” said one of our partners from K.K. Ashisuto in Japan. “The tool makes it very easy to run backups with our remote environments at the same time as our local databases. EDB has put a lot of thought into EDB BART and the roadmap.”

Going forward, BART will be supported by EDB’s world-class team and continually improved upon based on feedback from our strategic customers, RemoteDBA team and service consultants in the field. EDB has control over the development and release cycle, and we already have a well-defined roadmap for our next release, including commonly requested features such as:

  • Redundancy and recovery window retention management for backups
  • Archived WAL compression
  • Free space detection prior to backup / recovery
  • Better information and reporting for show & show-server commands, including display of disk utilization, number of archives currently managed, dates, beginning / ending WAL files, earliest recovery time & latest current recovery time.

EDB BART further reinforces EDB’s commitment to mission-critical Postgres deployments while meeting our users’ ever-evolving database needs. With more than 2,500 customers, EDB is well positioned to learn a great deal from Postgres users and gain deep insight into their needs and wishes. This interaction influences our product roadmap, and innovation in our new releases regularly features customer requests that benefit all. It’s further part of our commitment to being the single source for all Postgres tooling instead of a set of unrelated tools with inconsistent quality, release schedules, licenses, processes and support teams.

For more information on EDB tools, visit our site or contact us for more information.

Jason Davis is Director, Product Management at EnterpriseDB.

Share this