Skip to content

Instantly share code, notes, and snippets.

@alex-richman-onesignal

alex-richman-onesignal/init_provider.sql Secret

Created January 12, 2023 12:56
Show Gist options
  • Save alex-richman-onesignal/dd51837f43380afb21ba7906fe1ac549 to your computer and use it in GitHub Desktop.
Save alex-richman-onesignal/dd51837f43380afb21ba7906fe1ac549 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
init_provider.sql
CREATE TABLE IF NOT EXISTS test_table(id UUID PRIMARY KEY, data JSONB);
-- NB: Increase series size for longer update and higher memory peak
INSERT INTO test_table SELECT uuid_generate_v4(), '{"init_0": "1", "init_1": "1", "init_2": "1", "init_3": "1", "init_4": "1", "init_5": "1", "init_6": "1", "init_7": "1", "init_8": "1", "init_9": "1", "init_a": "1", "init_b": "1", "init_c": "1", "init_d": "1", "init_e": "1", "init_f": "1"}'::jsonb FROM generate_series(1, 16000000) s(i);
CREATE PUBLICATION test_pub FOR TABLE test_table;
SELECT pg_create_logical_replication_slot('test_slot', 'pgoutput', false);
Raw
init_subscriber.sql
CREATE TABLE IF NOT EXISTS test_table(id UUID PRIMARY KEY, data JSONB);
CREATE SUBSCRIPTION test_sub CONNECTION 'host=... port=5432 dbname=test' PUBLICATION test_pub WITH (
copy_data=true, create_slot=false, enabled=true, slot_name='test_slot', connect=true,
synchronous_commit=false, binary=true, streaming=true
);
-- Wait for initial sync
Raw
update_load.sh
#!/bin/bash
psql -c 'UPDATE test_table SET data = data || $${"test_0": "1", "test_1": "1", "test_2": "1", "test_3": "1", "test_4": "1", "test_5": "1", "test_6": "1", "test_7": "1", "test_8": "1", "test_9": "1", "test_a": "1", "test_b": "1", "test_c": "1", "test_d": "1", "test_e": "1", "test_f": "1"}$$ @- $${"init_0"}$$ WHERE id >= $$00000000-0000-0000-0000-000000000000$$ AND id < $$10000000-0000-0000-0000-000000000000$$' &
psql -c 'UPDATE test_table SET data = data || $${"test_0": "1", "test_1": "1", "test_2": "1", "test_3": "1", "test_4": "1", "test_5": "1", "test_6": "1", "test_7": "1", "test_8": "1", "test_9": "1", "test_a": "1", "test_b": "1", "test_c": "1", "test_d": "1", "test_e": "1", "test_f": "1"}$$ @- $${"init_0"}$$ WHERE id >= $$10000000-0000-0000-0000-000000000000$$ AND id < $$20000000-0000-0000-0000-000000000000$$' &
psql -c 'UPDATE test_table SET data = data || $${"test_0": "1", "test_1": "1", "test_2": "1", "test_3": "1", "test_4": "1", "test_5": "1", "test_6": "1", "test_7": "1", "test_8": "1", "test_9": "1", "test_a": "1", "test_b": "1", "test_c": "1", "test_d": "1", "test_e": "1", "test_f": "1"}$$ @- $${"init_0"}$$ WHERE id >= $$20000000-0000-0000-0000-000000000000$$ AND id < $$30000000-0000-0000-0000-000000000000$$' &
psql -c 'UPDATE test_table SET data = data || $${"test_0": "1", "test_1": "1", "test_2": "1", "test_3": "1", "test_4": "1", "test_5": "1", "test_6": "1", "test_7": "1", "test_8": "1", "test_9": "1", "test_a": "1", "test_b": "1", "test_c": "1", "test_d": "1", "test_e": "1", "test_f": "1"}$$ @- $${"init_0"}$$ WHERE id >= $$30000000-0000-0000-0000-000000000000$$ AND id < $$40000000-0000-0000-0000-000000000000$$' &
psql -c 'UPDATE test_table SET data = data || $${"test_0": "1", "test_1": "1", "test_2": "1", "test_3": "1", "test_4": "1", "test_5": "1", "test_6": "1", "test_7": "1", "test_8": "1", "test_9": "1", "test_a": "1", "test_b": "1", "test_c": "1", "test_d": "1", "test_e": "1", "test_f": "1"}$$ @- $${"init_0"}$$ WHERE id >= $$40000000-0000-0000-0000-000000000000$$ AND id < $$50000000-0000-0000-0000-000000000000$$' &
psql -c 'UPDATE test_table SET data = data || $${"test_0": "1", "test_1": "1", "test_2": "1", "test_3": "1", "test_4": "1", "test_5": "1", "test_6": "1", "test_7": "1", "test_8": "1", "test_9": "1", "test_a": "1", "test_b": "1", "test_c": "1", "test_d": "1", "test_e": "1", "test_f": "1"}$$ @- $${"init_0"}$$ WHERE id >= $$50000000-0000-0000-0000-000000000000$$ AND id < $$60000000-0000-0000-0000-000000000000$$' &
psql -c 'UPDATE test_table SET data = data || $${"test_0": "1", "test_1": "1", "test_2": "1", "test_3": "1", "test_4": "1", "test_5": "1", "test_6": "1", "test_7": "1", "test_8": "1", "test_9": "1", "test_a": "1", "test_b": "1", "test_c": "1", "test_d": "1", "test_e": "1", "test_f": "1"}$$ @- $${"init_0"}$$ WHERE id >= $$60000000-0000-0000-0000-000000000000$$ AND id < $$70000000-0000-0000-0000-000000000000$$' &
psql -c 'UPDATE test_table SET data = data || $${"test_0": "1", "test_1": "1", "test_2": "1", "test_3": "1", "test_4": "1", "test_5": "1", "test_6": "1", "test_7": "1", "test_8": "1", "test_9": "1", "test_a": "1", "test_b": "1", "test_c": "1", "test_d": "1", "test_e": "1", "test_f": "1"}$$ @- $${"init_0"}$$ WHERE id >= $$70000000-0000-0000-0000-000000000000$$ AND id < $$80000000-0000-0000-0000-000000000000$$' &
psql -c 'UPDATE test_table SET data = data || $${"test_0": "1", "test_1": "1", "test_2": "1", "test_3": "1", "test_4": "1", "test_5": "1", "test_6": "1", "test_7": "1", "test_8": "1", "test_9": "1", "test_a": "1", "test_b": "1", "test_c": "1", "test_d": "1", "test_e": "1", "test_f": "1"}$$ @- $${"init_0"}$$ WHERE id >= $$80000000-0000-0000-0000-000000000000$$ AND id < $$90000000-0000-0000-0000-000000000000$$' &
psql -c 'UPDATE test_table SET data = data || $${"test_0": "1", "test_1": "1", "test_2": "1", "test_3": "1", "test_4": "1", "test_5": "1", "test_6": "1", "test_7": "1", "test_8": "1", "test_9": "1", "test_a": "1", "test_b": "1", "test_c": "1", "test_d": "1", "test_e": "1", "test_f": "1"}$$ @- $${"init_0"}$$ WHERE id >= $$90000000-0000-0000-0000-000000000000$$ AND id < $$a0000000-0000-0000-0000-000000000000$$' &
psql -c 'UPDATE test_table SET data = data || $${"test_0": "1", "test_1": "1", "test_2": "1", "test_3": "1", "test_4": "1", "test_5": "1", "test_6": "1", "test_7": "1", "test_8": "1", "test_9": "1", "test_a": "1", "test_b": "1", "test_c": "1", "test_d": "1", "test_e": "1", "test_f": "1"}$$ @- $${"init_0"}$$ WHERE id >= $$a0000000-0000-0000-0000-000000000000$$ AND id < $$b0000000-0000-0000-0000-000000000000$$' &
psql -c 'UPDATE test_table SET data = data || $${"test_0": "1", "test_1": "1", "test_2": "1", "test_3": "1", "test_4": "1", "test_5": "1", "test_6": "1", "test_7": "1", "test_8": "1", "test_9": "1", "test_a": "1", "test_b": "1", "test_c": "1", "test_d": "1", "test_e": "1", "test_f": "1"}$$ @- $${"init_0"}$$ WHERE id >= $$b0000000-0000-0000-0000-000000000000$$ AND id < $$c0000000-0000-0000-0000-000000000000$$' &
psql -c 'UPDATE test_table SET data = data || $${"test_0": "1", "test_1": "1", "test_2": "1", "test_3": "1", "test_4": "1", "test_5": "1", "test_6": "1", "test_7": "1", "test_8": "1", "test_9": "1", "test_a": "1", "test_b": "1", "test_c": "1", "test_d": "1", "test_e": "1", "test_f": "1"}$$ @- $${"init_0"}$$ WHERE id >= $$c0000000-0000-0000-0000-000000000000$$ AND id < $$d0000000-0000-0000-0000-000000000000$$' &
psql -c 'UPDATE test_table SET data = data || $${"test_0": "1", "test_1": "1", "test_2": "1", "test_3": "1", "test_4": "1", "test_5": "1", "test_6": "1", "test_7": "1", "test_8": "1", "test_9": "1", "test_a": "1", "test_b": "1", "test_c": "1", "test_d": "1", "test_e": "1", "test_f": "1"}$$ @- $${"init_0"}$$ WHERE id >= $$d0000000-0000-0000-0000-000000000000$$ AND id < $$e0000000-0000-0000-0000-000000000000$$' &
psql -c 'UPDATE test_table SET data = data || $${"test_0": "1", "test_1": "1", "test_2": "1", "test_3": "1", "test_4": "1", "test_5": "1", "test_6": "1", "test_7": "1", "test_8": "1", "test_9": "1", "test_a": "1", "test_b": "1", "test_c": "1", "test_d": "1", "test_e": "1", "test_f": "1"}$$ @- $${"init_0"}$$ WHERE id >= $$e0000000-0000-0000-0000-000000000000$$ AND id < $$f0000000-0000-0000-0000-000000000000$$' &
psql -c 'UPDATE test_table SET data = data || $${"test_0": "1", "test_1": "1", "test_2": "1", "test_3": "1", "test_4": "1", "test_5": "1", "test_6": "1", "test_7": "1", "test_8": "1", "test_9": "1", "test_a": "1", "test_b": "1", "test_c": "1", "test_d": "1", "test_e": "1", "test_f": "1"}$$ @- $${"init_0"}$$ WHERE id >= $$f0000000-0000-0000-0000-000000000000$$ AND id <= $$ffffffff-ffff-ffff-ffff-ffffffffffff$$' &
wait
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment