pglogical is an extension that provides an advanced logical replication system that serves as a highly efficient method of replicating data as an alternative to physical replication.
Here we will show how to build a test environment to play with PostgreSQL and pglogical, and how to configure it using the OmniDB 2.2 web interface.
2. Building test environment
Let's build a 2-node test environment to illustrate how to configure PostgreSQL and pglogical within OmniDB.
2.1. Pull OmniDB repo
The first thing you need to do is to download OmniDB repo from GitHub and make sure you are in the development branch. Run the following:
git clone https://github.com/OmniDB/OmniDB
git checkout dev
2.2. Create 2 virtual machines with
During the Postgres Open 2017 conference in San Francisco, someone came to the 2ndQuadrant booth and struck up a conversation with me. During our shameless geeking out over database mechanics, he asked me if pglogical supported the new Postgres 10 partitions. Given my noted expertise in all things Postgres, I answered in the appropriate manner:
"I have no idea. I'll have to look into that."
Well, after a bit of experimentation, I have a more concrete answer, and it's reassuringly positive.
Given a table on a provider node, is it possible to capture only INSERT traffic such that it accumulates on a subscribed system for archival purposes? It's a fairly common tactic, and allows an active OLTP system to regularly purge old data, while a reporting OLAP system keeps it
PostgreSQL 9.6 has just been released and most of the postgres users will start asking themselves how to upgrade to the new major version. This post has the intention of showing different procedures for upgrading your PostgreSQL server.
Upgrading to a new major version is a task which has a high ratio of preparation over total execution time. Specifically when skipping a release in the middle, for example, when you jump from version 9.3 to version 9.5.
On the other hand, point release upgrades don't need as much preparation. Generally, the only requirement is for the postgres service to be restarted. There are no changes to the underlying data structure, so there's no need to dump and restore. In the worst case scenario you may need to recreate some of your indexes
You may ask why do we release packages for beta version of Postgres? Well, one of the reasons is that you can use pglogical to replicate your existing PostgreSQL 9.5 or 9.4 database to the 9.6beta1 in real-time and run tests on it to help weed out any remaining bugs in the beta release. Here is a quick tutorial on how to do that.
We have made pglogical 1.1 packages available for PostgreSQL 9.6beta1 for both rpm and deb based distributions. They are available for install from our standard
The new feature version of pglogical is now available. The new release brings support for sequence replication, manually configured parallel subscriptions, replica triggers, numerous usability improvements and bug fixes. Let's look into the changes in more detail.
In the previous blog post, I briefly explained how we got the performance numbers published in the pglogical announcement. In this blog post I’d like to discuss the performance limits of logical replication solutions in general, and also how they apply to pglogical.
Firstly, let’s see how physical replication (built into PostgreSQL since version 9.0) works. A somewhat simplified figure of the with two just two nodes looks like this:
Clients execute queries on the master node, the changes are written to a transaction log (WAL) and copied over network to WAL on the standby node. The recovery on the standby process on the standby then reads the changes from WAL and applies them to the data files just like during recovery. If the standby is in “hot_standby”
pglogical (logical replication for PostgreSQL) is the latest in the series of awesome products developed & supported by 2ndQuadrant. One of the key ingredients to making any product great is the process followed in developing it. We have tried to notch up our game with pglogical, let me describe some of the measures we have taken to ensure reliability.
Like all our other PostgreSQL tools, pglogical is hosted on 2ndQuadrant's private github. Version control tools like github not only have provisions for team coordination, allowing multiple people to work collaboratively on the same project, they also allow for maintaining multiple branches and multiple tags. This ability is extremely important to be able to support and patch production releases, sending out hotfixes
, a fully open-source logical replication solution for PostgreSQL, that’ll hopefully get included into the PostgreSQL tree in a not-too-distant future. I’m not going to discuss about all the things enabled by logical replication - the pglogical release announcement
presents a quite good overview, and Simon also briefly explained
the advantages of logical replication in another post a few days ago.
Instead I’d like to talk about one particular aspect mentioned in the announcement - performance comparison with existing solutions. The pglogical page mentions
... preliminary internal testing demonstrating a 5x increase in transaction throughput (OLTP workloads using pgBench ) over other replication methods like slony and londiste3. So let's see where the statement comes from.
A few days ago we released
PostgreSQL has built-in streaming replication. Why do we need new replication?
Well, in some cases, we do need more. Which is why we have pglogical.
The existing replication is more properly known as Physical Streaming Replication since we are streaming a series of physical changes from one node to another. That means that when we insert a row into a table we generate change records for the insert plus all of the index entries. When we VACUUM a table we also generate change records.
Also, Physical Streaming Replication records all changes at the byte/block level, making it very hard to do anything other than just replay everything.
Logical Streaming Replication sends changes in a more flexible form, sending only the logical change. So when we do an insert we send only the insert record