Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- echo master shard_{0,1,2,3} | xargs -n1 /usr/local/bin/dropdb -p 6432 -h /tmp -U postgres
- echo master shard_{0,1,2,3} | xargs -n1 /usr/local/bin/createdb -p 6432 -h /tmp -U postgres
- for a in {0..3}; do
- echo "
- CREATE TABLE users (id serial PRIMARY KEY, username TEXT NOT NULL);
- ALTER SEQUENCE users_id_seq INCREMENT BY 4 RESTART WITH $a;
- " | /usr/local/bin/psql -p 6432 -h /tmp -U postgres -d shard_$a;
- done
- cat <<'EOF' | /usr/local/bin/psql -p 6432 -h /tmp -U postgres -d master
- CREATE TABLE users (id serial PRIMARY KEY, username TEXT NOT NULL);
- CREATE EXTENSION postgres_fdw;
- CREATE SERVER shard_0 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'shard_0', host '/tmp', port '6432');
- CREATE SERVER shard_1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'shard_1', host '/tmp', port '6432');
- CREATE SERVER shard_2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'shard_2', host '/tmp', port '6432');
- CREATE SERVER shard_3 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'shard_3', host '/tmp', port '6432');
- CREATE USER MAPPING FOR POSTGRES SERVER shard_0 OPTIONS (user 'postgres');
- CREATE USER MAPPING FOR POSTGRES SERVER shard_1 OPTIONS (user 'postgres');
- CREATE USER MAPPING FOR POSTGRES SERVER shard_2 OPTIONS (user 'postgres');
- CREATE USER MAPPING FOR POSTGRES SERVER shard_3 OPTIONS (user 'postgres');
- CREATE FOREIGN TABLE users_shard_0 () INHERITS (users) SERVER shard_0 OPTIONS (table_name 'users');
- CREATE FOREIGN TABLE users_shard_1 () INHERITS (users) SERVER shard_1 OPTIONS (table_name 'users');
- CREATE FOREIGN TABLE users_shard_2 () INHERITS (users) SERVER shard_2 OPTIONS (table_name 'users');
- CREATE FOREIGN TABLE users_shard_3 () INHERITS (users) SERVER shard_3 OPTIONS (table_name 'users');
- ALTER FOREIGN TABLE users_shard_0 ADD CHECK (id % 4 = 0);
- ALTER FOREIGN TABLE users_shard_1 ADD CHECK (id % 4 = 1);
- ALTER FOREIGN TABLE users_shard_2 ADD CHECK (id % 4 = 2);
- ALTER FOREIGN TABLE users_shard_3 ADD CHECK (id % 4 = 3);
- CREATE OR REPLACE FUNCTION __trigger_users_before_insert(
- ) RETURNS trigger AS $__$
- BEGIN
- EXECUTE $$
- INSERT INTO $$ || ('users_shard_' || (NEW.id % 4)::text)::regclass || $$ VALUES (
- $1, $2
- )
- $$ USING
- NEW.id,
- NEW.username;
- RETURN null;
- END;
- $__$ LANGUAGE plpgsql;
- CREATE TRIGGER users_before_insert BEFORE INSERT ON users FOR EACH ROW EXECUTE PROCEDURE __trigger_users_before_insert();
- INSERT INTO users (username) SELECT 'random user ' || i FROM generate_series(1, 100) i;
- SELECT * FROM users_shard_0 LIMIT 10;
- SELECT * FROM users_shard_1 LIMIT 10;
- SELECT * FROM users_shard_2 LIMIT 10;
- SELECT * FROM users_shard_3 LIMIT 10;
- EOF
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement