Friday, November 24

Author: 2ndQuadrant Press

Back to the Future Part 3: pg_rewind with PostgreSQL 9.6

Back to the Future Part 3: pg_rewind with PostgreSQL 9.6

2ndQuadrant, Giuseppe's PlanetPostgreSQL
This is the third and last part of blog articles dedicated to pg_rewind. In the two previous articles we have seen how pg_rewind is useful to fix split-brain events due to mistakes in the switchover procedures, avoiding the need of new base backups. We have also seen that this is true for simple replication clusters, where more standby nodes are involved. In this case, just two nodes can be fixed, and the other ones need a new base backup to be re-synchronised. pg_rewind for PostgreSQL 9.6 is now able to work with complex replication clusters. Indeed, pg_rewind has been extended so it can view the timeline history graph of an entire HA cluster, like the one mentioned in my previous blog article. It is able to find out the most recent, shared point in the timeline history between (more…)
Back to the Future Pt. 2: How to use pg_rewind with PostgreSQL 9.5

Back to the Future Pt. 2: How to use pg_rewind with PostgreSQL 9.5

2ndQuadrant, Featured, Giuseppe's PlanetPostgreSQL
In the previous blog article we have seen how pg_rewind works with a simple HA cluster, composed of a master node replicating to a standby. In this context, an eventual switchover involves just two nodes that have to be aligned. But what happens with HA clusters when there are several (also cascading) standbys? Now, consider a more complicated HA cluster, composed of a master with two standbys, based on PostgreSQL 9.5; similar to what has been made in the first blog article dedicated to pg_rewind, we now create a master node replicating to two standby instances. Let's start with the master: # Set PATH variable export PATH=/usr/pgsql-9.5/bin:${PATH} # This is the directory where we will be working on # Feel free to change it and the rest of the script # will adapt itself (more…)
Back to the Future Pt. 1: Introduction to pg_rewind

Back to the Future Pt. 1: Introduction to pg_rewind

2ndQuadrant, Featured, Giuseppe's PlanetPostgreSQL
Since PostgreSQL 9.5, pg_rewind has been able to make a former master follow up a promoted standby although, in the meantime, it proceeded with its own timeline. Consider, for instance, the case of a switchover that didn't work properly. Have you ever experienced a "split brain" during a switchover operation? You know, when the goal is to switch the roles of the master and the standby, but instead you end up with two independent masters - each one with its own timeline? For PostgreSQL DBAs in HA contexts, this where pg_rewind comes in handy! Until PostgreSQL 9.5, there was only one solution to this problem: re-synchronise the PGDATA of the downgraded master with a new base backup and add it to the HA cluster as a new standby node. Generally, this is not a problem, unless your (more…)
BRIN for PostGIS: my story at the Code Sprint 2016 in Paris

BRIN for PostGIS: my story at the Code Sprint 2016 in Paris

Giuseppe's PlanetPostgreSQL
Last week I was pleased to attend Code Sprint 2016 in Paris, organised by the OSGeo Foundation: it has been an opportunity for many GIS users from all over the world to get together, discuss and actively work on many open tasks of some of the major open source frameworks on GIS. PostGIS was obviously one of the projects involved in the Code Sprint. Some background information first. A few months ago I started to work on adding BRIN support to the geospatial datatype available in PostGIS: I was able to develop a working prototype on my own. However, it was thanks to this Code Sprint that I was finally able to complete the job. Code Sprint was the opportunity for me to know Julien and Ronan from Dalibo, who shared the same goals. We worked as a team and shared our skills and (more…)

Why should you learn PostgreSQL?

PostgreSQL
A few weeks ago I asked one of my colleagues why I should learn PostgreSQL. Harald’s answer was: “To start any new journey of learning, asking ‘why’ is an excellent question. It gives us both a direction to send us on our way and an expected destination. Without a clear path to follow, our brains tend to filter out new information.” He then came up with some very valid reasons to learn PostgreSQL. SQL is a powerful tool to get data under control Why should we learn Structured Query Language (SQL)? SQL was initially created to enable non-IT specialists to query information from datasets without needing to ask a programmer for help. SQL aimed to be much more powerful than interfaces such as query by example, and more flexible than prebuild reports. The objective was to enable (more…)
NoSQL with PostgreSQL 9.4 and JSONB

NoSQL with PostgreSQL 9.4 and JSONB

Giuseppe's PlanetPostgreSQL
The introduction of the JSONB data type in PostgreSQL, definitely makes the “NoSQ” side of this relational DBMS come out: this introduction meets the requirements of all those who prefer a data structure in a “key-value” array, dictionary style (widely used in the field of development) and, at the same time, ensures all the advantages of a relational database. (more…)
The CHECK clause for updatable views

The CHECK clause for updatable views

Giuseppe's PlanetPostgreSQL, PostgreSQL
Written by Giuseppe Broccolo  First published in Italian   Since PostgreSQL 9.3, it is possible to update and insert into views directly, so long as the view refers to only one underlying table. PostgreSQL 9.4 allows us to use the CHECK clause for INSERTs into updatable views. For example, consider a table composed of just one integer column; and consider two views, one on numbers divisible by 2 and one on numbers divisible by 3. If we try to insert the number 123 into the first view: ---- $ CREATE TABLE some_data(id int4 PRIMARY KEY); CREATE TABLE $ CREATE VIEW first AS SELECT * FROM some_data WHERE 0 = id%2; CREATE VIEW $ CREATE VIEW second AS SELECT * FROM some_data WHERE 0 = id%3; CREATE VIEW $ INSERT INTO first(id) VALUES (123 (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 solution of (more…)