Thursday, May 24

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 (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…)

OmniDB: Console Tab

2ndQuadrant, OmniDB, William's PlanetPostgreSQL
Introduced in OmniDB 2.6.0, the new OmniDB Console Tab provides an easy and comfortable way to interact with your databases. Users familiar with the psql command line utility will find that Console Tab behaves very similarly. In fact, many of the backslash commands Console Tab provides are present in psql. For example, \? shows a list with all commands available, its syntax and description. The command \h can be another friend of yours, because it shows a comprehensive help about any PostgreSQL SQL command. The editor on the bottom of the tab area is full-featured just like the Query Tab editor (it provides syntax highlight and autocomplete with Ctrl-Space). To execute a command, just type it in this editor. If the command is a backslash (starts with \), just type Enter and (more…)

Partition Elimination in PostgreSQL 11

2ndQuadrant, David's PlanetPostgreSQL
The feature freeze for the PostgreSQL 11 release is now upon us. During the last few days my colleague Álvaro Herrera pushed two changes into the development branch of PostgreSQL: 1. Faster Partition Pruning 2. Partition Pruning at Execution Time These patches aim to improve the performance and usability of the declarative table partitioning feature (added in PostgreSQL 10). Amit Langote wrote the first of these two patches, with some assistance from me. I'm the author of the second patch. This one is based on an original patch by Beena Emerson. Background Internally in PostgreSQL, a partitioned table is made up from a series of individual tables. These tables are all grouped under one common parent partitioned table. Queries being run against the partitioned table need the (more…)

Using Java Database Interface (jDBI) with PostgreSQL

2ndQuadrant, Umair's PlanetPostgreSQL
jDBI is designed to be the middle ground between high level ORMs like JPA & Hibernate on one hand and raw JDBC on the other. You can think of it as a layer of convenience on top of JDBC that is still at a lower level than ORMs. When working with JDBC, you are exposed to the raw workings of the SQL language and that of the database you are connecting to. jDBI tries to expose API that encapsulates this and is oriented towards Java programmers rather than database developers. It does so in two different styles, namely fluent style and SQL object style. Fluent style provides the facility of inline querying within your Java program whereas the SQL object style simplifies DAO creation using annotations. Both styles are demonstrated below. Why Use jDBI? You should consider using jDBI (more…)

Power of Indexing in PostgreSQL [Webinar Follow-up]

Liaqat's PlanetPostgreSQL, Webinars
Given the massive size, complexity, and performance requirements of most databases, having an optimized database is critical for many organizations. A variety of different strategies are used for database performance enhancement - indexing being one of them. Since indexing is such a key component to database performance, 2ndQuadrant chose to highlight the power of indexing in the third instalment of their PostgreSQL webinar series. Power of Indexing in PostgreSQL was conducted by Pavan Deolasee, PostgreSQL contributor and developer at 2ndQuadrant. He covered various topics such as accessing data in PostgreSQL and types of indexes supported as well as their internals. If you weren’t able to make it to the live session, you can now view the recording here. Questions that (more…)

PGConf APAC 2018 – bigger & better each year

2ndQuadrant, Umair's PlanetPostgreSQL
Last week saw the 3rd event in as many years in Singapore, and yet this time something was very different. For the last 2 years, we had been conducting the conference as a PostgreSQL track with FOSSASIA. This year, we were able to pull off an entirely independent, 2-day event completely focused on PostgreSQL! The event kicked off with a keynote from Joe Conway (flying in all the way from USA!), who talked about the features to look forward to in PostgreSQL 11. The release is currently scheduled for September 2018. This was followed by 8 talks on the first day and 7 talks on the second day. We also had a round of lightening talks, which were followed by a speaker/sponsor dinner. As an organizer, one of the things that made the conference very exciting for me was the quality  (more…)