mkdir -p /build
wget -q -O - \ https://github.com/pgbackrest/pgbackrest/archive/release/2.55.1.tar.gz | \ tar zx -C /build
sudo apt-get install python3-distutils meson gcc libpq-dev libssl-dev libxml2-dev \ pkg-config liblz4-dev libzstd-dev libbz2-dev libz-dev libyaml-dev libssh2-1-dev
meson setup /build/pgbackrest /build/pgbackrest-release-2.55.1
ninja -C /build/pgbackrest
sudo apt-get install postgresql-client libxml2 libssh2-1
sudo scp build:/build/pgbackrest/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.55.1 - General help Usage: pgbackrest [options] [command] Commands: annotate add or modify backup annotation 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 server pgBackRest server server-ping ping pgBackRest server 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 verify verify contents of a repository version get version Use 'pgbackrest help [command]' for more information.
sudo -u postgres /usr/lib/postgresql/16/bin/initdb \ -D /var/lib/postgresql/16/demo -k -A peer
sudo pg_createcluster 16 demo
Configuring already existing cluster (configuration: /etc/postgresql/16/demo, data: /var/lib/postgresql/16/demo, owner: 102:103) Ver Cluster Port Status Owner Data directory Log file 16 demo 5432 down postgres /var/lib/postgresql/16/demo /var/log/postgresql/postgresql-16-demo.log
sudo -u postgres bash -c ' \ export PGBACKREST_LOG_PATH=/path/set/by/env && \ pgbackrest --log-level-console=error help backup log-path'
pgBackRest 2.55.1 - '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 pg_ctlcluster 16 demo restart
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info stanza-create
P00 INFO: stanza-create command begin 2.55.1: --exec-id=377-87743616 --log-level-console=info --no-log-timestamp --pg1-path=/var/lib/postgresql/16/demo --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.55.1: --exec-id=385-c96e0ee9 --log-level-console=info --no-log-timestamp --pg1-path=/var/lib/postgresql/16/demo --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/16-1/0000000100000000/000000010000000000000001-ce9289f79867819258af79ffa4d150deb16ca2d3.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.55.1: --exec-id=409-09d7370f --log-level-console=info --no-log-timestamp --pg1-path=/var/lib/postgresql/16/demo --repo1-cipher-pass=--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo --start-fast
P00 WARN: no prior backup exists, incr backup has been changed to full
P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes P00 INFO: backup start archive = 000000010000000000000002, lsn = 0/2000028 [filtered 3 lines of output] P00 INFO: check archive for segment(s) 000000010000000000000002:000000010000000000000003 P00 INFO: new backup label = 20250505-153608F
P00 INFO: full backup size = 22.0MB, file total = 963
P00 INFO: backup command end: completed successfully P00 INFO: expire command begin 2.55.1: --exec-id=409-09d7370f --log-level-console=info --no-log-timestamp --repo1-cipher-pass=--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo
sudo -u postgres pgbackrest --stanza=demo --type=diff \ --log-level-console=info backup
[filtered 7 lines of output] P00 INFO: check archive for segment(s) 000000010000000000000004:000000010000000000000005 P00 INFO: new backup label = 20250505-153608F_20250505-153612D
P00 INFO: diff backup size = 8.3KB, file total = 963
P00 INFO: backup command end: completed successfully P00 INFO: expire command begin 2.55.1: --exec-id=434-a44db86f --log-level-console=info --no-log-timestamp --repo1-cipher-pass=--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo
#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 (16): 000000010000000000000001/000000010000000000000005
full backup: 20250505-153608F
timestamp start/stop: 2025-05-05 15:36:08+00 / 2025-05-05 15:36:11+00 wal start/stop: 000000010000000000000002 / 000000010000000000000003 database size: 22.0MB, database backup size: 22.0MB repo1: backup set size: 2.9MB, backup size: 2.9MB
diff backup: 20250505-153608F_20250505-153612D
timestamp start/stop: 2025-05-05 15:36:12+00 / 2025-05-05 15:36:13+00 wal start/stop: 000000010000000000000004 / 000000010000000000000005 database size: 22.0MB, database backup size: 8.3KB repo1: backup set size: 2.9MB, backup size: 464B backup reference total: 1 full
sudo pg_ctlcluster 16 demo stop
sudo -u postgres rm /var/lib/postgresql/16/demo/global/pg_control
sudo pg_ctlcluster 16 demo start
Error: /usr/lib/postgresql/16/bin/pg_ctl /usr/lib/postgresql/16/bin/pg_ctl start -D /var/lib/postgresql/16/demo -l /var/log/postgresql/postgresql-16-demo.log -s -o -c config_file="/etc/postgresql/16/demo/postgresql.conf" exited with status 1:
postgres: could not find the database system
Expected to find it in the directory "/var/lib/postgresql/16/demo", but could not open file "/var/lib/postgresql/16/demo/global/pg_control": No such file or directory Examine the log output.
sudo -u postgres find /var/lib/postgresql/16/demo -mindepth 1 -delete
sudo -u postgres pgbackrest --stanza=demo restore
sudo pg_ctlcluster 16 demo start
sudo -u postgres cat \ /var/lib/postgresql/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 text); -- 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 replace(temp_pgbackrest_data.data, E'\n', '\n')::jsonb into data from temp_pgbackrest_data; drop table temp_pgbackrest_data; return data; end $$ language plpgsql;
sudo -u postgres psql -f \ /var/lib/postgresql/pgbackrest/doc/example/pgsql-pgbackrest-info.sql
sudo -u postgres cat \ /var/lib/postgresql/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/postgresql/pgbackrest/doc/example/pgsql-pgbackrest-query.sql
name | last_successful_backup | last_archived_wal --------+------------------------+-------------------------- "demo" | 2025-05-05 15:36:13+00 | 000000010000000000000005 (1 row)
sudo apt-get install jq
sudo -u postgres pgbackrest --output=json --stanza=demo info | \ jq '.[0] | .backup[-1] | .timestamp.stop'
1746459373
sudo -u postgres pgbackrest --output=json --stanza=demo info | \ jq '.[0] | .archive[-1] | .max'
"000000010000000000000005"
sudo -u postgres pgbackrest --stanza=demo --type=full backup
sudo -u postgres find /var/lib/pgbackrest/backup/demo/latest/ -type f | wc -l
5
sudo -u postgres pgbackrest --stanza=demo --annotation=source="demo backup" \ --annotation=key=value --type=full backup
sudo -u postgres pgbackrest --stanza=demo --set=20250505-153628F info
stanza: demo status: ok cipher: aes-256-cbc db (current) wal archive min/max (16): 000000020000000000000007/000000020000000000000009 full backup: 20250505-153628F timestamp start/stop: 2025-05-05 15:36:28+00 / 2025-05-05 15:36:29+00 wal start/stop: 000000020000000000000008 / 000000020000000000000009 lsn start/stop: 0/8000028 / 0/9000050 database size: 22.0MB, database backup size: 22.0MB repo1: backup size: 2.9MB database list: postgres (5)
annotation(s)
key: value source: demo backup
sudo -u postgres pgbackrest --stanza=demo --set=20250505-153628F \ --annotation=key= --annotation=new_key=new_value annotate
sudo -u postgres pgbackrest --stanza=demo --set=20250505-153628F info
stanza: demo status: ok cipher: aes-256-cbc db (current) wal archive min/max (16): 000000020000000000000007/000000020000000000000009 full backup: 20250505-153628F timestamp start/stop: 2025-05-05 15:36:28+00 / 2025-05-05 15:36:29+00 wal start/stop: 000000020000000000000008 / 000000020000000000000009 lsn start/stop: 0/8000028 / 0/9000050 database size: 22.0MB, database backup size: 22.0MB repo1: backup size: 2.9MB database list: postgres (5)
annotation(s)
new_key: new_value source: demo backup
sudo -u postgres pgbackrest --stanza=demo --type=full \ --log-level-console=detail backup
[filtered 975 lines of output] P00 INFO: repo1: remove expired backup 20250505-153625F P00 DETAIL: repo1: 16-1 archive retention on backup 20250505-153628F, start = 000000020000000000000008
P00 INFO: repo1: 16-1 remove archive, start = 000000020000000000000007, stop = 000000020000000000000007
P00 INFO: expire command end: completed successfully
sudo -u postgres pgbackrest --stanza=demo --type=full \ --log-level-console=info backup
[filtered 11 lines of output] P00 INFO: repo1: expire full backup 20250505-153628F P00 INFO: repo1: remove expired backup 20250505-153628F
P00 INFO: repo1: 16-1 remove archive, start = 000000020000000000000008, stop = 00000002000000000000000A
P00 INFO: expire command end: completed successfully
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 10 lines of output] P00 INFO: backup command end: completed successfully P00 INFO: expire command begin 2.55.1: --exec-id=874-b831e96e --log-level-console=info --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 20250505-153633F_20250505-153636D, 20250505-153633F_20250505-153638I
P00 INFO: repo1: remove expired backup 20250505-153633F_20250505-153638I P00 INFO: repo1: remove expired backup 20250505-153633F_20250505-153636D P00 INFO: expire command end: completed successfully
sudo -u postgres pgbackrest --stanza=demo --type=diff \ --log-level-console=info backup
[filtered 6 lines of output] P00 INFO: backup stop archive = 000000020000000000000017, lsn = 0/17000050 P00 INFO: check archive for segment(s) 000000020000000000000016:000000020000000000000017
P00 INFO: new backup label = 20250505-153633F_20250505-153643D
P00 INFO: diff backup size = 8.3KB, file total = 963 P00 INFO: backup command end: completed successfully [filtered 2 lines of output]
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.55.1: --exec-id=950-c6af69bb --log-level-console=detail --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: 16-1 archive retention on backup 20250505-153631F, start = 00000002000000000000000B, stop = 00000002000000000000000B P00 DETAIL: repo1: 16-1 archive retention on backup 20250505-153633F, start = 00000002000000000000000C, stop = 00000002000000000000000D
P00 DETAIL: repo1: 16-1 archive retention on backup 20250505-153633F_20250505-153640D, start = 000000020000000000000012, stop = 000000020000000000000013
P00 DETAIL: repo1: 16-1 archive retention on backup 20250505-153633F_20250505-153643D, start = 000000020000000000000016
P00 INFO: repo1: 16-1 remove archive, start = 00000002000000000000000E, stop = 000000020000000000000011 P00 INFO: repo1: 16-1 remove archive, start = 000000020000000000000014, stop = 000000020000000000000015
P00 INFO: expire command end: completed successfully
sudo pg_ctlcluster 16 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta \ --log-level-console=detail restore
[filtered 2 lines of output] P00 DETAIL: check '/var/lib/postgresql/16/demo' 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/postgresql/16/demo'
P00 DETAIL: remove invalid file '/var/lib/postgresql/16/demo/backup_label.old' P00 DETAIL: remove invalid file '/var/lib/postgresql/16/demo/base/1/pg_internal.init' [filtered 13 lines of output] P00 DETAIL: remove invalid file '/var/lib/postgresql/16/demo/postmaster.opts' P01 DETAIL: restore file /var/lib/postgresql/16/demo/backup_label (260B, 0.00%) checksum ad24e14059cb8ecf184023253f8b5fca6f14af04
P01 DETAIL: restore file /var/lib/postgresql/16/demo/PG_VERSION - exists and matches backup (bundle 20250505-153633F/1/0, 3B, 0.00%) checksum 3596ea087bfdaf52380eae441077572ed289d657
P01 DETAIL: restore file /var/lib/postgresql/16/demo/pg_multixact/members/0000 - exists and matches backup (bundle 20250505-153633F/1/24, 8KB, 0.04%) checksum 0631457264ff7f8d5fb1edc2c0211992a67c73e6 P01 DETAIL: restore file /var/lib/postgresql/16/demo/global/pg_filenode.map - exists and matches backup (bundle 20250505-153633F/1/64, 524B, 0.04%) checksum 3de878cf56cdb80345e88da11f91bc6d46f9f804 [filtered 988 lines of output]
sudo pg_ctlcluster 16 demo start
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
CREATE TABLE INSERT 0 1
sudo -u postgres psql -c "create table test2_table (id int); \ insert into test2_table (id) values (2);" test2
CREATE TABLE INSERT 0 1
sudo -u postgres pgbackrest --stanza=demo --type=incr backup
sudo -u postgres du -sh /var/lib/postgresql/16/demo/base/32768
7.4M /var/lib/postgresql/16/demo/base/32768
sudo -u postgres pgbackrest --stanza=demo \ --set=20250505-153633F_20250505-153651I info
[filtered 12 lines of output] repo1: backup size: 2.0MB backup reference list: 20250505-153633F, 20250505-153633F_20250505-153643D
database list: postgres (5), test1 (32768), test2 (32769)
sudo pg_ctlcluster 16 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta \ --db-include=test2 --type=immediate --target-action=promote restore
sudo pg_ctlcluster 16 demo start
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: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: relation mapping file "base/32768/pg_filenode.map" contains invalid data
sudo -u postgres du -sh /var/lib/postgresql/16/demo/base/32768
8.0K /var/lib/postgresql/16/demo/base/32768
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 4 | template0 5 | postgres
32769 | test2
(4 rows)
sudo -u postgres psql -c "begin; \ create table important_table (message text); \ insert into important_table values ('Important Data'); \ commit; \ select * from important_table;"
[filtered 4 lines of output] message ----------------
Important Data
(1 row)
sudo -u postgres psql -Atc "select current_timestamp"
2025-05-05 15:37:03.157376+00
sudo -u postgres psql -c "begin; \ drop table important_table; \ commit; \ select * from important_table;"
BEGIN DROP TABLE
COMMITERROR: 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
[filtered 38 lines of output] backup reference total: 1 full, 1 diff
incr backup: 20250505-153633F_20250505-153704I
timestamp start/stop: 2025-05-05 15:37:04+00 / 2025-05-05 15:37:05+00 wal start/stop: 00000004000000000000001A / 00000004000000000000001A [filtered 2 lines of output]
sudo pg_ctlcluster 16 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta \ --set=20250505-153633F_20250505-153704I --target-timeline=current \ --type=time "--target=2025-05-05 15:37:03.157376+00" --target-action=promote restore
sudo pg_ctlcluster 16 demo start
[filtered 13 lines of output] LOG: database system is ready to accept read-only connections LOG: redo done at 0/1A000100 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.02 s
FATAL: recovery ended before configured recovery target was reached
LOG: startup process (PID 1321) exited with exit code 1 LOG: terminating any other active server processes LOG: database system is shut down
sudo -u postgres pgbackrest --stanza=demo --delta \ --type=time "--target=2025-05-05 15:37:03.157376+00" \ --target-action=promote restore
sudo -u postgres cat /var/lib/postgresql/16/demo/postgresql.auto.conf
[filtered 9 lines of output] # Recovery settings generated by pgBackRest restore on 2025-05-05 15:37:08 restore_command = 'pgbackrest --stanza=demo archive-get %f "%p"'
recovery_target_time = '2025-05-05 15:37:03.157376+00'
recovery_target_action = 'promote'
sudo pg_ctlcluster 16 demo start
sudo -u postgres psql -c "select * from important_table"
message ----------------
Important Data
(1 row)
sudo -u postgres cat /var/log/postgresql/postgresql-16-demo.log
[filtered 4 lines of output] LOG: database system was interrupted; last known up at 2025-05-05 15:36:52 UTC LOG: restored log file "00000004.history" from archive
LOG: starting point-in-time recovery to 2025-05-05 15:37:03.157376+00
LOG: starting backup recovery with redo LSN 0/19000028, checkpoint LSN 0/19000060, on timeline ID 3 LOG: restored log file "00000004.history" from archive [filtered 5 lines of output] LOG: database system is ready to accept read-only connections LOG: restored log file "00000004000000000000001A" from archive
LOG: recovery stopping before commit of transaction 740, time 2025-05-05 15:37:04.47197+00
LOG: redo done at 0/19026050 system usage: CPU: user: 0.00 s, system: 0.01 s, elapsed: 0.07 s
LOG: last completed transaction was at log time 2025-05-05 15:37:01.902745+00
LOG: restored log file "000000040000000000000019" from archive LOG: selected new timeline ID: 5 [filtered 5 lines of output]
sudo pg_ctlcluster 16 demo stop
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info stop
P00 INFO: stop command begin 2.55.1: --exec-id=1443-49e13bce --log-level-console=info --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.55.1: --exec-id=1450-c989c5ff --log-level-console=info --no-log-timestamp --pg1-path=/var/lib/postgresql/16/demo --repo=1 --repo1-cipher-pass=--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=demo
P00 INFO: stanza-delete command end: completed successfully
hierarchical namespaceas this will cause errors during expire.
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info stanza-create
P00 INFO: stanza-create command begin 2.55.1: --exec-id=1521-8d36a4a8 --log-level-console=info --no-log-timestamp --pg1-path=/var/lib/postgresql/16/demo --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 --repo2-type=azure --stanza=demo P00 INFO: stanza-create for stanza 'demo' on repo1 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.55.1: --exec-id=1529-81f60a40 --log-level-console=info --no-log-timestamp --pg1-path=/var/lib/postgresql/16/demo --repo=2 --repo2-azure-account=--repo2-azure-container=demo-container --repo2-azure-key= --repo1-block --repo1-bundle --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-type=azure --stanza=demo --start-fast
P00 WARN: no prior backup exists, incr backup has been changed to full
P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes P00 INFO: backup start archive = 00000005000000000000001B, lsn = 0/1B000028 [filtered 3 lines of output] P00 INFO: check archive for segment(s) 00000005000000000000001B:00000005000000000000001B P00 INFO: new backup label = 20250505-153719F
P00 INFO: full backup size = 29.1MB, file total = 1265
P00 INFO: backup command end: completed successfully P00 INFO: expire command begin 2.55.1: --exec-id=1529-81f60a40 --log-level-console=info --no-log-timestamp --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-type=azure --stanza=demo
{ "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:PutObjectTagging", "s3:GetObject", "s3:GetObjectVersion", "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.55.1: --exec-id=1577-45157b4a --log-level-console=info --no-log-timestamp --pg1-path=/var/lib/postgresql/16/demo --repo=3 --repo2-azure-account=--repo2-azure-container=demo-container --repo2-azure-key= --repo1-block --repo1-bundle --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-type=azure --repo3-type=s3 --stanza=demo --start-fast
P00 WARN: no prior backup exists, incr backup has been changed to full
P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes P00 INFO: backup start archive = 00000005000000000000001C, lsn = 0/1C000028 [filtered 3 lines of output] P00 INFO: check archive for segment(s) 00000005000000000000001C:00000005000000000000001D P00 INFO: new backup label = 20250505-153728F
P00 INFO: full backup size = 29.1MB, file total = 1265
P00 INFO: backup command end: completed successfully P00 INFO: expire command begin 2.55.1: --exec-id=1577-45157b4a --log-level-console=info --no-log-timestamp --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-type=azure --repo3-type=s3 --stanza=demo
sudo -u postgres mkdir -m 750 -p /var/lib/postgresql/.ssh
sudo -u postgres ssh-keygen -f /var/lib/postgresql/.ssh/id_rsa_sftp \ -t rsa -b 4096 -N "" -m PEM
sudo -u pgbackrest mkdir -m 750 -p /home/pgbackrest/.ssh
(sudo ssh root@pg-primary cat /var/lib/postgresql/.ssh/id_rsa_sftp.pub) | \ sudo -u pgbackrest tee -a /home/pgbackrest/.ssh/authorized_keys
strict
ssh-keyscan -H sftp-server >> /var/lib/postgresql/.ssh/known_hosts 2>/dev/null
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info stanza-create
[filtered 6 lines of output] P00 INFO: stanza 'demo' already exists on repo3 and is valid P00 INFO: stanza-create for stanza 'demo' on repo4
P00 INFO: stanza-create command end: completed successfully
sudo -u postgres pgbackrest --stanza=demo --repo=4 \ --log-level-console=info backup
P00 INFO: backup command begin 2.55.1: --exec-id=1656-747a1781 --log-level-console=info --no-log-timestamp --pg1-path=/var/lib/postgresql/16/demo --process-max=4 --repo=4 --repo2-azure-account=--repo2-azure-container=demo-container --repo2-azure-key= --repo1-block --repo1-bundle --repo4-bundle --repo1-cipher-pass= --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo2-path=/demo-repo --repo3-path=/demo-repo --repo4-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 --repo4-sftp-host=sftp-server --repo4-sftp-host-key-hash-type=sha1 --repo4-sftp-host-user=pgbackrest --repo4-sftp-private-key-file=/var/lib/postgresql/.ssh/id_rsa_sftp --repo4-sftp-public-key-file=/var/lib/postgresql/.ssh/id_rsa_sftp.pub --repo2-type=azure --repo3-type=s3 --repo4-type=sftp --stanza=demo --start-fast P00 WARN: option 'repo4-retention-full' is not set for 'repo4-retention-full-type=count', the repository may run out of space HINT: to retain full backups indefinitely (without warning), set option 'repo4-retention-full' to the maximum.
P00 WARN: no prior backup exists, incr backup has been changed to full
P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes P00 INFO: backup start archive = 00000005000000000000001E, lsn = 0/1E000028 [filtered 3 lines of output] P00 INFO: check archive for segment(s) 00000005000000000000001E:00000005000000000000001F P00 INFO: new backup label = 20250505-153736F
P00 INFO: full backup size = 29.1MB, file total = 1265
P00 INFO: backup command end: completed successfully P00 INFO: expire command begin 2.55.1: --exec-id=1656-747a1781 --log-level-console=info --no-log-timestamp --repo=4 --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 --repo4-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 --repo4-sftp-host=sftp-server --repo4-sftp-host-key-hash-type=sha1 --repo4-sftp-host-user=pgbackrest --repo4-sftp-private-key-file=/var/lib/postgresql/.ssh/id_rsa_sftp --repo4-sftp-public-key-file=/var/lib/postgresql/.ssh/id_rsa_sftp.pub --repo2-type=azure --repo3-type=s3 --repo4-type=sftp --stanza=demo P00 INFO: expire command end: completed successfully
sudo pg_ctlcluster 16 demo stop
sudo -u postgres pgbackrest --stanza=demo stop
sudo -u postgres pgbackrest --stanza=demo --repo=3 stanza-delete
sudo -u postgres pgbackrest --stanza=demo --repo=3 info
stanza: demo
status: error (missing stanza data)
cipher: none
mc ls --versions s3/demo-bucket/demo-repo/backup/demo/backup.info
[2025-05-05 15:37:42 UTC] 0B STANDARD 4b8a6087-660b-4454-b54b-f23b4c99166d v3 DEL backup.info [2025-05-05 15:37:33 UTC] 1.0KiB STANDARD ff3f0b2c-36f0-4a39-bd98-95e231304bf7 v2 PUT backup.info [2025-05-05 15:37:27 UTC] 372B STANDARD 3665e998-7670-4201-be92-72f1f448dbea v1 PUT backup.info
[2025-05-05 15:37:42 UTC] 0B STANDARD 15063baa-a307-498c-95df-0c2c6c0e74f6 v3 DEL backup.info.copy [2025-05-05 15:37:33 UTC] 1.0KiB STANDARD 487bbb33-04f6-4b6d-81d7-84bb3521b2f5 v2 PUT backup.info.copy
sudo -u postgres pgbackrest --stanza=demo --repo=3 \ --repo-target-time="2025-05-05 15:37:33+00" info
[filtered 5 lines of output] wal archive min/max (16): 00000005000000000000001C/00000005000000000000001D
full backup: 20250505-153728F
timestamp start/stop: 2025-05-05 15:37:28+00 / 2025-05-05 15:37:33+00 wal start/stop: 00000005000000000000001C / 00000005000000000000001D repo3: backup set size: 3.9MB, backup size: 3.9MB
sudo -u postgres pgbackrest --stanza=demo --repo=3 --delta \ --repo-target-time="2025-05-05 15:37:33+00" --log-level-console=info restore
P00 INFO: restore command begin 2.55.1: --delta --exec-id=1733-c6bc056e --log-level-console=info --no-log-timestamp --pg1-path=/var/lib/postgresql/16/demo --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 --repo5-gcs-bucket=demo-bucket --repo5-gcs-key= --repo1-path=/var/lib/pgbackrest --repo2-path=/demo-repo --repo3-path=/demo-repo --repo4-path=/demo-repo --repo5-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 --repo4-sftp-host=sftp-server --repo4-sftp-host-key-hash-type=sha1 --repo4-sftp-host-user=pgbackrest --repo4-sftp-private-key-file=/var/lib/postgresql/.ssh/id_rsa_sftp --repo4-sftp-public-key-file=/var/lib/postgresql/.ssh/id_rsa_sftp.pub --repo-target-time="2025-05-05 15:37:33+00" --repo2-type=azure --repo3-type=s3 --repo4-type=sftp --repo5-type=gcs --stanza=demo
P00 INFO: repo3: restore backup set 20250505-153728F, recovery will start at 2025-05-05 15:37:28
P00 INFO: remove invalid files/links/paths from '/var/lib/postgresql/16/demo' P00 INFO: write updated /var/lib/postgresql/16/demo/postgresql.auto.conf [filtered 2 lines of output]
sudo pg_ctlcluster 16 demo start
sudo adduser --disabled-password --gecos "" pgbackrest
sudo apt-get install postgresql-client libxml2 libssh2-1
sudo scp build:/build/pgbackrest/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/postgresql/.ssh
sudo -u postgres ssh-keygen -f /var/lib/postgresql/.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/postgresql/.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/postgresql/.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 pg_ctlcluster 16 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta restore
sudo pg_ctlcluster 16 demo start
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 (16): 000000070000000000000023/000000070000000000000025 full backup: 20250505-153818F
timestamp start/stop: 2025-05-05 15:38:18+00 / 2025-05-05 15:38:21+00
wal start/stop: 000000070000000000000023 / 000000070000000000000023 database size: 29.1MB, database backup size: 29.1MB repo1: backup set size: 3.9MB, backup size: 3.9MB full backup: 20250505-153823F
timestamp start/stop: 2025-05-05 15:38:23+00 / 2025-05-05 15:38:28+00
wal start/stop: 000000070000000000000024 / 000000070000000000000025 database size: 29.1MB, database backup size: 29.1MB repo1: backup set size: 3.9MB, backup size: 3.9MB
sudo -u postgres pgbackrest stop
sudo -u pgbackrest pgbackrest --stanza=demo backup
P00 WARN: unable to check pg1: [StopError] raised from remote-0 ssh protocol on 'pg-primary': stop file exists for all stanzas
P00 ERROR: [056]: unable to find primary cluster - cannot proceed HINT: are all available clusters in recovery?
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 pg1: [StopError] raised from remote-0 ssh protocol on 'pg-primary': stop file exists for stanza demo
P00 ERROR: [056]: unable to find primary cluster - cannot proceed HINT: are all available clusters in recovery?
sudo -u postgres pgbackrest --stanza=demo start
sudo apt-get install postgresql-client libxml2 libssh2-1
sudo scp build:/build/pgbackrest/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/postgresql/.ssh
sudo -u postgres ssh-keygen -f /var/lib/postgresql/.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/postgresql/.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/postgresql/.ssh/authorized_keys
sudo -u pgbackrest ssh postgres@pg-standby
sudo -u postgres ssh pgbackrest@repository
sudo pg_createcluster 16 demo
sudo -u postgres pgbackrest --stanza=demo --delta --type=standby restore
sudo -u postgres cat /var/lib/postgresql/16/demo/postgresql.auto.conf
# Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. # Recovery settings generated by pgBackRest restore on 2025-05-05 15:36:15 restore_command = 'pgbackrest --stanza=demo archive-get %f "%p"' # Recovery settings generated by pgBackRest restore on 2025-05-05 15:36:45 restore_command = 'pgbackrest --stanza=demo archive-get %f "%p"' # Recovery settings generated by pgBackRest restore on 2025-05-05 15:37:08 restore_command = 'pgbackrest --stanza=demo archive-get %f "%p"' # Removed by pgBackRest restore on 2025-05-05 15:37:46 # recovery_target_time = '2025-05-05 15:37:03.157376+00' # Removed by pgBackRest restore on 2025-05-05 15:37:46 # recovery_target_action = 'promote' # Recovery settings generated by pgBackRest restore on 2025-05-05 15:37:46 restore_command = 'pgbackrest --repo=3 --repo-target-time="2025-05-05 15:37:33+00" --stanza=demo archive-get %f "%p"' # Recovery settings generated by pgBackRest restore on 2025-05-05 15:38:11 restore_command = 'pgbackrest --stanza=demo archive-get %f "%p"' # Recovery settings generated by pgBackRest restore on 2025-05-05 15:38:43 restore_command = 'pgbackrest --stanza=demo archive-get %f "%p"'
sudo pg_ctlcluster 16 demo start
sudo -u postgres cat /var/log/postgresql/postgresql-16-demo.log
[filtered 3 lines of output] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" LOG: database system was interrupted; last known up at 2025-05-05 15:38:23 UTC
LOG: entering standby mode
LOG: starting backup recovery with redo LSN 0/24000028, checkpoint LSN 0/24000060, on timeline ID 7 LOG: restored log file "00000007.history" from archive [filtered 6 lines of output]
sudo -u postgres psql -c " \ begin; \ create table replicated_table (message text); \ insert into replicated_table values ('Important Data'); \ commit; \ select * from replicated_table";
[filtered 4 lines of output] 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_wal()";
pg_switch_wal | current_timestamp ---------------+------------------------------- 0/2601A838 | 2025-05-05 15:38:50.596767+00 (1 row)
sudo -u postgres psql -c " \ select *, current_timestamp from replicated_table"
message | current_timestamp ----------------+-------------------------------
Important Data | 2025-05-05 15:38:56.632927+00
(1 row)
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info check
P00 INFO: check command begin 2.55.1: --exec-id=586-efbb5ee1 --log-level-console=info --log-level-file=detail --no-log-timestamp --pg1-path=/var/lib/postgresql/16/demo --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.8/32 md5" \ >> /etc/postgresql/16/demo/pg_hba.conf'
sudo pg_ctlcluster 16 demo reload
sudo -u postgres sh -c 'echo \ "172.17.0.6:*:replication:replicator:jw8s0F4" \ >> /var/lib/postgresql/.pgpass'
sudo -u postgres chmod 600 /var/lib/postgresql/.pgpass
sudo pg_ctlcluster 16 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta --type=standby restore
sudo -u postgres cat /var/lib/postgresql/16/demo/postgresql.auto.conf
# Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. # Recovery settings generated by pgBackRest restore on 2025-05-05 15:36:15 restore_command = 'pgbackrest --stanza=demo archive-get %f "%p"' # Recovery settings generated by pgBackRest restore on 2025-05-05 15:36:45 restore_command = 'pgbackrest --stanza=demo archive-get %f "%p"' # Recovery settings generated by pgBackRest restore on 2025-05-05 15:37:08 restore_command = 'pgbackrest --stanza=demo archive-get %f "%p"' # Removed by pgBackRest restore on 2025-05-05 15:37:46 # recovery_target_time = '2025-05-05 15:37:03.157376+00' # Removed by pgBackRest restore on 2025-05-05 15:37:46 # recovery_target_action = 'promote' # Recovery settings generated by pgBackRest restore on 2025-05-05 15:37:46 restore_command = 'pgbackrest --repo=3 --repo-target-time="2025-05-05 15:37:33+00" --stanza=demo archive-get %f "%p"' # Recovery settings generated by pgBackRest restore on 2025-05-05 15:38:11 restore_command = 'pgbackrest --stanza=demo archive-get %f "%p"' # Recovery settings generated by pgBackRest restore on 2025-05-05 15:38:59 primary_conninfo = 'host=172.17.0.6 port=5432 user=replicator' restore_command = 'pgbackrest --stanza=demo archive-get %f "%p"'
sudo pg_ctlcluster 16 demo start
sudo -u postgres cat /var/log/postgresql/postgresql-16-demo.log
[filtered 13 lines of output] LOG: consistent recovery state reached at 0/25000088 LOG: database system is ready to accept read-only connections
LOG: started streaming WAL from primary at 0/27000000 on timeline 7
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";
[filtered 4 lines of output] message | current_timestamp ----------------+-------------------------------
Important Data | 2025-05-05 15:39:06.086007+00
(1 row)
sudo -u postgres psql -c " \ select *, current_timestamp from stream_table"
message | current_timestamp ----------------+-------------------------------
Important Data | 2025-05-05 15:39:06.483319+00
(1 row)
sudo apt-get install postgresql-client libxml2 libssh2-1
sudo scp build:/build/pgbackrest/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/postgresql/.ssh
sudo -u postgres ssh-keygen -f /var/lib/postgresql/.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-alt cat /var/lib/postgresql/.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/postgresql/.ssh/authorized_keys
sudo -u pgbackrest ssh postgres@pg-alt
sudo -u postgres ssh pgbackrest@repository
sudo -u postgres /usr/lib/postgresql/16/bin/initdb \ -D /var/lib/postgresql/16/demo -k -A peer
sudo pg_createcluster 16 demo
Configuring already existing cluster (configuration: /etc/postgresql/16/demo, data: /var/lib/postgresql/16/demo, owner: 102:103) Ver Cluster Port Status Owner Data directory Log file 16 demo 5432 down postgres /var/lib/postgresql/16/demo /var/log/postgresql/postgresql-16-demo.log
sudo pg_ctlcluster 16 demo restart
sudo -u postgres pgbackrest --stanza=demo-alt --log-level-console=info stanza-create
P00 INFO: stanza-create command begin 2.55.1: --exec-id=350-5f466612 --log-level-console=info --log-level-file=detail --no-log-timestamp --pg1-path=/var/lib/postgresql/16/demo --repo1-host=repository --stanza=demo-alt P00 INFO: stanza-create for stanza 'demo-alt' on repo1
P00 INFO: stanza-create command end: completed successfully
sudo -u postgres pgbackrest --log-level-console=info check
P00 INFO: check command begin 2.55.1: --exec-id=359-3c8eb968 --log-level-console=info --log-level-file=detail --no-log-timestamp --repo1-host=repository
P00 INFO: check stanza 'demo-alt'
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-alt/16-1/0000000100000000/000000010000000000000001-d7ba3d079e9c0ecc7d33e16d7b45a0a2f58adc95.gz' on repo1
P00 INFO: check command end: completed successfully
sudo -u pgbackrest pgbackrest --log-level-console=info check
P00 INFO: check command begin 2.55.1: --exec-id=1241-54620747 --log-level-console=info --no-log-timestamp --repo1-path=/var/lib/pgbackrest
P00 INFO: check stanza 'demo'
P00 INFO: check repo1 configuration (primary) P00 INFO: check repo1 archive for WAL (primary)
P00 INFO: WAL segment 000000070000000000000027 successfully archived to '/var/lib/pgbackrest/archive/demo/16-1/0000000700000000/000000070000000000000027-14a933858863107b129b7f0e18a08d88ef43be6a.gz' on repo1 P00 INFO: check stanza 'demo-alt'
P00 INFO: check repo1 configuration (primary) P00 INFO: check repo1 archive for WAL (primary)
P00 INFO: WAL segment 000000010000000000000002 successfully archived to '/var/lib/pgbackrest/archive/demo-alt/16-1/0000000100000000/000000010000000000000002-c47a526be6ff2c84659563d1bb7e1b73eac35985.gz' on repo1
P00 INFO: check command end: completed successfully
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 pg_ctlcluster 16 demo restart
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_wal(); \ select pg_create_restore_point('test async push'); select pg_switch_wal(); \ select pg_create_restore_point('test async push'); select pg_switch_wal(); \ select pg_create_restore_point('test async push'); select pg_switch_wal(); \ select pg_create_restore_point('test async push'); select pg_switch_wal();"
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info check
P00 INFO: check command begin 2.55.1: --exec-id=2341-bdea8c52 --log-level-console=info --log-level-file=detail --no-log-timestamp --pg1-path=/var/lib/postgresql/16/demo --repo1-host=repository --stanza=demo P00 INFO: check repo1 configuration (primary) P00 INFO: check repo1 archive for WAL (primary)
P00 INFO: WAL segment 00000007000000000000002D successfully archived to '/var/lib/pgbackrest/archive/demo/16-1/0000000700000000/00000007000000000000002D-7d798d0ab5870d10a755a412464de99fb662b910.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.55.1: [/var/lib/postgresql/16/demo/pg_wal] --archive-async --exec-id=2327-27143da5 --log-level-console=off --log-level-file=detail --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/postgresql/16/demo --process-max=2 --repo1-host=repository --spool-path=/var/spool/pgbackrest --stanza=demo
P00 INFO: push 1 WAL file(s) to archive: 000000070000000000000028 P01 DETAIL: pushed WAL file '000000070000000000000028' to the archive
P00 INFO: archive-push:async command end: completed successfully -------------------PROCESS START------------------- P00 INFO: archive-push:async command begin 2.55.1: [/var/lib/postgresql/16/demo/pg_wal] --archive-async --exec-id=2345-a01809d1 --log-level-console=off --log-level-file=detail --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/postgresql/16/demo --process-max=2 --repo1-host=repository --spool-path=/var/spool/pgbackrest --stanza=demo
P00 INFO: push 5 WAL file(s) to archive: 000000070000000000000029...00000007000000000000002D P02 DETAIL: pushed WAL file '00000007000000000000002A' to the archive P01 DETAIL: pushed WAL file '000000070000000000000029' to the archive P02 DETAIL: pushed WAL file '00000007000000000000002B' to the archive P01 DETAIL: pushed WAL file '00000007000000000000002C' to the archive P02 DETAIL: pushed WAL file '00000007000000000000002D' 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.55.1: [000000070000000000000024, 000000070000000000000025, 000000070000000000000026, 000000070000000000000027, 000000070000000000000028, 000000070000000000000029, 00000007000000000000002A, 00000007000000000000002B] --archive-async --exec-id=790-af06cf41 --log-level-console=off --log-level-file=detail --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/postgresql/16/demo --process-max=2 --repo1-host=repository --spool-path=/var/spool/pgbackrest --stanza=demo P00 INFO: get 8 WAL file(s) from archive: 000000070000000000000024...00000007000000000000002B
P02 DETAIL: found 000000070000000000000025 in the repo1: 16-1 archive P01 DETAIL: found 000000070000000000000024 in the repo1: 16-1 archive P02 DETAIL: found 000000070000000000000026 in the repo1: 16-1 archive P01 DETAIL: found 000000070000000000000027 in the repo1: 16-1 archive
P00 DETAIL: unable to find 000000070000000000000028 in the archive P00 INFO: archive-get:async command end: completed successfully [filtered 14 lines of output] P00 INFO: archive-get:async command begin 2.55.1: [000000070000000000000028, 000000070000000000000029, 00000007000000000000002A, 00000007000000000000002B, 00000007000000000000002C, 00000007000000000000002D, 00000007000000000000002E, 00000007000000000000002F] --archive-async --exec-id=833-0b7bea8c --log-level-console=off --log-level-file=detail --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/postgresql/16/demo --process-max=2 --repo1-host=repository --spool-path=/var/spool/pgbackrest --stanza=demo P00 INFO: get 8 WAL file(s) from archive: 000000070000000000000028...00000007000000000000002F
P01 DETAIL: found 000000070000000000000028 in the repo1: 16-1 archive P02 DETAIL: found 000000070000000000000029 in the repo1: 16-1 archive P01 DETAIL: found 00000007000000000000002A in the repo1: 16-1 archive
P00 DETAIL: unable to find 00000007000000000000002B in the archive P00 INFO: archive-get:async command end: completed successfully [filtered 2 lines of output] P00 INFO: archive-get:async command begin 2.55.1: [00000007000000000000002B, 00000007000000000000002C, 00000007000000000000002D, 00000007000000000000002E, 00000007000000000000002F, 000000070000000000000030, 000000070000000000000031, 000000070000000000000032] --archive-async --exec-id=846-ea70e4e6 --log-level-console=off --log-level-file=detail --log-level-stderr=off --no-log-timestamp --pg1-path=/var/lib/postgresql/16/demo --process-max=2 --repo1-host=repository --spool-path=/var/spool/pgbackrest --stanza=demo P00 INFO: get 8 WAL file(s) from archive: 00000007000000000000002B...000000070000000000000032
P02 DETAIL: found 00000007000000000000002C in the repo1: 16-1 archive P01 DETAIL: found 00000007000000000000002B in the repo1: 16-1 archive P02 DETAIL: found 00000007000000000000002D in the repo1: 16-1 archive
P00 DETAIL: unable to find 00000007000000000000002E in the archive P00 INFO: archive-get:async command end: completed successfully [filtered 17 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 non-exclusive backup start: backup begins after the requested immediate checkpoint completes P00 INFO: backup start archive = 00000007000000000000002F, lsn = 0/2F000028
P00 INFO: wait for replay on the standby to reach 0/2F000028 P00 INFO: replay on the standby reached 0/2F000028
P00 INFO: check archive for prior segment 00000007000000000000002E
P01 DETAIL: backup file pg-primary:/var/lib/postgresql/16/demo/global/pg_control (8KB, 0.53%) checksum 20a516191e9d0ce661163067b70f348cd0c478e7
P01 DETAIL: match file from prior backup pg-primary:/var/lib/postgresql/16/demo/pg_logical/replorigin_checkpoint (8B, 0.53%) checksum 347fc8f2df71bd4436e38bd1516ccd7ea0d46532 P02 DETAIL: backup file pg-standby:/var/lib/postgresql/16/demo/base/5/1249 (464KB, 31.38%) checksum ad21eff1041d8672b2fbccf2b8e4af4c4d4cf7b2 [filtered 1278 lines of output]
sudo pg_ctlcluster 16 demo stop
sudo pg_ctlcluster 16 demo stop
sudo -u postgres /usr/lib/postgresql/17/bin/initdb \ -D /var/lib/postgresql/17/demo -k -A peer
sudo pg_createcluster 17 demo
sudo -u postgres sh -c 'cd /var/lib/postgresql && \ /usr/lib/postgresql/17/bin/pg_upgrade \ --old-bindir=/usr/lib/postgresql/16/bin \ --new-bindir=/usr/lib/postgresql/17/bin \ --old-datadir=/var/lib/postgresql/16/demo \ --new-datadir=/var/lib/postgresql/17/demo \ --old-options=" -c config_file=/etc/postgresql/16/demo/postgresql.conf" \ --new-options=" -c config_file=/etc/postgresql/17/demo/postgresql.conf"'
[filtered 40 lines of output] Checking for extension updates ok
Upgrade Complete
---------------- Optimizer statistics are not transferred by pg_upgrade. [filtered 3 lines of output]
sudo cp /etc/postgresql/16/demo/pg_hba.conf \ /etc/postgresql/17/demo/pg_hba.conf
sudo -u postgres pgbackrest --stanza=demo --no-online \ --log-level-console=info stanza-upgrade
P00 INFO: stanza-upgrade command begin 2.55.1: --exec-id=2734-020283ec --log-level-console=info --log-level-file=detail --no-log-timestamp --no-online --pg1-path=/var/lib/postgresql/17/demo --repo1-host=repository --stanza=demo P00 INFO: stanza-upgrade for stanza 'demo' on repo1
P00 INFO: stanza-upgrade command end: completed successfully
sudo pg_ctlcluster 17 demo start
sudo pg_lsclusters
sudo -u postgres pgbackrest --stanza=demo check
sudo pg_dropcluster 16 demo
sudo pg_dropcluster 16 demo
sudo pg_createcluster 17 demo
sudo -u pgbackrest pgbackrest --stanza=demo check
P00 WARN: unable to check pg2: [DbConnectError] raised from remote-0 ssh protocol on 'pg-standby': unable to connect to 'dbname='postgres' port=5432': connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory Is the server running locally and accepting connections on that socket?
sudo -u pgbackrest pgbackrest --stanza=demo --type=full backup
sudo -u postgres pgbackrest --stanza=demo --delta --type=standby restore
sudo pg_ctlcluster 17 demo start
sudo -u postgres pgbackrest --stanza=demo check