Partitioning in PostgreSQL 10

Question

Currently we are using pg_partman for partitioning our tables in PostgreSQL 9.4. We are planning on upgrading to PostgreSQL 10. I read that PostgreSQL 10 has better support for partitioning.

While I read the documentation on it, is there a standard process to automate creation of the partitions? Do we still need to use pg_partman to automate creation of the partitions or is there another way?

I am not a PostgreSQL DBA but I need to maintain the database so I am looking for any standard practices for PostgreSQL partitioning.

• Patrick Mevzek: Did you start by reading postgresql.org/docs/10/static/ddl-partitioning.html ? It is quite extensive...
• Anita: Yes, I did. In section 5.10.2.1 where they provided an example, they talk about manually creating the partitions but in a production environment, if we need a table partitioned monthly, this would need to be automated. Is there a standard way to do this? I am sorry if this is basic question.
• Patrick Mevzek: See wiki.postgresql.org/wiki/Table_partitioning it states: " No support for automatic creation of partitions (e.g. for values not covered) " and at bottom you have a link to pg_partman, which is still a useful tool there, it has been updated for PostgreSQL 10.
• Patrick Mevzek: "if we need a table partitioned monthly, this would need to be automated." if you do inserts through a procedure for example, you could update it to create the partitions needed on the fly.
• Anita: @patrick-mevzek Thanks. I know I can do it through the insert but I wonder if that's the recommended practice. May be I still need to use pg_partman for that.

Answer 1

I just wanted to share how we dealt with this - in case this helps others. Based on suggestions from some DBAs in Postgres User Groups, I created a cron job that executes twice a month. We use daily partitions. So the cron job executes a bash script. The bash script connects to the database using psql and calls a function which takes in the table that needs to be partitioned and the date interval to create the partitions for. Here is the function I used to create partitions. This has worked without any issues for us.

CREATE OR REPLACE FUNCTION public.create_partition_tables(p_parent_table text, p_partition_start_date date, p_partition_end_date date)
RETURNS void AS 
 $body$ 
 DECLARE
 partition_date date;
 v_tablename text;
 BEGIN
 for partition_date in
 select date(generate_series(p_partition_start_date, p_partition_end_date, interval '1 day'))
 loop
 SELECT tablename INTO v_tablename FROM pg_catalog.pg_tables WHERE schemaname='public' and tablename = p_parent_table || '_p' ||to_char(partition_date, 'yyyy_mm_dd');
 IF v_tablename IS NOT NULL THEN
 CONTINUE;
 END IF;
 execute format('create table public.' || p_parent_table || '_p' ||to_char(partition_date, 'yyyy_mm_dd')|| ' partition of public.' 
 || p_parent_table || ' for values from (%L) to (%L)', partition_date, date(partition_date + interval '1 day') ); 
 end loop;
 END; 
 $body$ 
LANGUAGE 'plpgsql' VOLATILE STRICT