Author: Le Tu

Pen name: lottu

Personal profile: Currently engaged in database architecture design and development in a game company, once led the company to

“O” related work; Personal blog: www.cnblogs.com/lottu

PostgreSQL introduces the partition table feature, which eliminates the cumbersome and inefficient partitioning of tables using table inheritance and triggers. Adding partitions, however, is manual SQL execution.

Purpose: Use Python to automatically partition PostgreSQL tables.

Python version: python3+

pip3 install psycopg2

1. Configure the data source

Database. ini file: records 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
Copy the code

2. Config script

Config.py file: The following config() function reads the database.ini file and returns connection parameters. The config() function is in the config.py file

#! /usr/bin/python3from 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

Create subtable scripts

Pg_add_partition_table. py: The create_table function creates a sub-table SQL. The parameter

Parameter names

meaning

db

Point to the database

table

The main table

sub_table

Name of the child table being created

start_date

Start value of range demarcation

end_date

End value of range demarcation

#! /usr/bin/python3 import psycopg2from 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. Execute file main.py

Main. py: main file; Generate the partitioned table by executing main.

Example:

#! /usr/bin/python3import datetimefrom datetime import datefrom dateutil.relativedelta import *from pg_add_partition_table import create_table # Get the 1st day of the next monthdef 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 a partition. If multiple tables; Handle with a for statement

Multiple table operations

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

Add scheduled tasks

N/A

For more exciting content, please pay attention to the following platforms and websites:

PostgreSQL China Branch official account (technical articles, technical activities) :

Open Source Software Alliance ****PostgreSQL chapter

PostgreSQL China Technical Q&A Community:

www.pgfans.cn

Official website of PostgreSQL China Chapter:

www.postgresqlchina.com