Craig's PlanetPostgreSQL |

Apr102017 by craig.ringerNo Comments

Using the PostgreSQL TAP framework in extensions

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 sub 2048R/739C93DD 2015-03-24 [expires: 2019-03-23] and if it is, run: sudo apt-get update (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.ringer35 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…)

Jul152016 by craig.ringerNo Comments

How to check the lock level taken by operations in PostgreSQL

Craig's PlanetPostgreSQL

PostgreSQL's manual is generally pretty clear about the locks taken by various operations - but nothing's perfect. If you need to, you can also ask PostgreSQL directly. You can check lock levels trivially with psql or PgAdmin. (more…)

May232016 by craig.ringer2 Comments

PostgreSQL-based application performance: latency and hidden delays

Craig's PlanetPostgreSQL, PostgreSQL

If you're trying to optimise the performance of your PostgreSQL-based application you're probably focusing on the usual tools: EXPLAIN (BUFFERS, ANALYZE), pg_stat_statements, auto_explain, log_statement_min_duration, etc. Maybe you're looking into lock contention with log_lock_waits, monitoring your checkpoint performance, etc too. But did you think about network latency? Gamers know about network latency, but did you think it mattered for your application server? (more…)

May32016 by craig.ringer2 Comments

Visual PostgreSQL debugging

Craig's PlanetPostgreSQL

Having recently been doing some debugging work where many watchpoints, conditional breakpoints etc were necessary I'd like to shout out to a really useful tool: The standalone CDT debugger. It's part of the Eclipse project, but before you run screaming - it doesn't require project setup or anything and it serves as a good GUI gdb wrapper. It's good for working with PostgreSQL because you don't need to set up a fake IDE project or any such time-wasting business. You just launch the debugger. You've still got your .gdbinit with postgresql debug helper macros and everything. (more…)

Apr282016 by craig.ringer3 Comments

Don’t set fsync=off if you want to keep your data

Craig's PlanetPostgreSQL

There are a lot of amazing features coming in PostgreSQL 9.6, but I'm personally very happy about a really small, simple one that helps close a long-standing user foot-gun. commit a31212b429cd3397fb3147b1a584ae33224454a6 Author: Robert Haas Date: Wed Apr 27 13:46:26 2016 -0400 Change postgresql.conf.sample to say that fsync=off will corrupt data. Discussion: [email protected] Per a suggestion from Craig Ringer. This wording from Tom Lane, following discussion. (more…)