Brian Pace
Brian Pace
Postgres' ability to execute queries in parallel is a powerful feature that can significantly improve query performance, especially on large datasets. However, like all resources, parallel workers are finite. When there aren't enough available workers, Postgres may downgrade a parallel query to a serial (non-parallel) execution. This sounds reasonable unless the performance of the downgraded query is well beyond the required response times needed by the application. While helping our clients w...
Read MoreBrian Pace
Brian Pace
Backups are dead. Now that I have your attention, let me clarify. Traditional backups have earned a solid reputation for their reliability over time. However, they are dead in the sense that a backup is essentially useless until it's restored—essentially "resurrected." In this post, we'll explore best practices for managing PostgreSQL snapshots and backups using pgBackRest . We will then provide some guidance of how you apply these techniques in Kubernetes using the Postgres Operator (PGO) from...
Read MoreBrian Pace
Brian Pace
In the evolving world of data management, ensuring consistency and accuracy across multiple database systems is paramount. Whether you're migrating data, synchronizing systems, or performing routine audits, the ability to compare data across different database platforms is crucial. Enter pgCompare , an open-source tool designed to simplify and enhance the process of data comparison across PostgreSQL, Oracle, MySQL, and MSSQL databases. The key features of pgCompare: • Multi-Database support : p...
Read MoreBrian Pace
Brian Pace
PostgreSQL uses the concept of a timeline to identify a series of WAL records in space and time. Each timeline is identified by a number, a decimal in some places, hexadecimal in others. Each time a database is recovered using point in time recovery and sometimes during standby/replica promotion, a new timeline is generated. A common mistake is to assume that a higher timeline number is synonymous with the most recent data. While the highest timeline points to the latest incarnation of the datab...
Read MoreBrian Pace
Brian Pace
Support for logical replication arrived at Postgres just over five years ago with Postgres 10. Since then it's had a steady stream of improvements, but mostly logical replication has been limited to migrating data or unidirectional change data capture workflows. With Postgres 16 freshly released today, Postgres now has a better foundation to leverage logical replication for active-active setups. If you're unfamiliar with the concepts of logical replication or what does active-active mean we've g...
Read MoreBrian Pace
Brian Pace
The Postgres Write Ahead Log ( WAL ) is a functional component to the database. WAL makes a lot of key functionality possible, like Point-in-Time-Recovery backups , recovering from an event , streaming replication , and more. From time to time, those deep inside the database will need to work directly with WAL files to diagnose or recover . Recently in working with one of Crunchy Data's customers, I came across a situation where understanding the names and sequence numbers was important....
Read MoreBrian Pace
Brian Pace
Getting frequent copies of data for development and testing purposes is a very common use case with our enterprise customer base. We have customers getting data copes for each sprint or development cycle. This increasing data copy problem can put a strain on IT budgets with the storage consumed and the hours spent performing database refreshes. A common process is to build or refresh environments using database restores. There can be some challenges with this approach. First, the data must be mo...
Read MoreBrian Pace
Brian Pace
If you're checking archives or working with Postgres replication, data reconciliation can be a necessary task. Row counts can be one of the go to comparison methods but that does not show data mismatches. You could pull table data across the network and then compare each row and each field, but that can be a demand on resources. Today we'll walk through a simple solution for your Postgres toolbox - using Foreign Data Wrappers to connect and compare the two source datasets. With the foreign data...
Read MoreBrian Pace
Brian Pace
UPDATE TO THIS CONTENT: Since releasing this article, newer versions of Crunchy Postgres for Kubernetes have additional features for streaming replication across clusters. See our post that accompanied the release: Multi-Cloud Strategies with Crunchy Postgres for Kubernetes . Read the disclaimer above, as this content is now out of date. A common need when running PostgreSQL in Kubernetes is to establish a standby database in a different Kubernetes cluster. In a typical configuration there...
Read MoreBrian Pace
Brian Pace
As a Solutions Architect at Crunchy Data, I work directly with customers testing and deploying Crunchy Postgres for Kubernetes . I often see our customers fully removing a PGO cluster during testing or migrations and still needing to keep their storage and backups intact. In this post, I will dig into the steps to removing your PGO cluster while keeping your storage and backups. With the move to a declarative approach in 5.0 PGO, storage and backup retention can be accomplished by following a...
Read More