PostgreSQL introduced the "partitioned" table feature, liberating the cumbersome and inefficient use of "table inheritance" + "trigger" to achieve the cumbersome and inefficiency of partitioned tables. Adding partitions is done manually.
Demo purpose: Use python to automatically add partitions to PostgreSQL tables.
Python version: python3+
pip3 install psycopg2
1. Configure the data source
File: Record database connection parameters
[adsas] host=192.168.1.201 database=adsas user=adsas password=adsas123 port=5432 [test] host=192.168.1.202 database=adsas user=adsas password=adsas123 port=5432
2. config script
File: The following config() function reads the file and returns the connection parameters. The config() function is located in the file
#!/usr/bin/python3
from configparser import ConfigParser
def config(section ,filename=''):
# create a parser
parser = ConfigParser()
# read config file
(filename)
# get section, default to postgresql
db = {}
if parser.has_section(section):
params = (section)
for param in params:
db[param[0]] = param[1]
else:
raise Exception('Section {0} not found in the {1} file'.format(section, filename))
return db
3. Create subtable scripts
pg_add_partition_table.py file: where create_table function creates a subtable SQL. Where parameters
| Parameter name | meaning |
|---|---|
| db | Point to the database |
| table | Main table |
| sub_table | The name of the new subtable |
| start_date | Range boundary start value |
| end_date | Range boundary end value |
#!/usr/bin/python3
import psycopg2
from config import config
# example: create table tbl_game_android_step_log_2021_07 PARTITION OF tbl_game_android_step_log FOR VALUES FROM ('2021-07-01') TO ('2021-08-01');
def create_table(db, table, sub_table, start_date, end_date):
""" create subtable in the PostgreSQL database"""
command = "create table {0} PARTITION OF {1} FOR VALUES FROM ('{2[0]}') TO ('{2[1]}');".format(sub_table, table, (start_date, end_date))
conn = None
try:
# read the connection parameters
params = config(section = db)
# connect to the PostgreSQL server
conn = (**params)
cur = ()
# create table one by one
(command)
# close communication with the PostgreSQL database server
()
# commit the changes
()
except (Exception, ) as error:
print(error)
finally:
if conn is not None:
()
IV. Execute the file
: Main file; generate partition table by executing main.
Example:
#!/usr/bin/python3
import datetime
from datetime import date
from import *
from pg_add_partition_table import create_table
# Get the 1st day of the next month
def get_next_month_first_day(d):
return date( + ( == 12), == 12 or + 1 , 1)
def create_sub_table(db, table):
# Get current date
d1 = ()
# Get next month's date
d2 = d1 + relativedelta(months=+1)
# Get the 1st day of the next month;As the starting value of the partitioned table
start_date = get_next_month_first_day(d1)
# Gets the 1st of the next two months as the end value of the partitioned table
end_date = get_next_month_first_day(d2)
# get sub table name
getmonth = (d2, '%Y_%m')
sub_table = table + '_' + getmonth
create_table(db, table, sub_table, start_date, end_date)
if __name__ == '__main__':
create_sub_table('test', 'tbl_game_android_step_log');
The above example is a table tbl_game_android_step_log separately; create partitions; if multiple tables; use a for statement to process
# Multi-table operation for table in ['tbl_game_android_step_log', 'tbl_game_android_game_log','tbl_game_android_pay_log']:
create_sub_table('test', table);
]
Before the demo:
adsas=> select * from pg_partition_tree('tbl_game_android_step_log');
relid | parentrelid | isleaf | level
-----------------------------------+---------------------------+--------+-------
tbl_game_android_step_log | | f | 0
tbl_game_android_step_log_2020_12 | tbl_game_android_step_log | t | 1
(2 rows)
After the demo:
adsas=> select * from pg_partition_tree('tbl_game_android_step_log');
relid | parentrelid | isleaf | level
-----------------------------------+---------------------------+--------+-------
tbl_game_android_step_log | | f | 0
tbl_game_android_step_log_2020_12 | tbl_game_android_step_log | t | 1
tbl_game_android_step_log_2021_01 | tbl_game_android_step_log | t | 1
Partition key: RANGE (visit_time)
Partitions: tbl_game_android_step_log_2020_12 FOR VALUES FROM ('2020-12-01 00:00:00') TO ('2021-01-01 00:00:00'),
tbl_game_android_step_log_2021_01 FOR VALUES FROM ('2021-01-01 00:00:00') TO ('2021-02-01 00:00:00')
5. Join the scheduled task
This is the end of this article about how to automatically add partitions to PostgreSQL tables. For more information about adding partitions to PostgreSQL tables, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!