Shard Code

gistfile1.txt

# Escaped with $ as this runs from bash!


bin/psql -p7777 -Upostgres <<EOF
\c postgres
DROP DATABASE IF EXISTS shard0;
CREATE DATABASE shard0;
\c shard0
CREATE EXTENSION postgres_fdw;
CREATE SERVER shard0 FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS(host '127.0.0.1',port '7777',dbname 'shard0');
CREATE SERVER shard1 FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS(host '127.0.0.1',port '8888',dbname 'shard1');
CREATE SERVER shard2 FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS(host '127.0.0.1',port '9999',dbname 'shard2');

CREATE USER MAPPING FOR postgres SERVER shard0 OPTIONS(user 'postgres');
CREATE USER MAPPING FOR postgres SERVER shard1 OPTIONS(user 'postgres');
CREATE USER MAPPING FOR postgres SERVER shard2 OPTIONS(user 'postgres');

CREATE TABLE main (group_id char(2), payload jsonb);
CREATE TABLE main_shard_p0 (CHECK (group_id = 'p0'))INHERITS (main) ;
CREATE INDEX ix_main_shard_p0_key ON main_shard_p0 ((payload->>'key'));
CREATE FOREIGN TABLE main_shard_p1 (CHECK (group_id = 'p1'))INHERITS (main) SERVER shard1;
CREATE FOREIGN TABLE main_shard_p2 (CHECK (group_id = 'p2'))INHERITS (main) SERVER shard2;

CREATE OR REPLACE FUNCTION f_main_part() RETURNS TRIGGER AS
 \$BODY\$
          DECLARE
            partition_name text;
          BEGIN
            partition_name := 'main_shard_' || NEW.group_id;
            EXECUTE  'INSERT INTO ' ||  quote_ident(partition_name) ||  ' SELECT (\$1).*' USING NEW ;
            RETURN NULL;
          END;
 \$BODY\$ LANGUAGE plpgsql;

CREATE TRIGGER t_main BEFORE INSERT ON main FOR EACH ROW EXECUTE PROCEDURE f_main_part();
EOF

# 

bin/psql -p8888 -Upostgres <<EOF
\c postgres
drop database shard1;
CREATE DATABASE shard1;
\c shard1
CREATE EXTENSION postgres_fdw;
CREATE SERVER shard0 FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS(host '127.0.0.1',port '7777',dbname 'shard0');
CREATE SERVER shard1 FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS(host '127.0.0.1',port '8888',dbname 'shard1');
CREATE SERVER shard2 FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS(host '127.0.0.1',port '9999',dbname 'shard2');

CREATE USER MAPPING FOR postgres SERVER shard0 OPTIONS(user 'postgres');
CREATE USER MAPPING FOR postgres SERVER shard1 OPTIONS(user 'postgres');
CREATE USER MAPPING FOR postgres SERVER shard2 OPTIONS(user 'postgres');

CREATE TABLE main (group_id char(2), payload jsonb);
CREATE TABLE main_shard_p1 (CHECK (group_id = 'p1'))INHERITS (main) ;
CREATE INDEX ix_main_shard_p1_key ON main_shard_p1 ((payload->>'key'));
CREATE FOREIGN TABLE main_shard_p0 (CHECK (group_id = 'p0'))INHERITS (main) SERVER shard0;
CREATE FOREIGN TABLE main_shard_p2 (CHECK (group_id = 'p2'))INHERITS (main) SERVER shard2;

CREATE OR REPLACE FUNCTION f_main_part() RETURNS TRIGGER AS
\$BODY\$
          DECLARE
            partition_name text;
          BEGIN
            partition_name := 'main_shard_' || NEW.group_id;
            EXECUTE  'INSERT INTO ' ||  quote_ident(partition_name) ||  ' SELECT (\$1).*' USING NEW ;
            RETURN NULL;
          END;
\$BODY\$ LANGUAGE plpgsql;

CREATE TRIGGER t_main BEFORE INSERT ON main FOR EACH ROW EXECUTE PROCEDURE f_main_part();

EOF

#

bin/psql -p9999 -Upostgres <<EOF
\c postgres
drop database shard2;
CREATE DATABASE shard2;
\c shard2
CREATE EXTENSION postgres_fdw;
CREATE SERVER shard0 FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS(host '127.0.0.1',port '7777',dbname 'shard0');
CREATE SERVER shard1 FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS(host '127.0.0.1',port '8888',dbname 'shard1');
CREATE SERVER shard2 FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS(host '127.0.0.1',port '9999',dbname 'shard2');

CREATE USER MAPPING FOR postgres SERVER shard0 OPTIONS(user 'postgres');
CREATE USER MAPPING FOR postgres SERVER shard1 OPTIONS(user 'postgres');
CREATE USER MAPPING FOR postgres SERVER shard2 OPTIONS(user 'postgres');

CREATE TABLE main (group_id char(2), payload jsonb);
CREATE TABLE main_shard_p2 (CHECK (group_id = 'p2'))INHERITS (main) ;
CREATE INDEX ix_main_shard_p2_key ON main_shard_p2 ((payload->>'key'));
CREATE FOREIGN TABLE main_shard_p0 (CHECK (group_id = 'p0'))INHERITS (main) SERVER shard0;
CREATE FOREIGN TABLE main_shard_p1 (CHECK (group_id = 'p1'))INHERITS (main) SERVER shard1;

CREATE OR REPLACE FUNCTION f_main_part() RETURNS TRIGGER AS
\$BODY\$
          DECLARE
            partition_name text;
          BEGIN
            partition_name := 'main_shard_' || NEW.group_id;
            EXECUTE  'INSERT INTO ' ||  quote_ident(partition_name) ||  ' SELECT (\$1).*' USING NEW ;
            RETURN NULL;
          END;
\$BODY\$ LANGUAGE plpgsql;

CREATE TRIGGER t_main BEFORE INSERT ON main FOR EACH ROW EXECUTE PROCEDURE f_main_part();

EOF