pglogical is the next generation replication system for PostgreSQL
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.
The diagrams below depict the functions database managers are able to perform with pglogical.
MIGRATIONS & UPGRADES
Migrate and upgrade PostgreSQL with almost zero downtime
Accumulate changes from sharded database servers into a Data Warehouse
Copy all or a selection of database tables to other nodes in a cluster
Feed database changes in real-time to other systems
(pglogical 1.2.2 released - January 4, 2017)
pglogical is available for PostgreSQL 9.4 - 9.6 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.
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. It has also been submitted to PostgreSQL core as a candidate for inclusion in PostgreSQL 10. 2ndQuadrant will provide and support pglogical until it is fully integrated within Postgres core.
Existing PostgreSQL users will find the efficiency of pglogical particularly exciting with preliminary internal testing demonstrating a 5x increase in transaction throughput (OLTP workloads using pgBench ) over other replication methods like slony and londiste3.
What benefits does pglogical provide?
- 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.6, 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
pglogical re-uses many elements from BDR, so it’s tried-and-tested technology; far from a rewrite.
Does pglogical replace bi-directional replication (BDR)?
No. pglogical does not provide multi-master replication, nor does it replicate sequence values or DDL, 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.
Where will pglogical go next?
pglogical is modular and will be extended to these future use cases:
- serve as base for updated BDR for full multi-master
- cascading logical replication, complex replication topologies
- data ingestion/transform via triggers on apply
- feed into star schema, cstore, etc
- row-level selective replication, e.g. “send data on just this customer to their server”
- streaming json, XML, etc, from output plugin
- use of output plugin by other projects as substitute for trigger-based data acquisition
- extensible, documented protocol suitable for consuming by multiple different clients
Support for pglogical
pglogical has been developed by 2ndQuadrant, is open source and fully supported for 2ndQuadrant 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.
Click here to look at answers to some of the most frequently asked questions.
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.