如何为PostgreSQL的表自动添加分区

目录

• 一、配置数据源
• 二、config 脚本
• 三、创建子表脚本
• 四、执行文件main.py

PostgreSQL 引进“分区”表特性，解放了之前采用“表继承”+ “触发器”来实现分区表的繁琐、低效。而添加分区，都是手动执行 SQL。

演示目的：利用 python 来为 PostgreSQL 的表自动添加分区。

python版本： python3+

piP3 install psycopg2

一、配置数据源

database.ini 文件：记录数据库连接参数

[adsas]host=192.168.1.201database=adsasuser=adsaspassword=adsas123port=5432[test]host=192.168.1.202database=adsasuser=adsaspassword=adsas123port=5432

二、config 脚本

config.py 文件：下面的config() 函数读取database.ini文件并返回连接参数。config() 函数位于config.py文件中

#!/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

三、创建子表脚本

pg_add_partition_table.py 文件：其中 create_table函数是创建子表SQL。其中参数

@H_360_53@

#!/usr/bin/python3import 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()

四、执行文件main.py

main.py：主文件；通过执行main生成分区表。

示例：

#!/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');
    

上面示例单独为表tbl_game_android_step_log；创建分区；若多个表；用for语句处理

 # 多表操作  for table in ['tbl_game_android_step_log', 'tbl_game_android_game_log','tbl_game_android_pay_log']:    create_sub_table('test', table);
    

]

演示之前：

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)

演示之后：

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

五、加入定时任务

到此这篇关于如何为PostgreSQL的表自动添加分区的文章就介绍到这了,更多相关PostgreSQL的表添加分区内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！