利用Python自动为PostgreSQL表添加分区

江屿

发布时间：2026-10-12 | 2人浏览过

原创

本文介绍如何使用Python脚本自动为PostgreSQL数据库表添加范围分区。通过配置数据库连接参数，利用psycopg2库执行SQL语句，动态生成子表并绑定到主表。示例展示了按月自动创建分区表的完整流程，包括连接配置、SQL生成、执行及验证步骤，适用于需要自动化维护分区表的场景。

PostgreSQL分区表与Python自动化方案

PostgreSQL 10及以上版本原生支持分区表特性，这取代了早期通过表继承结合触发器实现的复杂方案。对于需要处理大量历史数据的场景，自动按月或按天创建分区表是常见的运维需求。本文将演示如何利用Python 3及psycopg2库，编写脚本自动为PostgreSQL主表添加新的子分区表。

环境准备与依赖安装

在开始之前，请确保Python环境为3.x版本，并安装PostgreSQL的Python驱动。通过以下命令安装必要的库：

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.py来读取该配置文件并返回连接字典。该模块提供了一个config()函数，接收

参数和配置文件路径，解析后返回包含主机、数据库名、用户、密码和端口信息的字典。

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

核心功能：创建子分区表

PostgreSQL的范围分区语法要求指定子表名、主表名以及分区的起止值。我们编写pg_add_partition_table.py，其中包含create_table()函数。该函数接收数据库标识、主表名、子表名、开始日期和结束日期作为参数，拼接成标准的CREATE TABLE ... PARTITION OF ... SQL语句并执行。

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

注意：上述代码中SQL语句通过字符串格式化生成。若start_date或end_date包含用户可控的外部输入，直接拼接存在SQL注入风险。在生产环境中，建议使用参数化查询或严格校验日期格式。

自动化逻辑与执行入口

为了实现自动添加未来月份的分区，我们需要计算日期范围。在main.py中，我们利用Python标准库datetime和dateutil来计算下个月的第一天作为当前分区的开始，以及下下个月的第一天作为结束。子表名通常遵循主表名_年月的命名规范。

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

多表处理

如果需要对多个主表同时执行分区添加操作，可以在main.py中使用循环遍历表名列表：

# 多表操作

  for table in ['tbl_game_android_step_log', 'tbl_game_android_game_log','tbl_game_android_pay_log']:
    create_sub_table('test', table);

验证分区结果

执行脚本后，可以通过PostgreSQL的系统函数pg_partition_tree()来验证分区是否创建成功。该函数返回分区树的详细信息，包括子表ID、父表ID、是否为叶子节点以及层级。

创建分区前，查询结果可能如下：

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)

执行Python脚本添加新分区后，再次查询，可以看到新增的子表（如tbl_game_android_step_log_2021_01）已加入分区树：

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

以上就是利用Python为PostgreSQL表自动添加分区的详细内容，更多关于PostgreSQL分区管理、Python数据库操作及自动化运维的资料请关注本站其它相关文章！