Friday, June 23
Schedule is live for pgDay Paris 2017 on March 23!

Schedule is live for pgDay Paris 2017 on March 23!

Vik's PlanetPostgreSQL
pgDay Paris 2017 is the third annual one-day, one-track PostgreSQL conference, held in Paris, France. The schedule is live!This year, all presentations are in English in order to bring the very best of the international community to Paris and to show how PostgreSQL is used around the globe. Here is what you'll see at pgDay Paris 2017:A PostgreSQL DBAs Toolbelt for 2017 by Kaarel Moppel, a consultant from Austrian PostgreSQL company Cybertec, takes us on a tour of handy tools and scripts that every DBA should know about. Corruption War Stories by Christophe Pettus, CEO of American company PostgreSQL Experts, shares with us his experiences with data corruption and how he was able to overcome it. Can't imagine what a newbie can do with PostgreSQL on Linux in a month by Sylvie

Corruption in CREATE INDEX CONCURRENTLY

Pavan's PlanetPostgreSQL
Two weeks back we discovered an ancient bug in PostgreSQL which may cause index corruption when index is built via CREATE INDEX CONCURRENTLY (aka CIC). All supported releases of PostgreSQL are affected by this bug. The bug could be a source of index corruption reports that we receive from the field once in awhile.Now before we discuss the problem, let me make it absolutely clear that PostgreSQL is known for its gold standards as far as code quality and product stability is concerned. But just like any other software, the database engine can also have unnoticed bugs. But PostgreSQL community takes every known bug very seriously and try to fix it on priority. No wonder that the bug fix went into the scheduled 9.6.2 release (and all other stable releases) at a very short notice, but not b

Problem with Buildfarm registrations fixed

Andrew's PlanetPostgreSQL
Recently we switched the PostgreSQL Buildfarm to use https for its front-facing web transactions. That's worked pretty well, but one thing got broken in the change. The registration page uses a Captcha to help avoid web spam, which has been a problem in the past. However, the code to fetch the relevant html was still getting plain http, and thus browsers were refusing to display the Captcha because it came from an untrusted source. Without the Captcha the registration was rejected. This was discovered yesterday, and has now been fixed. The Captcha code now uses https. If you have recently found a buildfarm registration rejected like this, please resubmit it.

Processing PostgreSQL JSON & JSONB data in Java

Umair's PlanetPostgreSQL
This is part of a series of blogs on Java & PostgreSQL. For links to other blogs in this series, please scroll to the end of this post.Starting v9.2, PostgreSQL is providing native data type support for JSON objects. Subsequent releases introduced JSONB (binary formatted JSON objects) and many data manipulation functions for JSONs, making it a very powerful tool for NoSQL operations. String manipulation and parsing are very expensive operations in a database, so although you could have potentially stored JSON objects in strings in PostgreSQL before, introduction of the native data type has taken away overheads and made throughput a lot faster for JSON manipulation.JSON & JSONB JSONB was introduced as a native data type in v9.4 of PostgreSQL and it stores JSON objects...
Explaining CREATE INDEX CONCURRENTLY

Explaining CREATE INDEX CONCURRENTLY

2ndQuadrant, Pavan's PlanetPostgreSQL
This technical blog explains how CREATE INDEX CONCURRENTLY (CIC) works and how it manages to avoid locking the table from updates. A unique distinguishing factor of CIC is that it can build a new index on the table, without blocking it from updates/inserts/deletes.But even before that, let's understand how Heap-Only-Tuple (HOT) works. It was a landmark feature added in PostgreSQL 8.3 to reduce table bloat and improve performance significantly. But the feature also has some implications on the working of CIC. Heap-Only-Tuple (HOT) PostgreSQL uses multi-version concurrency control (MVCC) for transactions. In this technique, when a row is updated, a new version of the row is created and the old version is left unchanged. Each version has creator and destroyer information and transaction...

Dataloss at GitLab

2ndQuadrant, Simon's PlanetPostgreSQL
GitLab, thanks for using PostgreSQL 9.6 and its replication and backup facilities.We're sorry that you lost your database: https://about.gitlab.com/2017/02/01/gitlab-dot-com-database-incident/Thank you for posting this publicly to allow us to comment on this for your postmortem analysis.I'm very happy that you monitor Replication Lag, that is good. Replication lag of 4GB is at times normal, so shouldn't have caused major concern. I've recently fixed a bug in replication that caused replication to hang in some cases for up to a minute; we released a public fix to that and it will be included in the next maintenance release of PostgreSQL 9.6. It's not certain that the bug was hit and, if it was, whether that was enough to cause the slow down noted. The openness of your response ...
pgDay 2017 à Paris: conférence PostgreSQL internationale

pgDay 2017 à Paris: conférence PostgreSQL internationale

Cedric's FR PlanetePostgreSQL
Un événement communautaire Cette année encore 2ndQuadrant aide la communauté PostgreSQL en France en étant Partenaire du pgDay 2017 à ParisCette journée de conférences, en anglais exclusivement, est une opportunité unique d'en apprendre plus sur le fonctionnement et l'activité de PostgreSQL. Sécurité, benchmarks, supervision, roadmap pour la version 10, réplication, ... de nombreux sujets, variés et d'actualité. A noter qu'il n'est pas nécessaire de savoir lire Shakespeare dans la langue pour comprendre ce qu'il va se dire, c'est donc également une bonne occasion de renforcer votre anglais technique!2ndQuadrant tient à remercier Vik Fearing pour sa très forte implication dans l'organisation de cet événement communautaire appuyé par l'association Européenne. pgDay 2017 à Paris, so

repmgr 3.3

Ian's PlanetPostgreSQL
repmgr 3.3 introduces a number of additional options for setting up and managing replication clusters, with particular emphasis on cascading replication support. These changes will also make it easier to set up complex clusters using provisioning scripts.Additionally there are changes to the repmgr command line utility's logging behaviour which you should take into consideration when running therepmgrd daemon.repmgr is also tracking developments in the next major PostgreSQL release, 10.0, which will bring a lot of changes to the way PostgreSQL handles replication. At the time of writing, repmgr will work with the current PostgreSQL development code, but this combination is of course not suitable for use in production. Changes to logging behaviour Traditionally the repmgr command ...

What does pg_start_backup() do?

2ndQuadrant, Simon's PlanetPostgreSQL
Reading mailing lists can damage your health, as I recently discovered on the PostgreSQL Performance list where backup was being discussed.First off, don't read blogs for finding out critical pieces of info. Read the docs because they are accurate, fully reviewed and well maintained.I should add that I was the initial author of them as well, so maybe it's OK to carry on reading...pg_start_backup() is a function we execute to start a base backup. It was part of the original API for physical backup introduced in PostgreSQL 8.0. It's now been mostly superceded by the replication command BASE_BACKUP, which is most frequently executed by the pg_basebackup utility.So what does a base backup actually do? Well, first we execute a checkpoint so that as many changed data blocks are o...