pitr pgbackrest demo

backrest_pitr.sh

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


/usr/local/pgsql15/bin/pg_ctl -D /usr/local/pgsql15/data_42091266 stop
#info check
sudo  -u jian pgbackrest --stanza=pgsql15_3 --log-level-console=info check
sudo  -u jian pgbackrest --stanza=pgsql15_3 --type=diff --log-level-console=info backup

/usr/local/pgsql15/bin/pg_ctl -D /usr/local/pgsql15/data_42091266 stop

#start cluster
/usr/local/pgsql15/bin/pg_ctl -D /usr/local/pgsql15/data_42091266 start
sudo -u jian /usr/local/pgsql15/bin/psql test15 -c "\dconfig *unix*"
        Parameter        | Value
-------------------------+-------
 unix_socket_directories | /tmp
 unix_socket_group       |
 unix_socket_permissions | 0777
(3 rows)

# run a command 
sudo -u jian /usr/local/pgsql15/bin/psql test15 -c "begin; \
    create table important_table(message text);\
    insert into important_table values('important data');\
    commit;\
    select * from important_table;"

# get the current timestamp
sudo -u jian /usr/local/pgsql15/bin/psql test15 -Atc \
    "select current_timestamp"
##2022-09-16 11:44:39.33266+05:30

#drop the table
sudo -u jian /usr/local/pgsql15/bin/psql test15 -c "begin; \
    drop table important_table;\
    commit;\
    select * from important_table;"

#stop cluster
/usr/local/pgsql15/bin/pg_ctl -D /usr/local/pgsql15/data_42091266 stop
#
sudo -u jian pgbackrest --stanza=pgsql15_3 --delta \
    --type=time "--target=2022-09-16 11:44:39.33266+05:30" \
    --target-action=promote restore

sudo less postgresql.auto.conf    

/usr/local/pgsql15/bin/pg_ctl -D /usr/local/pgsql15/data_42091266 start
waiting for server to start....2022-09-16 11:56:30.507 IST [146297] LOG:  starting PostgreSQL 15beta3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit
2022-09-16 11:56:30.507 IST [146297] LOG:  listening on IPv4 address "127.0.0.1", port 5415
2022-09-16 11:56:30.513 IST [146297] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5415"
2022-09-16 11:56:30.520 IST [146300] LOG:  database system was interrupted; last known up at 2022-09-16 11:42:51 IST
2022-09-16 11:56:30.593 IST [146300] LOG:  starting point-in-time recovery to 2022-09-16 11:44:39.33266+05:30
2022-09-16 11:56:30.600 IST [146300] LOG:  restored log file "00000004.history" from archive
2022-09-16 11:56:30.644 IST [146300] LOG:  restored log file "000000040000000000000015" from archive
2022-09-16 11:56:30.663 IST [146300] LOG:  redo starts at 0/15000028
2022-09-16 11:56:30.707 IST [146300] LOG:  restored log file "000000040000000000000016" from archive
2022-09-16 11:56:30.724 IST [146300] LOG:  consistent recovery state reached at 0/15000138
2022-09-16 11:56:30.724 IST [146297] LOG:  database system is ready to accept read-only connections
2022-09-16 11:56:30.734 IST [146300] LOG:  recovery stopping before commit of transaction 1380, time 2022-09-16 11:46:20.89288+05:30
2022-09-16 11:56:30.734 IST [146300] LOG:  redo done at 0/1601B508 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.07 s
2022-09-16 11:56:30.734 IST [146300] LOG:  last completed transaction was at log time 2022-09-16 11:43:42.291077+05:30
2022-09-16 11:56:30.773 IST [146300] LOG:  restored log file "000000040000000000000016" from archive
 done
server started
tmp$2022-09-16 11:56:30.801 IST [146300] LOG:  selected new timeline ID: 5
2022-09-16 11:56:30.833 IST [146300] LOG:  restored log file "00000004.history" from archive
2022-09-16 11:56:30.839 IST [146300] LOG:  archive recovery complete
2022-09-16 11:56:30.842 IST [146298] LOG:  checkpoint starting: end-of-recovery immediate wait
2022-09-16 11:56:30.866 IST [146298] LOG:  checkpoint complete: wrote 28 buffers (0.2%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.007 s, sync=0.006 s, total=0.028 s; sync files=22, longest=0.004 s, average=0.001 s; distance=16493 kB, estimate=16493 kB
2022-09-16 11:56:30.871 IST [146297] LOG:  database system is ready to accept connections

sudo -u jian /usr/local/pgsql15/bin/psql test15 -c "\
    select * from important_table;"