Friday, November 24

pglogical

pglogical with OmniDB

OmniDB, pglogical, William's PlanetPostgreSQL
1. Introduction 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 cd OmniDB git checkout dev 2.2. Create 2 virtual machines with (more…)

PG Phriday: pglogical and Postgres 10 Partitions

pglogical, Shaun's PlanetPostgreSQL
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. The Problem 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 (more…)

Untangling the PostgreSQL upgrade

pglogical, PostgreSQL
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. Point releases 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 (more…)

PGLogical 1.1 packages for PostgreSQL 9.6beta1

2ndQuadrant, Petr's PlanetPostgreSQL, pglogical, PostgreSQL
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 pglogical package repository. 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. (more…)

Performance limits of logical replication solutions

2ndQuadrant, pglogical, PostgreSQL, Tomas' PlanetPostgreSQL
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. physical replication 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” (more…)

The process that created pglogical

pglogical, Umair's PlanetPostgreSQL
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. Version control 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 (more…)

On pglogical performance

pglogical, PostgreSQL, Tomas' PlanetPostgreSQL
A few days ago we released pglogical, 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. (more…)

Why Logical Replication?

pglogical, Simon's PlanetPostgreSQL
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 (more…)