Sunday, August 19

Keeping our perl code clean

Andrew's PlanetPostgreSQL
Recently I have been refining and adding utilities to look after our Perl code.  You might be surprised to learn that as well as 1.3 million or so lines of C code, there are about 30,000 lines of Perl code in our sources. This a sizeable body of code, even if it's dwarfed by our C code. What does it do? Well, lots of things. It runs some very critical code in building from source, so the code to set up our catalogs is created by some Perl code.  All the new data setup for catalogs is in fact Perl code. That's another 20,000 lines or so of code on top of the 30,000 mentioned above. We also use Perl to run TAP tests, such as testing initdb and pg_dump. And it runs building and testing when we're building with the Microsoft tool-sets on Windows. So, what changes have been made? First, we (more…)

Road test your patch in one command

Andrew's PlanetPostgreSQL
If you have Docker installed on your development machine, there is a simple way to road test your code using the buildfarm client, in a nicely contained environment. These preparatory steps only need to be done once. First clone the repository that has the required container definitions: git clone https://github.com/PGBuildFarm/Dockerfiles.git bf-docker Then build a container image to run the command (in this example we use the file based on Fedora 28): cd bf-docker docker build --rm=true -t bf-f28 -f Dockerfile.fedora-28 . Make a directory to contain all the build artefacts: mkdir buildroot-f28 That's all the preparation required. Now you can road test your code with this command: docker run -v buildroot-f28:/app/buildroot \ -v /path/to/postgres/source:/app/ (more…)

Using Window Functions for Time Series IoT Analytics in Postgres-BDR

Haroon's PlanetPostgreSQL, Internet of Things, Time Series Data
Internet of Things tends to generate large volumes of data at a great velocity. Often times this data is collected from geographically distributed sources and aggregated at a central location for data scientists to perform their magic i.e. find patterns, trends and make predictions. Let’s explore what the IoT Solution using Postgres-BDR has to offer for Data Analytics. Postgres-BDR is offered as an extension on top of PostgreSQL 10 and above. It is not a fork. Therefore, we get the power of complete set of analytic functions that PostgreSQL has to offer. In particular, I am going to play around with PostgreSQL's Window Functions here to analyze a sample of time series data from temperature sensors. Let's take an example of IoT temperature sensor time series data spread over a (more…)

PGInstaller – A GUI based, user-friendly installer for PostgreSQL

2ndQuadrant, Liaqat's PlanetPostgreSQL, PGInstaller, PostgreSQL
Installing the world’s top enterprise-class open source database - PostgreSQL - is now a whole lot easier with PGInstaller. PGInstaller is a GUI based, user-friendly installer for PostgreSQL that is digitally signed and certified by 2ndQuadrant. The installer is currently available for PostgreSQL versions 9.5, 9.6, 10 and 11(beta) and has the ability to run in graphical, command line, or quiet installation modes. In addition, PGInstaller provides the following features: Built-in support for Python 3 Compression support using zlib Integration with native service control managers such as systemd for Linux, Service Control Manager for Windows, and LaunchControl for OSX Consistent interface across all supported platforms How to get started with PGInstaller: (more…)

Introduction to Postgres-BDR [Webinar Follow-up]

2ndQuadrant, Liaqat's PlanetPostgreSQL, Webinars
The announcement of Postgres-BDR 3.0 last month at PostgresConf US had been long-awaited in the PostgreSQL community. Complex use cases from customers have driven the development of BDR far beyond the original feature set, resulting in a more robust technology than ever imagined - so we're happy to say that it was worth the wait. Postgres-BDR (Bi-Directional Replication) enhances PostgreSQL with advanced multi-master replication technology that can be used to implement very high availability applications. For an introduction to Postgres-BDR covering an overview of its complex architecture and its common use cases - 2ndQuadrant held the "Introduction to Postgres-BDR" webinar as part of its PostgreSQL webinar series. The webinar was presented by Simon Riggs, Founder and CEO of (more…)

PostgreSQL Buildfarm Client Release 8

Andrew's PlanetPostgreSQL
I have just release version 8 of the PostgreSQL Buildfarm client It can be downloaded from https://buildfarm.postgresql.org/downloads/releases/build-farm-8.tgz or https://github.com/PGBuildFarm/client-code/archive/REL_8.tar.gz This release contains a number of small changes to make using the --from-source feature a bit nicer. It also contains a good deal of code cleanup to be perlcritic clean, with some exceptions, down to severity level 3, and also to remove some out of date code that referred to Postgresql branches we no longer support. It also contains the following features: --tests and --schedule now apply to the check step as well as the installcheck step a new --delay-check switch delays the check step until after install. This helps work around a bug or lack of (more…)

PG Phriday: BDR Around the Globe

Shaun's PlanetPostgreSQL
With the addition of logical replication in Postgres 10, it's natural to ask "what's next"? Though not directly supported yet, would it be possible to subscribe two Postgres 10 nodes to each other? What kind of future would that be, and what kind of scenarios would be ideal for such an arrangement? As it turns out, we already have a kind of answer thanks to the latency inherent to the speed of light: locality. If we can provide a local database node for every physical app location, we also reduce latency by multiple orders of magnitude. Let's explore the niche BDR was designed to fill. What is Postgres-BDR? Postgres-BDR is simply short for Postgres Bi-Directional Replication. Believe it or not, that's all it needs to be. The implications of the name itself are numerous once fully (more…)
Near-Zero Downtime Automated Upgrades of PostgreSQL Clusters in Cloud (Part II)

Near-Zero Downtime Automated Upgrades of PostgreSQL Clusters in Cloud (Part II)

2ndQuadrant, DevOps, Featured, Gulcin's PlanetPostgreSQL, pglogical, PostgreSQL
I've started to write about the tool (pglupgrade) that I developed to perform near-zero downtime automated upgrades of PostgreSQL clusters. In this post, I'll be talking about the tool and discuss its design details. You can check the first part of the series here: Near-Zero Downtime Automated Upgrades of PostgreSQL Clusters in Cloud (Part I). The tool is written in Ansible. I have prior experience of working with Ansible, and I currently work with it in 2ndQuadrant as well, which is why it was a comfortable option for me. That being said, you can implement the minimal downtime upgrade logic, which will be explained later in this post, with your favorite automation tool. Further reading: Blog posts Ansible Loves PostgreSQL , PostgreSQL Planet in Ansible Galaxy and  (more…)
2ndQuadrant at PostgresConf US 2018

2ndQuadrant at PostgresConf US 2018

2ndQuadrant, Marriya's PlanetPostgreSQL, PostgreSQL
The world of PostgreSQL continues to grow stronger by the year! Last week we saw it all come together at PostgresConf US 2018 in Jersey City. 2ndQuadrant was proud to participate again this year as a platinum sponsor. The conference was held to promote awareness and usage of PostgreSQL through tutorials and case-studies, as well as providing the opportunity to listen first hand to some of the best minds in the community. The 5-day event allocated two days to hands-on trainings on various PostgreSQL development and management topics. The remaining days were filled with over 80 breakout sessions covering everything related to PostgreSQL you could imagine. At 2ndQuadrant, we take pride in supporting the continued development of the world’s most advanced open source database. (more…)

PG Phriday: Securing PgBouncer

Shaun's PlanetPostgreSQL
We all love PgBouncer. It's a great way to multiplex tens, hundreds, or even thousands of client connections to a small handful of Postgres sessions. What isn't necessarily so endearing, is that it can't pass authentication from itself to Postgres, as each Postgres session may exist before the connection to PgBouncer is established. Or can it? Let's explore how things have evolved, but the news never really got out. Tell you what I got in mind As a proxy, PgBouncer authenticates on a per user/database basis. Since Postgres authenticates sessions before they connect, PgBouncer used to have no way to re-auth its own connections. The old, and probably most prevalent way to circumvent this, was to build a userlist.txt file that contained every user and password hash that should be allowed to (more…)