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 replic...
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…)
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
BDR is both a patch to PostgreSQL core and an extension on top of PostgreSQL core. How did that come about, and what's it's future?Development of BDR was initiated around the time PostgreSQL 9.2 was in development. Arguably earlier if you count things like the extension mechanism. The goal of BDR is, and has always been, to add necessary features to core PostgreSQL to perform asynchronous loosely-coupled multi-master logical replication. BDR improvements to core PostgreSQL Since it's such a large set of changes it was necessary to structure development as a series of discrete features. A natural dividing line was "things that require changes to the core PostgreSQL code" vs "things that can be done in an extension". So the code was structured accordingly, making BDR a set of patches t...
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...
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 TravelSynchronous CommitBy default, PostgreSQL implements
An Uber technical blog of July 2016 described the perception of “many Postgres limitations”. Regrettably, a number of important technical points are either not correct or not wholly correct because they overlook many optimizations in PostgreSQL that were added specifically to address the cases discussed. In most cases, those limitations were actually true in the distant past of 5-10 years ago, so that leaves us with the impression of comparing MySQL as it is now with PostgreSQL as it was a decade ago. This is no doubt because the post was actually written some time/years? ago and only recently published.This document looks in detail at those points to ensure we have detailed information available for a wider audience, so nobody is confused by PostgreSQL's capabilities.(more&hellip
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 third post of the series and we’ll talk about timeline issues and their effects on fault tolerance and dependability of PostgreSQL.If you would like to witness the evolution progress from the beginning, please check the first two blog posts of the series:Evolution of Fault Tolerance in PostgreSQL Evolution of Fault Tolerance in PostgreSQL: Replication Phase Timelines The ability to restore the database to a previous point in time creates some complexities which we’ll cover some of the cases by explaining failover (Fig. 1), switchover (Fig. 2) and pg_rewind (Fig. 3) case
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 second post of the series and we'll talk about replication and its importance on fault tolerance and dependability of PostgreSQL.If you would like to witness the evolution progress from the beginning, please check the first blog post of the series: Evolution of Fault Tolerance in PostgreSQLPostgreSQL Replication Database replication is the term we use to describe the technology used to maintain a copy of a set of data on a remote system. Keeping a reliable copy of a running system is one of the biggest concerns of redundancy and we all like maintainable, easy-to-use and stable
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…)