Re: [HACKERS] Logical Replication WIP - Mailing list pgsql-hackers

On 2016-12-19 08:04, Erik Rijkers wrote:
> On 2016-12-18 11:12, Petr Jelinek wrote:
> 
> (now using latest: patchset:)
> 
> 0001-Add-PUBLICATION-catalogs-and-DDL-v14.patch
> 0002-Add-SUBSCRIPTION-catalog-and-DDL-v14.patch
> 0003-Define-logical-replication-protocol-and-output-plugi-v14.patch
> 0004-Add-logical-replication-workers-v14.patch
> 0005-Add-separate-synchronous-commit-control-for-logical--v14.patch
> 
> Sometimes  replication (caused by a pgbench run)  runs for a few
> seconds replicating all 4 pgbench tables correctly, but never longer
> than 10 to 20 seconds.
> 

I've concocted pgbench_derail.sh.  It assumes 2 instances running, 
initially without the publication and subsciption.

There are two separate installations, on the same machine.

To startup the two instances I use instance.sh:

# ./instances.sh
#!/bin/sh
port1=6972
port2=6973
project1=logical_replication
project2=logical_replication2
pg_stuff_dir=$HOME/pg_stuff
PATH1=$pg_stuff_dir/pg_installations/pgsql.$project1/bin:$PATH
PATH2=$pg_stuff_dir/pg_installations/pgsql.$project2/bin:$PATH
server_dir1=$pg_stuff_dir/pg_installations/pgsql.$project1
server_dir2=$pg_stuff_dir/pg_installations/pgsql.$project2
data_dir1=$server_dir1/data
data_dir2=$server_dir2/data
options1="
-c wal_level=logical
-c max_replication_slots=10
-c max_worker_processes=12
-c max_logical_replication_workers=10
-c max_wal_senders=10
-c logging_collector=on
-c log_directory=$server_dir1
-c log_filename=logfile.${project1} "

options2="
-c wal_level=replica
-c max_replication_slots=10
-c max_worker_processes=12
-c max_logical_replication_workers=10
-c max_wal_senders=10
-c logging_collector=on
-c log_directory=$server_dir2
-c log_filename=logfile.${project2} "
which postgres
export PATH=$PATH1; postgres -D $data_dir1 -p $port1 ${options1} &
export PATH=$PATH2; postgres -D $data_dir2 -p $port2 ${options2} &
# end ./instances.sh







#--- pgbench_derail.sh
#!/bin/sh

# assumes both instances are running

# clear logs
# echo > 
$HOME/pg_stuff/pg_installations/pgsql.logical_replication/logfile.logical_replication
# echo > 
$HOME/pg_stuff/pg_installations/pgsql.logical_replication2/logfile.logical_replication2

port1=6972
port2=6973


function cb()
{  #  display the 4 pgbench tables' accumulated content as md5s  #  a,b,t,h stand for:  pgbench_accounts, -branches,
-tellers,-history  for port in $port1 $port2  do    md5_a=$(echo "select * from pgbench_accounts order by aid"        
 
|psql -qtAXp$port|md5sum|cut -b 1-9)    md5_b=$(echo "select * from pgbench_branches order by bid"        
|psql -qtAXp$port|md5sum|cut -b 1-9)    md5_t=$(echo "select * from pgbench_tellers  order by tid"        
|psql -qtAXp$port|md5sum|cut -b 1-9)    md5_h=$(echo "select * from pgbench_history  order by 
aid,bid,tid"|psql -qtAXp$port|md5sum|cut -b 1-9)    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)    printf "$port a,b,t,h: %6d %6d %6d %6d" $cnt_a  $cnt_b  $cnt_t  
$cnt_h    echo -n "   $md5_a  $md5_b  $md5_t  $md5_h"    if   [[ $port -eq $port1 ]]; then echo "   master"    elif [[
$port-eq $port2 ]]; then echo "   replica"    else                              echo "             ERROR"    fi  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 -X -p $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 -X -p $port2 \  && pgbench -p $port1 -qis 1 \  && echo "alter table
pgbench_historyreplica identity full;" | psql 
 
-1p $port1 \  && pg_dump -F c  -p $port1 \         -t pgbench_accounts \         -t pgbench_branches \         -t
pgbench_tellers \         -t pgbench_history  \    | pg_restore -p $port2 -d testdb
 

echo  "$(cb)"

sleep 2

echo  "$(cb)"

echo "create publication pub1 for all tables;" | psql -p $port1 -aqtAX

echo "
create subscription sub1  connection 'port=${port1}'  publication pub1  with (disabled);
alter subscription sub1 enable;
" | psql -p $port2 -aqtAX
#------------------------------------

# repeat a short (10 s) pgbench-un to show that during such
# short runs the logical replication often remains intact.
# Longer pgbench-runs always derail the logrep of one or more
# of these 4 table
#
# bug:  pgbench_history no longer replicates
#       sometimes also the other 3 table de-synced.

echo  "$(cb)"
echo "-- pgbench -c 1 -T 10 -P 5 (short run, first)"         pgbench -c 1 -T 10 -P 5
sleep 2
echo  "$(cb)"

echo "-- pgbench -c 1 -T 10 -P 5 (short run, second)"         pgbench -c 1 -T 10 -P 5
sleep 2
echo  "$(cb)"

echo "-- pgbench -c 1 -T 120 -P 15 (long run)"         pgbench -c 1 -T 120 -P 15
sleep 2
echo "-- 60 second (1)"
echo  "$(cb)"
#--- end pgbench_derail.sh


(Sorry for the messy bash.)

thanks,

Erik Rijkers