frgn2n.sh

application/x-shellscript

Download

#!/bin/bash

# ##############################################################################
#
# Deploy local 2-node configuration of postgres instances. Node1 has foreign
# partitions at the node2.
#
#
# ##############################################################################

PGINSTALL=`pwd`/tmp_install/

export LD_LIBRARY_PATH=$PGINSTALL/lib:$LD_LIBRARY_PATH
export PATH=$PGINSTALL/bin:$PATH
export LC_ALL=C
export LANGUAGE="en_US:en"
export PGPORT=5432 #default head
export PGDATABASE=`whoami`
export PGHOST=localhost
export PGUSER=`whoami`

PARTSNUM=4
pkill -U `whoami` -9 -e postgres
pkill -U `whoami` -9 -e pgbench

D1=`pwd`/PGDATA1
D2=`pwd`/PGDATA2

rm -rf $D1 && mkdir $D1 && rm -rf $D2 && mkdir $D2
rm -rf $PGINSTALL && rm n1.log && rm n2.log

# Building project
make > /dev/null
make -C contrib > /dev/null
make install > /dev/null
make -C contrib install > /dev/null

remoteSrvName=fdwremote

initdb -D $D1 -E UTF8 --locale=C
initdb -D $D2 -E UTF8 --locale=C

echo "shared_preload_libraries = 'postgres_fdw'" >> $D1/postgresql.conf
echo "shared_preload_libraries = 'postgres_fdw'" >> $D2/postgresql.conf
echo "enable_partitionwise_join = true" >> $D1/postgresql.conf
echo "enable_partitionwise_aggregate = true" >> $D1/postgresql.conf

echo "listen_addresses = '*'" >> $D1/postgresql.conf
echo "listen_addresses = '*'" >> $D2/postgresql.conf
echo "host    all             all             0.0.0.0/0                 trust" >> $D1/pg_hba.conf
echo "host    all             all             0.0.0.0/0                 trust" >> $D2/pg_hba.conf

# Takeoff
pg_ctl -w -c -o "-p 5433" -D $D2 -l n2.log start
pg_ctl -w -c -o "-p 5432" -D $D1 -l n1.log start
createdb -p 5432
createdb -p 5433

# Init foreign tables
psql -p 5433 -c "CREATE EXTENSION postgres_fdw;"
psql -p 5432 -c "CREATE EXTENSION postgres_fdw;"

psql -p 5432 -c "CREATE SERVER remote FOREIGN DATA WRAPPER postgres_fdw 
	OPTIONS (port '5433', use_remote_estimate 'on');
	CREATE USER MAPPING FOR PUBLIC SERVER remote;"

# Create partitions
psql -p 5432 -c "
	CREATE TABLE parts (a serial, b int) PARTITION BY HASH(a);
	CREATE TABLE part_0 PARTITION OF parts FOR VALUES WITH (modulus $PARTSNUM, remainder 0);
	CREATE TABLE second (a serial, b int) PARTITION BY HASH(a);
	CREATE TABLE second_0 PARTITION OF second FOR VALUES WITH (modulus $PARTSNUM, remainder 0);"

for (( i=1; i < $PARTSNUM; i++ ))
do
	echo "create partition $i."
	psql -p 5433 -c "CREATE TABLE part_$i (a int, b int)"
	psql -p 5432 -c "
		CREATE FOREIGN TABLE part_$i PARTITION OF parts 
		FOR VALUES WITH (modulus $PARTSNUM, remainder $i) SERVER remote;"

	psql -p 5433 -c "CREATE TABLE second_$i (a serial, b int)"
	psql -p 5432 -c "
		CREATE FOREIGN TABLE second_$i PARTITION OF second 
		FOR VALUES WITH (modulus $PARTSNUM, remainder $i) SERVER remote;"
done

psql -p 5432 -c "INSERT INTO parts (b) (SELECT * FROM generate_series(1, 1e5) as g)"
psql -p 5432 -c "INSERT INTO second (b) (SELECT * FROM generate_series(1, 1e3) as g)"
psql -p 5432 -c "analyze parts"

# The end of deploy

# select * from parts;
# explain select * from parts;
# SELECT * FROM parts AS a, (SELECT * FROM parts) AS b WHERE a.a != b.b;
# SELECT sum(b) FROM parts, second WHERE parts.a = second.a;
# explain SELECT * FROM parts, second WHERE parts.a = second.a AND parts.b < 100;
# explain SELECT sum(parts.b) FROM parts, second WHERE parts.a = second.a AND parts.b < 100;