citus failover testing

gistfile1.txt

failover testing citus

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

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

#coordinator
if [[ -d dbcr ]]
then
        pg_ctl -D dbcr stop
        rm -rf dbcr*
fi

for i in db1 db2 db3 db4
do
        pg_ctl -D $i stop
        [[ -f ${i}.log ]] && rm ${i}.log
        [[ -d ${i} ]] && rm -rf ${i}
        if [[ $1x == "start"x ]]; then
          initdb -D $i
          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 "max_files_per_process=128" >>  $i/postgresql.auto.conf
          echo "citus.show_shards_for_app_name_prefixes='psql'" >>  $i/postgresql.auto.conf
          echo "log_min_duration_statement=0" >> $i/postgresql.auto.conf
          echo "log_autovacuum_min_duration=0" >> $i/postgresql.auto.conf
          echo "log_lock_waits=1" >> $i/postgresql.auto.conf
          #echo "citus.stat_tenants_track = 'all'" >> $i/postgresql.auto.conf
          #echo "citus.enable_schema_based_sharding = 'on'" >> $i/postgresql.auto.conf
          #echo "archive_mode='on'" >> $i/postgresql.auto.conf
          #echo "archive_command = 'pgbackrest --stanza=pg$i archive-push %p'" >> $i/postgresql.auto.conf
          #echo "max_connections=40" >> $i/postgresql.auto.conf
          #echo "log_connections='on'" >> $i/postgresql.auto.conf
          #echo "log_min_duration_statement=0" >> $i/postgresql.auto.conf
          #echo "max_parallel_workers_per_gather=2" >> $i/postgresql.auto.conf
          #echo "logical_decoding_work_mem='32MB'" >> $i/postgresql.auto.conf
          #echo "citus.explain_all_tasks=1" >> $i/postgresql.auto.conf
          port=$(( port + 1 ))
        fi
done
[[ $1 == "start" ]] || exit 1

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

for p in 5432 5433 5434 5435
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_add_node('localhost', 5435);
 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');
 create table local_t(id int primary key);
 insert into local_t select 1;

EOF

#setup replica for each node
pg_basebackup -D pg1 -c fast -S pg1 -C -R  -d "port=5432"
pg_basebackup -D pg2 -c fast -S pg2 -C -R  -d "port=5433"
pg_basebackup -D pg3 -c fast -S pg3 -C -R  -d "port=5434"
pg_basebackup -D pg4 -c fast -S pg4 -C -R  -d "port=5435"

#update port numbers
for i in 1 2 3 4
do
        perl -i -pe 's/543/643/' pg${i}/postgresql.auto.conf
        pg_ctl -D pg${i} -l pg${i}.log start
done

#this is downtime stuff. force all existing nodes to stop in one dc, to assume no data and replication all caught up
for i in 1 2 3 4
do
        pg_ctl -D db${i} -l pg${i}.log stop
done

#promote replicas
for i in {6432..6435}
do
        psql citusdb -p $i -c 'select pg_promote();'
done

#update pg_dist_node
for i in {6432..6435}
do
psql citusdb -p $i <<EOF
update pg_dist_node set nodeport = 6432 where nodeport = 5432;
update pg_dist_node set nodeport = 6433 where nodeport = 5433;
update pg_dist_node set nodeport = 6434 where nodeport = 5434;
update pg_dist_node set nodeport = 6435 where nodeport = 5435;
EOF
done

#i am not sure if this is needed if i updated manually on all nodes, but need to confirm
psql citusdb -p 6432 <<EOF
select citus_update_node(1, 'localhost', 6432);
select citus_update_node(2, 'localhost', 6433);
select citus_update_node(3, 'localhost', 6434);
select citus_update_node(4, 'localhost', 6435);
SELECT * FROM citus_get_active_worker_nodes();
EOF


#if [[ -d dbcr || $1 == "start" ]]
#then
#       pg_ctl -D dbcr stop
#       rm -rf dbcr*
#        pg_basebackup -D dbcr -c fast -S dbcr -C -R  -d "port=5432"
#       echo 'port=6432' >> dbcr/postgresql.auto.conf
#       pg_ctl -D dbcr -l dbcr.log start
#fi

#psql -p 5432 citusdb <<'EOF'
#        -- SET citus.shard_count = 3;
#        -- SET citus.shard_replication_factor = 1;
#        SELECT citus_set_coordinator_host('localhost', 5432);
#       create table dist_t(dist_id bigint primary key);
#       create table ref_t(ref_id bigint primary key);
#       insert into dist_t select generate_series(1, 10000) x;
#       insert into ref_t select generate_series(1, 100) x;
#       SELECT * from citus_add_node('localhost', 5433);
#       SELECT * from citus_add_node('localhost', 5434);
#       SELECT * from citus_add_node('localhost', 5435);
#       SELECT * FROM master_get_active_worker_nodes();
#       SELECT create_distributed_table('dist_t',   'dist_id');
#       SELECT truncate_local_data_after_distributing_table($$public.dist_t$$);
#       SELECT create_reference_table('ref_t');
#       SELECT truncate_local_data_after_distributing_table($$public.ref_t$$);
#        create table local_t(local_id bigint primary key);
#        insert into local_t select generate_series(1, 10) x;
#       alter table dist_t add column dist_col1 int;
#       alter table ref_t add column ref_col1 int;
#       alter table local_t add column local_col1 int;
#       update dist_t set dist_col1 = 0;
#       update ref_t set ref_col1 = 0;
#       update local_t set local_col1 = 0;
#       create index on dist_t (dist_col1);
#       create index on ref_t (ref_col1);
#       create index on local_t (local_col1);
#EOF

#for p in  5432 5433 5434 5435
#do
#psql -p $p citusdb <<EOF
#delete from dist_t where dist_id = $p;
#EOF
#done