pgstef's blog

SELECT * FROM pgstef

Home About me Talks PITR tools View on GitHub
2019-03-26

pgBackRest archiving tricks

pgBackRest is a well-known powerful backup and restore tool.

While the documentation describes all the parameters, it’s not always that simple to imagine what you can really do with it.

In this post, I will introduce the asynchronous archiving and the possibility to avoid PostgreSQL to go down in case of archiving problems.

With its “info” command, for performance reasons, pgBackRest doesn’t check that all the needed WAL segments are still present. check_pgbackrest is clearly built for that. The two tricks mentioned above can produce gaps in the archived WAL segments. The new 1.5 release of check_pgbackrest provides ways to handle that, we’ll also see how.

Read More
2019-03-01

PostgreSQL major version upgrade impact on pgBackRest

pgBackRest is a well-known powerful backup and restore tool.

While it works with a really simple configuration, a major version upgrade of PostgreSQL has some impact on it.

Immediately after upgrading PostgreSQL to a newer major version, the pg-path for all pgBackRest configurations must be set to the new database location and the stanza-upgrade command must be run.

That command updates the stanza information to reflect the new cluster information and, for example, allows to archiving process to work.

Read More
2018-11-28

Combining pgBackRest and Streaming Replication

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.

Read More
2018-11-26

PostgreSQL 12 preview - recovery.conf disappears

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.

Read More
2018-02-28

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
2018-02-09

PAF cluster management

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.

Read More
2018-02-07

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

Read More
2018-01-23

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