#!/bin/sh # # cb.sh - display pgbench tables content-md5's # # outf=cb_$( date +"%Y%m%d %H:%M:%S %s%N" | cut -b1-22 | perl -ne 's{[: ]}{_}g;print ' ).out outf=cb_$( date +"%Y%m%d %H:%M:%S" | perl -ne 's{[: ]}{_}g;print ' ).out SLEEP=1 echo >$outf echo echo "tail -F $outf" echo rc=0 while [[ $rc -eq 0 ]] do # num_tables=$( echo "select count(*) from information_schema.tables where table_schema = 'public' and table_name ~ '^pgbench_'" | psql -qtAX ) num_tables=$( echo "select count(*) from pg_class where relkind = 'r' and relname ~ '^pgbench_'" | psql -qtAX ) if [[ $num_tables -ne 4 ]] then echo "pgbench tables not 4 - bailing out" >> $outf sleep $SLEEP continue fi for port in 6972 6973 do md5_a=$( echo "select * from pgbench_accounts /* where aid % 100 = 0 */ order by aid"|psql -qtAXp$port | md5sum | cut -b 1-9 ) md5_b=$( echo "select * from pgbench_branches /* where bid % 100 = 0 */ order by bid"|psql -qtAXp$port | md5sum | cut -b 1-9 ) md5_t=$( echo "select * from pgbench_tellers /* where tid % 100 = 0 */ order by tid"|psql -qtAXp$port | md5sum | cut -b 1-9 ) md5_h=$( echo "select * from pgbench_history /* where hid % 100 = 0 */ order by hid"|psql -qtAXp$port | md5sum | cut -b 1-9 ) md5_total[$port]=$( echo "${md5_a} ${md5_b} ${md5_t} ${md5_h}" | md5sum ) cnt_a=$(echo "select count(*) from pgbench_accounts"|psql -qtAXp $port) cnt_b=$(echo "select count(*) from pgbench_branches"|psql -qtAXp $port) cnt_t=$(echo "select count(*) from pgbench_tellers" |psql -qtAXp $port) cnt_h=$(echo "select count(*) from pgbench_history" |psql -qtAXp $port) d=$( date +"%Y%m%d %H:%M:%S %s%N " | cut -b1-22 | perl -ne 'chomp; s{[: ]}{_}g; print ' ) printf "$d $port a,b,t,h: %6d %6d %6d %6d" $cnt_a $cnt_b $cnt_t $cnt_h >> $outf echo -n " $md5_a $md5_b $md5_t $md5_h" >> $outf if [[ $port -eq 6972 ]]; then echo " master" >> $outf elif [[ $port -eq 6973 ]]; then echo -n " replica" >> $outf else echo " ERROR" >> $outf fi done if [[ "${md5_total[6972]}" == "${md5_total[6973]}" ]] then echo " ok" >> $outf else echo " NOK" >> $outf fi echo 'select now(), * from pg_subscription_rel order by srrelid; -- select now(), * from pg_stat_subscription' | psql -qXp6973 >> $outf sleep $SLEEP rc=$? done