Author: Letu
Pseudonym: lottu
Personal profile: at present, I am engaged in the design and development of database architecture in a game company, and have led the company to go to “O” related workhttps://www.cnblogs.com/lottu
PostgreSQL introduces the feature of “partition” table, which liberates the tedious and inefficient implementation of partition table by using “table inheritance” + “trigger”. When adding partitions, SQL is executed manually.
Purpose of demonstration: use Python to automatically add partitions to PostgreSQL tables.
Python version: Python 3+
pip3 install psycopg2
1、 Configure data sources
database.ini Files: recording 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
config.py File: the following config() function reads database.ini File and returns the connection parameters. The config() function is located in the config.py In the file
#!/usr/bin/python3
from configparser import ConfigParser
def config(section ,filename='database.ini'):
# create a parser
parser = ConfigParser()
# read config file
parser.read(filename)
# get section, default to postgresql
db = {}
if parser.has_section(section):
params = parser.items(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 script
pg_ add_ partition_ table.py File: where create_ The table function is to create a sub table SQL. Where parameters
Parameter name | meaning |
---|---|
db | Point to database |
table | Main table |
sub_table | The name of the child table you are about to create |
start_date | Start value of range boundary |
end_date | End value of range boundary |
#!/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 = psycopg2.connect(**params)
cur = conn.cursor()
# create table one by one
cur.execute(command)
# close communication with the PostgreSQL database server
cur.close()
# commit the changes
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
4、 Executive document main.py
main.py : main file; generate partition table by executing main.
Example:
#!/usr/bin/python3
import datetime
from datetime import date
from dateutil.relativedelta 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(d.year + (d.month == 12), d.month == 12 or d.month + 1 , 1)
def create_sub_table(db, table):
# Get current date
d1 = date.today()
# 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 = datetime.datetime.strftime(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 separate TABLE tbl_ game_ android_ step_ Log; create partition; if more than one table; use 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 presentation:
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 presentation:
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 timing task
N/A
For more wonderful content, please pay attention to the following platforms and websites:
Official account of China PostgreSQL branch (technical articles and technical activities):
PostgreSQL branch of Open Source Software Alliance
China PostgreSQL technical Q & a community:
www.pgfans.cn
Official website of China PostgreSQL branch:
www.postgresqlchina.com