pglogical
v2 - The next generation of logical replication for PostgreSQL is here!
pglogical is a logical replication system implemented entirely as a PostgreSQL extension. Fully integrated, it requires no triggers or external programs. This alternative to physical replication is a highly efficient method of replicating data using a publish/subscribe model for selective replication.
What’s new in pglogical 2?
The pglogical 2.2 release brings new features allowing it to be used for even more use-cases and also several bug fixes and behavior improvements listed below:
- PG 10 support
- Improved compatibility with pglogical 1.x provider
- Improved error reporting
- Row filtering on provider
- Column filtering on provider
- Delayed replication
- MS Windows support (source code only)
- Ability to convert physical standby to logical replication
- Can publish data from PostgreSQL to a Postgres-XL subscriber
- Greatly improved performance for replication of large INSERT/COPY transactions
- Improved memory management.
For a more comprehensive list, please see the release notes.
pglogical use cases
The diagrams below depict the functions database managers are able to perform with pglogical.
MIGRATIONS & UPGRADES
Migrate and upgrade PostgreSQL with almost zero downtime
AGGREGATE
Accumulate changes from sharded database servers into a Data Warehouse
SCALE OUT
Copy all or a selection of database tables to other nodes in a cluster
INTEGRATE
Feed database changes in real-time to other systems
Availability
(pglogical 2.2 released - March 20, 2018)
pglogical is available for PostgreSQL 9.4 - 10. It is available via 2ndQuadrant’s apt and yum repositories for Debian and Red Hat family (RHEL, CentOS, and Fedora). You can use standard apt and yum package managers for installing pglogical with your instance of PostgreSQL.
Detailed installation instructions are available here.
Release notes are available here.
You can also access pglogical's github repository.
pglogical is fully open source, released under the PostgreSQL licence with copyright novated to the PostgreSQL Development Group.
What benefits does pglogical provide?
- Synchronous replication
- Delayed replication
- Configurable conflict resolution
- Ability to convert physical standby to logical replication
- Can publish data from PostgreSQL to a Postgres-XL subscriber
- Sequences can be replicated
- No triggers means reduced write load on the Provider
- No re-execution of SQL means reduced overhead and latency for the Subscriber
- Subscriber isn’t in hot standby recovery, so you can use temp, unlogged, or normal tables
- No need to cancel queries to allow the replica to continue replay
- Subscriber can have different users & security, different indexes, different parameter settings
- Replicate just one database, or a subset of tables, known as Replication Sets
- Replicate across PostgreSQL versions or architectures, allowing low- or zero-downtime upgrades
- Multiple upstream servers into a single subscriber for change accumulation
How does pglogical work?
pglogical utilises the Logical Decoding features added by 2ndQuadrant (and available since PostgreSQL 9.4). pglogical works even faster with PostgreSQL 9.5 and newer, with low overhead on both provider and subscriber.
pglogical relies heavily on features introduced as part of BDR development, including:
- logical decoding
- replication slots
- static background workers
- replication origins
- commit timestamps
- logical WAL messages
Does pglogical replace Bi-Directional Replication (BDR)?
No. pglogical does not provide full multi-master replication features and consistent schema change support, as BDR does. pglogical incorporates features from BDR and lessons learned from BDR to produce a simpler and easier to use solution for one-way replication, usable by more people for a wide variety of use cases. BDR is designed first and foremost for n-way mesh multi-master, and this proved hard to adapt well to single-master one-way replication.
BDR development will continue for those that require full multi-master capabilities, reusing much of the code from pglogical.
Support for pglogical
pglogical has been developed by 2ndQuadrant, is open source and fully supported for 2ndQuadrant 24/7 Support customers. We also offer consulting services to help organisations with application development, conflict handler design, cluster design, deployment, feature development and performance analysis.
Need more help?
Click here for pglogical's documentation.
Want to know even more? Need some help to implement pglogical? As the developers we're probably the best people in the world to help you get up and running with pglogical. We have consultants available to provide assistance, plus our unique 24/7 Production Support subscription service also covers pglogical.
Complete the contact form below and we'll be in touch.
