pgBackRest is a well-known powerful backup and restore tool.
Relying on the status information given by the “info” command, we’ve build a specific plugin for Nagios : check_pgbackrest.
This post will help you discover this plugin and assume you already know pgBackRest and Nagios.
pgBackRest is a well-known powerful backup and restore tool. It offers a lot of possibilities.
While pg_basebackup is commonly used to setup the initial database copy for the Streaming Replication, it could be interesting to reuse a previous database backup (eg. taken with pgBackRest) to perform this initial copy.
Furthermore, the --delta option provided by pgBackRest can help us to
re-synchronize an old secondary server without having to rebuild it from
scratch.
To reduce the load on the primary server during a backup, pgBackRest even allows to take backups from a standby server.
We’ll see in this blog post how to do that.
PostgreSQL needs some infrastructure changes to have a more dynamic reconfiguration around recovery, eg. to change the primary_conninfo at runtime.
The first step, mostly to avoid having to duplicate the GUC logic, results on the following patch.
PGDay Amsterdam regrouped more than 90 PostgreSQL fans (according to Devrim) on 12 July 2018. It was for me a really nice day and I’d like to share it with you.
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.
PAF (aka. “PostgreSQL Automatic Failover” : http://clusterlabs.github.io/PAF/) 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.
As described by Magnus Hagander during his great talk about “PostgreSQL Replication in 2018” at the last FOSDEM event, PAF (aka. “PostgreSQL Automatic Failover” : http://clusterlabs.github.io/PAF/) 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.
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.
check_pgactivity (https://github.com/OPMDG/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 !
Remark: This post has been updated on 2019-07-19, with pgBackRest 2.15
pgBackRest (http://pgbackrest.org/) 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 to communicate with remote systems. Removing reliance on tar and rsync allows better solutions to database-specific backup challenges. The custom remote protocol also allows more flexibility and limits the types of connections that are required to perform a backup which increases security.