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