Posts by: craig.ringer

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…

Traceable commit for PostgreSQL 10

PostgreSQL 10 now supports finding out the status of a recent transaction for recovery after network connection loss or crash.

Re-import repository keys for BDR and pglogical apt repositories

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

BDR History and future

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…

BDR is coming to PostgreSQL 9.6

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…

BDR 1.0

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…

How to check the lock level taken by operations in PostgreSQL

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.

PostgreSQL-based application performance: latency and hidden delays

A pipeline in the Western Australian goldfields

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…

Visual PostgreSQL debugging

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…

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

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…

12345