PG新建分区

检查现有分区情况

1、通过python脚本获取当前分区情况

# run: python check\_child\_table.py

# python2即可运行，日志log.log保留到运行目录

import os

def run\_cmd(tab\_no):
\# 数据库连接信息
host = '127.0.0.1'
port = 1921
username = 'postgres'
db\_name = 'point2'

    # relname为父表表名，point_trans_shard_XX  xx为十六进制
    # con.contype = 'c' 限制约束为 检查约束
    # con.consrc like '((processedtime%' 。processedtime子表分区范围
    # d.relname, c.relname, con.consrc, au.rolname分别为：父表名，子表名，子表分区范围，owner
    demo_sql = """
    select
        d.relname, c.relname, con.consrc, au.rolname
    from
        pg_class c
        join pg_inherits i on i.inhrelid = c. oid
        join pg_class d on d.oid = i.inhparent
        join pg_constraint con on i.inhrelid = con.conrelid and con.contype = 'c'
        join pg_authid au on au.oid = c.relowner
        join pg_namespace ns on ns.oid = c.relnamespace and ns.nspname = 'public'
    where
        d.relname = 'point_trans_shard_{}' and con.consrc like '((processedtime%';
    """.format(tab_no)

    exit_status = os.system('psql -h %s -U %s -p %s -d %s -Atc "%s" >> ./log.log' % (host, username, port, db_name, demo_sql))

    exit_status = os.WEXITSTATUS(exit_status)
    print("result: %s" % (exit_status))

if **name** == '**main**':
\# 十进制转换为十六进制
for i in range(256):
if i < 16:
tab\_no = '0' + hex(i)\[2:]
print(tab\_no)
else:
tab\_no =  str(hex(i)\[2:])
print(tab\_no)
run\_cmd(tab\_no)

2、脚本输出结果如图所示 lue

3、将日志结果复制excel中，按照|进行分列即可

建分区脚本

该脚本可以自动创建一年月分区 sh create\_partition\_by\_year.sh \[year]不指定年份默认创建下一年的月分区 创建point\_trans\_shard\_xx和point\_trans\_source\_shard\_xx表的分区。若不同，注意修改 赋权：将public模式下所有表的all权限赋权给kgoldpointapp用户

\#!/bin/bash

\##################################################################

# 运行：sh create\_partition\_by\_year.sh \[year]

\##################################################################

export LANG=en\_US.utf8
export PGHOME=/usr/pgsql-9.6/
export LD\_LIBRARY\_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD\_LIBRARY\_PATH
export PATH=$PGHOME/bin:$PATH:.
export PGHOST=127.0.0.1
export PGPORT=1921

# 默认是生成下一年的分区表

YEAR=`date -d "now 1 years" "+%Y"`

if \[ $1>0 ]; then
    YEAR="$1"
fi

echo "==============================> start to create partitions on \$YEAR!"

# 12 个月

for((j=1;j<13;j=\$j+1))
do

    k=$[j+1]
    ym=$YEAR`printf "%02d" $j`
    echo $ym

    # 日期转化成秒
    time_start=`date -d"${ym}01" +%s`
    time_end=`date -d"${ym}01 1 months" +%s`

# 256 个子分区

for((i=0;i<256;i=$i+1))
  do
    part=`printf "%02x" $i\`

    #  create partition start .....
    ddl_sql="create table point_trans_shard_${part}_part_${ym}(like point_trans_shard_${part} including all) inherits(point_trans_shard_${part});"
    chk_sql="Alter table point_trans_shard_${part}_part_${ym} ADD CONSTRAINT chk_point_trans_shard_${ym} CHECK (processedtime >= '${time_start}000'::bigint AND processedtime < '${time_end}000'::bigint );"

    ddl_sql2="create table point_trans_source_shard_${part}_part_${ym}(like point_trans_source_shard_${part} including all) inherits(point_trans_source_shard_${part});"
    chk_sql2="Alter table point_trans_source_shard_${part}_part_${ym} ADD CONSTRAINT chk_point_trans_source_shard_${ym} CHECK (processedtime >= '${time_start}000'::bigint AND processedtime < '${time_end}000'::bigint );"


    psql -d point2 -c "$ddl_sql"
    psql -d point2 -c "$chk_sql"
    psql -d point2 -c "$ddl_sql2"
    psql -d point2 -c "$chk_sql2"
    # exchange_order end .....

done
done
echo "==============================> partitions on \$YEAR created!\n\n"

# 赋权

echo "==============================> grand start "
psql -d point2 -c "grant all on all tables in schema public to kgoldpointapp ;"

echo "==============================> done"