logrep_cascade_15.sh

#!/bin/bash

unset PGPORT PGSERVICE PGDATA PGPASSFILE
export PGDATABASE=postgres

env | grep PG
# exit

project=REL_15_STABLE

root_dir=/tmp/cascade/$project

mkdir -p $root_dir

BIN=$HOME/pg_stuff/pg_installations/pgsql.$project/bin

export PATH=$BIN:$PATH

  initdb=$BIN/initdb
postgres=$BIN/postgres
  pg_ctl=$BIN/pg_ctl

baseport=6525
   port1=$(( $baseport + 0 )) 
   port2=$(( $baseport + 1 ))
   port3=$(( $baseport + 2 ))
 appname=$project
num_instances=3       # or 2
        scale=2       #  pgbench -s
      clients=64      #  pgbench -c
     duration=2       #  how long to run: pgbench -T $duration 
         wait=3
if [[ -d $root_dir/instance1 ]]; then rm -rf $root_dir/instance1; fi
if [[ -d $root_dir/instance2 ]]; then rm -rf $root_dir/instance2; fi
if [[ -d $root_dir/instance3 ]]; then rm -rf $root_dir/instance3; fi
if [[ -d $root_dir/instance1 ]]; then exit ; fi
if [[ -d $root_dir/instance2 ]]; then exit ; fi
if [[ -d $root_dir/instance3 ]]; then exit ; fi
for n in `seq 1 $num_instances`
do
  instance=instance$n
  server_dir=$root_dir/$instance
  data_dir=$server_dir/data
  port=$(( $baseport + $n -1 ))
  logfile=$server_dir/logfile.$port
  echo "-- $initdb --pgdata=$data_dir --encoding=UTF8 # $port"
           $initdb --pgdata=$data_dir --encoding=UTF8 &> /dev/null

  ( $postgres  -D $data_dir -p $port \
    --wal_level=logical \
    --logging_collector=on \
    --client_min_messages=warning \
    --log_directory=$server_dir \
    --log_filename=logfile.${port} \
    --log_replication_commands=on & ) &> /dev/null
done 

echo "
  drop table if exists pgbench_accounts;
  drop table if exists pgbench_branches;
  drop table if exists pgbench_tellers;
  drop table if exists pgbench_history;" | psql -qXp $port1 \
&& echo "
  drop table if exists pgbench_accounts;
  drop table if exists pgbench_branches;
  drop table if exists pgbench_tellers;
  drop table if exists pgbench_history;" | psql -qXp $port2 \
&& pgbench -p $port1 -qis $scale \
&& echo "alter table pgbench_history add column hid serial primary key;" \
  | psql -q1Xp $port1 && pg_dump -F c -p $port1 \
     --exclude-table-data=pgbench_history  \
     --exclude-table-data=pgbench_accounts \
     --exclude-table-data=pgbench_branches \
     --exclude-table-data=pgbench_tellers  \
   -t pgbench_history -t pgbench_accounts \
   -t pgbench_branches -t pgbench_tellers \
  | pg_restore -1 -p $port2 -d postgres

if [[ $num_instances -eq 3 ]]
then
  pg_dump -F c -p $port1 \
     --exclude-table-data=pgbench_history  \
     --exclude-table-data=pgbench_accounts \
     --exclude-table-data=pgbench_branches \
     --exclude-table-data=pgbench_tellers  \
     -t pgbench_history -t pgbench_accounts \
     -t pgbench_branches -t pgbench_tellers \
  | pg_restore -1 -p $port3 -d postgres
fi
pub1=pub_${port1}_to_$port2 sub1=pub_${port2}_from_$port1
pub2=pub_${port2}_to_$port3 sub2=pub_${port3}_from_$port2
echo -ne "
create publication $pub1;
alter  publication $pub1 add table pgbench_accounts;
alter  publication $pub1 add table pgbench_branches;
alter  publication $pub1 add table pgbench_tellers;
alter  publication $pub1 add table pgbench_history;" | psql -p $port1 -aqtAX

if [[ $num_instances -eq 3 ]]; then
  echo -ne "
create publication $pub2;
alter  publication $pub2 add table pgbench_accounts;
alter  publication $pub2 add table pgbench_branches;
alter  publication $pub2 add table pgbench_tellers;
alter  publication $pub2 add table pgbench_history;" | psql -p $port2 -aqtAX
fi

if [[ $num_instances -eq 3 ]]; then
echo "
create subscription $sub2 connection 'port=$port2 application_name=$appname'
       publication  $pub2 with(enabled=false);
alter  subscription $sub2 enable;" | psql -p $port3 -aqtAX

fi

echo "
create subscription $sub1 connection 'port=$port1 application_name=$appname'
       publication  $pub1 with(enabled=false);
alter  subscription $sub1 enable;" | psql -p $port2 -aqtAX

echo "-- pgbench -p $port1 -c $clients -j 8 -T $duration -n postgres    #  scale $scale"
         pgbench -p $port1 -c $clients -j 8 -T $duration -n postgres    #  scale $scale
echo

for p in 6526 ; do
  echo "select current_setting('port')port, current_setting('server_version_num')svn, slot_name, slot_type 
                      from pg_replication_slots " | psql -qX -p $p -d postgres
done

echo "       accounts branches tellers  history"
echo "       -------- -------- -------- --------"

while [[ 1 -eq 1 ]]; do
  md5_6515=$port1  md5_6516=$port2  md5_6517=$por3
  sql_a="select * from pgbench_accounts order by aid;"
  sql_b="select * from pgbench_branches order by bid;"
  sql_t="select * from pgbench_tellers  order by tid;"
  sql_h="select * from pgbench_history  order by hid;"
  md5_a=$(echo "$sql_a"    | psql -qtAXp $port1 | md5sum | cut -b1-8)
  md5_b=$(echo "$sql_b"    | psql -qtAXp $port1 | md5sum | cut -b1-8)
  md5_t=$(echo "$sql_t"    | psql -qtAXp $port1 | md5sum | cut -b1-8)
  md5_h=$(echo "$sql_h"    | psql -qtAXp $port1 | md5sum | cut -b1-8)
  md5_6515=$(echo "$md5_a $md5_b $md5_t $md5_h" | md5sum | cut -b1-8)
  echo -ne  "$port1   $md5_a $md5_b $md5_t $md5_h    $md5_6515 "
  echo
  md5_a=$(echo "$sql_a"    | psql -qtAXp $port2 | md5sum | cut -b1-8)
  md5_b=$(echo "$sql_b"    | psql -qtAXp $port2 | md5sum | cut -b1-8)
  md5_t=$(echo "$sql_t"    | psql -qtAXp $port2 | md5sum | cut -b1-8)
  md5_h=$(echo "$sql_h"    | psql -qtAXp $port2 | md5sum | cut -b1-8)
  md5_6516=$(echo "$md5_a $md5_b $md5_t $md5_h" | md5sum | cut -b1-8)
  echo      "$port2   $md5_a $md5_b $md5_t $md5_h    $md5_6516 "
  if [[ $num_instances -eq 3 ]]; then
    md5_a=$(echo "$sql_a"    | psql -qtAXp $port3 | md5sum | cut -b1-8)
    md5_b=$(echo "$sql_b"    | psql -qtAXp $port3 | md5sum | cut -b1-8)
    md5_t=$(echo "$sql_t"    | psql -qtAXp $port3 | md5sum | cut -b1-8)
    md5_h=$(echo "$sql_h"    | psql -qtAXp $port3 | md5sum | cut -b1-8)
    md5_6517=$(echo "$md5_a $md5_b $md5_t $md5_h" | md5sum | cut -b1-8)
    echo -ne  "$port3   $md5_a $md5_b $md5_t $md5_h    $md5_6517 "
  else
    echo
  fi

  if [[ "$md5_6515" == "$md5_6516" ]]
  then
    if [[ $num_instances -eq 2 ]]; then
      echo "   ok (2)"
      break
    elif [[ $num_instances -eq 3 ]]; then
      if [[ "$md5_6515" == "$md5_6517" ]]
      then
        echo "   ok"
        break
      fi
    fi
  fi

  # not ready replicating yet, so report, wait, and loop
  echo -ne "  NOK (${wait}s)                         [$project]   scale [$scale]"  
  echo
  echo
  sleep $wait
done
echo

if [[ 1 -eq 1 ]]; then

# stop instances
for n in `seq 1 $num_instances`
do
  instance=instance$n
  server_dir=$root_dir/$instance
  data_dir=$server_dir/data
  port=$(( $baseport + $n - 1 ))
  logfile=$server_dir/logfile.$port
  $pg_ctl stop -D $data_dir --mode=immediate --wait
done

fi

# delete everything
if [[ 0 -eq 1 ]]
then
   echo "rm -rf /tmp/cascade/$project/instance*"
         rm -rf /tmp/cascade/$project/instance*
fi