138 lines
6.6 KiB
SQL
138 lines
6.6 KiB
SQL
\timing on
|
|
|
|
-- Schema extracted using:
|
|
-- sudo -u postgres pg_dump -t 'public.messages' --schema-only datanommer
|
|
-- then modified to handle partitioning.
|
|
|
|
-- Create the messages2 table that will be partitioned and will have the messages
|
|
-- moved to
|
|
|
|
CREATE TABLE messages2 (
|
|
id integer NOT NULL,
|
|
i integer NOT NULL,
|
|
"timestamp" timestamp without time zone NOT NULL,
|
|
certificate text,
|
|
signature text,
|
|
topic text,
|
|
_msg text NOT NULL,
|
|
category text,
|
|
source_name text,
|
|
source_version text,
|
|
msg_id text,
|
|
_headers text,
|
|
username text,
|
|
crypto text,
|
|
CONSTRAINT messages2_pkey PRIMARY KEY (id),
|
|
-- It is weird that we have to use the 'id' in this constraint but it doesn't seem to
|
|
-- work w/o it.
|
|
CONSTRAINT messages2_msg_id_key UNIQUE (msg_id, id)
|
|
) PARTITION BY RANGE(id);
|
|
|
|
CREATE SEQUENCE public.messages2_id_seq
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
ALTER TABLE public.messages2 OWNER TO datanommer;
|
|
ALTER TABLE public.messages2_id_seq OWNER TO datanommer;
|
|
-- ALTER SEQUENCE public.messages2_id_seq OWNED BY public.messages2.id;
|
|
ALTER TABLE ONLY public.messages2 ALTER COLUMN id SET DEFAULT nextval('public.messages2_id_seq'::regclass);
|
|
GRANT SELECT ON TABLE public.messages2 TO datagrepper;
|
|
GRANT SELECT ON SEQUENCE public.messages2_id_seq TO datagrepper;
|
|
|
|
|
|
-- Create the partitions
|
|
|
|
CREATE TABLE messages_010g PARTITION OF messages2 FOR VALUES FROM (MINVALUE) TO (10000000);
|
|
CREATE TABLE messages_020g PARTITION OF messages2 FOR VALUES FROM (10000000) TO (20000000);
|
|
CREATE TABLE messages_030g PARTITION OF messages2 FOR VALUES FROM (20000000) TO (30000000);
|
|
CREATE TABLE messages_040g PARTITION OF messages2 FOR VALUES FROM (30000000) TO (40000000);
|
|
CREATE TABLE messages_050g PARTITION OF messages2 FOR VALUES FROM (40000000) TO (50000000);
|
|
CREATE TABLE messages_060g PARTITION OF messages2 FOR VALUES FROM (50000000) TO (60000000);
|
|
CREATE TABLE messages_070g PARTITION OF messages2 FOR VALUES FROM (60000000) TO (70000000);
|
|
CREATE TABLE messages_080g PARTITION OF messages2 FOR VALUES FROM (70000000) TO (80000000);
|
|
CREATE TABLE messages_090g PARTITION OF messages2 FOR VALUES FROM (80000000) TO (90000000);
|
|
CREATE TABLE messages_100g PARTITION OF messages2 FOR VALUES FROM (90000000) TO (100000000);
|
|
CREATE TABLE messages_110g PARTITION OF messages2 FOR VALUES FROM (100000000) TO (110000000);
|
|
CREATE TABLE messages_120g PARTITION OF messages2 FOR VALUES FROM (110000000) TO (120000000);
|
|
CREATE TABLE messages_130g PARTITION OF messages2 FOR VALUES FROM (120000000) TO (130000000);
|
|
CREATE TABLE messages_140g PARTITION OF messages2 FOR VALUES FROM (130000000) TO (140000000);
|
|
CREATE TABLE messages_150g PARTITION OF messages2 FOR VALUES FROM (140000000) TO (150000000);
|
|
CREATE TABLE messages_160g PARTITION OF messages2 FOR VALUES FROM (150000000) TO (160000000);
|
|
CREATE TABLE messages_170g PARTITION OF messages2 FOR VALUES FROM (160000000) TO (170000000);
|
|
CREATE TABLE messages_180g PARTITION OF messages2 FOR VALUES FROM (170000000) TO (180000000);
|
|
CREATE TABLE messages_190g PARTITION OF messages2 FOR VALUES FROM (180000000) TO (190000000);
|
|
CREATE TABLE messages_200g PARTITION OF messages2 FOR VALUES FROM (190000000) TO (200000000);
|
|
CREATE TABLE messages_210g PARTITION OF messages2 FOR VALUES FROM (200000000) TO (210000000);
|
|
CREATE TABLE messages_220g PARTITION OF messages2 FOR VALUES FROM (210000000) TO (220000000);
|
|
|
|
ALTER TABLE messages_010g OWNER TO datanommer;
|
|
ALTER TABLE messages_020g OWNER TO datanommer;
|
|
ALTER TABLE messages_030g OWNER TO datanommer;
|
|
ALTER TABLE messages_040g OWNER TO datanommer;
|
|
ALTER TABLE messages_050g OWNER TO datanommer;
|
|
ALTER TABLE messages_060g OWNER TO datanommer;
|
|
ALTER TABLE messages_070g OWNER TO datanommer;
|
|
ALTER TABLE messages_080g OWNER TO datanommer;
|
|
ALTER TABLE messages_090g OWNER TO datanommer;
|
|
ALTER TABLE messages_100g OWNER TO datanommer;
|
|
ALTER TABLE messages_110g OWNER TO datanommer;
|
|
ALTER TABLE messages_120g OWNER TO datanommer;
|
|
ALTER TABLE messages_130g OWNER TO datanommer;
|
|
ALTER TABLE messages_140g OWNER TO datanommer;
|
|
ALTER TABLE messages_150g OWNER TO datanommer;
|
|
ALTER TABLE messages_160g OWNER TO datanommer;
|
|
ALTER TABLE messages_170g OWNER TO datanommer;
|
|
ALTER TABLE messages_180g OWNER TO datanommer;
|
|
ALTER TABLE messages_190g OWNER TO datanommer;
|
|
ALTER TABLE messages_200g OWNER TO datanommer;
|
|
ALTER TABLE messages_210g OWNER TO datanommer;
|
|
ALTER TABLE messages_220g OWNER TO datanommer;
|
|
|
|
-- Insert the data
|
|
|
|
INSERT INTO messages2 (i,"timestamp",certificate,signature,topic,_msg,category,source_name,source_version,msg_id,_headers,username,crypto)
|
|
SELECT i,"timestamp",certificate,signature,topic,_msg,category,source_name,source_version,msg_id,_headers,username,crypto FROM messages ORDER BY messages.id;
|
|
|
|
-- Create the indexes ones the data is in
|
|
|
|
CREATE INDEX index_msg2_category ON public.messages2 USING btree (category);
|
|
CREATE INDEX index_msg2_timestamp ON public.messages2 USING btree ("timestamp");
|
|
CREATE INDEX index_msg2_topic ON public.messages2 USING btree (topic);
|
|
CREATE INDEX index_msg_msg_id ON public.messages2 USING btree (msg_id);
|
|
CREATE INDEX index_msg_id ON public.messages2 USING btree (id);
|
|
CREATE INDEX messages2_datanommer_timestamp_category_idx ON public.messages2 USING btree ("timestamp" DESC, category);
|
|
CREATE INDEX messages2_datanommer_timestamp_topic_idx ON public.messages2 USING btree ("timestamp" DESC, topic);
|
|
|
|
|
|
ALTER TABLE user_messages DROP CONSTRAINT user_messages_msg_fkey;
|
|
ALTER TABLE user_messages ADD CO
|
|
|
|
ALTER TABLE package_messages DROP CONSTRAINT package_messages_msg_fkey;
|
|
ALTER TABLE package_messages ADD CONSTRAINT package_messages_msg_fkey FOREIGN KEY (msg) REFERENCES messages2(id);
|
|
|
|
--------------------------------------------------------------------------------
|
|
|
|
DROP TABLE messages_010g , messages_020g, messages_030g, messages_040g, messages_050g,
|
|
messages_060g, messages_070g, messages_080g, messages_090g, messages_100g,
|
|
messages_110g, messages_120g, messages_130g, messages_140g, messages_150g,
|
|
messages_160g, messages_170g, messages_180g, messages_190g, messages_200g,
|
|
messages_210g, messages_220g
|
|
|
|
|
|
-- 1. Dropping the original primary key
|
|
ALTER TABLE messages3 DROP CONSTRAINT messages3_pkey;
|
|
|
|
-- 2. Renaming existing index for another_id (optional)
|
|
ALTER INDEX uniq_1483a5e93414710b RENAME TO users_pkey
|
|
|
|
-- 3. Creating new primary key using existing index for another_id
|
|
ALTER TABLE messages3 ADD PRIMARY KEY USING INDEX id
|
|
|
|
-- 4. Creating index for old id column (optional)
|
|
CREATE UNIQUE INDEX messages3_idx_id ON messages3 (id)
|
|
|
|
-- 5. You can drop the original sequence generator if you won't need it
|
|
DROP SEQUENCE users_id_seq
|