Skip to content

Instantly share code, notes, and snippets.

@marcocitus marcocitus/pg_partman.sql
Last active Mar 16, 2019

Embed
What would you like to do?
Setting up pg_partman on cloud
-- as superuser:
CREATE EXTENSION pg_cron;
GRANT USAGE ON SCHEMA cron TO citus;
CREATE SCHEMA partman;
GRANT USAGE ON SCHEMA partman TO citus;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
GRANT ALL ON TABLE partman.part_config TO citus;
GRANT ALL ON TABLE partman.part_config_sub TO citus;
GRANT ALL ON TABLE partman.custom_time_partitions TO citus;
-- as citus user:
-- enable periodic maintenance (disable analyze!)
SELECT cron.schedule('@hourly', $$SELECT partman.run_maintenance(p_analyze := false)$$);
CREATE SCHEMA github;
CREATE TABLE github.events (
event_id bigint,
event_type text,
event_public boolean,
repo_id bigint,
payload jsonb,
repo jsonb, actor jsonb,
org jsonb,
created_at timestamp
) PARTITION BY RANGE (created_at);
SELECT create_distributed_table('github.events', 'repo_id');
CREATE TABLE github.events_template (LIKE github.events);
CREATE INDEX ON github.events_template ((repo->>'name'));
SELECT partman.create_parent('github.events', 'created_at', 'native', 'daily', p_start_partition := '2018-01-01', p_template_table := 'github.events_template');
UPDATE partman.part_config SET infinite_time_partitions = true, retention_keep_table = false, retention = '1 month';
-- after dropping all partitioned tables, make sure they are also removed from part_config, e.g.
TRUNCATE partman.part_config CASCADE;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.