Posts about Production Postgres

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.

Base Use Case

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

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 recent major releases, including continuous improvements to streaming replication and the addition of logical replication in PostgreSQL 10. While these capabilities represent important enhancements to PostgreSQL, enabling users to address a wide variety of clustered architectures, they do not (easily) address the use cases where the application requires access to an updatable database in two or more geographic regions - often referred to as an "active-active" cluster.

More specifically, an active-active cluster is one where the application can write to any instance in the cluster and the data will be written to all of the instances in the cluster, enabling each instance in the cluster to be used to:

• Provide near zero downtime as the new instance is already in a read/write state; there is no need to reconfigure it.

• Provide near zero downtime upgrades from one version to another

• Improve latency for users in geographically distributed clusters. By providing an instance physically closer to the user latency is reduced.

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

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

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

I’ve been noticing that the query used in v1.x of my pg_bloat_check.py script (obtained from the check_postgres.pl module) was not always accurate and was often not reporting on bloat that I knew for a fact was there (Ex: I just deleted over 300 million rows, vacuumed & analyzed the table and still no bloat? Sure it could happen, but highly unlikely). So I continued looking around and discovered the pgstattuple

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.

Getting Started

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

Basic statement logging can be provided by the standard logging facility in PostgreSQL. Out of the box logging provided by PostgreSQL is acceptable for monitoring and other usages but does not provide the level of detail generally required for an audit.

pgAudit enhances PostgreSQL's logging abilities by allowing administrators to audit specific classes of operations or choosing specific objects to monitor.

Getting Started

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

Introduction

pgBackRest