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