Posts about Production Postgres

For connection pooling in PostgreSQL, one of the best and most popular tools out there is PgBouncer. However, monitoring PgBouncer can be challenging due to its use of SHOW commands, which are only available via a special database connection as opposed to making its statistics available via a standard table or view.

In order to more easily monitor PgBouncer, the team at Crunchy Data developed an open source PgBouncer Foreign Data Wrapper (pgbouncer_fdw). This blog post describes why monitoring PgBouncer is important, and how you can do this easily using pgMonitor

Odds are you've been tasked with upgrading software from one major release to another at some point. Trust me, I understand how cumbersome and difficult these upgrades can be! Luckily, Crunchy Data has some tested methods to help get you upgraded with the least amount of headache possible! For this use case, we’ll be using pg_upgrade. Let’s get started!

This part is critical to a successful and healthy upgrade: read the release notes. Sometimes, even within minor upgrades, additional steps may be required and you won’t know if this is the case unless you read the release notes or risk running into an issue during the upgrade. Usually, you won’t have any extra steps to perform, but sometimes things like rebuilding indexes or changing paths may be needed. Again, be sure to read the release notes to avoid issues when implementing the upgrade.

In this instance, we’ll be using pg_upgrade to upgrade from Crunchy Certified PostgreSQL 11 to Crunchy Certified PostgreSQL 12 on CentOS 7. I choose pg_upgrade due to the upgrade speed. It’s literally done in minutes most of the time. If you’ve already tried to guess where the pg_upgrade binary is located and guessed /usr/pgsql-##/bin, then congratulations! You’re right! pg_upgrade can be found in the same default location that you’ll find initdb, pg_ctl, etc in.

After the appropriate release notes have been read thoroughly, it’s time to get upgrading!

Before we get underway with the actual pg_upgrade command and implementation, we should take a backup using our favorite backup tool. As a pgBackRest advocate, I would suggest using pgBackRest. Some details on getting pgBackRest setup can be found here

For anyone following my previous blog posts, this is a bit of a departure for me. Typically, I get down in the weeds and show lots of code. This post, on the other hand, is more of a philosophical rant. At least you can't say I didn't warn you!

Yesterday I was made aware of this bulletin from HPE

pgBackRest is a reliable and simple to configure backup and restore solution for PostgreSQL, which provides a powerful solution for any PostgreSQL database; be it a small project, or scaled up to enterprise-level use cases.

Many powerful features are included in pgBackRest, including parallel backup and restore, local or remote operation, full, incremental, and differential backup types, backup rotation, archive expiration, backup integrity, page checksums, backup resume, streaming compression and checksums, delta restore, and much more.

With the recent release of PostgreSQL 12 (and more recently 12.1), pgBackRest also received a number of updates and changes to take advantage of the latest features of Postgres.

On October 1st 2019, pgBackRest released version 2.18 which is the first release of pgBackRest to support PostgreSQL 12. As such, any deployment using PostgreSQL 12 where pgBackRest will be used requires version 2.18 or greater.

As you can see from my previous blogs (A Guide to Building an Active-Active PostgreSQL Cluster) I’m interested in the ways that we can replicate data in PostgreSQL. For this post, I've decided to write about a product that enabled replicating heterogeneous databases.

Through my involvement in the PostgreSQL JDBC project, I’ve had the opportunity to help out the folks in the Debezium

PostgreSQL 12, the latest version of the "world's most advanced open source relational database," is being released in the next few weeks, barring any setbacks. This follows the project's cadence of providing a raft of new database features once a year, which is quite frankly, amazing and one of the reasons why I wanted to be involved in the PostgreSQL community.

In my opinion, and this is a departure from previous years, PostgreSQL 12 does not contain one or two single features that everyone can point to and say that "this is the 'FEATURE' release," (partitioning and query parallelism are recent examples that spring to mind). I've half-joked that the theme of this release should be "PostgreSQL 12: Now More Stable" -- which of course is not a bad thing when you are managing mission critical data for your business.

And yet, I believe this release is a lot more than that: many of the features and enhancements in PostgreSQL 12 will just make your applications run better without doing any work other than upgrading!

Version 2.28 (release notes) of the GNU C library introduces many changes to the collations it provides. Collations determine how strings are compared and by default, PostgreSQL uses the operating system’s collations which on Linux means glibC. When your operating system updates to this version of glibc and you aren't using the “C” or “POSIX” collation, you may encounter some differently ordered indexes. This unexpected change in the order of indexes will lead to incorrectly ordered query results and possible data corruption. Currently, the following distributions are affected:

• Ubuntu 18.10 (cosmic)
• RHEL/CentOS 8
• Debian 10 (buster)

One of the most important things to using PostgreSQL successfully in your development and production environments is simply getting started! One of the most popular ways to install PostgreSQL is by using RPM packages. The PostgreSQL RPM packages work across many Linux distributions, including, RedHat Enterprise Linux

PostgreSQL provides a bevy of authentication methods to allow you to pick the one that makes the most sense for your environment. One desired implementation that I have found customers wanting is to use Windows Active Directory

pgBackRest is an open source tool designed for efficiently performing backups and restores for PostgreSQL on up to terabytes of data at a time. Its aim is to be reliable, scalable, and flexible while offering robust performance during the backup process.

Many powerful features are included in pgBackRest, including parallel backup and restore