\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