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
for all pgBackRest configurations must be set to the new database location and
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.
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
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
Configure archiving in the
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
The following instructions are not meant to be a full guide over upgrading
PostgreSQL. I’ll here use
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
postgresql.conf configuration about
pg_upgrade without the
--check option. You should get a result
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:
Before starting the new PostgreSQL cluster, the
stanza-upgrade command must
$ 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
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.