Craig’s PlanetPostgreSQL

Feb232018 by craig.ringer4 Comments

Dev Corner: error context stack corruption

PostgreSQL uses error context callbacks to allow code paths to annotate errors with additional information. For example, pl/pgsql uses them to add a CONTEXT message reporting the procedure that was executing at the time of the error. But if you get it wrong when you use one in an extension or a patch to core, it can be quite hard to debug. I'd like to share some hints here for people involved in PostgreSQL's C core and extensions. (more…)

Feb12018 by craig.ringer2 Comments

Using Multimaster and BDR appropriately – LinuxConfAu

Craig's PlanetPostgreSQL

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

Oct162017 by craig.ringerNo Comments

Plan for the unexpected: install diagnostic tools on your PostgreSQL servers

Craig's PlanetPostgreSQL

There's a lot of information out there on how to configure PostgreSQL, on the importance of backups and testing them, etc. But what about the server you run PostgreSQL on? We tend to pay a lot less attention to tools that you won't need unless something breaks. But it's worth taking some time to do so now, because that's time you won't have when your server is down and you're in a rush. Debuginfo and gdb Seriously consider installing debug-info packages for the major services you run on a server, and for the libraries that service uses. It wastes some disk space, but it saves you a lot of time if you end up needing that information in a rush. Debug info lets tools like the GNU Debugger gdb show variables, program arguments, etc when it is connected to a running program, or to a (more…)

Sep262017 by craig.ringer1 Comment

Transaction traceability in PostgreSQL 10 with txid_status(…)

Craig's PlanetPostgreSQL

One feature quietly added to PostgreSQL 10 is the ability to determine the commit status of any transaction by transaction-id. It's reasonable to wonder why you'd want this, since you know if you committed the transaction, it's still in progress, or if you or rolled it back. And you can check for in-progress transactions in pg_stat_activity. It exists to help the application recover to a known state after a failure without having to use heavyweight two-phase commit. It's also useful for querying standbys. Recovery Imagine that your application has just sent the COMMIT for a transaction that's part of a queue processing system. Before the application receives a reply to its commit request, the database connection breaks due to network issues, a database crash, etc. It's possible (more…)

Apr102017 by craig.ringer3 Comments

Using the PostgreSQL TAP framework in extensions

Craig's PlanetPostgreSQL

Support for using the TAP protocol to run extended regression tests was added to PostgreSQL back in 9.4 with the adoption of Perl's prove tool and Test::More to test initdb, pg_basebackup, etc. Since then the TAP-based tests have been greatly expanded, particularly with the advent of the src/test/recovery tests and the PostgresNode module in PostgreSQL 9.6. PostgreSQL now comes with a built-in test harness for easily starting up postgres instances, creating and restoring backups for replication, setting up streaming, and lots more. You can now use this to test your extensions. pg_regress and its limitations Extensions have long supported pg_regress based tests. Just drop the test scripts in sql/. Put the expected results in expected/. List the test names (sans directory and file (more…)

Apr52017 by craig.ringerNo Comments

Traceable commit for PostgreSQL 10

Craig's PlanetPostgreSQL

PostgreSQL 10 now supports finding out the status of a recent transaction for recovery after network connection loss or crash. (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…)

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

Aug122016 by craig.ringer7 Comments

BDR 1.0

Craig's PlanetPostgreSQL

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 Installation instructions Upgrade instructions for BDR 0.9.x users Git repository 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. About Postgres-BDR Bi-Directional Replication for PostgreSQL (Postgres-BDR, or BDR) is an asynchronous multi- (more…)