wget -q -O - \ https://github.com/pgbackrest/pgbackrest/archive/release/2.33.tar.gz | \ tar zx -C /build
sudo yum install make gcc postgresql95-devel \ openssl-devel libxml2-devel lz4-devel libzstd-devel bzip2-devel
cd /build/pgbackrest-release-2.33/src && ./configure && make
sudo yum install postgresql-libs
sudo scp build:/build/pgbackrest-release-2.33/src/pgbackrest /usr/bin
sudo chmod 755 /usr/bin/pgbackrest
sudo mkdir -p -m 770 /var/log/pgbackrest
sudo chown postgres:postgres /var/log/pgbackrest
sudo mkdir -p /etc/pgbackrest
sudo mkdir -p /etc/pgbackrest/conf.d
sudo touch /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
sudo -u postgres pgbackrest
pgBackRest 2.33 - 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. repo-get Get a file from a repository. repo-ls List files in a repository. 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.
sudo -u postgres /usr/pgsql-9.5/bin/initdb \ -D /var/lib/pgsql/9.5/data -k -A peer
sudo -u postgres bash -c ' \ export PGBACKREST_LOG_PATH=/path/set/by/env && \ pgbackrest --log-level-console=error help backup log-path'
pgBackRest 2.33 - 'backup' command - 'log-path' option help Path where log files are stored. The log path provides a location for pgBackRest to store log files. Note that if log-level-file=off then no log path is required.
current: /path/set/by/env
default: /var/log/pgbackrest
sudo mkdir -p /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest
sudo chown postgres:postgres /var/lib/pgbackrest
sudo systemctl restart postgresql-9.5.service
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info stanza-create
P00 INFO: stanza-create command begin 2.33: --exec-id=750-a87b9253 --log-level-console=info --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/pgsql/9.5/data --repo1-cipher-pass=--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=demo P00 INFO: stanza-create for stanza 'demo' on repo1
P00 INFO: stanza-create command end: completed successfully
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info check
P00 INFO: check command begin 2.33: --exec-id=771-96dd6ed3 --log-level-console=info --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/pgsql/9.5/data --repo1-cipher-pass=--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=demo P00 INFO: check repo1 configuration (primary) P00 INFO: check repo1 archive for WAL (primary)
P00 INFO: WAL segment 000000010000000000000001 successfully archived to '/var/lib/pgbackrest/archive/demo/9.5-1/0000000100000000/000000010000000000000001-7322edf854009717567901c3aab28e4cd9dd53ff.gz' on repo1
P00 INFO: check command end: completed successfully
sudo -u postgres pgbackrest --stanza=demo \ --log-level-console=info backup
P00 INFO: backup command begin 2.33: --exec-id=792-4611bd26 --log-level-console=info --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/pgsql/9.5/data --repo1-cipher-pass=--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo
P00 WARN: no prior backup exists, incr backup has been changed to full
P00 INFO: execute exclusive pg_start_backup(): backup begins after the next regular checkpoint completes P00 INFO: backup start archive = 000000010000000000000002, lsn = 0/2000028 [filtered 851 lines of output] P01 INFO: backup file /var/lib/pgsql/9.5/data/base/1/12280 (0B, 100%) P01 INFO: backup file /var/lib/pgsql/9.5/data/base/1/12275 (0B, 100%)
P00 INFO: full backup size = 20.6MB
P00 INFO: execute exclusive pg_stop_backup() and wait for all WAL segments to archive P00 INFO: backup stop archive = 000000010000000000000002, lsn = 0/2000130 [filtered 4 lines of output]
sudo -u postgres pgbackrest --stanza=demo --type=diff \ --log-level-console=info backup
[filtered 4 lines of output] P01 INFO: backup file /var/lib/pgsql/9.5/data/global/pg_control (8KB, 97%) checksum db7ef6d28f3b2c4a106a2b1668cd9bb18ba85b0c P01 INFO: backup file /var/lib/pgsql/9.5/data/backup_label (246B, 100%) checksum 96ac3be1e3d1b22390057f54f4114df49fd975ed
P00 INFO: diff backup size = 8.2KB
P00 INFO: execute exclusive pg_stop_backup() and wait for all WAL segments to archive P00 INFO: backup stop archive = 000000010000000000000003, lsn = 0/30000F8 [filtered 4 lines of output]
#m h dom mon dow command 30 06 * * 0 pgbackrest --type=full --stanza=demo backup 30 06 * * 1-6 pgbackrest --type=diff --stanza=demo backup
sudo -u postgres pgbackrest info
stanza: demo status: ok cipher: aes-256-cbc db (current) wal archive min/max (9.5): 000000010000000000000001/000000010000000000000003
full backup: 20210402-175747F
timestamp start/stop: 2021-04-02 17:57:47 / 2021-04-02 17:58:00 wal start/stop: 000000010000000000000002 / 000000010000000000000002 database size: 20.6MB, database backup size: 20.6MB repo1: backup set size: 2.4MB, backup size: 2.4MB
diff backup: 20210402-175747F_20210402-175801D
timestamp start/stop: 2021-04-02 17:58:01 / 2021-04-02 17:58:03 wal start/stop: 000000010000000000000003 / 000000010000000000000003 database size: 20.6MB, database backup size: 8.2KB repo1: backup set size: 2.4MB, backup size: 400B backup reference list: 20210402-175747F
sudo systemctl stop postgresql-9.5.service
sudo -u postgres rm /var/lib/pgsql/9.5/data/global/pg_control
sudo systemctl start postgresql-9.5.service
sudo systemctl status postgresql-9.5.service
[filtered 10 lines of output] Apr 02 17:58:06 pg-primary pg_ctl[925]: but could not open file "/var/lib/pgsql/9.5/data/global/pg_control": No such file or directory Apr 02 17:58:07 pg-primary systemd[1]: postgresql-9.5.service: control process exited, code=exited status=1
Apr 02 17:58:07 pg-primary systemd[1]: Failed to start PostgreSQL 9.5 database server.
Apr 02 17:58:07 pg-primary systemd[1]: Unit postgresql-9.5.service entered failed state. Apr 02 17:58:07 pg-primary systemd[1]: postgresql-9.5.service failed.
sudo -u postgres find /var/lib/pgsql/9.5/data -mindepth 1 -delete
sudo -u postgres pgbackrest --stanza=demo restore
sudo systemctl start postgresql-9.5.service
sudo -u postgres pgbackrest --stanza=demo --type=incr \ --log-level-console=info backup
P00 INFO: backup command begin 2.33: --exec-id=1069-03461a10 --log-level-console=info --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/pgsql/9.5/data --repo1-cipher-pass=--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo --type=incr P00 INFO: last backup label = 20210402-175747F_20210402-175801D, version = 2.33
P00 INFO: execute exclusive pg_start_backup(): backup begins after the next regular checkpoint completes
P00 INFO: backup start archive = 000000020000000000000005, lsn = 0/5000028 P00 WARN: a timeline switch has occurred since the 20210402-175747F_20210402-175801D backup, enabling delta checksum [filtered 11 lines of output]
sudo -u postgres pgbackrest --stanza=demo --type=incr \ --log-level-console=info backup
P00 INFO: backup command begin 2.33: --exec-id=1120-dca4fea0 --log-level-console=info --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/pgsql/9.5/data --repo1-cipher-pass=--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo --start-fast --type=incr P00 INFO: last backup label = 20210402-175747F_20210402-175821I, version = 2.33
P00 INFO: execute exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
P00 INFO: backup start archive = 000000020000000000000006, lsn = 0/6000028 P01 INFO: backup file /var/lib/pgsql/9.5/data/global/pg_control (8KB, 97%) checksum a6811a04bf47509a59ed95a624fbe69cb81fa90c [filtered 8 lines of output]
sudo chmod 550 /var/lib/pgbackrest/backup/demo/
sudo -u postgres pgbackrest --stanza=demo --type=incr \ --log-level-console=info backup
[filtered 2 lines of output] P00 INFO: execute exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes P00 INFO: backup start archive = 000000020000000000000007, lsn = 0/7000028
P00 ERROR: [047]: unable to create path '/var/lib/pgbackrest/backup/demo/20210402-175747F_20210402-175831I': [13] Permission denied
P00 INFO: backup command end: aborted with exception [047]
sudo chmod 750 /var/lib/pgbackrest/backup/demo/
sudo -u postgres pgbackrest --stanza=demo --type=incr \ --log-level-console=info backup
P00 INFO: backup command begin 2.33: --exec-id=1203-9a4740ef --log-level-console=info --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/pgsql/9.5/data --repo1-cipher-pass=--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo --start-fast --type=incr P00 INFO: last backup label = 20210402-175747F_20210402-175827I, version = 2.33 P00 INFO: execute exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
P00 ERROR: [057]: unable to execute query 'select lsn::text as lsn,
pg_catalog.pg_xlogfile_name(lsn)::text as wal_segment_name
from pg_catalog.pg_start_backup('pgBackRest backup started at ' || current_timestamp, true) as lsn': ERROR: a backup is already in progress
HINT: Run pg_stop_backup() and try again. P00 INFO: backup command end: aborted with exception [057]
sudo -u postgres pgbackrest --stanza=demo --type=incr \ --log-level-console=info backup
P00 INFO: backup command begin 2.33: --exec-id=1253-95a4badd --log-level-console=info --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/pgsql/9.5/data --repo1-cipher-pass=--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo --start-fast --stop-auto --type=incr P00 INFO: last backup label = 20210402-175747F_20210402-175827I, version = 2.33
P00 INFO: execute exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes P00 WARN: the cluster is already in backup mode but no pgBackRest backup process is running. pg_stop_backup() will be called so a new backup can be started.
P00 INFO: backup start archive = 000000020000000000000008, lsn = 0/8000028 P01 INFO: backup file /var/lib/pgsql/9.5/data/global/pg_control (8KB, 84%) checksum e866f12f3548ecc04c0b416977f7dc71d0de95a6 [filtered 9 lines of output]
sudo -u postgres cat \ /var/lib/pgsql/pgbackrest/doc/example/pgsql-pgbackrest-info.sql
-- An example of monitoring pgBackRest from within PostgreSQL -- -- Use copy to export data from the pgBackRest info command into the jsonb -- type so it can be queried directly by PostgreSQL. -- Create monitor schema create schema monitor; -- Get pgBackRest info in JSON format create function monitor.pgbackrest_info() returns jsonb AS $$ declare data jsonb; begin -- Create a temp table to hold the JSON data create temp table temp_pgbackrest_data (data jsonb); -- Copy data into the table directly from the pgBackRest info command copy temp_pgbackrest_data (data) from program 'pgbackrest --output=json info' (format text); select temp_pgbackrest_data.data into data from temp_pgbackrest_data; drop table temp_pgbackrest_data; return data; end $$ language plpgsql;
sudo -u postgres psql -f \ /var/lib/pgsql/pgbackrest/doc/example/pgsql-pgbackrest-info.sql
sudo -u postgres cat \ /var/lib/pgsql/pgbackrest/doc/example/pgsql-pgbackrest-query.sql
-- Get last successful backup for each stanza -- -- Requires the monitor.pgbackrest_info function. with stanza as ( select data->'name' as name, data->'backup'->( jsonb_array_length(data->'backup') - 1) as last_backup, data->'archive'->( jsonb_array_length(data->'archive') - 1) as current_archive from jsonb_array_elements(monitor.pgbackrest_info()) as data ) select name, to_timestamp( (last_backup->'timestamp'->>'stop')::numeric) as last_successful_backup, current_archive->>'max' as last_archived_wal from stanza;
sudo -u postgres psql -f \ /var/lib/pgsql/pgbackrest/doc/example/pgsql-pgbackrest-query.sql
name | last_successful_backup | last_archived_wal --------+------------------------+-------------------------- "demo" | 2021-04-02 17:58:41+00 | 000000020000000000000008 (1 row)
sudo -u postgres pgbackrest --stanza=demo --type=full \ --log-level-console=detail backup
[filtered 861 lines of output] P00 INFO: backup command end: completed successfully P00 INFO: expire command begin 2.33: --exec-id=1431-278dd984 --log-level-console=detail --log-level-stderr=off --no-log-timestamp --repo1-cipher-pass=--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo
P00 DETAIL: repo1: 9.5-1 archive retention on backup 20210402-175747F, start = 000000010000000000000002 P00 DETAIL: repo1: 9.5-1 remove archive, start = 000000010000000000000001, stop = 000000010000000000000001
P00 INFO: expire command end: completed successfully
sudo -u postgres pgbackrest --stanza=demo --type=full \ --log-level-console=info backup
[filtered 861 lines of output] P00 INFO: backup command end: completed successfully P00 INFO: expire command begin 2.33: --exec-id=1478-3f00f687 --log-level-console=info --log-level-stderr=off --no-log-timestamp --repo1-cipher-pass=--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo
P00 INFO: repo1: expire full backup set 20210402-175747F, 20210402-175747F_20210402-175801D, 20210402-175747F_20210402-175821I, 20210402-175747F_20210402-175827I, 20210402-175747F_20210402-175837I
P00 INFO: repo1: remove expired backup 20210402-175747F_20210402-175837I P00 INFO: repo1: remove expired backup 20210402-175747F_20210402-175827I [filtered 3 lines of output]
rollingset of differentials for the last day or more. This allows quick restores to recent points-in-time but reduces overall space consumption.
sudo -u postgres pgbackrest --stanza=demo --type=diff backup
sudo -u postgres pgbackrest --stanza=demo --type=incr backup
sudo -u postgres pgbackrest --stanza=demo --type=diff \ --log-level-console=info backup
[filtered 12 lines of output] P00 INFO: backup command end: completed successfully P00 INFO: expire command begin 2.33: --exec-id=1604-9ef0d836 --log-level-console=info --log-level-stderr=off --no-log-timestamp --repo1-cipher-pass=--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-diff=1 --repo1-retention-full=2 --stanza=demo
P00 INFO: repo1: expire diff backup set 20210402-175857F_20210402-175913D, 20210402-175857F_20210402-175917I
P00 INFO: repo1: remove expired backup 20210402-175857F_20210402-175917I P00 INFO: repo1: remove expired backup 20210402-175857F_20210402-175913D
sudo -u postgres pgbackrest --stanza=demo --type=diff \ --log-level-console=info backup
[filtered 10 lines of output] P00 INFO: backup stop archive = 000000020000000000000012, lsn = 0/120000F8 P00 INFO: check archive for segment(s) 000000020000000000000012:000000020000000000000012
P00 INFO: new backup label = 20210402-175857F_20210402-175928D
P00 INFO: backup command end: completed successfully P00 INFO: expire command begin 2.33: --exec-id=1701-93dbca82 --log-level-console=info --log-level-stderr=off --no-log-timestamp --repo1-cipher-pass=--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-diff=2 --repo1-retention-full=2 --stanza=demo
sudo -u postgres pgbackrest --stanza=demo --log-level-console=detail \ --repo1-retention-archive-type=diff --repo1-retention-archive=1 expire
P00 INFO: expire command begin 2.33: --exec-id=1747-ef19267f --log-level-console=detail --log-level-stderr=off --no-log-timestamp --repo1-cipher-pass=--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-archive=1 --repo1-retention-archive-type=diff --repo1-retention-diff=2 --repo1-retention-full=2 --stanza=demo P00 DETAIL: repo1: 9.5-1 archive retention on backup 20210402-175844F, start = 00000002000000000000000A, stop = 00000002000000000000000A P00 DETAIL: repo1: 9.5-1 archive retention on backup 20210402-175857F, start = 00000002000000000000000B, stop = 00000002000000000000000B
P00 DETAIL: repo1: 9.5-1 archive retention on backup 20210402-175857F_20210402-175921D, start = 00000002000000000000000F, stop = 00000002000000000000000F
P00 DETAIL: repo1: 9.5-1 archive retention on backup 20210402-175857F_20210402-175928D, start = 000000020000000000000012
P00 DETAIL: repo1: 9.5-1 remove archive, start = 00000002000000000000000C, stop = 00000002000000000000000E P00 DETAIL: repo1: 9.5-1 remove archive, start = 000000020000000000000010, stop = 000000020000000000000011
P00 INFO: expire command end: completed successfully
sudo systemctl stop postgresql-9.5.service
sudo -u postgres pgbackrest --stanza=demo --delta \ --log-level-console=detail restore
[filtered 2 lines of output] P00 DETAIL: check '/var/lib/pgsql/9.5/data' exists P00 DETAIL: remove 'global/pg_control' so cluster will not start if restore does not complete
P00 INFO: remove invalid files/links/paths from '/var/lib/pgsql/9.5/data'
P00 DETAIL: remove invalid file '/var/lib/pgsql/9.5/data/backup_label.old' P00 DETAIL: remove invalid file '/var/lib/pgsql/9.5/data/base/12442/pg_internal.init' [filtered 900 lines of output]
sudo systemctl start postgresql-9.5.service
sudo -u postgres psql -c "create database test1;"
CREATE DATABASE
sudo -u postgres psql -c "create database test2;"
CREATE DATABASE
sudo -u postgres psql -c "create table test1_table (id int); \ insert into test1_table (id) values (1);" test1
INSERT 0 1
sudo -u postgres psql -c "create table test2_table (id int); \ insert into test2_table (id) values (2);" test2
INSERT 0 1
sudo -u postgres pgbackrest --stanza=demo --type=incr backup
sudo -u postgres du -sh /var/lib/pgsql/9.5/data/base/24576
6.9M /var/lib/pgsql/9.5/data/base/24576
sudo -u postgres pgbackrest --stanza=demo \ --set=20210402-175857F_20210402-175939I info
[filtered 11 lines of output] repo1: backup set size: 4MB, backup size: 1.6MB backup reference list: 20210402-175857F, 20210402-175857F_20210402-175928D
database list: postgres (12442), test1 (24576), test2 (24577)
sudo systemctl stop postgresql-9.5.service
sudo -u postgres pgbackrest --stanza=demo --delta \ --db-include=test2 --type=immediate --target-action=promote restore
sudo systemctl start postgresql-9.5.service
sudo -u postgres psql -c "select * from test2_table;" test2
id ---- 2 (1 row)
sudo -u postgres psql -c "select * from test1_table;" test1
psql: FATAL: relation mapping file "base/24576/pg_filenode.map" contains invalid data
sudo -u postgres du -sh /var/lib/pgsql/9.5/data/base/24576
8.0K /var/lib/pgsql/9.5/data/base/24576
sudo -u postgres psql -c "drop database test1;"
DROP DATABASE
sudo -u postgres psql -c "select oid, datname from pg_database order by oid;"
oid | datname -------+----------- 1 | template1 12437 | template0 12442 | postgres
24577 | test2
(4 rows)
sudo -u postgres pgbackrest --stanza=demo --type=diff backup
sudo -u postgres psql -c "begin; \ create table important_table (message text); \ insert into important_table values ('Important Data'); \ commit; \ select * from important_table;"
message ----------------
Important Data
(1 row)
sudo -u postgres psql -Atc "select current_timestamp"
2021-04-02 18:00:06.028373+00
sudo -u postgres psql -c "begin; \ drop table important_table; \ commit; \ select * from important_table;"
ERROR: relation "important_table" does not exist
LINE 1: ...le important_table; commit; select * from important_... ^
sudo systemctl stop postgresql-9.5.service
sudo -u postgres pgbackrest --stanza=demo --delta \ --type=time "--target=2021-04-02 18:00:06.028373+00" \ --target-action=promote restore
sudo -u postgres cat /var/lib/pgsql/9.5/data/recovery.conf
# Recovery settings generated by pgBackRest restore on 2021-04-02 18:00:09 restore_command = 'pgbackrest --stanza=demo archive-get %f "%p"'
recovery_target_time = '2021-04-02 18:00:06.028373+00'
recovery_target_action = 'promote'
sudo systemctl start postgresql-9.5.service
sudo -u postgres psql -c "select * from important_table"
message ----------------
Important Data
(1 row)
sudo -u postgres cat /var/lib/pgsql/9.5/data/pg_log/postgresql.log
LOG: database system was interrupted; last known up at 2021-04-02 17:59:57 UTC
LOG: starting point-in-time recovery to 2021-04-02 18:00:06.028373+00
LOG: restored log file "00000004.history" from archive LOG: restored log file "000000040000000000000015" from archive LOG: redo starts at 0/15000028 LOG: consistent recovery state reached at 0/150000F8 LOG: restored log file "000000040000000000000016" from archive
LOG: recovery stopping before commit of transaction 712, time 2021-04-02 18:00:07.759952+00
LOG: redo done at 0/1601B9D8
LOG: last completed transaction was at log time 2021-04-02 18:00:04.745809+00
LOG: selected new timeline ID: 5 LOG: archive recovery complete [filtered 3 lines of output]
sudo -u postgres psql -c "begin; \ drop table important_table; \ commit; \ select * from important_table;"
ERROR: relation "important_table" does not exist
LINE 1: ...le important_table; commit; select * from important_... ^
sudo -u postgres pgbackrest --stanza=demo --type=incr backup
sudo -u postgres pgbackrest info
stanza: demo status: ok cipher: aes-256-cbc db (current) wal archive min/max (9.5): 00000002000000000000000A/000000050000000000000017 full backup: 20210402-175844F timestamp start/stop: 2021-04-02 17:58:44 / 2021-04-02 17:58:56 wal start/stop: 00000002000000000000000A / 00000002000000000000000A database size: 20.6MB, database backup size: 20.6MB repo1: backup set size: 2.4MB, backup size: 2.4MB full backup: 20210402-175857F timestamp start/stop: 2021-04-02 17:58:57 / 2021-04-02 17:59:10 wal start/stop: 00000002000000000000000B / 00000002000000000000000B database size: 20.6MB, database backup size: 20.6MB repo1: backup set size: 2.4MB, backup size: 2.4MB diff backup: 20210402-175857F_20210402-175928D timestamp start/stop: 2021-04-02 17:59:28 / 2021-04-02 17:59:30 wal start/stop: 000000020000000000000012 / 000000020000000000000012 database size: 20.6MB, database backup size: 113.9KB repo1: backup set size: 2.4MB, backup size: 12.6KB backup reference list: 20210402-175857F incr backup: 20210402-175857F_20210402-175939I timestamp start/stop: 2021-04-02 17:59:39 / 2021-04-02 17:59:47 wal start/stop: 000000030000000000000014 / 000000030000000000000014 database size: 34MB, database backup size: 13.5MB repo1: backup set size: 4MB, backup size: 1.6MB backup reference list: 20210402-175857F, 20210402-175857F_20210402-175928D diff backup: 20210402-175857F_20210402-175957D timestamp start/stop: 2021-04-02 17:59:57 / 2021-04-02 18:00:03 wal start/stop: 000000040000000000000015 / 000000040000000000000015 database size: 27.3MB, database backup size: 6.9MB repo1: backup set size: 3.2MB, backup size: 836.2KB backup reference list: 20210402-175857F
incr backup: 20210402-175857F_20210402-180015I
timestamp start/stop: 2021-04-02 18:00:15 / 2021-04-02 18:00:18 wal start/stop: 000000050000000000000017 / 000000050000000000000017 database size: 27.3MB, database backup size: 2.0MB repo1: backup set size: 3.2MB, backup size: 209.5KB backup reference list: 20210402-175857F, 20210402-175857F_20210402-175957D
sudo systemctl stop postgresql-9.5.service
sudo -u postgres pgbackrest --stanza=demo --delta \ --set=20210402-175857F_20210402-180015I \ --type=time "--target=2021-04-02 18:00:06.028373+00" --target-action=promote restore
sudo systemctl start postgresql-9.5.service
sudo -u postgres psql -c "select * from important_table"
ERROR: relation "important_table" does not exist
LINE 1: select * from important_table ^
recovery stopping before...and
last completed transaction...log messages. If they are not present then the recovery to the specified point-in-time was not successful.
sudo -u postgres cat /var/lib/pgsql/9.5/data/pg_log/postgresql.log
LOG: database system was interrupted; last known up at 2021-04-02 18:00:15 UTC
LOG: starting point-in-time recovery to 2021-04-02 18:00:06.028373+00
LOG: restored log file "00000005.history" from archive LOG: restored log file "000000050000000000000017" from archive LOG: redo starts at 0/17000028
LOG: consistent recovery state reached at 0/170000F8
LOG: redo done at 0/170000F8 LOG: restored log file "000000050000000000000017" from archive [filtered 7 lines of output]
sudo systemctl stop postgresql-9.5.service
sudo -u postgres pgbackrest --stanza=demo --delta \ --type=time "--target=2021-04-02 18:00:06.028373+00" \ --target-action=promote restore
sudo systemctl start postgresql-9.5.service
sudo -u postgres psql -c "select * from important_table"
message ----------------
Important Data
(1 row)
recovery stopping before...and
last completed transaction...messages showing that the recovery was successful.
sudo -u postgres cat /var/lib/pgsql/9.5/data/pg_log/postgresql.log
LOG: database system was interrupted; last known up at 2021-04-02 17:59:57 UTC
LOG: starting point-in-time recovery to 2021-04-02 18:00:06.028373+00
LOG: restored log file "00000004.history" from archive LOG: restored log file "000000040000000000000015" from archive LOG: redo starts at 0/15000028 LOG: consistent recovery state reached at 0/150000F8 LOG: restored log file "000000040000000000000016" from archive
LOG: recovery stopping before commit of transaction 712, time 2021-04-02 18:00:07.759952+00
LOG: redo done at 0/1601B9D8
LOG: last completed transaction was at log time 2021-04-02 18:00:04.745809+00
LOG: restored log file "00000005.history" from archive LOG: restored log file "00000006.history" from archive [filtered 5 lines of output]
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info stanza-create
[filtered 2 lines of output] P00 INFO: stanza 'demo' already exists on repo1 and is valid P00 INFO: stanza-create for stanza 'demo' on repo2
P00 INFO: stanza-create command end: completed successfully
sudo -u postgres pgbackrest --stanza=demo --repo=2 \ --log-level-console=info backup
P00 INFO: backup command begin 2.33: --exec-id=3075-ecb3b8ba --log-level-console=info --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/pgsql/9.5/data --process-max=4 --repo=2 --repo2-azure-account=--repo2-azure-container=demo-container --repo2-azure-key= --repo1-cipher-pass= --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo2-path=/demo-repo --repo1-retention-diff=2 --repo1-retention-full=2 --repo2-retention-full=4 --repo2-storage-host=blob.core.windows.net --repo2-type=azure --stanza=demo --start-fast --stop-auto
P00 WARN: no prior backup exists, incr backup has been changed to full
P00 INFO: execute exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes P00 INFO: backup start archive = 000000070000000000000017, lsn = 0/17000028 [filtered 1119 lines of output] P01 INFO: backup file /var/lib/pgsql/9.5/data/base/1/12280 (0B, 100%) P02 INFO: backup file /var/lib/pgsql/9.5/data/base/1/12275 (0B, 100%)
P00 INFO: full backup size = 27.3MB
P00 INFO: execute exclusive pg_stop_backup() and wait for all WAL segments to archive P00 INFO: backup stop archive = 000000070000000000000017, lsn = 0/170000F8 [filtered 4 lines of output]
{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:ListBucket" ], "Resource": [ "arn:aws:s3:::demo-bucket" ], "Condition": { "StringEquals": { "s3:prefix": [ "", "demo-repo" ], "s3:delimiter": [ "/" ] } } }, { "Effect": "Allow", "Action": [ "s3:ListBucket" ], "Resource": [ "arn:aws:s3:::demo-bucket" ], "Condition": { "StringLike": { "s3:prefix": [ "demo-repo/*" ] } } }, { "Effect": "Allow", "Action": [ "s3:PutObject", "s3:GetObject", "s3:DeleteObject" ], "Resource": [ "arn:aws:s3:::demo-bucket/demo-repo/*" ] } ] }
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info stanza-create
[filtered 4 lines of output] P00 INFO: stanza 'demo' already exists on repo2 and is valid P00 INFO: stanza-create for stanza 'demo' on repo3
P00 INFO: stanza-create command end: completed successfully
sudo -u postgres pgbackrest --stanza=demo --repo=3 \ --log-level-console=info backup
P00 INFO: backup command begin 2.33: --exec-id=3187-c64c39b6 --log-level-console=info --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/pgsql/9.5/data --process-max=4 --repo=3 --repo2-azure-account=--repo2-azure-container=demo-container --repo2-azure-key= --repo1-cipher-pass= --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo2-path=/demo-repo --repo3-path=/demo-repo --repo1-retention-diff=2 --repo1-retention-full=2 --repo2-retention-full=4 --repo3-retention-full=4 --repo3-s3-bucket=demo-bucket --repo3-s3-endpoint=s3.us-east-1.amazonaws.com --repo3-s3-key= --repo3-s3-key-secret= --repo3-s3-region=us-east-1 --repo2-storage-host=blob.core.windows.net --repo2-type=azure --repo3-type=s3 --stanza=demo --start-fast --stop-auto
P00 WARN: no prior backup exists, incr backup has been changed to full
P00 INFO: execute exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes P00 INFO: backup start archive = 000000070000000000000019, lsn = 0/19000028 [filtered 1119 lines of output] P03 INFO: backup file /var/lib/pgsql/9.5/data/base/1/12280 (0B, 100%) P02 INFO: backup file /var/lib/pgsql/9.5/data/base/1/12275 (0B, 100%)
P00 INFO: full backup size = 27.3MB
P00 INFO: execute exclusive pg_stop_backup() and wait for all WAL segments to archive P00 INFO: backup stop archive = 000000070000000000000019, lsn = 0/190000F8 [filtered 4 lines of output]
sudo systemctl stop postgresql-9.5.service
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info stop
P00 INFO: stop command begin 2.33: --exec-id=3266-67edbf12 --log-level-console=info --log-level-stderr=off --no-log-timestamp --stanza=demo
P00 INFO: stop command end: completed successfully
sudo -u postgres pgbackrest --stanza=demo --repo=1 \ --log-level-console=info stanza-delete
P00 INFO: stanza-delete command begin 2.33: --exec-id=3286-1fa40366 --log-level-console=info --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/pgsql/9.5/data --repo=1 --repo2-azure-account=--repo2-azure-container=demo-container --repo2-azure-key= --repo1-cipher-pass= --repo1-cipher-type=aes-256-cbc --repo4-gcs-bucket=demo-bucket --repo4-gcs-key= --repo1-path=/var/lib/pgbackrest --repo2-path=/demo-repo --repo3-path=/demo-repo --repo4-path=/demo-repo --repo3-s3-bucket=demo-bucket --repo3-s3-endpoint=s3.us-east-1.amazonaws.com --repo3-s3-key= --repo3-s3-key-secret= --repo3-s3-region=us-east-1 --repo2-storage-host=blob.core.windows.net --repo2-type=azure --repo3-type=s3 --repo4-type=gcs --stanza=demo
P00 INFO: stanza-delete command end: completed successfully
sudo groupadd pgbackrest
sudo adduser -gpgbackrest -n pgbackrest
sudo yum install postgresql-libs
sudo scp build:/build/pgbackrest-release-2.33/src/pgbackrest /usr/bin
sudo chmod 755 /usr/bin/pgbackrest
sudo mkdir -p -m 770 /var/log/pgbackrest
sudo chown pgbackrest:pgbackrest /var/log/pgbackrest
sudo mkdir -p /etc/pgbackrest
sudo mkdir -p /etc/pgbackrest/conf.d
sudo touch /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown pgbackrest:pgbackrest /etc/pgbackrest/pgbackrest.conf
sudo mkdir -p /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest
sudo chown pgbackrest:pgbackrest /var/lib/pgbackrest
sudo -u pgbackrest mkdir -m 750 /home/pgbackrest/.ssh
sudo -u pgbackrest ssh-keygen -f /home/pgbackrest/.ssh/id_rsa \ -t rsa -b 4096 -N ""
sudo -u postgres mkdir -m 750 -p /var/lib/pgsql/.ssh
sudo -u postgres ssh-keygen -f /var/lib/pgsql/.ssh/id_rsa \ -t rsa -b 4096 -N ""
(echo -n 'no-agent-forwarding,no-X11-forwarding,no-port-forwarding,' && \ echo -n 'command="/usr/bin/pgbackrest ${SSH_ORIGINAL_COMMAND#* }" ' && \ sudo ssh root@pg-primary cat /var/lib/pgsql/.ssh/id_rsa.pub) | \ sudo -u pgbackrest tee -a /home/pgbackrest/.ssh/authorized_keys
(echo -n 'no-agent-forwarding,no-X11-forwarding,no-port-forwarding,' && \ echo -n 'command="/usr/bin/pgbackrest ${SSH_ORIGINAL_COMMAND#* }" ' && \ sudo ssh root@repository cat /home/pgbackrest/.ssh/id_rsa.pub) | \ sudo -u postgres tee -a /var/lib/pgsql/.ssh/authorized_keys
sudo -u pgbackrest ssh postgres@pg-primary
sudo -u postgres ssh pgbackrest@repository
sudo -u pgbackrest pgbackrest --stanza=demo stanza-create
sudo -u postgres pgbackrest --stanza=demo check
sudo -u pgbackrest pgbackrest --stanza=demo check
sudo -u pgbackrest pgbackrest --stanza=demo backup
P00 WARN: no prior backup exists, incr backup has been changed to full
sudo systemctl stop postgresql-9.5.service
sudo -u postgres pgbackrest --stanza=demo --delta restore
sudo systemctl start postgresql-9.5.service
sudo -u pgbackrest pgbackrest --stanza=demo --type=full backup
sudo -u pgbackrest pgbackrest --stanza=demo --type=full backup
sudo -u pgbackrest pgbackrest info
stanza: demo status: ok cipher: none db (current) wal archive min/max (9.5): 00000008000000000000001E/00000008000000000000001F full backup: 20210402-180141F
timestamp start/stop: 2021-04-02 18:01:41 / 2021-04-02 18:01:54
wal start/stop: 00000008000000000000001E / 00000008000000000000001E database size: 27.3MB, database backup size: 27.3MB repo1: backup set size: 3.2MB, backup size: 3.2MB full backup: 20210402-180156F
timestamp start/stop: 2021-04-02 18:01:56 / 2021-04-02 18:02:03
wal start/stop: 00000008000000000000001F / 00000008000000000000001F database size: 27.3MB, database backup size: 27.3MB repo1: backup set size: 3.2MB, backup size: 3.2MB
sudo -u postgres pgbackrest stop
sudo -u pgbackrest pgbackrest --stanza=demo backup
P00 WARN: unable to check pg-1: [StopError] raised from remote-0 protocol on 'pg-primary': stop file exists for all stanzas
P00 ERROR: [056]: unable to find primary cluster - cannot proceed
sudo -u postgres pgbackrest stop
P00 WARN: stop file already exists for all stanzas
sudo -u postgres pgbackrest start
sudo -u postgres pgbackrest --stanza=demo stop
sudo -u pgbackrest pgbackrest --stanza=demo backup
P00 WARN: unable to check pg-1: [StopError] raised from remote-0 protocol on 'pg-primary': stop file exists for stanza demo
P00 ERROR: [056]: unable to find primary cluster - cannot proceed
sudo -u postgres pgbackrest --stanza=demo start
sudo yum install postgresql-libs
sudo scp build:/build/pgbackrest-release-2.33/src/pgbackrest /usr/bin
sudo chmod 755 /usr/bin/pgbackrest
sudo mkdir -p -m 770 /var/log/pgbackrest
sudo chown postgres:postgres /var/log/pgbackrest
sudo mkdir -p /etc/pgbackrest
sudo mkdir -p /etc/pgbackrest/conf.d
sudo touch /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
sudo -u postgres mkdir -m 750 -p /var/lib/pgsql/.ssh
sudo -u postgres ssh-keygen -f /var/lib/pgsql/.ssh/id_rsa \ -t rsa -b 4096 -N ""
(echo -n 'no-agent-forwarding,no-X11-forwarding,no-port-forwarding,' && \ echo -n 'command="/usr/bin/pgbackrest ${SSH_ORIGINAL_COMMAND#* }" ' && \ sudo ssh root@pg-standby cat /var/lib/pgsql/.ssh/id_rsa.pub) | \ sudo -u pgbackrest tee -a /home/pgbackrest/.ssh/authorized_keys
(echo -n 'no-agent-forwarding,no-X11-forwarding,no-port-forwarding,' && \ echo -n 'command="/usr/bin/pgbackrest ${SSH_ORIGINAL_COMMAND#* }" ' && \ sudo ssh root@repository cat /home/pgbackrest/.ssh/id_rsa.pub) | \ sudo -u postgres tee -a /var/lib/pgsql/.ssh/authorized_keys
sudo -u pgbackrest ssh postgres@pg-standby
sudo -u postgres ssh pgbackrest@repository
sudo -u postgres mkdir -p -m 700 /var/lib/pgsql/9.5/data
sudo -u postgres pgbackrest --stanza=demo --type=standby restore
sudo -u postgres cat /var/lib/pgsql/9.5/data/recovery.conf
# Recovery settings generated by pgBackRest restore on 2021-04-02 18:02:25 restore_command = 'pgbackrest --stanza=demo archive-get %f "%p"' standby_mode = 'on'
sudo systemctl start postgresql-9.5.service
sudo -u postgres cat /var/lib/pgsql/9.5/data/pg_log/postgresql.log
LOG: database system was interrupted; last known up at 2021-04-02 18:01:56 UTC
LOG: entering standby mode
LOG: restored log file "00000008.history" from archive LOG: restored log file "00000008000000000000001F" from archive LOG: redo starts at 0/1F000028 LOG: consistent recovery state reached at 0/1F0000F8
LOG: database system is ready to accept read only connections
sudo -u postgres psql -c " \ begin; \ create table replicated_table (message text); \ insert into replicated_table values ('Important Data'); \ commit; \ select * from replicated_table";
message ----------------
Important Data
(1 row)
sudo -u postgres psql -c "select * from replicated_table;"
ERROR: relation "replicated_table" does not exist
LINE 1: select * from replicated_table; ^
sudo -u postgres psql -c "select *, current_timestamp from pg_switch_xlog()";
pg_switch_xlog | now ----------------+------------------------------- 0/2001F428 | 2021-04-02 18:02:33.709133+00 (1 row)
sudo -u postgres psql -c " \ select *, current_timestamp from replicated_table"
message | now ----------------+-------------------------------
Important Data | 2021-04-02 18:02:35.570803+00
(1 row)
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info check
P00 INFO: check command begin 2.33: --exec-id=727-bf7e4013 --log-level-console=info --log-level-file=detail --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/pgsql/9.5/data --repo1-host=repository --stanza=demo P00 INFO: check repo1 (standby)
P00 INFO: switch wal not performed because this is a standby
P00 INFO: check command end: completed successfully
sudo -u postgres psql -c " \ create user replicator password 'jw8s0F4' replication";
CREATE ROLE
sudo -u postgres sh -c 'echo \ "host replication replicator 172.17.0.7/32 md5" \ >> /var/lib/pgsql/9.5/data/pg_hba.conf'
sudo systemctl reload postgresql-9.5.service
sudo -u postgres sh -c 'echo \ "172.17.0.5:*:replication:replicator:jw8s0F4" \ >> /var/lib/pgsql/.pgpass'
sudo -u postgres chmod 600 /var/lib/pgsql/.pgpass
sudo systemctl stop postgresql-9.5.service
sudo -u postgres pgbackrest --stanza=demo --delta --type=standby restore
sudo -u postgres cat /var/lib/pgsql/9.5/data/recovery.conf
# Recovery settings generated by pgBackRest restore on 2021-04-02 18:02:40 primary_conninfo = 'host=172.17.0.5 port=5432 user=replicator' restore_command = 'pgbackrest --stanza=demo archive-get %f "%p"' standby_mode = 'on'
sudo systemctl start postgresql-9.5.service
sudo -u postgres cat /var/lib/pgsql/9.5/data/pg_log/postgresql.log
[filtered 6 lines of output] LOG: database system is ready to accept read only connections LOG: restored log file "000000080000000000000020" from archive
LOG: started streaming WAL from primary at 0/21000000 on timeline 8
sudo -u postgres psql -c " \ begin; \ create table stream_table (message text); \ insert into stream_table values ('Important Data'); \ commit; \ select *, current_timestamp from stream_table";
message | now ----------------+-------------------------------
Important Data | 2021-04-02 18:02:45.443371+00
(1 row)
sudo -u postgres psql -c " \ select *, current_timestamp from stream_table"
message | now ----------------+-------------------------------
Important Data | 2021-04-02 18:02:46.137722+00
(1 row)
sudo mkdir -p -m 750 /var/spool/pgbackrest
sudo chown postgres:postgres /var/spool/pgbackrest
sudo mkdir -p -m 750 /var/spool/pgbackrest
sudo chown postgres:postgres /var/spool/pgbackrest
sudo -u postgres psql -c "alter user replicator password 'bogus'"
ALTER ROLE
sudo systemctl restart postgresql-9.5.service
looking aheadto see which WAL segments are ready to be archived beyond the request that PostgreSQL is currently making via the archive_command. WAL segments are transferred to the archive directly from the pg_xlog/pg_wal directory and success is only returned by the archive_command when the WAL segment has been safely stored in the archive.
sudo -u postgres psql -c " \ select pg_create_restore_point('test async push'); select pg_switch_xlog(); \ select pg_create_restore_point('test async push'); select pg_switch_xlog(); \ select pg_create_restore_point('test async push'); select pg_switch_xlog(); \ select pg_create_restore_point('test async push'); select pg_switch_xlog(); \ select pg_create_restore_point('test async push'); select pg_switch_xlog();"
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info check
P00 INFO: check command begin 2.33: --exec-id=4046-cf1483ad --log-level-console=info --log-level-file=detail --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/pgsql/9.5/data --repo1-host=repository --stanza=demo P00 INFO: check repo1 configuration (primary) P00 INFO: check repo1 archive for WAL (primary)
P00 INFO: WAL segment 000000080000000000000026 successfully archived to '/var/lib/pgbackrest/archive/demo/9.5-1/0000000800000000/000000080000000000000026-06011efce5c8a0e9f634b97cceff699a1dacd6ca.gz' on repo1
P00 INFO: check command end: completed successfully
sudo -u postgres cat /var/log/pgbackrest/demo-archive-push-async.log
-------------------PROCESS START------------------- P00 INFO: archive-push:async command begin 2.33: [/var/lib/pgsql/9.5/data/pg_xlog] --archive-async --exec-id=4019-ef44b67e --log-level-console=off --log-level-file=detail --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/pgsql/9.5/data --process-max=2 --repo1-host=repository --spool-path=/var/spool/pgbackrest --stanza=demo
P00 INFO: push 1 WAL file(s) to archive: 000000080000000000000021 P01 DETAIL: pushed WAL file '000000080000000000000021' to the archive
P00 INFO: archive-push:async command end: completed successfully -------------------PROCESS START------------------- P00 INFO: archive-push:async command begin 2.33: [/var/lib/pgsql/9.5/data/pg_xlog] --archive-async --exec-id=4048-55ceefdf --log-level-console=off --log-level-file=detail --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/pgsql/9.5/data --process-max=2 --repo1-host=repository --spool-path=/var/spool/pgbackrest --stanza=demo
P00 INFO: push 4 WAL file(s) to archive: 000000080000000000000022...000000080000000000000025 P01 DETAIL: pushed WAL file '000000080000000000000022' to the archive P02 DETAIL: pushed WAL file '000000080000000000000023' to the archive P01 DETAIL: pushed WAL file '000000080000000000000024' to the archive P02 DETAIL: pushed WAL file '000000080000000000000025' to the archive
P00 INFO: archive-push:async command end: completed successfully -------------------PROCESS START------------------- P00 INFO: archive-push:async command begin 2.33: [/var/lib/pgsql/9.5/data/pg_xlog] --archive-async --exec-id=4060-5280c63f --log-level-console=off --log-level-file=detail --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/pgsql/9.5/data --process-max=2 --repo1-host=repository --spool-path=/var/spool/pgbackrest --stanza=demo
P00 INFO: push 1 WAL file(s) to archive: 000000080000000000000026 P01 DETAIL: pushed WAL file '000000080000000000000026' to the archive
P00 INFO: archive-push:async command end: completed successfully
sudo -u postgres cat /var/log/pgbackrest/demo-archive-get-async.log
-------------------PROCESS START------------------- P00 INFO: archive-get:async command begin 2.33: [00000008000000000000001F, 000000080000000000000020, 000000080000000000000021, 000000080000000000000022, 000000080000000000000023, 000000080000000000000024, 000000080000000000000025, 000000080000000000000026] --archive-async --exec-id=1134-be0c24c7 --log-level-console=off --log-level-file=detail --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/pgsql/9.5/data --process-max=2 --repo1-host=repository --spool-path=/var/spool/pgbackrest --stanza=demo P00 INFO: get 8 WAL file(s) from archive: 00000008000000000000001F...000000080000000000000026
P01 DETAIL: found 00000008000000000000001F in the repo1: 9.5-1 archive P02 DETAIL: found 000000080000000000000020 in the repo1: 9.5-1 archive
P00 DETAIL: unable to find 000000080000000000000021 in the archive P00 INFO: archive-get:async command end: completed successfully [filtered 8 lines of output] P00 INFO: archive-get:async command begin 2.33: [000000080000000000000021, 000000080000000000000022, 000000080000000000000023, 000000080000000000000024, 000000080000000000000025, 000000080000000000000026, 000000080000000000000027, 000000080000000000000028] --archive-async --exec-id=1174-91099db7 --log-level-console=off --log-level-file=detail --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/pgsql/9.5/data --process-max=2 --repo1-host=repository --spool-path=/var/spool/pgbackrest --stanza=demo P00 INFO: get 8 WAL file(s) from archive: 000000080000000000000021...000000080000000000000028
P01 DETAIL: found 000000080000000000000021 in the repo1: 9.5-1 archive P02 DETAIL: found 000000080000000000000022 in the repo1: 9.5-1 archive P01 DETAIL: found 000000080000000000000023 in the repo1: 9.5-1 archive P02 DETAIL: found 000000080000000000000024 in the repo1: 9.5-1 archive P02 DETAIL: found 000000080000000000000026 in the repo1: 9.5-1 archive P01 DETAIL: found 000000080000000000000025 in the repo1: 9.5-1 archive
P00 DETAIL: unable to find 000000080000000000000027 in the archive P00 INFO: archive-get:async command end: completed successfully [filtered 5 lines of output]
sudo -u postgres psql -c "alter user replicator password 'jw8s0F4'"
ALTER ROLE
sudo -u pgbackrest pgbackrest --stanza=demo --log-level-console=detail backup
[filtered 2 lines of output] P00 INFO: execute exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes P00 INFO: backup start archive = 000000080000000000000028, lsn = 0/28000028
P00 INFO: wait for replay on the standby to reach 0/28000028 P00 INFO: replay on the standby reached 0/28000028 P01 INFO: backup file pg-primary:/var/lib/pgsql/9.5/data/global/pg_control (8KB, 0%) checksum 6b6fa2f56b15617837a6dba96d248281b81f7307 P01 INFO: backup file pg-primary:/var/lib/pgsql/9.5/data/pg_log/postgresql.log (5.3KB, 0%) checksum 61c1a652ab6d51495f5eb55a7339cb5637cb7573
P03 INFO: backup file pg-standby:/var/lib/pgsql/9.5/data/base/12442/2674 (360KB, 17%) checksum 5f2d8ce4ba484c237801c2c2fa05ee2699d12827
P01 INFO: backup file pg-primary:/var/lib/pgsql/9.5/data/pg_hba.conf (4.2KB, 17%) checksum f28f3f76528c7fd372aa22474954691c6341da76
P02 INFO: backup file pg-standby:/var/lib/pgsql/9.5/data/base/12442/2608 (432KB, 37%) checksum 16ca8431086fc0815453aeb7303728cb6703cdfc P04 INFO: backup file pg-standby:/var/lib/pgsql/9.5/data/base/12442/1249 (360KB, 54%) checksum a495cd5683699a4b5a78327d2e2d7fe53f004596
P01 INFO: backup file pg-primary:/var/lib/pgsql/9.5/data/backup_label (248B, 54%) checksum 84de72f6c1e85863027141f7e9d39f011187cd26
P03 INFO: backup file pg-standby:/var/lib/pgsql/9.5/data/base/12442/2673 (304KB, 68%) checksum f08fdd5262e892312729b9caa067362f061dcb54 P02 INFO: backup file pg-standby:/var/lib/pgsql/9.5/data/base/12442/2658 (112KB, 73%) checksum a84863c94717549cc097e0135db8f9bf94a4c854 [filtered 1127 lines of output]
sudo systemctl stop postgresql-9.5.service
sudo systemctl stop postgresql-9.5.service
sudo -u postgres /usr/pgsql-9.6/bin/initdb \ -D /var/lib/pgsql/9.6/data -k -A peer
sudo -u postgres sh -c 'cd /var/lib/pgsql && \ /usr/pgsql-9.6/bin/pg_upgrade \ --old-bindir=/usr/pgsql-9.5/bin \ --new-bindir=/usr/pgsql-9.6/bin \ --old-datadir=/var/lib/pgsql/9.5/data \ --new-datadir=/var/lib/pgsql/9.6/data \ --old-options=" -c config_file=/var/lib/pgsql/9.5/data/postgresql.conf" \ --new-options=" -c config_file=/var/lib/pgsql/9.6/data/postgresql.conf"'
[filtered 70 lines of output] Creating script to delete old cluster ok
Upgrade Complete
---------------- Optimizer statistics are not transferred by pg_upgrade so, [filtered 4 lines of output]
sudo cp /var/lib/pgsql/9.5/data/pg_hba.conf \ /var/lib/pgsql/9.6/data/pg_hba.conf
sudo -u postgres pgbackrest --stanza=demo --no-online \ --log-level-console=info stanza-upgrade
P00 INFO: stanza-upgrade command begin 2.33: --exec-id=4458-49751b43 --log-level-console=info --log-level-file=detail --log-level-stderr=off --no-log-timestamp --no-online --pg1-path=/var/lib/pgsql/9.6/data --repo1-host=repository --stanza=demo P00 INFO: stanza-upgrade for stanza 'demo' on repo1
P00 INFO: stanza-upgrade command end: completed successfully
sudo systemctl start postgresql-9.6.service
sudo -u postgres systemctl status postgresql-9.6.service
● postgresql-9.6.service - PostgreSQL 9.6 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-9.6.service; disabled; vendor preset: disabled) Active: active (running) since Fri 2021-04-02 18:03:29 UTC; 140ms ago Docs: https://www.postgresql.org/docs/9.6/static/ Process: 4480 ExecStartPre=/usr/pgsql-9.6/bin/postgresql96-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS) Main PID: 4485 (postmaster) CGroup: /docker/74f527caa59037838afc8377480e9f096064cb371f32cae4a8998ab9f66bc13b/system.slice/postgresql-9.6.service ├─4485 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/ ├─4486 postgres: logger process ├─4488 postgres: checkpointer process ├─4489 postgres: writer process ├─4490 postgres: wal writer process ├─4491 postgres: autovacuum launcher process ├─4492 postgres: archiver process └─4493 postgres: stats collector process
sudo -u postgres pgbackrest --stanza=demo check
sudo rm -rf /var/lib/pgsql/9.5/data
sudo rm -rf /var/lib/pgsql/9.5/data
sudo -u postgres mkdir -p -m 700 /usr/pgsql-9.6/bin
sudo -u pgbackrest pgbackrest --stanza=demo check
P00 WARN: unable to check pg-2: [DbConnectError] raised from remote-0 protocol on 'pg-standby': unable to connect to 'dbname='postgres' port=5432': could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
sudo -u pgbackrest pgbackrest --stanza=demo --type=full backup
sudo -u postgres pgbackrest --stanza=demo --type=standby restore
sudo systemctl start postgresql-9.6.service
sudo -u postgres pgbackrest --stanza=demo check