Binary Replication Tools
From PostgreSQL wiki
This is a Work-In-Progress, started Dec 28, 2012.
Additions welcome, and Selena reserves the right to edit. :)
Compare binary replication tools for PostgreSQL for features and ease of use. This document should classify and differentiate binary replication tools for easier selection and fit to purpose.
|Tool||Documentation||License||Makes base backups||Makes base backups from replicas||Manages backups||Creates replicas||Monitors replication delay||Supports automated failover||Transport used||Source includes replication tests|
|pg_basebackup*||Postgres docs||PostgreSQL||Yes||Yes||No||Manual||No||No||PostgreSQL connection|
|OmniPITR||Intro||PostgreSQL||Yes||Yes||No||Manual||WAL archive delay||No||rsync / ssh|
|pghoard||Readme||Apache||Yes||Yes||Yes||Yes||No||No||AWS S3, Azure, Ceph, Google Cloud Storage||Yes|
|pg-rman||Readme||BSD||Yes||Yes||Yes||Manual||No||No||local / NFS mount|
|repmgr||Readme||GPLv3||No||No||No||Yes||Yes||Yes||rsync / ssh|
- pg_basebackup is included with a standard PostgreSQL installation.
Tool: name of the project that manages binary replication or WAL archiving
Documentation: Link to canonical documentation for the project. Several projects have broken links that show up as top results in Google.
License: License software is released under. So far, we only have open/free software projects listed. We could add commercial projects.
Makes base backups: Yes if the project supports creating binary archives, including the necessary WAL to restore a backed-up instance.
Makes base backups from replicas: Yes if the project supports creating binary archives and WAL using the PGDATA directory from a replica rather than the master database.
Manages backups: Yes if the project adds, removes and lists binary archives.
Creates replicas: Yes if the project automatically adds a recovery.conf (sets up replication) as part of restoring a base backup.
Monitors replication delay: Yes if the project supports monitoring of replication delay (WAL shipping or streaming replication).
Supports automated failover: Yes if the project has an option for detecting master failure and promoting a replica to master.
Transport used: Supported methods for file transfer for making backups or replicas
Summary of features:
- Creates base backups
- Uses SSH as transport for backup
- Configuration stored in file or command-line
- Restore is automatable for creating replicas, although not explictly supported
- Had a dependency problem with 9.1 on Ubuntu Precise, so installed from source
- Missing dep for argcomplete, noted in README in demo repo
- written in Python
Summary of Features:
- Creating PITR backups from Master or Slave
- Restoring a PITR backup for DR
- Creating replicas (by untarring backups)
- Monitoring of replicas
- Supports 'pause removal' of WAL during a backup (nice!)
- PostgreSQL license
- No packaging, perl
- No documented support for streaming replication
- Uses '^' instead of '%' in custom logfile naming
- No configuration file option (instead of using long command-line options)
- Supported on all Linux, Solaris variants
Summary of features:
- Stores basebackups and WAL to cloud object stores (AWS S3, Azure, Ceph, Google Cloud)
- Restore existing basebackups and setup a new cluster with a recovery.conf pointing to another master database
- Create scheduled basebackups
- Can be used as archive_command to archive WALs in the object store
- Can be used as restore_command to restore WALs from the object store
- Basebackup and WAL compression using LZMA
- Optionally encrypts backups
- Supports PITR using timestamps, names and xids
- Written in Python, includes Debian and Fedora packaging scripts
- 'pghoard' daemon manages basebackups and cleans up WALs
- 'pghoard_archive_command' and 'pghoard_restore' access the locally running 'pghoard' daemon to store and restore files
Summary of features:
- Online backup and recovery, including backup from a replica
- Archive management and restore
- .ini configuration file
- Simple command-line options
- Written in C, install with 'make USE_PGXS=1'
- On Ubuntu/Debian, installs in non-default bin directory
- Commands are simple, manages WAL archive as well as backups
- Setting up new replicas/hot_standby with streaming replication (makes recovery.conf itself)
- Making base backups
- Failover (automated, or not, including redirecting replicas to connect to a new master after failover)
- Lag monitoring (repmgrd)
- A "witness" DB server for monitoring (typically on a replica)
- License: GPLv3
- Written in C
- Developed on Debian systems, so support for package is present. Ubuntu packages are available since Trusty (14.04).
- Otherwise, installs like a typical UNIX utility out of postgresql/contrib source tree (make USE_PGXS=1; make USE_PGXS=1 install)
- Detailed docs are in the README for installing on many Linux platforms
- Doesn't appear to be supported on Mac OS X