Tuesday, October 24

PG Phriday: Getting RAD with Docker [Part 1]

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

pglogical with OmniDB

OmniDB, pglogical, William's PlanetPostgreSQL
1. Introduction 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 cd OmniDB git checkout dev 2.2. Create 2 virtual machines with (more…)

Postgres-XL with OmniDB

OmniDB, William's PlanetPostgreSQL
1. Introduction 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: Machine IP Role xl_gtmcoord 192.168.56.105 GTM and coordinator xl_datanode1 192.168.56. (more…)

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