Setting up pg_partman on cloud

pg_partman.sql

-- 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;