pgstef's blog

SELECT * FROM pgstef

Home About me Talks View on GitHub

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.


For the purpose of this post, I’ll use a fresh CentOS 7 install.

We’ll talk about the stanza-upgrade command of pgBackRest but won’t go deeper in the PostgreSQL configuration, nor in the PostgreSQL major version upgrade best practices.


Installation

First of all, install PostgreSQL and pgBackRest packages directly from the PGDG yum repositories:

$ sudo yum install -y https://download.postgresql.org/pub/repos/yum/10/redhat/\
rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
$ sudo yum install -y postgresql10-server postgresql10-contrib
$ sudo yum install -y pgbackrest

Check that pgBackRest is correctly installed:

$ pgbackrest
pgBackRest 2.10 - General help

Usage:
    pgbackrest [options] [command]

Commands:
    archive-get     Get a WAL segment from the archive.
    archive-push    Push a WAL segment to the archive.
    backup          Backup a database cluster.
    check           Check the configuration.
    expire          Expire backups that exceed retention.
    help            Get help.
    info            Retrieve information about backups.
    restore         Restore a database cluster.
    stanza-create   Create the required stanza data.
    stanza-delete   Delete a stanza.
    stanza-upgrade  Upgrade a stanza.
    start           Allow pgBackRest processes to run.
    stop            Stop pgBackRest processes from running.
    version         Get version.

Use 'pgbackrest help [command]' for more information.

Create a basic PostgreSQL cluster with some data in it:

$ sudo /usr/pgsql-10/bin/postgresql-10-setup initdb
$ sudo systemctl start postgresql-10
$ sudo -iu postgres createdb bench
$ sudo -iu postgres /usr/pgsql-10/bin/pgbench -i -s 100 bench

Configure pgBackRest to backup the local cluster

By default, the configuration file is /etc/pgbackrest.conf. Let’s make a copy:

$ sudo cp /etc/pgbackrest.conf /etc/pgbackrest.conf.bck

Update the configuration:

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=1
process-max=2
log-level-console=info
log-level-file=debug

[some_cool_stanza_name]
pg1-path=/var/lib/pgsql/10/data

Make sure that the postgres user can write in /var/lib/pgbackrest.

Configure archiving in the postgresql.conf file:

archive_mode = on
archive_command = 'pgbackrest --stanza=some_cool_stanza_name archive-push %p'

The PostgreSQL cluster must be restarted after making these changes and before performing a backup.

Let’s finally create the stanza and check the configuration:

$ sudo -iu postgres pgbackrest --stanza=some_cool_stanza_name stanza-create
P00   INFO: stanza-create command end: completed successfully

$ sudo -iu postgres pgbackrest --stanza=some_cool_stanza_name check
P00   INFO: WAL segment 00000001000000000000004E successfully stored in the 
    archive at '/var/lib/pgbackrest/archive/some_cool_stanza_name/
    10-1/0000000100000000/
    00000001000000000000004E-201c08f0d6be79ba6c9b08c7011bfdab156c4638.gz'
P00   INFO: check command end: completed successfully

Perform a backup and simulate some activity

Let’s take our first backup:

$ sudo -iu postgres pgbackrest --stanza=some_cool_stanza_name --type=full backup
...
P00   INFO: new backup label = 20190301-102816F
P00   INFO: backup command end: completed successfully
...

Then, let’s use pgbench to simulate some activity (over a 600s period):

$ sudo -iu postgres /usr/pgsql-10/bin/pgbench -c 10 -T 600 bench

PostgreSQL upgrade

The following instructions are not meant to be a full guide over upgrading PostgreSQL. I’ll here use pg_upgrade.

Before running it you must:

  • create a new database cluster (using the new version of initdb)
  • shutdown the postmaster servicing the old cluster
  • shutdown the postmaster servicing the new cluster
$ sudo yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/\
rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm
$ sudo yum install -y postgresql11-server postgresql11-contrib
$ sudo /usr/pgsql-11/bin/postgresql-11-setup initdb
$ sudo systemctl stop postgresql-10
$ sudo systemctl stop postgresql-11

When you run pg_upgrade, you must provide the following information:

  • the data directory for the old cluster
  • the data directory for the new cluster
  • the “bin” directory for the old version
  • the “bin” directory for the new version
$ sudo -iu postgres /usr/pgsql-11/bin/pg_upgrade \
--old-datadir=/var/lib/pgsql/10/data/ \
--new-datadir=/var/lib/pgsql/11/data/ \
--old-bindir=/usr/pgsql-10/bin \
--new-bindir=/usr/pgsql-11/bin \
--check 

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

*Clusters are compatible*

Don’t forget to configure correctly your new cluster. Here, report the previous postgresql.conf configuration about archive_mode and archive_command.

Then, run pg_upgrade without the --check option. You should get a result like that:

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

Once PostgreSQL updated, update the pgBackRest configuration (/etc/pgbackrest.conf) to point to the new cluster:

pg1-path=/var/lib/pgsql/11/data

Before starting the new PostgreSQL cluster, the stanza-upgrade command must be run:

$ sudo -iu postgres pgbackrest --stanza=some_cool_stanza_name --no-online stanza-upgrade
P00   INFO: stanza-upgrade command end: completed successfully

Start the new cluster, confirm it is successfully installed and test the configuration:

$ sudo systemctl start postgresql-11
$ sudo -iu postgres pgbackrest --stanza=some_cool_stanza_name check
P00   INFO: WAL segment 000000010000000100000040 successfully stored in the 
    archive at '/var/lib/pgbackrest/archive/some_cool_stanza_name/
    11-2/0000000100000001/
    000000010000000100000040-cb9963f36306e7a5410af9f29a37bd30a2d79b1c.gz'
P00   INFO: check command end: completed successfully

Refresh the optimizer statistics and remove the old cluster:

$ sudo -iu postgres ./analyze_new_cluster.sh
This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy.  When it is done, your system will
have the default level of optimizer statistics.

If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after
running this script because they will delay fast statistics generation.

If you would like default statistics as quickly as possible, cancel
this script and run:
    "/usr/pgsql-11/bin/vacuumdb" --all --analyze-only

vacuumdb: processing database "bench": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "bench": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "bench": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
Done

$ sudo -iu postgres ./delete_old_cluster.sh

Finally, take a new fresh full backup:

$ sudo -iu postgres pgbackrest --stanza=some_cool_stanza_name --type=full backup
...
P00   INFO: new backup label = 20190301-114628F
P00   INFO: backup command end: completed successfully

Since I configured repo1-retention-full=1, the expire command will react:

P00   INFO: expire command begin
P00   INFO: expire full backup 20190301-102816F
P00   INFO: remove expired backup 20190301-102816F
P00   INFO: remove archive path: /var/lib/pgbackrest/archive/some_cool_stanza_name/10-1
P00   INFO: expire command end: completed successfully

Conclusion

It’s always better to take a backup before upgrading a major version of PostgreSQL. pgBackRest requires the stanza-upgrade to be executed to work with the new PostgreSQL version.

It’s not really complicated but it’s definitively something you have to think about in your PostgreSQL upgrade procedure.