On 27/05/17 20:30, Erik Rijkers wrote:
Here is what I have:
instances.sh:
starts up 2 assert enabled sessions
instances_fast.sh:
alternative to instances.sh
starts up 2 assert disabled 'fast' sessions
testset.sh
loop to call pgbench_derail2.sh with varying params
pgbench_derail2.sh
main test program
can be called 'standalone'
./pgbench_derail2.sh $scale $clients $duration $date_str
so for instance this should work:
./pgbench_derail2.sh 25 64 60 20170527_1019
to remove publication and subscription from sessions, add a 5th
parameter 'clean'
./pgbench_derail2.sh 1 1 1 1 'clean'
pubsub.sh
displays replication state. also called by pgbench_derail2.sh
must be in path
result.sh
display results
I keep this in a screen-session as:
watch -n 20 './result.sh 201705'
Peculiar to my setup also:
server version at compile time stamped with date + commit hash
I misuse information_schema.sql_packages at compile time to store
patch information
instances are in $pg_stuff_dir/pg_installations/pgsql.<project name>
So you'll have to outcomment a line here and there, and adapt paths,
ports, and things like that.
It's a bit messy, I should have used perl from the beginning...
Considering it is all shell - pretty nice! I spent a bit of time today
getting this working in a vanilla Ubuntu 16.04 cloud server. I found a
few things that didn't work (suspect Erik has some default env variables
set for ports and databases). These were sufficient to stop logical
replication working for me at all - due to no dbname specified in the
subscription connection.
Given I had to make some changes anyway, I moved all the config into one
place (new file config.sh) - made all the programs use /bin/bash as
interpreter (/bin/sh just does not work for scripts on Ubuntu), added
ports and databases as reqd and fixed the need to mess too much with
PATH (see attached diff).
So running in cloud land now...so for no errors - will update.
regards
Mark
diff -Naur test.orig/config.sh test/config.sh
--- test.orig/config.sh 1970-01-01 12:00:00.000000000 +1200
+++ test/config.sh 2017-05-28 15:21:33.261701918 +1200
@@ -0,0 +1,13 @@
+#!/bin/bash
+port1=6972
+project1=logical_replication
+port2=6973
+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
+db=bench
diff -Naur test.orig/instances_fast.sh test/instances_fast.sh
--- test.orig/instances_fast.sh 2017-05-28 15:18:33.315780487 +1200
+++ test/instances_fast.sh 2017-05-28 15:19:02.511439749 +1200
@@ -1,17 +1,10 @@
-#!/bin/sh
+#!/bin/bash
# assertions on in $pg_stuff_dir/pg_installations/pgsql.<project name>/bin
# assertions off in $pg_stuff_dir/pg_installations/pgsql.<project name>/bin.fast
-port1=6972 project1=logical_replication
-port2=6973 project2=logical_replication2
-pg_stuff_dir=$HOME/pg_stuff
-PATH1=$pg_stuff_dir/pg_installations/pgsql.$project1/bin.fast:$PATH
-PATH2=$pg_stuff_dir/pg_installations/pgsql.$project2/bin.fast:$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
+. config.sh
+
options1="
-c wal_level=logical
-c max_replication_slots=10
@@ -36,6 +29,6 @@
-c log_filename=logfile.${project2}
-c log_replication_commands=on "
-export PATH=$PATH1; PG=$(which postgres); $PG -D $data_dir1 -p $port1 ${options1} &
-export PATH=$PATH2; PG=$(which postgres); $PG -D $data_dir2 -p $port2 ${options2} &
+export PATH=$PATH1; export PG=$(which postgres); $PG -D $data_dir1 -p $port1 ${options1} &
+export PATH=$PATH2; export PG=$(which postgres); $PG -D $data_dir2 -p $port2 ${options2} &
diff -Naur test.orig/instances.sh test/instances.sh
--- test.orig/instances.sh 2017-05-28 15:18:33.291780768 +1200
+++ test/instances.sh 2017-05-28 15:19:02.511439749 +1200
@@ -1,17 +1,9 @@
-#!/bin/sh
+#!/bin/bash
# assertions on in $pg_stuff_dir/pg_installations/pgsql.<project name>/bin
# assertions off in $pg_stuff_dir/pg_installations/pgsql.<project name>/bin.fast
-port1=6972 project1=logical_replication
-port2=6973 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
+. config.sh
options1="
-c wal_level=logical
diff -Naur test.orig/pgbench_derail2.sh test/pgbench_derail2.sh
--- test.orig/pgbench_derail2.sh 2017-05-28 15:18:33.363779926 +1200
+++ test/pgbench_derail2.sh 2017-05-28 15:19:02.511439749 +1200
@@ -11,11 +11,13 @@
# I misuse information_schema.sql_packages at compile time to store patch information
# instances are in $pg_stuff_dir/pg_installations/pgsql.<project name>
-pg_stuff_dir=$HOME/pg_stuff
+. config.sh
+
+export PATH=$PATH1
# assume both instances are running, on port 6972 (master) and 6973 (replica)
-port1=6972 logfile1=$pg_stuff_dir/pg_installations/pgsql.logical_replication/logfile.logical_replication
-port2=6973 logfile2=$pg_stuff_dir/pg_installations/pgsql.logical_replication2/logfile.logical_replication2
+logfile1=$pg_stuff_dir/pg_installations/pgsql.logical_replication/logfile.logical_replication
+logfile2=$pg_stuff_dir/pg_installations/pgsql.logical_replication2/logfile.logical_replication2
# clear logs
echo > $logfile1
@@ -96,16 +98,16 @@
pgsettings
unset PGSERVICEFILE PGSERVICE # PGPORT PGDATA PGHOST
-export PGDATABASE=testdb
+export PGDATABASE=$db
- pgdata_master=$pg_stuff_dir/pg_installations/pgsql.logical_replication/data
+pgdata_master=$pg_stuff_dir/pg_installations/pgsql.logical_replication/data
pgdata_replica=$pg_stuff_dir/pg_installations/pgsql.logical_replication2/data
function cb()
{
# display the 4 pgbench tables' accumulated content as md5s
# a,b,t,h stand for: pgbench_accounts, -branches, -tellers, -history
- num_tables=$( echo "select count(*) from pg_class where relkind = 'r' and relname ~ '^pgbench_'" | psql -qtAX )
+ num_tables=$( echo "select count(*) from pg_class where relkind = 'r' and relname ~ '^pgbench_'" | psql -qtAX -p $port1 )
if [[ $num_tables -ne 4 ]]
then
echo "pgbench tables not 4 - exit"
@@ -145,7 +147,7 @@
echo "$1"
- pubsub.sh $port1
+ ./pubsub.sh $port1
sub_count=$( echo "select count(*) from pg_subscription" | psql -qtAXp $port2 )
if [[ $sub_count -ne 0 ]]; then
@@ -254,14 +256,14 @@
&& 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 testdb
+ -t pgbench_history -t pgbench_accounts -t pgbench_branches -t pgbench_tellers | pg_restore -1 -p $port2 -d $db
appname=derail2
echo "create publication pub1 for all tables;" | psql -p $port1 -aqtAX
-echo "create subscription sub1 connection 'port=${port1} application_name=$appname' publication pub1 with(enabled=false);
+echo "create subscription sub1 connection 'port=${port1} dbname=${db} application_name=$appname' publication pub1 with(enabled=false);
alter subscription sub1 enable;" | psql -p $port2 -aqtAX
-pubsub.sh $port1
+./pubsub.sh $port1
RUN_PGBENCH=1
@@ -269,7 +271,7 @@
then
pseconds=$( echo "$duration / 5" | bc )
echo "-- pgbench -c $clients -j $threads -T $duration -P $pseconds -n -- scale $scale"
- pgbench -c $clients -j $threads -T $duration -P $pseconds -n # scale $scale
+ pgbench -c $clients -j $threads -T $duration -P $pseconds -n -p $port1
else
echo "-- not running pgbench..."
fi
@@ -280,7 +282,7 @@
wait_total=$waiting1
-pubsub.sh $port1
+./pubsub.sh $port1
date +"%Y.%m.%d %H:%M:%S"
echo "-- getting md5 (cb)"
diff -Naur test.orig/pubsub.sh test/pubsub.sh
--- test.orig/pubsub.sh 2017-05-28 15:18:33.383779692 +1200
+++ test/pubsub.sh 2017-05-28 15:19:02.511439749 +1200
@@ -2,16 +2,9 @@
db=testdb
-# default: use HEAD1 and HEAD2 instances (from unaltered git master instances)
-port1=6511 project1=HEAD1
-port2=6512 project2=HEAD2
+. config.sh
+export PATH=$PATH
-if [[ "$1" == "6972" ]]
-then
- # use the logical_replication and logical_replication2 instances (these aer instances of patched master).
- port1=6972 project1=logical_replication
- port2=6973 project2=logical_replication2
-fi
# echo '\dRp' | psql -d $db -qX -p $port1
# echo '\dRs' | psql -d $db -qX -p $port2
diff -Naur test.orig/results.sh test/results.sh
--- test.orig/results.sh 2017-05-28 15:18:33.399779505 +1200
+++ test/results.sh 2017-05-28 15:19:02.511439749 +1200
@@ -1,4 +1,4 @@
-#!/bin/sh
+#!/bin/bash
# cd $HOME/pg_stuff/pg_sql/pgsql.logical_replication/outs
diff -Naur test.orig/testset.sh test/testset.sh
--- test.orig/testset.sh 2017-05-28 15:18:33.335780253 +1200
+++ test/testset.sh 2017-05-28 15:19:02.511439749 +1200
@@ -1,8 +1,8 @@
#!/bin/bash
-pg_stuff_dir=$HOME/pg_stuff
-port1=6972 project1=logical_replication
-port2=6973 project2=logical_replication2
-db=testdb
+
+. config.sh
+
+export PATH=$PATH1
rc=0
duration=60
while [[ $rc -eq 0 ]]
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
