pgstef's blog

SELECT * FROM pgstef

Home About me Talks View on GitHub

Custom PGDATA with systemd

By default, on CentOS 7, the PostgreSQL v10 data directory is located in /var/lib/pgsql/10/data.

Here’s a simple trick to easily place it somewhere else without using symbolic links.

Read More

PAF cluster management

PAF (aka. “PostgreSQL Automatic Failover” : is a Resource Agent providing service High Availability for PostgreSQL, based on Pacemaker and Corosync.

In the previous post, we saw how to quickly install it. Let’s see now how to manage it.

Read More

Introduction to PostgreSQL Automatic Failover

As described by Magnus Hagander during his great talk about “PostgreSQL Replication in 2018” at the last FOSDEM event, PAF (aka. “PostgreSQL Automatic Failover” : is a Resource Agent providing service High Availability for PostgreSQL, based on Pacemaker and Corosync.

If you have good system skills and wish a reliable way of having automatic failover, you should definitively consider using PAF!

Let’s see in this post how to quickly install it.

Read More

PostgreSQL 11 preview - update a partition key

In the case of a partitioned table, updating a row might cause it to no longer satisfy the partition constraint of the containing partition.

There’s a recent commit for version 11 modifying PostgreSQL behavior in that case.

Read More

Filter out pg_dump from check_pgactivity alerts

check_pgactivity ( is designed to monitor PostgreSQL clusters from Nagios. It offers many options to measure and monitor useful performance metrics.

Imagine you have a very large database and pg_dump produce abnormal query time alerts. The upcoming release of check_pgactivity offers a way to filter out those alerts !

Read More

introduction to pgBackRest

pgBackRest ( aims to be a simple, reliable backup and restore system that can seamlessly scale up to the largest databases and workloads.

Instead of relying on traditional backup tools like tar and rsync, pgBackRest implements all backup features internally and uses a custom protocol for communicating with remote systems. Removing reliance on tar and rsync allows for better solutions to database-specific backup challenges. The custom remote protocol allows for more flexibility and limits the types of connections that are required to perform a backup which increases security.

Read More

combine pgreplay with pgBadger

pgreplay, written by Laurenz Albe, reads a PostgreSQL log file, extracts the SQL statements and executes them in the same order and with the original timing against a PostgreSQL database.

While pgreplay will find out if your database application will encounter performance problems, it does not provide a lot of help in the analysis of the cause of these problems. Combine pgreplay with a specialized analysis program like pgBadger ( for that.

Read More