Thursday, August 17

Tag: replication

Logical Replication in PostgreSQL 10

Petr's PlanetPostgreSQL
PostgreSQL 10 is getting close to its first beta release and it will include the initial support for logical replication, which is was written primarily by me and committed by my colleague Peter Eisentraut, and is internally based on the work 2ndQuadrant did on pglogical (even though the user interface is somewhat different). I'd like to share some overview of basics in this blog post. What's logical replication? Let me start with briefly mentioning what logical replication is and what's it good for. I expect that most people know the PostgreSQL streaming master-standby replication that has been part of PostgreSQL for years and is commonly used both for high availability and read scaling. So why add another replication mechanism and why call it logical? Well, the traditional (more…)

When autovacuum does not vacuum

2ndQuadrant, PostgreSQL, Tomas' PlanetPostgreSQL
A few weeks ago I explained basics of autovacuum tuning. At the end of that post I promised to look into problems with vacuuming soon. Well, it took a bit longer than I planned, but here we go. To quickly recap, autovacuum is a background process cleaning up dead rows, e.g. old deleted row versions. You can also perform the cleanup manually by running VACUUM, but autovacuum does that automatically depending on the amount of dead rows in the table, at the right moment - not too often but frequently enough to keep the amount of "garbage" under control. (more…)

repmgr 3.3

Ian's PlanetPostgreSQL
repmgr 3.3 introduces a number of additional options for setting up and managing replication clusters, with particular emphasis on cascading replication support. These changes will also make it easier to set up complex clusters using provisioning scripts. Additionally there are changes to the repmgr command line utility's logging behaviour which you should take into consideration when running therepmgrd daemon. repmgr is also tracking developments in the next major PostgreSQL release, 10.0, which will bring a lot of changes to the way PostgreSQL handles replication. At the time of writing, repmgr will work with the current PostgreSQL development code, but this combination is of course not suitable for use in production. Changes to logging behaviour Traditionally the repmgr command (more…)

pglogical 1.2 with PostgreSQL 9.6 support

Petr's PlanetPostgreSQL
PostgreSQL 9.6 is now out and so is an updated version of pglogical that works with it. For quick guide on how to upgrade the database with pglogical you can check my post which announced 9.6beta support. The main change besides the support for 9.6.x release of PostgreSQL is in the way we handle the output plugin and apply plugin. They have now been merged into single code base and single package so that there is no need to track the pglogical_output separately for the users and developers alike. We fixed several bugs this time and also made upgrades from 9.4 much easier. Here is a more detailed list of changes: keepalive is tuned to much smaller values by default so that pglogical will notice network issues earlier better compatibility when upgrading from PostgreSQL 9.4 (more…)
Back to the Future Part 3: pg_rewind with PostgreSQL 9.6

Back to the Future Part 3: pg_rewind with PostgreSQL 9.6

2ndQuadrant, Giuseppe's PlanetPostgreSQL
This is the third and last part of blog articles dedicated to pg_rewind. In the two previous articles we have seen how pg_rewind is useful to fix split-brain events due to mistakes in the switchover procedures, avoiding the need of new base backups. We have also seen that this is true for simple replication clusters, where more standby nodes are involved. In this case, just two nodes can be fixed, and the other ones need a new base backup to be re-synchronised. pg_rewind for PostgreSQL 9.6 is now able to work with complex replication clusters. Indeed, pg_rewind has been extended so it can view the timeline history graph of an entire HA cluster, like the one mentioned in my previous blog article. It is able to find out the most recent, shared point in the timeline history between (more…)

repmgr 3.2 is here with Barman support and Brand New High Availability features

Ian's PlanetPostgreSQL, repmgr
repmgr 3.2 has recently been released with a number of enhancements, particularly support for 2ndQuadrant's Barman archive management server, additional cluster monitoring functionality and improvements to the standby cloning process. One aim of this release is to remove the requirement to set up passwordless SSH between servers, which means when using repmgr's standard functionality to clone a standby, this is no longer a prerequisite. However, some advanced operations do require SSH access to be enabled. Barman support repmgr 3.2 can now clone a standby directly from the Barman backup and recovery manager. In particular it is now possible to clone a standby from a Barman archive, rather than directly from a running database server. This means the server is not subjected to the I/O (more…)
Back to the Future Pt. 2: How to use pg_rewind with PostgreSQL 9.5

Back to the Future Pt. 2: How to use pg_rewind with PostgreSQL 9.5

2ndQuadrant, Featured, Giuseppe's PlanetPostgreSQL
In the previous blog article we have seen how pg_rewind works with a simple HA cluster, composed of a master node replicating to a standby. In this context, an eventual switchover involves just two nodes that have to be aligned. But what happens with HA clusters when there are several (also cascading) standbys? Now, consider a more complicated HA cluster, composed of a master with two standbys, based on PostgreSQL 9.5; similar to what has been made in the first blog article dedicated to pg_rewind, we now create a master node replicating to two standby instances. Let's start with the master: # Set PATH variable export PATH=/usr/pgsql-9.5/bin:${PATH} # This is the directory where we will be working on # Feel free to change it and the rest of the script # will adapt itself (more…)

BDR is coming to PostgreSQL 9.6

Craig's PlanetPostgreSQL
I'm pleased to say that Postgres-BDR is on its way to PostgreSQL 9.6, and even better, it works without a patched PostgreSQL. BDR has always been an extension, but on 9.4 it required a heavily patched PostgreSQL, one that isn't fully on-disk-format compatible with stock community PostgreSQL 9.4. The goal all along has been to allow it to run as an extension on an unmodified PostgreSQL ... and now we're there. The years of effort we at 2ndQuadrant have put into getting the series of patches from BDR into PostgreSQL core have paid off. As of PostgreSQL 9.6, the only major patch that Postgres-BDR on 9.4 has that PostgreSQL core doesn't, is the sequence access method patch that powers global sequences. This means that Postgres-BDR on 9.6 will not support global sequences, at least not (more…)
Back to the Future Pt. 1: Introduction to pg_rewind

Back to the Future Pt. 1: Introduction to pg_rewind

2ndQuadrant, Featured, Giuseppe's PlanetPostgreSQL
Since PostgreSQL 9.5, pg_rewind has been able to make a former master follow up a promoted standby although, in the meantime, it proceeded with its own timeline. Consider, for instance, the case of a switchover that didn't work properly. Have you ever experienced a "split brain" during a switchover operation? You know, when the goal is to switch the roles of the master and the standby, but instead you end up with two independent masters - each one with its own timeline? For PostgreSQL DBAs in HA contexts, this where pg_rewind comes in handy! Until PostgreSQL 9.5, there was only one solution to this problem: re-synchronise the PGDATA of the downgraded master with a new base backup and add it to the HA cluster as a new standby node. Generally, this is not a problem, unless your (more…)
Evolution of Fault Tolerance in PostgreSQL: Synchronous Commit

Evolution of Fault Tolerance in PostgreSQL: Synchronous Commit

2ndQuadrant, Featured, Gulcin's PlanetPostgreSQL, PostgreSQL
PostgreSQL is an awesome project and it evolves at an amazing rate. We’ll focus on evolution of fault tolerance capabilities in PostgreSQL throughout its versions with a series of blog posts. This is the fourth post of the series and we’ll talk about synchronous commit and its effects on fault tolerance and dependability of PostgreSQL. If you would like to witness the evolution progress from the beginning, please check the first three blog posts of the series below. Each post is independent, so you don't actually need to read one to understand another. Evolution of Fault Tolerance in PostgreSQL  Evolution of Fault Tolerance in PostgreSQL: Replication Phase  Evolution of Fault Tolerance in PostgreSQL: Time Travel Synchronous Commit By default, PostgreSQL (more…)