Blog | Data Data

Using Kubernetes Deployments for Running PostgreSQL

Running PostgreSQL databases in containerized environments is more popular than ever and is moving beyond running only in local, development environments and into large scale production environments. To answer the need to orchestrate complex, database workloads, the Crunchy Data team created the PostgreSQL Operator to automate many typical database administrator tasks at scale:

 

  • Provisioning new PostgreSQL clusters
  • Scaling up replicas
  • Setup and manage disaster recovery, high-availability, and monitoring
  • Allocate resources (memory, CPU, etc.) and suggest nodes for databases to run on
  • Mass applying user policies
  • Performing major/minor upgrades

and more. In order to keep all of its PostgreSQL databases up and running, the PostgreSQL Operator uses Kubernetes Deployments, which provides an API to manage replicated applications. In order to understand why this is, first we need to understand about running stateful applications with Kubernetes.

Troubleshooting PostgreSQL Streaming Replication Synchronization

Having replicas in a database environment can fulfill many goals: create a high-availability environment, load balance read-only queries across several nodes, have a read-only database to run analytical queries on, and more. PostgreSQL introduced streaming replication in PostgreSQL 9.0 almost 8 years ago to help facilitate satisfying the above requirements, and through the years, the community has added many features to continue to enhance the replication user experience.

There are many different ways to configure streaming replication in PostgreSQL, but fundamentally you need to consider if you want to have asynchronous or synchronous replication. With asynchronous replication, the default in PostgreSQL, a transaction is considered complete when it is committed on the primary database; the replica databases will replay and consume the transaction on their own time. With synchronous replication, the transaction will not be considered complete until it has been committed on all of the replicas (though PostgreSQL 10 introduced “quorum commit” which allows you to configure how many replicas need to commit the transaction before it’s considered complete).

Choosing which form of replication you need for your environment is out-of-scope for this article. Rather, we will look at what happens when a replica becomes out-of-sync with a primary, what to do, and how you can avoid this situation. Credits to Doug Hunley for originally compiling this guide.

Detecting An Out-of-Sync Replica

Upgrading PostgreSQL from 9.4 to 10.3 with pglogical

April 22, 2018

Managing PostgreSQL

I recently helped a customer upgrade a PostgreSQL instance from 9.4 on RHEL to 10.x on Ubuntu. While it initially sounded daunting, the use of pglogical and some planning actually made it rather straightforward. While there’s nothing new or original in this post, I still felt compelled to write it up for anyone else that might find the info useful as an example in their own endeavors.

pglogical is a logical replication system implemented entirely as a PostgreSQL extension. Fully integrated, it requires no triggers or external programs. This makes it faster than Slony, Londiste, et al. It is also (roughly) the basis upon which logical replication in PostgreSQL 10 core is built.

pgAudit: Auditing Database Operations Part 2

 

In the last blog post, pgAudit was configured to audit entire classes of statements (session auditing). Session auditing works great, but it can generate a lot of logs and not every administrator needs all that information. In this blog post pgAudit will be configured to use an auditing role to watch only specific objects.

pgAudit: Auditing Database Operations Part 1

 The PostgreSQL Audit extension (pgaudit) provides detailed session and/or object audit logging via the standard PostgreSQL logging facility.

pgBackRest - Reliable PostgreSQL Backup & Restore

September 20, 2016

Managing PostgreSQL

In our ongoing series of blog posts designed to help you better run, manage, and support PostgreSQL, today we have a post discussing pgBackRest, a powerful open source tool for managing backups and restores of PostgreSQL databases...