Postgres Partitioned Table Monthly Script DDL Generator

README.md

Postgres Partitioned Table Monthly Script DDL Generator

USING PYTHON3

1. this script was created based on this
2. writing a DDL for every single table is time consuming so i created this script, this script's purpose is to create a monthly postgres partitioned table based on the date
3. this will create a partitioned table with name like child_table_y2020m01

Parameters

1. filename is filename
2. months_back is how many months behind you want for the partitioned table
3. months_front is how many months ahead you want for the partitioned table
4. parent_table is the name for the parent table
5. child_table is the name for the child table (partitioned one)
6. index_prefix is when you create index they cant accept schema in the index name, so you need to remove it
7. col_index_dict is the name of the columns you want to index and what kind of index (btree, hash, etc.) written in Python Dictionary Format

To run this script, simply just python postgres_partition_ddl_generator.py and it will create the filename you specify in the same directory.

postgres_partition_ddl_generator.py

import datetime
import os
import sys
from dateutil.relativedelta import relativedelta
from datetime import date, timedelta


def generate_partition(filename, months_back, months_front, parent_table, child_table, index_prefix, col_index_dict):
    orig_stdout = sys.stdout
    f = open(filename, 'w')
    sys.stdout = f

    for i in range(months_back, 0, -1):
        back = datetime.datetime.now() - relativedelta(months=i)
        back_plus = datetime.datetime.now() - relativedelta(months=i + 1)
        back_month = str(back.strftime("%m"))
        back_month_plus = str(back_plus.strftime("%m"))
        back_year = str(back.strftime("%Y"))
        back_year_plus = str(back_plus.strftime("%Y"))
        print("CREATE TABLE " + child_table + "_y" + back_year_plus + "m" + back_month_plus +
              " PARTITION OF " + parent_table + "\nFOR VALUES FROM ('" + back_year_plus + "-" + back_month_plus + "-01') TO ('" + back_year + "-" + back_month + "-01');\n")
        for column, index in col_index_dict.items():
            print("create index " + index_prefix + "_y" + back_year_plus + "m" + back_month_plus + "_" + column + "_idx\n" +
                  "on " + child_table + "_y" + back_year_plus + "m" + back_month_plus + "\n"
                  "using " + index + "(" + column + ");\n")

    for i in range(0, months_front):
        front = datetime.datetime.now() + relativedelta(months=i)
        front_plus = datetime.datetime.now() + relativedelta(months=i + 1)
        front_month = str(front.strftime("%m"))
        front_month_plus = str(front_plus.strftime("%m"))
        front_year = str(front.strftime("%Y"))
        front_year_plus = str(front_plus.strftime("%Y"))
        print("CREATE TABLE " + child_table + "_y" + front_year + "m" + front_month +
              " PARTITION OF " + parent_table + "\nFOR VALUES FROM ('" + front_year + "-" + front_month + "-01') TO ('" + front_year_plus + "-" + front_month_plus + "-01');\n")
        for column, index in col_index_dict.items():
            print("create index " + index_prefix + "_y" + front_year + "m" + front_month + "_" + column + "_idx\n" +
                  "on " + child_table + "_y" + front_year + "m" + front_month + "\n"
                  "using " + index + "(" + column + ");\n")

    sys.stdout = orig_stdout
    f.close()


if __name__ == '__main__':
    # this script was created based on this : https://www.postgresql.org/docs/10/ddl-partitioning.html
    # this script's purpose is to create a monthly postgres partitioned table based on the date
    # this will create a partitioned table with name like [table_name]_y2020m01
    # writing a DDL for every single table is time consuming so i created this script
    # filename is filename
    # months_back is how many months behind you want for the partitioned table
    # months_front is how many months ahead you want for the partitioned table
    # parent_table is the name for the parent table
    # child_table is the name for the child table (partitioned one)
    # index_prefix is when you create index they cant accept schema in the index name, so you need to remove it
    # col_index_dict is the name of the columns you want to index and what kind of index (btree, hash, etc.)

    col_index_dict = {"table_id": "btree",
                      "application_id": "btree"}
    generate_partition('test.sql', 0, 10, 'public.parent_table', 'public.child_table', 'child_table', col_index_dict)