Wednesday, January 16

Francesco’s PlanetPostgreSQL

PostgreSQL 9.6: Parallel Sequential Scan

PostgreSQL 9.6: Parallel Sequential Scan

Featured, Francesco's PlanetPostgreSQL, PostgreSQL
For a long time, one of the most known shortcomings of PostgreSQL was the ability to parallelise queries. With the release of version 9.6, this will no longer be an issue. A great job has been done on this subject, starting from the commit 80558c1, the introduction of parallel sequential scan, which we will see in the course of this article. First, you must take note: the development of this feature has been continuous and some parameters have changed names between a commit and another. This article has been written using a checkout taken on June 17 and some features here illustrated will be present only in the version 9.6 beta2. Compared to the 9.5 release, new parameters have been introduced inside the configuration file. These are: max_parallel_workers_per_gather: the (more…)
Automating Barman with Puppet: it2ndq/barman (part three)

Automating Barman with Puppet: it2ndq/barman (part three)

Francesco's PlanetPostgreSQL
In the second part of the Automating Barman with Puppet series we configured, via Puppet, two virtual machines: a PostgreSQL server and a Barman server to back it up. However, human intervention was required to perform the SSH key exchange and most of the manifest was written to allow the servers to access each other. In this third and final part of the series, we will look at how to configure a third VM that will act as the Puppet Master and use it to simplify the configuration of PostgreSQL and Barman.  (more…)
Automating Barman with Puppet: <code>it2ndq/barman</code> (part two)

Automating Barman with Puppet: it2ndq/barman (part two)

Barman, Francesco's PlanetPostgreSQL, PostgreSQL
In the first part of this article we configured Vagrant to execute two Ubuntu 14.04 Trusty Tahr virtual machines, respectively called pg and backup. In this second part we will look at how to use Puppet to set up and configure a PostgreSQL server on pg and back it up via Barman from the backup box. Puppet: configuration After defining the machines as per the previous article, we need to specify the required Puppet modules that librarian-puppet will manage for us. Two modules are required: puppetlabs/postgresql (https://github.com/puppetlabs/puppetlabs-postgresql/) to install PostgreSQL on the pg VM it2ndq/barman (https://github.com/2ndquadrant-it/puppet-barman) to install Barman on backup Both modules will be installed from Puppet Forge. For the puppetlabs/postgresql (more…)
Automating Barman with Puppet: it2ndq/barman (part one)

Automating Barman with Puppet: it2ndq/barman (part one)

Barman, Francesco's PlanetPostgreSQL, PostgreSQL
This is not the first time that 2ndQuadrant has looked at Puppet. Gabriele Bartolini has already written an article in two parts on how to rapidly configure a PostgreSQL server through Puppet and Vagrant, accompanied by the release of the code used in the example on GitHub (https://github.com/2ndquadrant-it/vagrant-puppet-postgresql). (more…)

PostgreSQL 9.4 for administrators (part two)

Francesco's PlanetPostgreSQL
  Written by Francesco Canovai First published in Italian    In the previous instalment, we introduced the logical replication feature which has been added to PostgreSQL 9.4. Let's go on exploring the multitude of new features that version 9.4 brings to the Operation field, easing the management of PostgreSQL databases for system and database administrators. pg_prewarm pg_prewarm is a new extension to solve the problem of slow servers after a restart. Buffers are emptied during a restart, therefore Postgres won't be able to find in RAM the data it needs, forcing disk reads. With pg_prewarm it is possible to load in memory an important table immediately after a reboot with the simple query: SELECT pg_prewarm('my_table'); This way, we won't (more…)

PostgreSQL 9.4 for administrators (part one)

Francesco's PlanetPostgreSQL
Written by Francesco Canovai     Version 9.4 of PostgreSQL, soon to be released, has many innovations for administrators, including the introduction of support for logical replication, which is the first step towards the integration of multi-master replication into core PostgreSQL. In this two-part article we will show you the main new features for administrators; we begin with logical replication, and describe the following concepts: Physical replication slots WAL level "logical" Logical replication slots Logical decoding Replica identity The development of these features is a direct result of the work carried out by 2ndQuadrant, in particular by Andres Freund, the main developer of Bi-Directional Replication (BDR). BDR is an open source (more…)