Q. How is logical replication different from streaming replication?
Streaming replication sends changes at the byte/block level whereas logical replication sends only the logical change, making it much more flexible. Simon Riggs talks about this in more detail in his blog Why Logical Replication?.
Q. How is pglogical licensed?
pglogical is released under the PostgreSQL licence with copyright novated to the PostgreSQL Development Group.
Q. How does pglogical work?
pglogical utilizes the Logical Decoding features added by 2ndQuadrant (and available since PostgreSQL 9.4).
- pglogical works on a per-database level, not whole server level like physical streaming replication
- One Provider may feed multiple Subscribers without incurring additional disk write overhead
- One Subscriber can merge changes from several origins and detect conflict between changes with automatic and configurable conflict resolution.
pglogical relies heavily on features introduced as part of BDR development, including:
- logical decoding
- replication slots
- background workers
- replication origins
- commit timestamps
Q. Can pglogical be used as a replacement of streaming replication?
Yes, pglogical can be used instead of streaming replication though not yet usable for failover.
Q. Is pglogical production ready?
Yes, pglogical is production ready.
Q. What are some of the supported use cases ?
- Upgrades between major versions (given following restrictions)
- Provider & subscriber nodes must run PostgreSQL 9.4+
- PostgreSQL 9.5+ is required for replication origin filtering and conflict detection.
- Full database replication
- Selective replication of sets of tables using replication sets
- Data gather/merge from multiple upstream servers
Q. What are the supported platforms?
pglogical is currently tested, released and supported for following platforms and distributions.
- Jessie (8.x)
- Xenial (16.04)
- Trusty (14.04)
- RHEL Family
- CentOS 6 & 7
- RedHat 6 & 7
- Fedora 22 & 23
Q. What PostgreSQL versions are supported?
pglogical supports PostgreSQL 9.4 and later.
Q. How can I install pglogical?
If you are running PostgreSQL 9.4 or higher on one of the supported platforms, you can configure 2ndQuadrant’s YUM and APT repositories to install pglogical for your instance of PostgreSQL. Detailed instructions are available here.
Q. Does pglogical need to be installed on all nodes?
Yes. pglogical needs to be installed on all nodes that need to participate in the replication as publisher or as subscriber.
Q. Does pglogical replicate DDL queries?
DDL statements can be handled by using the provided pglogical.replicate_ddl_command() function. The function does have a limitation that it will not handle CREATE INDEX with the CONCURRENTLY parameter.
Q. Can subscriber and publisher PostgreSQL versions be different?
Yes. pglogical fully supports cross version replication. For example, publisher node can be PostgreSQL 9.5 with subscribers being combination of 9.4 or greater.
Q. Does pglogical replace BDR?
No. BDR is designed first and foremost for n-way mesh multi-master. 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 development will continue for those that require full multi-master capabilities.
Q. Does pglogical support cascaded replication?
Subscribers can be configured as publishers as well thus cascaded replication can be achieved by forwarding/chaining (again no failover though).
Q. Does pglogical support multi-master replication?
No. pglogical does not provide multi-master replication, nor does it provide globally unique sequence values or transparent DDL replication, as BDR does.
Q. Can subscriber nodes support both read & write operations?
Yes. Subscriber isn’t in hot standby recovery, so you can use temp, unlogged, or normal tables.
Q. Is pglogical better compared with other replication technologies e.g. slony, londiste3?
Our preliminary internal testing demonstrates a 5x increase in transaction throughput (OLTP workloads using pgBench ) over other replication methods like slony and londiste3. Tomas gives a detailed analysis in his blog, On pglogical performance.
Q. Are there any additional software requirements for installing/setting up pglogical replication?
No. pglogical doesn’t need any additional softwares/libraries to install and function.
Q. Is pglogical completely open source?
Yes, pglogical is completely open source with active 2ndQuadrant development team backing it. Source tarballs can be found here.
Q. Is pglogical fully supported?
Yes. 2ndQuadrant completely supports setup and usage of the product as part of its professional and RDBA support services.
Q. What major benefits does pglogical provide over other replication solutions?
Here is a list of some of the key benefits:
- No triggers means reduced write load on the Publisher
- 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.