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…)
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…)
Last week, I was at Nordic PGDay 2018 and I had quite a few conversations about the tool that I wrote, namely pglupgrade, to automate PostgreSQL major version upgrades in a replication cluster setup. I was quite happy that it has been heard and some other people in different communities giving talks at meetups and other conferences about near-zero downtime upgrades using logical replication. Given that there is a talk that I gave at PGDAY'17 Russia, PGConf.EU 2017 in Warsaw and lastly at FOSDEM PGDay 2018 in Brussels, I thought it is better to create a blog post to keep this presentation available to the folks who could not make it to any of the conferences aforementioned. If you would like to directly go the talk and skip reading this blog post here is your link: Near-Zero Downtime (more…)
The short answer … Hell Yeah!
The long answer lies in extensive improvements and the impressive new feature list that makes up this major release - which, by the way, changes the version scheme of PostgreSQL as well (more details on that here). This wiki page lists out, in detail, all the new features in PostgreSQL 10, but for the purpose of this blog, I will focus on some of the exciting features contributed by 2ndQuadrant. (more…)
PostgreSQL 10 offers an exciting new set of features in addition to making further improvements to many of the already existing features including Big Data, Replication and Scaling, Administration, SQL, XML and JSON, Security, Performance and a lot more.
If you are planning to try your hands at PostgreSQL 10 and wondering on how you can easily get it on your machine, 2ndQuadrant’s GUI installers 2UDA can help you with an easier installation of PostgreSQL 10 for Windows, OS X and Linux platforms.
2ndQuadrant is a Platinum Sponsor of the PostgreSQL project and is committed to following community timelines for all releases; major & minor. This ensures that 2UDA releases are always up to date and available in a timely manner. With 2UDA's builtin upgrade feature for minor releases, it (more…)
Postgres-BDR (or just BDR, for short) is an open source project from 2ndQuadrant that provides multi-master features for PostgreSQL.
Here we will show how to build a test environment to play with BDR and how to configure it using the OmniDB 2.1 web interface.
2. Building test environment
Let's build a 2-node test environment to illustrate how to configure BDR within OmniDB.
2.1. Pull OmniDB repo
The first thing you need to do is to download OmniDB repo from GitHub and make sure you are in the development branch. Run the following:
git clone https://github.com/OmniDB/OmniDB
git checkout dev
2.2. Create 2 virtual machines with BDR
On your host machine, you need to have installed:
Vagrant plugin (more…)
One feature quietly added to PostgreSQL 10 is the ability to determine the commit status of any transaction by transaction-id.
It's reasonable to wonder why you'd want this, since you know if you committed the transaction, it's still in progress, or if you or rolled it back. And you can check for in-progress transactions in pg_stat_activity.
It exists to help the application recover to a known state after a failure without having to use heavyweight two-phase commit. It's also useful for querying standbys.
Imagine that your application has just sent the COMMIT for a transaction that's part of a queue processing system. Before the application receives a reply to its commit request, the database connection breaks due to network issues, a database crash, etc. It's possible (more…)
During the Postgres Open 2017 conference in San Francisco, someone came to the 2ndQuadrant booth and struck up a conversation with me. During our shameless geeking out over database mechanics, he asked me if pglogical supported the new Postgres 10 partitions. Given my noted expertise in all things Postgres, I answered in the appropriate manner:
"I have no idea. I'll have to look into that."
Well, after a bit of experimentation, I have a more concrete answer, and it's reassuringly positive.
Given a table on a provider node, is it possible to capture only INSERT traffic such that it accumulates on a subscribed system for archival purposes? It's a fairly common tactic, and allows an active OLTP system to regularly purge old data, while a reporting OLAP system keeps it (more…)
Logical replication uses a publish/subscribe model and so we create publications on the upstream (or publisher) and subscriptions on downstream (or subscriber). For more details about it, please refer to this blog post from my colleague Petr Jelinek, and also to the PostgreSQL documentation.
Here we will show how to build a test environment to play with this new feature from PostgreSQL 10, and how to configure it using OmniDB 2.1.
2. Building test environment
Let's build a 2-node test environment to illustrate how to configure PG10 logical replication feature within OmniDB.
2.1. Pull OmniDB repo
The first thing you need to do is to download OmniDB in the repo from GitHub and make sure you are in the development branch. Run the following:
2.2. Create 2 virtual (more…)