Friday, September 22


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…)