sudo wget -q -O - \
https://github.com/pgbackrest/pgbackrest/archive/release/2.12dev.tar.gz | \
sudo tar zx -C /rootsudo apt-get install build-essential libssl-dev libxml2-dev libperl-dev zlib1g-dev
perl -V | grep USE_64_BIT_INT
Output suppressed for testing
sudo make -s -C /root/pgbackrest-release-2.12dev/src
sudo scp build:/root/pgbackrest-release-2.12dev/src/pgbackrest /usr/bin
sudo chmod 755 /usr/bin/pgbackrest
sudo apt-get install libdbd-pg-perl
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
Output suppressed for testing
sudo -u postgres /usr/lib/postgresql/10/bin/initdb \
-D /var/lib/postgresql/10/demo -k -A peersudo pg_createcluster 10 demo
Output suppressed for testing
sudo -u postgres bash -c ' \
export PGBACKREST_LOG_PATH=/path/set/by/env && \
pgbackrest --log-level-console=error help backup log-path'Output suppressed for testing
sudo mkdir -p /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest
sudo chown postgres:postgres /var/lib/pgbackrest
sudo pg_ctlcluster 10 demo restart
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info stanza-create
Output suppressed for testing
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info check
Output suppressed for testing
sudo -u postgres pgbackrest --stanza=demo \
--log-level-console=info backupOutput suppressed for testing
sudo -u postgres pgbackrest --stanza=demo --type=diff \
--log-level-console=info backupOutput suppressed for testing
#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
Output suppressed for testing
sudo pg_ctlcluster 10 demo stop
sudo -u postgres rm /var/lib/postgresql/10/demo/global/pg_control
sudo pg_ctlcluster 10 demo start
Output suppressed for testing
sudo -u postgres find /var/lib/postgresql/10/demo -mindepth 1 -delete
sudo -u postgres pgbackrest --stanza=demo restore
sudo pg_ctlcluster 10 demo start
sudo -u postgres pgbackrest --stanza=demo --type=incr \
--log-level-console=info backupOutput suppressed for testing
sudo -u postgres pgbackrest --stanza=demo --type=incr \
--log-level-console=info backupOutput suppressed for testing
sudo -u postgres pgbackrest --output=json info
Output suppressed for testing
sudo -u postgres cat \
/var/lib/postgresql/pgbackrest/doc/example/pgsql-pgbackrest-info.sqlOutput suppressed for testing
sudo -u postgres psql -f \
/var/lib/postgresql/pgbackrest/doc/example/pgsql-pgbackrest-info.sqlsudo -u postgres cat \
/var/lib/postgresql/pgbackrest/doc/example/pgsql-pgbackrest-query.sqlOutput suppressed for testing
sudo -u postgres psql -f \
/var/lib/postgresql/pgbackrest/doc/example/pgsql-pgbackrest-query.sqlOutput suppressed for testing
sudo apt-get install jq
sudo -u postgres pgbackrest --output=json --stanza=demo info | \
jq '.[0] | .backup[-1] | .timestamp.stop'Output suppressed for testing
sudo -u postgres pgbackrest --output=json --stanza=demo info | \
jq '.[0] | .archive[-1] | .max'Output suppressed for testing
sudo -u postgres pgbackrest --stanza=demo --type=full \
--log-level-console=detail backupOutput suppressed for testing
sudo -u postgres pgbackrest --stanza=demo --type=full \
--log-level-console=info backupOutput suppressed for testing
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 backupOutput suppressed for testing
sudo -u postgres pgbackrest --stanza=demo --type=diff \
--log-level-console=info backupOutput suppressed for testing
sudo -u postgres pgbackrest --stanza=demo --log-level-console=detail \
--repo1-retention-archive-type=diff --repo1-retention-archive=1 expireOutput suppressed for testing
sudo pg_ctlcluster 10 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta \
--log-level-console=detail restoreOutput suppressed for testing
sudo pg_ctlcluster 10 demo start
sudo -u postgres psql -c "create database test1;"
Output suppressed for testing
sudo -u postgres psql -c "create database test2;"
Output suppressed for testing
sudo -u postgres pgbackrest --stanza=demo --type=incr backup
sudo -u postgres psql -c "create table test1_table (id int); \
insert into test1_table (id) values (1);" test1Output suppressed for testing
sudo -u postgres psql -c "create table test2_table (id int); \
insert into test2_table (id) values (2);" test2Output suppressed for testing
sudo -u postgres du -sh /var/lib/postgresql/10/demo/base/[Test Variable]
Output suppressed for testing
sudo pg_ctlcluster 10 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta \
--db-include=test2 restoresudo pg_ctlcluster 10 demo start
sudo -u postgres psql -c "select * from test2_table;" test2
Output suppressed for testing
sudo -u postgres psql -c "select * from test1_table;" test1
Output suppressed for testing
sudo -u postgres du -sh /var/lib/postgresql/10/demo/base/[Test Variable]
Output suppressed for testing
sudo -u postgres psql -c "drop database test1;"
Output suppressed for testing
sudo -u postgres psql -c "select oid, datname from pg_database order by oid;"
Output suppressed for testing
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;"Output suppressed for testing
sudo -u postgres psql -Atc "select current_timestamp"
Output suppressed for testing
sudo -u postgres psql -c "begin; \
drop table important_table; \
commit; \
select * from important_table;"Output suppressed for testing
sudo pg_ctlcluster 10 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta \
--type=time "--target=[Test Variable]" \
--target-action=promote restoresudo -u postgres cat /var/lib/postgresql/10/demo/recovery.conf
Output suppressed for testing
sudo pg_ctlcluster 10 demo start
sudo -u postgres psql -c "select * from important_table"
Output suppressed for testing
sudo -u postgres cat /var/log/postgresql/postgresql-10-demo.log
Output suppressed for testing
sudo -u postgres psql -c "begin; \
drop table important_table; \
commit; \
select * from important_table;"Output suppressed for testing
sudo -u postgres pgbackrest --stanza=demo --type=incr backup
sudo pg_ctlcluster 10 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta \
--type=time "--target=[Test Variable]" --target-action=promote restoresudo pg_ctlcluster 10 demo start
sudo -u postgres psql -c "select * from important_table"
Output suppressed for testing
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/log/postgresql/postgresql-10-demo.log
Output suppressed for testing
sudo -u postgres pgbackrest info
Output suppressed for testing
sudo pg_ctlcluster 10 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta \
--type=time "--target=[Test Variable]" \
--set=[Test Variable] --target-action=promote restoresudo pg_ctlcluster 10 demo start
sudo -u postgres psql -c "select * from important_table"
Output suppressed for testing
recovery stopping before...and
last completed transaction...messages showing that the recovery was successful.
sudo -u postgres cat /var/log/postgresql/postgresql-10-demo.log
Output suppressed for testing
sudo apt-get install libio-socket-ssl-perl libxml-libxml-perl
{
"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
Output suppressed for testing
sudo -u postgres pgbackrest --stanza=demo \
--log-level-console=info backupOutput suppressed for testing
sudo pg_ctlcluster 10 demo stop
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info stop
Output suppressed for testing
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info stanza-delete
Output suppressed for testing
sudo adduser --disabled-password --gecos "" pgbackrest
sudo scp build:/root/pgbackrest-release-2.12dev/src/pgbackrest /usr/bin
sudo chmod 755 /usr/bin/pgbackrest
sudo apt-get install libdbd-pg-perl
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_keyssudo -u pgbackrest ssh postgres@pg-primary
sudo -u postgres ssh pgbackrest@repository
sudo -u pgbackrest pgbackrest --stanza=demo stanza-create
Output suppressed for testing
sudo -u postgres pgbackrest --stanza=demo check
Output suppressed for testing
sudo -u pgbackrest pgbackrest --stanza=demo check
Output suppressed for testing
sudo -u pgbackrest pgbackrest --stanza=demo backup
Output suppressed for testing
sudo pg_ctlcluster 10 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta restore
sudo pg_ctlcluster 10 demo start
sudo -u pgbackrest pgbackrest --stanza=demo backup
sudo -u pgbackrest pgbackrest --stanza=demo --type=full backup
sudo -u pgbackrest pgbackrest --stanza=demo --type=full backup
sudo -u pgbackrest pgbackrest info
Output suppressed for testing
sudo -u postgres pgbackrest stop
sudo -u pgbackrest pgbackrest --stanza=demo backup
Output suppressed for testing
sudo -u postgres pgbackrest stop
Output suppressed for testing
sudo -u postgres pgbackrest start
sudo -u postgres pgbackrest --stanza=demo stop
sudo -u pgbackrest pgbackrest --stanza=demo backup
Output suppressed for testing
sudo -u postgres pgbackrest --stanza=demo start
sudo scp build:/root/pgbackrest-release-2.12dev/src/pgbackrest /usr/bin
sudo chmod 755 /usr/bin/pgbackrest
sudo apt-get install libdbd-pg-perl
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_keyssudo -u pgbackrest ssh postgres@pg-standby
sudo -u postgres ssh pgbackrest@repository
sudo pg_createcluster 10 demo
sudo -u postgres pgbackrest --stanza=demo --delta restore
sudo -u postgres cat /var/lib/postgresql/10/demo/recovery.conf
Output suppressed for testing
sudo pg_ctlcluster 10 demo start
sudo -u postgres cat /var/log/postgresql/postgresql-10-demo.log
Output suppressed for testing
sudo -u postgres psql -c " \
begin; \
create table replicated_table (message text); \
insert into replicated_table values ('Important Data'); \
commit; \
select * from replicated_table";Output suppressed for testing
sudo -u postgres psql -c "select * from replicated_table;"
Output suppressed for testing
sudo -u postgres psql -c "select *, current_timestamp from pg_switch_wal()";
Output suppressed for testing
sudo -u postgres psql -c " \
select *, current_timestamp from replicated_table"Output suppressed for testing
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info check
Output suppressed for testing
sudo -u postgres psql -c " \
create user replicator password 'jw8s0F4' replication";Output suppressed for testing
sudo -u postgres sh -c 'echo \
"host replication replicator {[host-pg2-ip]}/32 md5" \
>> /etc/postgresql/10/demo/pg_hba.conf'sudo pg_ctlcluster 10 demo reload
sudo -u postgres sh -c 'echo \
"{[host-pg1-ip]}:*:replication:replicator:jw8s0F4" \
>> /var/lib/postgresql/.pgpass'sudo -u postgres chmod 600 /var/lib/postgresql/.pgpass
sudo pg_ctlcluster 10 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta restore
sudo -u postgres cat /var/lib/postgresql/10/demo/recovery.conf
Output suppressed for testing
sudo pg_ctlcluster 10 demo start
sudo -u postgres cat /var/log/postgresql/postgresql-10-demo.log
Output suppressed for testing
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";Output suppressed for testing
sudo -u postgres psql -c " \
select *, current_timestamp from stream_table"Output suppressed for testing
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'"
Output suppressed for testing
sudo pg_ctlcluster 10 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
Output suppressed for testing
sudo -u postgres cat /var/log/pgbackrest/demo-archive-push-async.log
Output suppressed for testing
sudo -u postgres cat /var/log/pgbackrest/demo-archive-get-async.log
Output suppressed for testing
sudo -u postgres psql -c "alter user replicator password 'jw8s0F4'"
Output suppressed for testing
sudo -u pgbackrest pgbackrest --stanza=demo --log-level-console=detail backup
Output suppressed for testing
sudo pg_ctlcluster 10 demo stop
sudo pg_ctlcluster 10 demo stop
sudo -u postgres /usr/lib/postgresql/11/bin/initdb \
-D /var/lib/postgresql/11/demo -k -A peersudo pg_createcluster 11 demo
sudo -u postgres sh -c 'cd /var/lib/postgresql && \
/usr/lib/postgresql/11/bin/pg_upgrade \
--old-bindir=/usr/lib/postgresql/10/bin \
--new-bindir=/usr/lib/postgresql/11/bin \
--old-datadir=/var/lib/postgresql/10/demo \
--new-datadir=/var/lib/postgresql/11/demo \
--old-options=" -c config_file=/etc/postgresql/10/demo/postgresql.conf" \
--new-options=" -c config_file=/etc/postgresql/11/demo/postgresql.conf"'Output suppressed for testing
sudo cp /etc/postgresql/10/demo/pg_hba.conf \
/etc/postgresql/11/demo/pg_hba.confsudo -u pgbackrest pgbackrest --stanza=demo --no-online \
--log-level-console=info stanza-upgradeOutput suppressed for testing
sudo pg_ctlcluster 11 demo start
Output suppressed for testing
sudo -u postgres pg_lsclusters
Output suppressed for testing
sudo -u postgres pgbackrest --stanza=demo check
Output suppressed for testing
sudo pg_dropcluster 10 demo
sudo pg_dropcluster 10 demo
sudo pg_createcluster 11 demo
sudo -u pgbackrest pgbackrest --stanza=demo check
Output suppressed for testing
sudo -u pgbackrest pgbackrest --stanza=demo --type=full backup
sudo -u postgres pgbackrest --stanza=demo --delta restore
sudo pg_ctlcluster 11 demo start
sudo -u postgres pgbackrest --stanza=demo check
Output suppressed for testing