Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Dave Cramer
Dave Cramer
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 project. Debezium is more than just another heterogeneous replication solution. Debezium is built upon the Ap...
Read MoreJonathan S. Katz
Jonathan S. Katz
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 c...
Read MoreDavid Thomas
David Thomas
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 resu...
Read MoreYogesh Sharma
Yogesh Sharma
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 ( RHEL ), CentOS, Fedora, Scientific Linux, and more, and the PostgreSQL community provides installers for these distributions. This guide will help you get started with install...
Read MoreStephen Frost
Stephen Frost
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 with PostgreSQL's GSSAPI authentication interface using Kerberos . I've put together this guide to help you take advantage of this setup in your own environment. The first step in setting up a Windows Active Directory is to create a regular user account. The passw...
Read MoreSarah Conway Schnurr
Sarah Conway Schnurr
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 , local or remote operation, full, incremental, and differential backup types , backup rotation, archive expiration, backup integrity, page checksums,...
Read MoreJoe Conway
Joe Conway
I want to take a few minutes for a deep dive into the effect your data model has on storage density when using PostgreSQL . When this topic came up with a customer, I explained my thoughts on the matter, but I realized at the time that I had never done a reasonably careful apples-to-apples test to see just exactly what the effect is, at least for a model sample size of one. So here it is. Let's start out with a presumed use-case. I have two tables: one is a parent table and the other a detail t...
Read MoreKeith Fiske
Keith Fiske
One of the most critical topics to understand when administering a PostgreSQL database is the concept of transaction IDs ( TXID ) and that they can be exhausted if not monitored properly. However, this blog post isn't going to go into the details of what it TXID exhaustion actually is. The Routine Vacuuming section of the documentation is probably one of the most important to read and understand so I will refer you there. What this blog post is going to cover is an easy way to monitor for it...
Read MoreDave Cramer
Dave Cramer
One of the toughest challenges facing database engineers today is ensuring their data is always accessible so they can meet the high-availability requirements for their applications. While this problem may seem trivial in the world of applications where one can have many instances behind geographically distributed load balancers, in the database world where there is only one version of the data globally, the problem faces many obstacles. PostgreSQL replication has advanced considerably in rece...
Read MoreDavid Thomas
David Thomas
For most major upgrades using a utility such as pg_upgrade or a replication tool such as pglogical will be the best solution. However if these options are not available, pg_dumpall can be used to perform a major upgrade. What follows is a guide on how you can safely upgrade your database to a newer version of PostgreSQL with pg_dumpall. You will first need to install the latest PostgreSQL binaries on the new system (hostname new in this example). Once the binaries are installed you will ne...
Read More