Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more

  • 12 min read

    How To Migrate From Trigger-Based Partitioning To Native in PostgreSQL

    Keith Fiske

    PostgreSQL 10 introduced native partitioning and more recent versions have continued to improve upon this feature. However, many people set up partition sets before native partitioning was available and would greatly benefit from migrating to it. This article will cover how to migrate a partition set using the old method of triggers/inheritance/constraints to a partition set using the native features found in PostgreSQL 11+. Note these instructions do not cover migrating to PG10 since some key f...

    Read More
  • Guard Against Transaction Loss with PostgreSQL Synchronous Replication

    Andrew L'Ecuyer

    Crunchy Data recently released its latest version of the open source PostgreSQL Operator for Kubernetes , version 4.2. Among the various enhancements included within this release is support for Synchronous Replication within deployed PostgreSQL clusters. As discussed in our prior post , the PostgreSQL Operator 4.2 release introduces distributed consensus based high-availability . For workloads that are sensitive to transaction loss, the Crunchy PostgreSQL Operator supports PostgreSQL synchro...

    Read More
  • 5 min read

    Scheduled PostgreSQL Backups and Retention Policies with Kubernetes

    Jonathan S. Katz

    It is important (understatement) that you take regularly scheduled backups of your PostgreSQL system as well as manage how many backups you have, which is known as "backup retention." These best practices ensure that you always have a recent backup of your database system to recover from in the event of a disaster (or use to clone a new copy of your database ) and that you don't run out of storage on your backup device or blow up your object storage bill (true story from a previous life, I ha...

    Read More
  • Migrating from Oracle to PostgreSQL: Tips and Tricks

    Yorvi Arias

    Migrating to PostgreSQL from Oracle is a topic that often comes up in discussions around PostgreSQL. At Crunchy Data, we are of course not surprised that there is broad interest in moving to the world's most advanced database. There are a lot of great reasons to migrate to PostgreSQL, and if you haven't looked at PostgreSQL lately, it would be a good time to do so again given the various improvements in PostgreSQL 12. That said, migrating from one database to another is inherently new technology...

    Read More
  • 9 min read

    Deploy High Availability PostgreSQL Clusters on Kubernetes by Example

    Crunchy Data

    One of the great things about PostgreSQL is its reliability: it is very stable and typically “just works.” However, there are certain things that can happen in the environment that PostgreSQL is deployed in that can affect its uptime, such as: • The database storage disk fails or some other hardware failure occurs • The network on which the database resides becomes unreachable • The host operating system becomes unstable and crashes • A key database file becomes corrupted • A data center is lost...

    Read More
  • How To Simplify pgBouncer Monitoring with pgbouncer_fdw

    Keith Fiske

    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...

    Read More
  • 10 min read

    How To Improve PgBouncer Security with TLS/SSL

    David Youatt

    PgBouncer is a commonly deployed and recommended connection pooler for PostgreSQL. It supports a number of authentication methods including TLS/SSL client certificate authentication. Since PgBouncer is located logically between the client and PostgreSQL you have the option of using TLS and cert authentication from client to PgBouncer and from PgBouncer to PostgreSQL. In this brief blog post, we’ll describe configuring securing the client-to-PgBouncer transport first, then build on that to use cl...

    Read More
  • How to Perform a Major Version Upgrade Using pg_upgrade in PostgreSQL

    James Chanco Jr.

    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 b...

    Read More
  • Hope Is Not A Strategy: How Hardware Issues Affect Your PostgreSQL Data

    Joe Conway

    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 , which alerts that certain models of HPE SSDs have a firmware bug that will cause the drives to deterministically fail very suddenly at precisely 32,768 hours of operation. You may recognize this as a...

    Read More
  • How To Get Started with pgBackRest and PostgreSQL 12

    Tom Swartz

    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 a...

    Read More