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

pip3 install psycopg2

[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

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

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

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

# 多表操作

  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 0' ) TO ( '2021-01-01 0' ),

       tbl_game_android_step_log_2021_01 FOR VALUES FROM ( '2021-01-01 0' ) TO ( '2021-02-01 0' )