Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit145aa5e

Browse files
author
Your Name
committed
Scripts for deploy 3-node local configuration of FDW+partitioning
1 parent1f1dac8 commit145aa5e

File tree

7 files changed

+193
-0
lines changed

7 files changed

+193
-0
lines changed

‎cln

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
make clean > /dev/null
2+
make -C contrib clean > /dev/null

‎cmp

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
export CFLAGS="-O0"
2+
./configure --prefix=`pwd`/tmp_install --enable-tap-tests --enable-debug \
3+
--enable-cassert --enable-nls --with-openssl --with-perl --with-python

‎mk

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
make -C contrib > /dev/null
2+
make -C contrib install > /dev/null
3+
make > /dev/null
4+
make install > /dev/null

‎n0_pgb_init.sql

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
CREATE SERVER remote1 FOREIGN DATA WRAPPER postgres_fdwOPTIONS (port'5433', use_remote_estimate'on');
2+
CREATEUSERMAPPING FOR PUBLIC SERVER remote1;
3+
CREATE SERVER remote2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (port'5434', use_remote_estimate'on');
4+
CREATEUSERMAPPING FOR PUBLIC SERVER remote2;
5+
6+
DROPTABLE pgbench_accounts_2 CASCADE;
7+
DROPTABLE pgbench_accounts_3 CASCADE;
8+
ALTERTABLE pgbench_accounts DROPCONSTRAINT pgbench_accounts_pkey;
9+
10+
DROPTABLE pgbench_branches_2 CASCADE;
11+
DROPTABLE pgbench_branches_3 CASCADE;
12+
ALTERTABLE pgbench_branches DROPCONSTRAINT pgbench_branches_pkey;
13+
14+
DROPTABLE pgbench_tellers_2 CASCADE;
15+
DROPTABLE pgbench_tellers_3 CASCADE;
16+
ALTERTABLE pgbench_tellers DROPCONSTRAINT pgbench_tellers_pkey;
17+
18+
CREATE FOREIGN TABLE pgbench_accounts_2 PARTITION OF pgbench_accounts FORVALUES WITH (modulus3, remainder1) SERVER remote1;
19+
CREATE FOREIGN TABLE pgbench_accounts_3 PARTITION OF pgbench_accounts FORVALUES WITH (modulus3, remainder2) SERVER remote2;
20+
ALTERTABLE pgbench_accounts ADDPRIMARY KEY (aid);
21+
22+
CREATE FOREIGN TABLE pgbench_branches_2 PARTITION OF pgbench_branches FORVALUES WITH (modulus3, remainder1) SERVER remote1;
23+
CREATE FOREIGN TABLE pgbench_branches_3 PARTITION OF pgbench_branches FORVALUES WITH (modulus3, remainder2) SERVER remote2;
24+
ALTERTABLE pgbench_branches ADDPRIMARY KEY (bid);
25+
26+
CREATE FOREIGN TABLE pgbench_tellers_2 PARTITION OF pgbench_tellers FORVALUES WITH (modulus3, remainder1) SERVER remote1;
27+
CREATE FOREIGN TABLE pgbench_tellers_3 PARTITION OF pgbench_tellers FORVALUES WITH (modulus3, remainder2) SERVER remote2;
28+
ALTERTABLE pgbench_tellers ADDPRIMARY KEY (tid);
29+

‎n1_pgb_init.sql

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
CREATE SERVER remote1 FOREIGN DATA WRAPPER postgres_fdwOPTIONS (port'5432', use_remote_estimate'on');
2+
CREATEUSERMAPPING FOR PUBLIC SERVER remote1;
3+
CREATE SERVER remote2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (port'5434', use_remote_estimate'on');
4+
CREATEUSERMAPPING FOR PUBLIC SERVER remote2;
5+
6+
DROPTABLE pgbench_accounts_1 CASCADE;
7+
DROPTABLE pgbench_accounts_3 CASCADE;
8+
ALTERTABLE pgbench_accounts DROPCONSTRAINT pgbench_accounts_pkey;
9+
10+
DROPTABLE pgbench_branches_1 CASCADE;
11+
DROPTABLE pgbench_branches_3 CASCADE;
12+
ALTERTABLE pgbench_branches DROPCONSTRAINT pgbench_branches_pkey;
13+
14+
DROPTABLE pgbench_tellers_1 CASCADE;
15+
DROPTABLE pgbench_tellers_3 CASCADE;
16+
ALTERTABLE pgbench_tellers DROPCONSTRAINT pgbench_tellers_pkey;
17+
18+
CREATE FOREIGN TABLE pgbench_accounts_1 PARTITION OF pgbench_accounts FORVALUES WITH (modulus3, remainder0) SERVER remote1;
19+
CREATE FOREIGN TABLE pgbench_accounts_3 PARTITION OF pgbench_accounts FORVALUES WITH (modulus3, remainder2) SERVER remote2;
20+
ALTERTABLE pgbench_accounts ADDPRIMARY KEY (aid);
21+
22+
CREATE FOREIGN TABLE pgbench_branches_1 PARTITION OF pgbench_branches FORVALUES WITH (modulus3, remainder0) SERVER remote1;
23+
CREATE FOREIGN TABLE pgbench_branches_3 PARTITION OF pgbench_branches FORVALUES WITH (modulus3, remainder2) SERVER remote2;
24+
ALTERTABLE pgbench_branches ADDPRIMARY KEY (bid);
25+
26+
CREATE FOREIGN TABLE pgbench_tellers_1 PARTITION OF pgbench_tellers FORVALUES WITH (modulus3, remainder0) SERVER remote1;
27+
CREATE FOREIGN TABLE pgbench_tellers_3 PARTITION OF pgbench_tellers FORVALUES WITH (modulus3, remainder2) SERVER remote2;
28+
ALTERTABLE pgbench_tellers ADDPRIMARY KEY (tid);
29+

‎n2_pgb_init.sql

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
CREATE SERVER remote1 FOREIGN DATA WRAPPER postgres_fdwOPTIONS (port'5432', use_remote_estimate'on');
2+
CREATEUSERMAPPING FOR PUBLIC SERVER remote1;
3+
CREATE SERVER remote2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (port'5433', use_remote_estimate'on');
4+
CREATEUSERMAPPING FOR PUBLIC SERVER remote2;
5+
6+
DROPTABLE pgbench_accounts_1 CASCADE;
7+
DROPTABLE pgbench_accounts_2 CASCADE;
8+
ALTERTABLE pgbench_accounts DROPCONSTRAINT pgbench_accounts_pkey;
9+
10+
DROPTABLE pgbench_branches_1 CASCADE;
11+
DROPTABLE pgbench_branches_2 CASCADE;
12+
ALTERTABLE pgbench_branches DROPCONSTRAINT pgbench_branches_pkey;
13+
14+
DROPTABLE pgbench_tellers_1 CASCADE;
15+
DROPTABLE pgbench_tellers_2 CASCADE;
16+
ALTERTABLE pgbench_tellers DROPCONSTRAINT pgbench_tellers_pkey;
17+
18+
CREATE FOREIGN TABLE pgbench_accounts_1 PARTITION OF pgbench_accounts FORVALUES WITH (modulus3, remainder0) SERVER remote1;
19+
CREATE FOREIGN TABLE pgbench_accounts_2 PARTITION OF pgbench_accounts FORVALUES WITH (modulus3, remainder1) SERVER remote2;
20+
ALTERTABLE pgbench_accounts ADDPRIMARY KEY (aid);
21+
22+
CREATE FOREIGN TABLE pgbench_branches_1 PARTITION OF pgbench_branches FORVALUES WITH (modulus3, remainder0) SERVER remote1;
23+
CREATE FOREIGN TABLE pgbench_branches_2 PARTITION OF pgbench_branches FORVALUES WITH (modulus3, remainder1) SERVER remote2;
24+
ALTERTABLE pgbench_branches ADDPRIMARY KEY (bid);
25+
26+
CREATE FOREIGN TABLE pgbench_tellers_1 PARTITION OF pgbench_tellers FORVALUES WITH (modulus3, remainder0) SERVER remote1;
27+
CREATE FOREIGN TABLE pgbench_tellers_2 PARTITION OF pgbench_tellers FORVALUES WITH (modulus3, remainder1) SERVER remote2;
28+
ALTERTABLE pgbench_tellers ADDPRIMARY KEY (tid);
29+

‎shdep.sh

Lines changed: 97 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,97 @@
1+
#!/bin/bash
2+
3+
# ##############################################################################
4+
#
5+
# Deploy local 3-node configuration of sharded postgres.
6+
#
7+
# This script performs initialization of fdw+partitioning infrastructure for
8+
# parallel (distributed) query execution purposes.
9+
#
10+
# ##############################################################################
11+
12+
PGINSTALL=`pwd`/tmp_install/
13+
DEPLOY_SCRIPTS_PATH=`pwd`
14+
SCALE=100
15+
16+
export LD_LIBRARY_PATH=$PGINSTALL/lib:$LD_LIBRARY_PATH
17+
export PATH=$PGINSTALL/bin:$PATH
18+
export LC_ALL=C
19+
export LANGUAGE="en_US:en"
20+
export PGPORT=5432#default head
21+
export PGDATABASE=shardman
22+
export PGHOST=localhost
23+
export PGUSER=`whoami`
24+
25+
pkill -U`whoami` -9 -e postgres
26+
pkill -U`whoami` -9 -e pgbench
27+
28+
D1=`pwd`/PGDATA1
29+
D2=`pwd`/PGDATA2
30+
D3=`pwd`/PGDATA3
31+
32+
rm -rf$D1&& mkdir$D1&& rm -rf$D2&& mkdir$D2&& rm -rf$D3&& mkdir$D3
33+
rm -rf$PGINSTALL&& rm n0.log&& rm n1.log&& rm n2.log
34+
35+
# Building project
36+
make> /dev/null
37+
make -C contrib> /dev/null
38+
make install> /dev/null
39+
make -C contrib install> /dev/null
40+
41+
remoteSrvName=fdwremote
42+
43+
initdb -D$D1 -E UTF8 --locale=C
44+
initdb -D$D2 -E UTF8 --locale=C
45+
initdb -D$D3 -E UTF8 --locale=C
46+
47+
echo"shared_preload_libraries = 'postgres_fdw'">>$D1/postgresql.conf
48+
echo"shared_preload_libraries = 'postgres_fdw'">>$D2/postgresql.conf
49+
echo"shared_preload_libraries = 'postgres_fdw'">>$D3/postgresql.conf
50+
echo"shared_buffers = 10GB">>$D1/postgresql.conf
51+
echo"shared_buffers = 10GB">>$D2/postgresql.conf
52+
echo"shared_buffers = 10GB">>$D3/postgresql.conf
53+
echo"listen_addresses = '*'">>$D1/postgresql.conf
54+
echo"listen_addresses = '*'">>$D2/postgresql.conf
55+
echo"listen_addresses = '*'">>$D3/postgresql.conf
56+
echo"host all all 0.0.0.0/0 trust">>$D1/pg_hba.conf
57+
echo"host all all 0.0.0.0/0 trust">>$D2/pg_hba.conf
58+
echo"host all all 0.0.0.0/0 trust">>$D3/pg_hba.conf
59+
60+
# Takeoff
61+
pg_ctl -w -c -o"-p 5434" -D$D3 -l n2.log start
62+
pg_ctl -w -c -o"-p 5433" -D$D2 -l n1.log start
63+
pg_ctl -w -c -o"-p 5432" -D$D1 -l n0.log start
64+
createdb -p 5432
65+
createdb -p 5433
66+
createdb -p 5434
67+
68+
# Init foreign tables
69+
psql -p 5434 -c"CREATE EXTENSION postgres_fdw;"
70+
psql -p 5433 -c"CREATE EXTENSION postgres_fdw;"
71+
psql -p 5432 -c"CREATE EXTENSION postgres_fdw;"
72+
73+
# Create pgbench partitions
74+
pgbench -p 5432 -i -s$SCALE --partitions=3 --partition-method=hash
75+
pgbench -p 5433 -i -s$SCALE --partitions=3 --partition-method=hash
76+
pgbench -p 5434 -i -s$SCALE --partitions=3 --partition-method=hash
77+
78+
# Drop unneeded local partitions and init foreign partitions
79+
psql -p 5432 -f"$DEPLOY_SCRIPTS_PATH/n0_pgb_init.sql"
80+
psql -p 5433 -f"$DEPLOY_SCRIPTS_PATH/n1_pgb_init.sql"
81+
psql -p 5434 -f"$DEPLOY_SCRIPTS_PATH/n2_pgb_init.sql"
82+
83+
# The end of deploy
84+
85+
86+
# Create tables pt,rt,st partitioned by hash on id column.
87+
#psql -p 5432 -f "$DEPLOY_SCRIPTS_PATH/init_node0.sql"
88+
#psql -p 5433 -f "$DEPLOY_SCRIPTS_PATH/init_node1.sql"
89+
#psql -p 5434 -f "$DEPLOY_SCRIPTS_PATH/init_node2.sql"
90+
91+
# Fill the pt relation
92+
#psql -p 5432 -c \
93+
#"INSERT INTO pt (id, payload, test)
94+
#(
95+
#SELECT a.*, b.*,0
96+
#);"
97+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp