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

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

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

python版本: python3+

pip3 install psycopg2

一、配置数据源

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

[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

二、config 脚本

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

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

三、创建子表脚本

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

参数名 含义
db 指向数据库
table 主表
sub_table 正要新建的子表名
start_date 范围分界开始值
end_date 范围分界结束值
#!/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()

四、执行文件main.py

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

示例:

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

上面示例单独为表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   |   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')

五、加入定时任务

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

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐