Tag: pglogical

May42018 by Shaun Thomas2 Comments

PG Phriday: BDR Around the Globe

With the addition of logical replication in Postgres 10, it's natural to ask "what's next"? Though not directly supported yet, would it be possible to subscribe two Postgres 10 nodes to each other? What kind of future would that be, and what kind of scenarios would be ideal for such an arrangement? As it turns out, we already have a kind of answer thanks to the latency inherent to the speed of light: locality. If we can provide a local database node for every physical app location, we also reduce latency by multiple orders of magnitude. Let's explore the niche BDR was designed to fill. What is Postgres-BDR? Postgres-BDR is simply short for Postgres Bi-Directional Replication. Believe it or not, that's all it needs to be. The implications of the name itself are numerous once fully (more…)

Apr302018 by Gulcin YildirimNo Comments

Near-Zero Downtime Automated Upgrades of PostgreSQL Clusters in Cloud (Part II)

2ndQuadrant, DevOps, Featured, Gulcin's PlanetPostgreSQL, pglogical, PostgreSQL

I've started to write about the tool (pglupgrade) that I developed to perform near-zero downtime automated upgrades of PostgreSQL clusters. In this post, I'll be talking about the tool and discuss its design details. You can check the first part of the series here: Near-Zero Downtime Automated Upgrades of PostgreSQL Clusters in Cloud (Part I). The tool is written in Ansible. I have prior experience of working with Ansible, and I currently work with it in 2ndQuadrant as well, which is why it was a comfortable option for me. That being said, you can implement the minimal downtime upgrade logic, which will be explained later in this post, with your favorite automation tool. Further reading: Blog posts Ansible Loves PostgreSQL , PostgreSQL Planet in Ansible Galaxy and (more…)

Mar232018 by Gulcin Yildirim5 Comments

Near-Zero Downtime Automated Upgrades of PostgreSQL Clusters in Cloud (Part I)

2ndQuadrant, DevOps, Featured, Gulcin's PlanetPostgreSQL, pglogical, PostgreSQL

Last week, I was at Nordic PGDay 2018 and I had quite a few conversations about the tool that I wrote, namely pglupgrade, to automate PostgreSQL major version upgrades in a replication cluster setup. I was quite happy that it has been heard and some other people in different communities giving talks at meetups and other conferences about near-zero downtime upgrades using logical replication. Given that there is a talk that I gave at PGDAY'17 Russia, PGConf.EU 2017 in Warsaw and lastly at FOSDEM PGDay 2018 in Brussels, I thought it is better to create a blog post to keep this presentation available to the folks who could not make it to any of the conferences aforementioned. If you would like to directly go the talk and skip reading this blog post here is your link: Near-Zero Downtime (more…)

Sep222017 by Shaun Thomas5 Comments

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

Apr282017 by Petr Jelinek29 Comments

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

Mar232017 by craig.ringer2 Comments

Re-import repository keys for BDR and pglogical apt repositories

Craig's PlanetPostgreSQL

The BDR and pglogical apt repository GnuPG signing keys have been renewed. Users should re-import the existing keys. You can verify that it's still the same key as referenced in the documentation, just with a later expiry date. Simply run: wget --quiet -O - http://packages.2ndquadrant.com/bdr/apt/AA7A6805.asc | sudo apt-key add - sudo apt-key finger AA7A6805 | grep -A2 -B3 BDR Now check the fingerprint printed by the second command to verify it's the same as this output: pub 2048R/AA7A6805 2015-03-24 [expires: 2019-03-23] Key fingerprint = 855A F5C7 B897 6564 17FA 73D6 5D94 1908 AA7A 6805 uid BDR Apt Signing Key for 2ndQuadrant <[email protected]> sub 2048R/739C93DD 2015-03-24 [expires: 2019-03-23] and if it is, run: sudo apt- (more…)

Oct202016 by Petr JelinekNo Comments

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

Oct182016 by craig.ringerNo Comments

BDR History and future

Craig's PlanetPostgreSQL

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

Sep212016 by craig.ringer39 Comments

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

Aug22016 by Simon Riggs6 Comments

Thoughts on Uber’s List of Postgres Limitations

2ndQuadrant, Simon's PlanetPostgreSQL

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