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
There's a lot of information out there on how to configure PostgreSQL, on the importance of backups and testing them, etc.
But what about the server you run PostgreSQL on? We tend to pay a lot less attention to tools that you won't need unless something breaks. But it's worth taking some time to do so now, because that's time you won't have when your server is down and you're in a rush.
Debuginfo and gdb
Seriously consider installing debug-info packages for the major services you run on a server, and for the libraries that service uses. It wastes some disk space, but it saves you a lot of time if you end up needing that information in a rush.
Debug info lets tools like the GNU Debugger gdb show variables, program arguments, etc when it is connected to a running program, or to a
Someone recently tweeted about the fantastic news that MySQL fixed a bug.
Now in my world, bugs get fixed quickly and well. Bugs happen and they need to be fixed. It never occurred to me that we should ever tweet or blog about the fixing of a bug. I guess I assume it's just quality: bugs get fixed, no drama - people depend upon us to do just that so that the (literally) millions of PostgreSQL servers out there run well. That's our job and I'm happy and proud to do that job alongside my colleagues at 2ndQuadrant and my colleagues in other PostgreSQL companies and in the wider community.
So the bug in question was "number 199"... check this out
It's always been a big argument in the PostgreSQL community about whether we need a bug tracker