citus backup using pgbackrest and pitr restore using citus_create_restore_point

gistfile1.txt

using citus_create_restore_point() for pitr


postgres@pg:~/citusdb/demo$ cat setup.sh
#!/bin/bash

export PATH=/opt/15/usr/local/bin:$PATH
port=5432

for i in db1 db2 db3
do
        [[ -d ${i} ]] && pg_ctl -D $i stop
        [[ -f ${i}.log ]] && rm ${i}.log
        [[ -d ${i} ]] && rm -rf ${i}
        [[ -d backup/$i ]] && rm -rf backup/$i
        if [[ $1x == "start"x ]]; then
          initdb -D $i 2>/dev/null >/dev/null
          echo "listen_addresses='*'" >> $i/postgresql.auto.conf
          echo "shared_preload_libraries = 'citus'" >> $i/postgresql.auto.conf
          echo "port=$port" >>  $i/postgresql.auto.conf
          echo "wal_level=logical"  >>  $i/postgresql.auto.conf
          echo "archive_mode='on'" >> $i/postgresql.auto.conf
          echo "archive_command = 'pgbackrest --config /var/lib/postgresql/citusdb/demo/pgbackrest_$i.conf --stanza=$i archive-push %p'" >> $i/postgresql.auto.conf
          port=$(( port + 1 ))
        fi
done
[[ $1 == "start" ]] || exit 1

for i in 1 2 3
do
        db=db${i}
        pg_ctl -D ${db} -l ${db}.log start
done

for p in 5432 5433 5434
do
        createdb -p $p citusdb
        psql -p $p -d citusdb -c "create extension citus;"
done

psql -p 5432 citusdb <<'EOF'
 SET citus.shard_count = 32;
 SELECT citus_set_coordinator_host('localhost', 5432);
 SELECT * from citus_add_node('localhost', 5433);
 SELECT * from citus_add_node('localhost', 5434);
 SELECT * FROM citus_get_active_worker_nodes();
 CREATE TABLE dist_t(dist_id int primary key, col1 int, col2 text);
 insert into dist_t select x,x,x from generate_series(1, 10000) x;
 CREATE TABLE ref_t(ref_id int primary key, col1 int, col2 int);
 insert into ref_t select x,x,x from generate_series(1, 100000) x;
 SELECT create_reference_table('ref_t');
 SELECT create_distributed_table('dist_t',   'dist_id');
 select pg_sleep(2);
 select pg_switch_wal();
 select pg_switch_wal();
 create table local_t(id int primary key);
 insert into local_t select generate_series(1, 1000);
EOF

sleep 5

for i in db1 db2 db3
do
pgbackrest --config /var/lib/postgresql/citusdb/demo/pgbackrest_${i}.conf --stanza=$i  stop
pgbackrest --config /var/lib/postgresql/citusdb/demo/pgbackrest_${i}.conf --stanza=$i  start
pgbackrest --config /var/lib/postgresql/citusdb/demo/pgbackrest_${i}.conf --stanza=$i  stanza-create
pgbackrest --config /var/lib/postgresql/citusdb/demo/pgbackrest_${i}.conf --stanza=$i  backup
sleep 2;
done

for p in 5432 5433 5434
do
        for i in 1..100
        do
                psql -p $p -d citusdb <<EOF
create table if not exists foo(col1 int);
insert into foo select random()::int;
select pg_switch_wal();
checkpoint;
EOF

        done
done
sleep 5;

psql -p 5432  -d citusdb -c "select citus_create_restore_point('yolo');"
sleep 4;

#we dont expect this to be restored, so foo will exist
psql -p 5432  -d citusdb -c "checkpoint; select pg_switch_wal();"
psql -p 5433  -d citusdb -c "checkpoint; select pg_switch_wal();"
psql -p 5432  -d citusdb -c "checkpoint; select pg_switch_wal(); drop table foo;"

for i in db1 db2 db3
do
        pg_ctl -D /var/lib/postgresql/citusdb/demo/$i stop
        [[ -d $i ]] && rm -rf /var/lib/postgresql/citusdb/demo/$i
        pgbackrest --config /var/lib/postgresql/citusdb/demo/pgbackrest_$i.conf \
                --stanza=$i \
                --log-level-console=warn \
                --type=name --target-action=promote \
                --target='yolo' \
                restore

        #pgbackrest --config /var/lib/postgresql/citusdb/demo/pgbackrest_$i.conf --stanza=$i --log-level-console=info --type=immediate --target-action=promote restore
sleep 2;
done


for i in db1 db2 db3
do
        pg_ctl -D /var/lib/postgresql/citusdb/demo/$i -l $i.log start
done
exit 1;


#################################
cat pgbackrest_db1.conf
[global]
repo1-path=/var/lib/postgresql/citusdb/demo/backup/db1
repo1-retention-full=2

# general options
process-max=2
log-level-console=info
log-level-file=debug

repo1-retention-diff=1
start-fast=y
archive-async=y

[db1]
pg1-path=/var/lib/postgresql/citusdb/demo/db1
pg1-socket-path=/tmp
pg1-port=5432


postgres@pg:~/citusdb/demo$ cat pgbackrest_db2.conf
[global]
repo1-path=/var/lib/postgresql/citusdb/demo/backup/db2
repo1-retention-full=2

# general options
process-max=2
log-level-console=info
log-level-file=debug

repo1-retention-diff=1
start-fast=y
archive-async=y

[db2]
pg1-path=/var/lib/postgresql/citusdb/demo/db2
pg1-socket-path=/tmp
pg1-port=5433
postgres@pg:~/citusdb/demo$ cat pgbackrest_db3.conf
[global]
repo1-path=/var/lib/postgresql/citusdb/demo/backup/db3
repo1-retention-full=2

# general options
process-max=2
log-level-console=info
log-level-file=debug

repo1-retention-diff=1
start-fast=y
archive-async=y

[db3]
pg1-path=/var/lib/postgresql/citusdb/demo/db3
pg1-socket-path=/tmp
pg1-port=5434