Tuesday, January 23

Plan for the unexpected: install diagnostic tools on your PostgreSQL servers

Craig's PlanetPostgreSQL
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 (more…)

Why PostgreSQL is better than MySQL

Simon's PlanetPostgreSQL
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 http://lefred.be/content/bye-bye-bug-199/ It's always been a big argument in the PostgreSQL community about whether we need a bug tracker (more…)

PG10 at the London PostgreSQL Meetup

Simon's PlanetPostgreSQL
We recently hosted the London PostgreSQL Meetup at a new location in Covent Garden. Room was great and its about 100m from Leicester Square tube, so very centrally located and easy to get to. https://www.meetup.com/London-PostgreSQL-Meetup-Group/ I've been the organizer of the group for more than 6 years now, in my role as the PostgreSQL Project's UK Press representative, though a few others have also helped out, principally Bart Swedrowski, though now also Sam Marshall. Anybody interested in PostgreSQL is welcome to come along and we have many speakers from a variety of companies and types of company. Please come along and join in with the other 849 members. At our recent meeting we discussed all the new features of PostgreSQL 10. Lots of interest in the new SCRAM authentication (more…)
v10, The Best PostgreSQL Yet?

v10, The Best PostgreSQL Yet?

Umair's PlanetPostgreSQL
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 easy installation with 2UDA

Haroon's PlanetPostgreSQL
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 with OmniDB

OmniDB, William's PlanetPostgreSQL
1. Introduction 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 cd OmniDB git checkout dev 2.2. Create 2 virtual machines with BDR On your host machine, you need to have installed: VirtualBox Vagrant Vagrant plugin (more…)

Transaction traceability in PostgreSQL 10 with txid_status(…)

Craig's PlanetPostgreSQL
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. Recovery 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…)

PG Phriday: pglogical and Postgres 10 Partitions

pglogical, Shaun's PlanetPostgreSQL
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. The Problem 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…)

Setting up a build machine for Visual Studio 2017

Andrew's PlanetPostgreSQL
Recently we've had a patch submitted to support the latest incarnation of the Microsoft build tools, Visual Studio 2017. I didn't have a spare Windows machine available to test with, so I set up Windows machine on Amazon AWS to test with. I chose Windows Server 2016, a t2.medium instance with a 50 GB root disk (The default 30Gb is a bit tight.) This costs about US$0.065 per hour to run, so it's pretty cheap. The first things I did, as I always do with these machines, were to turn off Internet Enhanced Security, which has a habit of getting in the way, and then install the Firefox web browser. Then I installed the Chocolatey package manager for Windows. This is a pretty useful tool, somewhat similar to yum, dnf and apt. You need to install this via an administrative command shell. Once (more…)

PostgreSQL 10 Logical Replication with OmniDB

OmniDB, William's PlanetPostgreSQL
1. Introduction 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…)