Posts about Production Postgres

Prometheus is a popular open source monitoring tool and we have many customers that leverage it when using the Crunchy PostgreSQL Operator or Crunchy PostgreSQL High Availability

I'll admit it: I used to have triskaidekaphobia, otherwise known as a "fear or avoidance of the number 13." After all, I grew up in an area with many tall buildings where the elevators have no 13th floor

More and more frequently, customers are being given access to company databases for purposes of account management, receiving customer support, or placing and tracking an order. Although this provides great convenience for the end user, it also opens the database up to certain vulnerabilities

Backups are a key staple of running any database. Way back in the day, a good friend and colleague wrote one of the most used Postgres backup tools called wal-e. Wal-e was initially written in just a few days, and rolled out to the fleet of databases we managed in the early days at Heroku. We got pretty lucky with rolling that out, because shortly after we had there was the great AWS Apocalypse of 2011. This was a full day outage of AWS with lingering effects for nearly a week... Reddit was down, Netflix was down, so you couldn't even kill time waiting for things to come back up. At the time, AWS came back to us saying they couldn't recover a number of disks. Had it not been for wal-e and our disaster recovery setup customers would have lost data. Luckily no bytes of data were lost, and customers were back up and running much faster than had they been on RDS.

Fast forward nearly 10 years and now there are numerous backup options for Postgres. And while wal-e was a great tool for it's time and place, it hasn't materially changed in the last 10 years. Enter pgBackRest

An important part of performing a migration from Oracle to PostgreSQL is selecting the right tool for helping with the conversion between systems. When people ask me how they can get the migration process started, I like to recommend a powerful open source utility called "ora2pg

Creation and clean up of WAL files in the primary's pg_wal folder (pg_xlog prior to PG10) is a normal part of PostgreSQL operation. The WAL files on the primary are used to ensure data consistency during crash recovery. Use of write-ahead logs (also called redo logs or transaction logs in other products) is common for data stores that must provide durability and consistency of data when writing to storage. The same technique is used in modern journaling and log-structured filesystems.

As the DB is operating, blocks of data are first written serially and synchronously as WAL files, then some time later, usually a very short time later, written to the DB data files. Once the data contained in these WAL files has been flushed out to their final destination in the data files, they are no longer needed by the primary. At some point, depending on your configuration, the primary will remove or recycle the WAL files whose data has been committed to the DB. This is necessary to keep the primary's disk from filling up. However, these WAL files are also what streaming replicas read when they are replicating data from the primary. If the replica is able to keep up with the primary, using these WAL files generally isn't an issue.

If the replica falls behind or is disconnected from the primary for an extended period of time, the primary may have already removed or recycled the WAL file(s) that a replica needs (but see Streaming Replication Slots

Most queries against a database are short lived. Whether you're inserting a new record or querying for a list of upcoming tasks for a user, you're not typically aggregating millions of records or sending back thousands of rows to the end user. A typical short lived query in Postgres can easily be accomplished in a few milliseconds or less. For the typical application, this means a well tuned production Postgres

By design, the out of the box configuration for PostgreSQL is defined to be a "Jack of All Trades, Master of None". The default configuration for PostgreSQL is fairly painstakingly chosen to ensure that it will run on every environment it is installed, meeting the lowest common denominator resources across most platforms.

Because of this, it's always recommended that one of the first actions performed once an install of PostgreSQL is completed, would be to tune and configure some high-level settings.

There are four high-level settings which will be discussed here: shared_buffers

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

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 and can raise a multitude of questions. To help ease the transition, we are providing a few frequently asked questions and answers from users transitioning from Oracle to PostgreSQL, based on real life scenarios, to serve as a helpful resource.

Common Questions, Differences and Solutions