At the 2ndQuadrant PostgreSQL conference in New York City this year, I had the pleasure of delivering a training on Postgres-XL together with Andrew Dunstan. We capped the attendees at 8 because we wanted to have a lot of lab time with the students and spend time helping them get set up with Postgres-XL. The class sold out. We had attendees not only from New York City but also from other parts of the United States and one from Europe. I was honored to have folks travel so far to take the course Andrew and I put together, based on work Pavan Deolasee had done previously. We received a lot of positive feedback on the class. Andrew put together a script that enabled all the students to operate on 3 separate 5 node Postgres-XL clusters.
Each of the students could of had their own 5 node
Building an Immortal ClusterBy now we've learned about basic Postgres Docker usage and rudimentary clustering. For the uninitiated, constructing a Postgres cluster can be a daunting task, and we've greatly simplified the process. So why don't we take the next logical step and use Docker to deploy a cluster that is effectively immortal as well? How is that possible? Why, with repmgr of course! 2ndQuadrant has a tool specifically designed to set up and maintain Postgres clusters. One of the components of repmgr is a daemon that can automatically promote replicas whenever the current primary goes down. Let's leverage that to make something that's always online until every node is stopped.My power is in my own handAs before, we're going to need a few scripts to manage the finer points. We can
In our last article, we explored how to run Postgres in some very basic Docker scenarios. Based on our experiments back then, we can obtain images, create containers, and mount to various types of storage.Boring!It's not just boring, it's mundane. It doesn't do anything. Sure we can run Postgres in a container, but that's true about a lot of things. You know what's exciting? Setting up Postgres streaming replication between two docker containers.Let's get started.He say "I know you, you know me"The first thing we need to do is create a container that will be our primary host. Postgres requires a user with REPLICATION permission, as well as a special entry in the pg_hba.conf file for the "replication" pseudo-database. We could start a regular Postgres container, connect to it, and set all
OmniDB is an open source web tool designed specifically for managing relational databases with focus on simplicity and user interaction, but at the same time be fast, powerful and light on memory. OmniDB, at the moment, supports several different relational database systems such as PostgreSQL.
The latest release of OmniDB, version 2.3.0, has introduced ‘Debugger for PL/pgSQL functions’ - a much requested feature for OmniDB users.
If you’re a PostgreSQL user and want to extract the power of its functionalities through a simple GUI tool, here is Simon Riggs from 2ndQuadrant demonstrating how to run OmniDB on top of PostgreSQL 10.
My name is Simon Riggs from 2ndQuadrant, and I’m going to give you a demo of OmniDB running on top of Postgres 10.
What is OmniDB
You're clever, which means you're mostly right about things. But everybody is wrong sometime, so how long does it take for you to change your mind?
People don't often change their minds quickly. A snap answer is always whatever you were thinking currently. If it was a No, you say No. If it was a Yes, you say Yes. If you answer too quickly you can't possibly have taken in what was being said to you.
Can you change your mind?
"When the facts change, I change my mind. What do you do, sir?", often misattributed to John Maynard Keynes.
For Humans, changing your mind based on new information takes days or months. If you have emotional objections, it can take months or years. If anyone has research on that, I'd be interested
OmniDB's 2.3.0 release, which is coming out on November 2nd, comes with a much requested feature: a debugger for PL/pgSQL functions. This post will walk you through the steps to start debugging your functions inside OmniDB.
The debugger takes advantage of hooks, an extensibility in PostgreSQL's source code that allows us to perform custom actions when specific actions are performed in the database. For the debugger we use hooks that are triggered when PL/pgSQL functions are called, and each statement is executed.
This requires the user to install a binary library and enable it in PostgreSQL's config file.
The debugger also uses a special schema with special tables to control the whole debugging process. This can be manually created or with an extension.
Sometimes people ask about certain Oracle High Availability features and whether they exist in PostgreSQL.
In most cases, very similar features exist. The reason for the similarity is that PostgreSQL and Oracle have very similar architectures and so the mechanisms to protect data have also developed along the same lines.
For example, Oracle Data Guard is streaming replication of the transaction log, so is very similar in concept to physical streaming replication in PostgreSQL. Active Data Guard is where Oracle users can run queries on a standby server, which again is similar in concept to Hot Standby.
Oracle RAC? You don't really need Oracle RAC. Or at least qualified Oracle experts tell me so and my own hands on experience confirms that. Not least because disaster recovery for
Fans of Rapid Application Development (RAD!) are probably already familiar with Docker, but what does that have to do with Postgres? Database-driven applications are a dime a dozen these days, and a good RAD environment is something of a Holy Grail to coders and QA departments alike. Docker lets us spin up a Postgres instance in seconds, and discard it with a clean conscience.
There have even been some noises within certain circles about using it in a production context. Can we do something like that responsibly? Docker containers are practically guaranteed to be ephemeral, while production data most decidedly isn't. The answer to this is ultimately complex, and something we'll be exploring over the next several weeks.
Let's get started.
Let There Be Light
Since Docker itself is a
pglogical is an extension that provides an advanced logical replication system that serves as a highly efficient method of replicating data as an alternative to physical replication.
Here we will show how to build a test environment to play with PostgreSQL and pglogical, and how to configure it using the OmniDB 2.2 web interface.
2. Building test environment
Let's build a 2-node test environment to illustrate how to configure PostgreSQL and pglogical 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
Postgres-XL (or just XL, for short) is an open source project from 2ndQuadrant. It is a massively parallel database built on top of PostgreSQL, and it is designed to be horizontally scalable and flexible enough to handle various workloads.
Here we will show how to build a test environment to play with XL and how to configure it using the OmniDB 2.2 web interface.
2. Building test environment
In this experiment, we will build a cluster with 1 GTM, 1 coordinator and 2 data nodes. It would be simpler to put them in the same virtual machine, however split them across multiple virtual machines is a more realistic scenario. So we will build 3 virtual machines:
GTM and coordinator