Unless you were in Sydney for linux.conf.au 2018 last week you probably didn't see my talk Geographically distributed multi-master replication with PostgreSQL and BDR. Luckily for you it's on archive.org and YouTube.
If you're interested in multi-master replication of any sort, even non-PostgreSQL-based multi-master, it should be worth taking a look. The talk could've been better titled "Understand Multi-Master and if it's right for you" or "Physics is Mean".
Comments and questions are welcome - @craig2ndq.
You can also just read the slides (pdf), but they're really intended to support an explanation, not to stand alone.
Huge thanks to Next Day Video for the amazing work they did on the recordings, to the conference conveners, the organizing team and volunteers.
Postgres-BDR (or just BDR, for short) is an open source project from 2ndQuadrant that provides multi-master features for PostgreSQL.
Here we will show how to build a test environment to play with BDR and how to configure it using the OmniDB 2.1 web interface.
2. Building test environment
Let's build a 2-node test environment to illustrate how to configure BDR 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
git checkout dev
2.2. Create 2 virtual machines with BDR
On your host machine, you need to have installed:
Postgres-BDR is an open source project from 2ndQuadrant that provides multi-master features for PostgreSQL. We have pursued a joint strategy of providing both working code available now and also submitting the features into core PostgreSQL.
Postgres-BDR 1.0 runs on a variant distro of PG9.4. This is in Production now and receives regular maintenance and security updates. 2ndQuadrant will support this until 9.4 End of Life in December 2019.
One of the greatest achievements to come out of our work on BDR is the logical replication technology. Our engineers spent a considerable amount of energy to contribute the tech to PostgreSQL core and I feel especially proud that this is a headline feature of the upcoming PG10 release.
And Now BDR 2.0 …
BDR 2.0 runs on community PG9.6 as
sub 2048R/739C93DD 2015-03-24 [expires: 2019-03-23]
and if it is, run:
sudo apt- (more…)
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.
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 <
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
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
I was in Jakarta a couple of weeks ago and there happened to be a meetup of the Indonesia PUG in Bandung while I was there. Because it is just a 2 hour, rather picturesque drive, from Jakarta, I thought it was too good of an opportunity to miss. So I showed up.
The meetup was hosted by Julyanto Sutandang of Equnix Business Solutions and the conversation was mostly centered around convincing the local technology industry about PostgreSQL in comparison to Oracle. We got into fairly detailed discussions on the typical challenges of moving an Oracle production database to PostgreSQL. I especially love talking about hierarchical queries - Oracle's CONNECT BY PRIOR and PostgreSQL's WITH RECURSIVE.
It was very interesting to find out how popular BDR - the Multi Master
The right answer is of course "Use PostgreSQL". It's the main distro and we want you to use that as often as possible.
The Postgres-BDR and Postgres-XL projects are also fully open source projects, using the same copyright and licence as the main PostgreSQL project. So if you're using PostgreSQL, they are also options to consider if you want extended functionality.
What does Postgres-BDR do? BDR allows you to have a widely distributed cluster of nodes that give you multiple full copies of a database. So you can have copies of the database in London, New York, San Francisco, Rome, Dubai, New Delhi, Hong Kong, Tokyo, Sydney, São Paulo, Buenos Aires and Johannesburg. And all your customers in those places get fast access for read AND write to the database. As long as your application
Postgres-BDR has now reached 1.0 production status.
Over the last 2 years, Postgres-BDR has been used daily for mission critical production systems.
As you might imagine, it's been improved by both bug fixes and feature enhancements that allow it to be used smoothly, so its mature, robust and feature-rich.
The BDR Project introduced logical replication for PostgreSQL, now available as pglogical. In addition, it introduced replication slots, background workers and many other features. But is it still relevant?
Postgres-BDR delivers all of these features that aren't yet in PostgreSQL 9.6, and likely won't all be in PostgreSQL 10.0 either
Automatic replication of DDL, reducing maintenance costs for DBAs
Automatic replication of sequences
Conflict resolution and logging
I'm pleased to say that we've just released Postgres-BDR 1.0, based on PostgreSQL 9.4.9.
This release contains significant improvements to DDL replication locking, global sequences, documentation, performance, and more. It also removes the deprecated UDR component in favour of pglogical.
The release announcement on the pgsql-announce mailing list
Postgres-BDR 1.0 release notes
Upgrade instructions for BDR 0.9.x users
It's taken a lot of work to get to this point. This release sets the foundation to port BDR to PostgreSQL 9.6 and to enhance its high-availability capabilities, and I'm excited to be pushing BDR forward.
Bi-Directional Replication for PostgreSQL (Postgres-BDR, or BDR) is an asynchronous multi-