I recently had to replace Barman by pgBackRest as Backup & Recovery solution for one of my customer.
The purpose of this blog is not to show how I made the setup (some dbi blogs already exists for that), but only to share a small issue I encountered during the creation of the Stanza :
postgres@pgbackrest:/ [cms2] pgbackrest --stanza=cms2_primary stanza-create
ERROR: [058]: version '11' and path '(null)' queried from cluster do not match version '11' and '/services/data/pgdata/cms2' read from '/services/data/pgdata/cms2/global/pg_control'
HINT: the pg1-path and pg1-port settings likely reference different clusters.
postgres@pgbackrest:/ [cms2]
According to the error message it seems that the path to the data directory can not be retrieved.
I’m using a dedicated user to perform the backup operations (instead of the “postgres” user) :
postgres@pgbackrest:/ [cms2] cat /etc/pgbackrest.conf
[global]
repo1-host=pgserver
repo1-host-user=postgres
log-level-file=detail
[cms2_primary]
pg1-path=/services/data/pgdata/cms2
pg1-socket-path=/tmp
pg1-user=backupuser
postgres@pgbackrest:/ [cms2]
The source of the problem is that backupuser is not allowed to read instance settings :
(postgres@[local]:5432) [postgres] > c postgres backupuser
You are now connected to database "postgres" as user "backupuser".
(backupuser@[local]:5432) [postgres] > select setting from pg_catalog.pg_settings where name = 'data_directory';
setting
---------
(0 rows)
So, to solve this it’s required to grant the “pg_read_all_settings” role to the user :
(postgres@[local]:5432) [postgres] > grant pg_read_all_settings to backupuser;
GRANT ROLE
(postgres@[local]:5432) [postgres] > c postgres backupuser
You are now connected to database "postgres" as user "backupuser".
(backupuser@[local]:5432) [postgres] > select setting from pg_catalog.pg_settings where name = 'data_directory';
setting
----------------------------
/services/data/pgdata/cms2
(1 row)
Some other privileges are also required to allow the user to perform backup/restore operations :
(postgres@[local]:5432) [postgres] > grant execute on function pg_create_restore_point(text) to backupuser;
GRANT
(postgres@[local]:5432) [postgres] > grant execute on function pg_start_backup(text, boolean, boolean) to backupuser;
GRANT
(postgres@[local]:5432) [postgres] > grant execute on function pg_stop_backup(boolean, boolean) to backupuser;
GRANT
(postgres@[local]:5432) [postgres] > grant execute on function pg_create_restore_point(text) to backupuser;
GRANT
(postgres@[local]:5432) [postgres] > grant execute on function pg_switch_wal() to backupuser;
GRANT
(postgres@[local]:5432) [postgres] >
With the correct privilege the Stanza can now be created :
postgres@pgbackrest:/ [cms2] pgbackrest --stanza=cms2_primary stanza-create --log-level-console=detail
2020-04-28 20:36:12.252 P00 INFO: stanza-create command begin 2.26: --log-level-console=detail --pg1-path=/services/data/pgdata/cms2 --pg1-socket-path=/tmp --pg1-user=backupuser --repo1-path=/var/lib/pgbackrest --stanza=cms2_primary
2020-04-28 20:36:12.785 P00 INFO: stanza-create command end: completed successfully (534ms)
postgres@pgbackrest:/ [cms2]
postgres@pgbackrest:/ [cms2] pgbackrest --stanza=cms2_primary check --log-level-console=detail
2020-04-28 20:36:48.379 P00 INFO: check command begin 2.26: --log-level-console=detail --pg1-path=/services/data/pgdata/cms2 --pg1-socket-path=/tmp --pg1-user=backupuser --repo1-path=/var/lib/pgbackrest --stanza=cms2_primary
2020-04-28 20:36:49.887 P00 INFO: WAL segment 000000010000004E00000036 successfully archived to '/var/lib/pgbackrest/archive/cms2_primary/11-1/000000010000004E/000000010000004E00000036-5164039a67a3f865cfc6c188b9cd02d38c2a1c55.gz'
2020-04-28 20:36:49.887 P00 INFO: check command end: completed successfully (1509ms)
postgres@pgbackrest:/ [cms2]
And then the first initial full backup can be done :
postgres@pgbackrest:/ [cms2] pgbackrest backup --type=full --stanza=cms2_primary --log-level-console=detail
2020-04-28 23:21:50.546 P00 INFO: backup command begin 2.26: --pg1-host=pgserver --pg1-path=/services/data/pgdata/cms2 --pg1-port=5432 --pg1-socket-path=/tmp --pg1-user=backupuser --repo1-path=/services/data/pgdata --repo1-retention-full=2 --stanza=cms2_primary --start-fast --type=diff
2020-04-28 23:21:51.303 P00 WARN: no prior backup exists, diff backup has been changed to full
2020-04-28 23:21:51.303 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
2020-04-28 23:21:52.012 P00 INFO: backup start archive = 000000010000004F00000007, lsn = 4F/1C000028
2020-04-28 23:24:00.160 P01 INFO: backup file pgserver:/services/data/pgdata/cms2/base/23633/58871.8 (2GB, 2%) checksum 7573c5848b1d2352666d3c3937c818e92654876e
2020-04-28 23:25:58.255 P01 INFO: backup file pgserver:/services/data/pgdata/cms2/base/23633/58871.7 (2GB, 4%) checksum 09149d631f2b703b47e928c296aec70f21a74a00
2020-04-28 23:28:04.379 P01 INFO: backup file pgserver:/services/data/pgdata/cms2/base/23633/58871.6 (2GB, 6%) checksum f4196a0f54037b18577b8f3f08e61b6b7f84deb1
2020-04-28 23:29:57.413 P01 INFO: backup file pgserver:/services/data/pgdata/cms2/base/23633/58871.5 (2GB, 9%) checksum 9e334b2366f5bee850c782a62827e0fe1c83e1df
...
...
2020-04-29 00:54:48.900 P01 INFO: backup file pgserver:/services/data/pgdata/cms2/base/1/13084 (0B, 100%)
2020-04-29 00:54:48.906 P01 INFO: backup file pgserver:/services/data/pgdata/cms2/base/1/13079 (0B, 100%)
2020-04-29 00:54:48.910 P01 INFO: backup file pgserver:/services/data/pgdata/cms2/base/1/13074 (0B, 100%)
2020-04-29 00:54:48.929 P00 INFO: full backup size = 88.1GB
2020-04-29 00:54:48.930 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2020-04-29 00:54:49.232 P00 INFO: backup stop archive = 000000010000004F00000008, lsn = 4F/20000050
2020-04-29 00:54:49.339 P00 INFO: check archive for segment(s) 000000010000004F00000007:000000010000004F00000008
2020-04-29 00:54:50.011 P00 INFO: new backup label = 20200428-232153F
2020-04-29 00:54:50.452 P00 INFO: backup command end: completed successfully (5579906ms)
postgres@pgbackrest:/ [cms2]
Fairly easy… but not documented. So I hope this helps.