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')
五、加入定时任务
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件!
如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
白云城资源网 Copyright www.dyhadc.com
暂无“如何为PostgreSQL的表自动添加分区”评论...
更新日志
2024年06月26日
2024年06月26日
- 瑞鸣音乐《中国音乐地图之听见河南豫剧经典唱段》2020[WAV分轨]
- 【伊比萨沙发】VA-2024-IbizaSpring2024(LoungeCocktailsfromtheHeart)[FLAC]
- 群星《世界顶级汽车音响试音王》4K金碟16CD[WAV+CUE]
- 群星-音效环绕天碟《极致环绕人声天碟天品》7CD[WAV]
- 泰拉克SACD古典试音碟7SACD-ISO
- 群星《红歌宝典》20CD[WAV/分轨]
- 《101系列:好歌101首 细听年代精选》[WAV+CUE][3.6GB]
- 《101系列 6CD 国语经典101首歌曲 张学友 王菲代表作》[APE+CUE][2.8GB]
- 《101系列:国语经典101 VOL2 6CD 精选最经典101首》[WAV+CUE][2.3GB]
- 群星《十年网络红歌》15CD[WAV分轨]
- 宝丽金-环球10周年至50周年经典金曲15CD[低速原抓WAV+CUE]
- FIM唱片《十大男伶》[低速原抓WAV+CUE]
- 《101系列:情歌101 6CD》[WAV+CUE][3.1GB]
- 庄心妍《降调版合集》[320K/MP3][52.96MB]
- 庄心妍《降调版合集》[FLAC/分轨][118.78MB]