pgbackrest

pgbackrest backup & restore demo

# https://pgbackrest.org/user-guide.html#introduction 

#in psql. 
show wal_level \; show archive_mode \; show max_wal_senders;
ALTER SYSTEM SET wal_level = replica;
ALTER SYSTEM SET archive_mode = on;
ALTER SYSTEM SET max_wal_senders = 3;
ALTER SYSTEM SET archive_command = 'pgbackrest --stanza=pgsql15_3 archive-push %p';

#check the cluster status. 
/usr/local/pgsql15/bin/pg_ctl -D /usr/local/pgsql15/data_42091266 status 

#if cluster if running then restart. 
/usr/local/pgsql15/bin/pg_ctl -D /usr/local/pgsql15/data_42091266 restart

#vertify all the command is working.
show wal_level \; show archive_mode \; show max_wal_senders \; show archive_command;

#get the conninfo
\conninfo
# You are connected to database "test15" as user "jian" via socket in "/tmp" at port "5415".

#################################################################
#Now create stanza
#
sudo  -u jian pgbackrest --stanza=pgsql15_3 --log-level-console=info stanza-create
sudo  -u jian pgbackrest --stanza=pgsql15_3 --log-level-console=info check
sudo  -u jian pgbackrest --stanza=pgsql15_3 --log-level-console=info backup

#check the last successful backup time
sudo -u jian pgbackrest --output=json --stanza=pgsql15_3 info | \
    jq '.[0] | .backup[-1] | .timestamp.stop'
#query the last archived WAL
sudo -u jian pgbackrest --output=json --stanza=pgsql15_3 info | \
    jq '.[0] | .archive[-1] | .max'
#diff backup    
sudo  -u jian pgbackrest --stanza=pgsql15_3 --type=diff --log-level-console=info backup

#stop cluster then do a backup.
/usr/local/pgsql15/bin/pg_ctl -D /usr/local/pgsql15/data_42091266 stop

#restore an backup.
sudo  -u jian pgbackrest --stanza=pgsql15_3 --delta --log-level-console=info restore

#restart the cluster.
sudo /usr/local/pgsql15/bin/pg_ctl -D /usr/local/pgsql15/data_42091266 start

#restart the database
/usr/local/pgsql15/bin/psql test15

# create 2 databases.
sudo -u jian /usr/local/pgsql15/bin/psql test15 -c 'create database test1 template template0;'
sudo -u jian /usr/local/pgsql15/bin/psql test15 -c 'create database test2 template template0;'

# create test table for each database.
sudo -u jian /usr/local/pgsql15/bin/psql test1 -c \
"create table test1_table(id int); 
insert into test1_table(id) values(1);"

sudo -u jian /usr/local/pgsql15/bin/psql test2 -c \
"create table test2_table(id int); 
insert into test2_table(id) values(1);"

#incremental backup    
sudo  -u jian pgbackrest --stanza=pgsql15_3 --type=incr --log-level-console=info backup

#show space used by test1 and test2
sudo -u jian du -sh /usr/local/pgsql15/data_42091266/base/32965
sudo -u jian du -sh /usr/local/pgsql15/data_42091266/base/32966

sudo -u jian pgbackrest --stanza=pgsql15_3 --set=20220914-112333F_20220914-121813I info
: '
stanza: pgsql15_3
    status: ok
    cipher: aes-256-cbc

    db (current)
        wal archive min/max (15): 000000010000000000000004/00000002000000000000000D

        incr backup: 20220914-112333F_20220914-121813I
            timestamp start/stop: 2022-09-14 12:18:13 / 2022-09-14 12:18:17
            wal start/stop: 00000002000000000000000D / 00000002000000000000000D
            lsn start/stop: 0/D0000D8 / 0/D0001E8
            database size: 52.6MB, database backup size: 14.4MB
            repo1: backup set size: 7MB, backup size: 1.9MB
            backup reference list: 20220914-112333F, 20220914-112333F_20220914-114324D
            database list: postgres (5), test (17618), test1 (32965), test15 (16388), test2 (32966)
'

# stop the cluster.
/usr/local/pgsql15/bin/pg_ctl -D /usr/local/pgsql15/data_42091266 stop

#restore from last backup including only the test2 database
sudo -u jian pgbackrest --stanza=pgsql15_3 --delta \
    --db-include=test2 --type=immediate --target-action=promote \
    --log-level-console=info restore
<<'###BLOCK-COMMENT'
2022-09-14 12:37:19.359 P00   INFO: repo1: restore backup set 20220914-112333F_20220914-121813I, recovery will start at 2022-09-14 12:18:13
2022-09-14 12:37:19.364 P00   INFO: remove invalid files/links/paths from '/usr/local/pgsql15/data_42091266'
2022-09-14 12:37:22.747 P00   INFO: write updated /usr/local/pgsql15/data_42091266/postgresql.auto.conf
2022-09-14 12:37:22.758 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2022-09-14 12:37:22.762 P00   INFO: restore size = 52.6MB, file total = 2211
2022-09-14 12:37:22.762 P00   INFO: restore command end: completed successfully (3421ms)
###BLOCK-COMMENT

#WAIT for sometime, then restart the cluster.
/usr/local/pgsql15/bin/pg_ctl -D /usr/local/pgsql15/data_42091266 start

#Demonstrate that the test2 database was recovered.
sudo -u jian /usr/local/pgsql15/bin/psql test2 -c 'select * from test2_table;'

#trying to connect test1
sudo -u jian /usr/local/pgsql15/bin/psql test1 -c 'select * from test1_table;'
<<'###BLOCK-COMMENT'
2022-09-14 12:44:59.576 IST [68611] FATAL:  relation mapping file "base/32965/pg_filenode.map" contains invalid data
psql: error: connection to server on socket "/tmp/.s.PGSQL.5415" failed: FATAL:  relation mapping file "base/32965/pg_filenode.map" contains invalid data
###BLOCK-COMMENT

#show test1 database space.
sudo -u jian du -sh /usr/local/pgsql15/data_42091266/base/32966

# stop the cluster.
/usr/local/pgsql15/bin/pg_ctl -D /usr/local/pgsql15/data_42091266 stop

#restore from last backup including only the test15 database
sudo -u jian pgbackrest --stanza=pgsql15_3 --delta \
    --db-include=test15 --type=immediate --target-action=promote \
    --log-level-console=info restore

#WAIT for sometime, then restart the cluster.
/usr/local/pgsql15/bin/pg_ctl -D /usr/local/pgsql15/data_42091266 start

#restart the database
/usr/local/pgsql15/bin/psql test15

#drop database test1;
drop database test1;