Monday, June 18

PG Phriday: Adventures in BAR Management

Shaun's PlanetPostgreSQL
Backups are a critical component to a fully covered Postgres database infrastructure. In some ways, it's fair to say a database without a backup is no database at all---sometimes literally. 2ndQuadrant's Barman tool is aptly named as a Backup And Recovery Manager for Postgres, and it exists primarily for encouraging a stable and robust backup process. Backing up a database and then restoring from that backup often has an army of associated scripts and utilities. Some of these components are probably the Postgres default tools of pg_dump, pg_restore, and pg_basebackup. The rest are often shell scripts, homegrown or otherwise copied from blogs or git repositories. Let's just assume the best case scenario and assume all of these work just fine; I used to think the same thing myself. But a (more…)

PostgreSQL Meltdown

Simon's PlanetPostgreSQL
Spectre and Meltdown have caused severe alarm in recent days. You may have read about up to 30% impact on PostgreSQL databases, which I believe to be overstated because of misunderstandings in the media. Let's dig into this in more detail. TL;DR Summary: no PostgreSQL patch required, -7% performance hit In response to these new security threats various OS patches have been released. Various authors have published benchmarks around these and they have, in some cases, stated worst-case measurements as impact measurements. For example: stating a 30% hit when, in fact, we are seeing a 7% hit on a busy server. Regrettably, it looks to me like some people outside the PostgreSQL community have spread this news as a problem for PostgreSQL, without clearly stating the workload measured, or (more…)

PostgreSQL is the DBMS of the Year 2017

Simon's PlanetPostgreSQL
The annual DBEngines ranking have just been updated, with PostgreSQL winning the "DBMS of the Year 2017" award. PostgreSQL was a runner up last year. So across the year, we have made more gains in popularity than all other databases. Looking at the detail we see that all of the other 4 databases that make up the Top 5 have reduced in popularity from last year. PostgreSQL has gone up, others have gone down. What's even more interesting, is that PostgreSQL is the only database in the top 5 systems that has increased steadily over the last 4 years. Slow, steady progress. The word is out! Why is that? PostgreSQL is multi-talented, offering relational features when needed, as well as JSON features for (more…)

Buildfarm Client Bugfix Release 6.1

Andrew's PlanetPostgreSQL
I have released version 6.1 of the PostgreSQL Buildfarm client. It is available at This release fixes a couple of bugs that became apparent in yesterday's release. The first was a relatively minor one where the verbosity was always set if using the script. The second was a portability issue where some versions of Perl and its Time::HiRes module behaved unexpectedly and as a result log files were not sorted in correct order, leading to very strange timing results. The second one is sufficiently bad to warrant this point release. Thanks to Tom Lane for identifying and helping to diagnose and patch these bugs. Also, one other small bug is fixed in some utility scripts, and the BlackholeFDW module (more…)

Announcing Release 6 of PostgreSQL Buildfarm client

Andrew's PlanetPostgreSQL
PostgreSQL Buildfarm client Release 6 is now available and can be downloaded from Features use a persistent cache for configure, leading to a substantial performance improvement for that step, based on an idea from Tom Lane improve installcheck security, mirroring earlier changes on this in Postgres itself allow skipping individual TAP tests, e,g, "--skip_steps=pg_dump-check" a use_installcheck_parallel setting that uses the parallel schedule rather than the serial schedule for the installcheck step (not for MSVC) use_valgrind and valgrind_options settings that will cause the installed postmaster to be run under valgrind and report any output. This doesn't apply to the check step, TAP tests, or any (more…)

PG Phriday: Postgres on ZFS

Shaun's PlanetPostgreSQL
ZFS is a filesystem originally created by Sun Microsystems, and has been available for BSD over a decade. While Postgres will run just fine on BSD, most Postgres installations are historically Linux-based systems. ZFS on Linux has had much more of a rocky road to integration due to perceived license incompatibilities. As a consequence, administrators were reluctant or outright refused to run ZFS on their Linux clusters. It wasn't until OpenZFS was introduced in 2013 that this slowly began to change. These days, ZFS and Linux are starting to become more integrated, and Canonical of Ubuntu fame even announced direct support for ZFS in their 16.04 LTS release. So how can a relatively obscure filesystem designed by a now-defunct hardware and software company help Postgres? Let's find out! (more…)

Webinar: Introduction to JSON data types in PostgreSQL [Follow Up]

Liaqat's PlanetPostgreSQL, Webinars
On 12-Dec-2017, 2ndQuadrant held the first in a series of PostgreSQL webinars. The session was conducted by Andrew Dunstan, Principal Contributor of JSON functionality to PostgreSQL and Senior Developer & PostgreSQL Committer at 2ndQuadrant. As promised, the recording of the Webinar is now available. Those who couldn't make it to the live session, can now view Introduction to JSON data types in PostgreSQL here. While Andrew was able to address many questions live, there were several queries that couldn’t be answered due to time restrictions. Andrew Dunstan has taken time to answer those questions below. Q1: Since PostgreSQL 9.4 JSONB supports GIN index, does that means that GIN index has a better performance on JSONB? What about JSON? A:  There is no direct indexing (more…)

pglogical 2.1 and Logical Replication in PostgreSQL 10

Petr's PlanetPostgreSQL
One of the headline features of the brand new PostgreSQL release out 2 months ago is Logical Replication. Logical replication allows more flexibility than physical replication, including replication between different major versions of PostgreSQL and selective-table replication. You can get more details on the feature here. So, now that we have this, I've been asked on occasion if we are still going to continue develop pglogical and if it's even needed. I was also asked a couple of times why we put the native logical replication into PostgreSQL when we already have pglogical. I'd like to answer those questions in this blog post. Why Logical Replication in PostgreSQL 10? Let's start with the simpler, or rather shorter, topic of why we added logical replication into PostgreSQL 10. The (more…)

PG Phriday: Getting RAD with Docker [Part 4]

Shaun's PlanetPostgreSQL
PgBouncer is a popular proxy and pooling layer for Postgres. It's extremely common to reconfigure PgBouncer with repmgr so it always directs connections to the current primary node. It just so happens our emerging Docker stack could use such a component.In our last article, we combined Postgres with repmgr to build a Docker container that could initialize and maintain a Postgres cluster with automated failover capabilities. Yet there was the lingering issue of connecting to the cluster. It's great that Postgres is always online, but how do we connect to whichever node is the primary?While we could write a layer into our application stack to call repmgr cluster show to find the primary before connecting, that's extremely cumbersome. Besides that, there's a better way. Let's alter our stack (more…)

PostgreSQL 11 – Server-side Procedures (Part 1)

Umair's PlanetPostgreSQL
Last week marked a rather big step in the PostgreSQL world that went largely unnoticed. Thanks to the work done by 2ndQuadrant contributors, we now have the ability to write Stored Procedures in PostgreSQL! (Ok, well not exactly now but we will have the ability once PostgreSQL 11 comes out) A procedure is essentially a set of commands to be executed in a particular order. As opposed to functions, procedures are not required to return a value. With this addition, you can now invoke a procedure by simply using the new CALL statement rather than using SELECT. The implementation is fully compliant with the SQL standard and will allow users to write procedures that are somewhat compatible with DB2, MySQL, and to a lesser extent, Oracle. The commit from last week adds new commands CALL (more…)