如何为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的表添加分区内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • PostgreSQL 创建表分区

    创建表分区步骤如下: 1. 创建主表 CREATE TABLE users ( uid int not null primary key, name varchar(20)); 2. 创建分区表(必须继承上面的主表) CREATE TABLE users_0 ( check (uid >= 0 and uid< 100) ) INHERITS (users); CREATE TABLE users_1 ( check (uid >= 100)) INHERITS (users); 3.

  • PostgreSQL教程(三):表的继承和分区表详解

    一.表的继承: 这个概念对于很多已经熟悉其他数据库编程的开发人员而言会多少有些陌生,然而它的实现方式和设计原理却是简单易懂,现在就让我们从一个简单的例子开始吧.     1. 第一个继承表:   复制代码 代码如下: CREATE TABLE cities (   --父表         name        text,         population float,         altitude     int     );     CREATE TABLE capitals (

  • PostgreSQL之分区表(partitioning)

    PostgreSQL有一项非常有用的功能,分区表,或者partitioning.当某个TABLE的记录非常的多,千万甚至更多的时候,我们其实需要将他分割成子表.一个庞大的TABLE,就像水果仓库杂乱无章地堆放着无数的苹果桃子和桔子,查找不方便,性能降低,比较合理的做法是将仓库分成三个子区域,分表放苹果桃子和桔子.一张大表就变成了三个小表的集合. 通过合理的设计,可以将选择一定的规则,将大表切分多个不重不漏的子表,这就是传说中的partitioning.比如,我们可以按时间切分,每天一张子表,比如

  • PostgreSQL分区表(partitioning)应用实例详解

    前言 项目中有需求要垂直分表,即按照时间区间将数据拆分到n个表中,PostgreSQL提供了分区表的功能.分区表实际上是把逻辑上的一个大表分割成物理上的几小块,提供了很多好处,比如: 1.查询性能大幅提升 2.删除历史数据更快 3.可将不常用的历史数据使用表空间技术转移到低成本的存储介质上 那么什么时候该使用分区表呢?官方给出的指导意见是:当表的大小超过了数据库服务器的物理内存大小则应当使用分区表,接下来结合一个例子具体记录一下创建分区表的详细过程. 创建分区表 首先看一下需求,现在有一张日志表

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

    PostgreSQL 引进"分区"表特性,解放了之前采用"表继承"+ "触发器"来实现分区表的繁琐.低效.而添加分区,都是手动执行 SQL. 演示目的:利用 python 来为 PostgreSQL 的表自动添加分区. python版本: python3+ pip3 install psycopg2 一.配置数据源 database.ini 文件:记录数据库连接参数 [adsas] host=192.168.1.201 database=adsa

  • 利用python为PostgreSQL的表自动添加分区

    PostgreSQL引进"分区"表特性,解放了之前采用"表继承"+"触发器"来实现分区表的繁琐.低效.而添加分区,都是手动执行SQL. 演示目的:利用python来为PostgreSQL的表自动添加分区. python版本:python3+ pip3 install psycopg2 一.配置数据源 database.ini 文件:记录数据库连接参数 [adsas] host=192.168.1.201 database=adsas user=a

  • Java如何为 PPT 中的图形添加阴影效果

    在PowerPoint文档中,给图片添加阴影效果能增强图片的逼真度,使其贴近现实效果,提升文档的美观度. 本文将展示如何使用Free Spire.Presentation for Java为PPT中的图形添加阴影效果.除了文中展示的预设阴影效果,还可以添加内部阴影(InnerShadowEffect).外部阴影(OuterShadowEffect).柔化边缘阴影(SoftEdgeEffect)等. JAR包导入 方法一:下载Free Spire.Presentation for Java包并解压

  • PostgreSQL LIST、RANGE 表分区的实现方案

    简 介 PG分区:就是把逻辑上的一个大表分割成物理上的几块. 分区的优点 1. 某些类型的查询性能得到提升 2. 更新的性能也可以得到提升,因为某块的索引要比在整个数据集上的索引要小. 3. 批量删除可以通过简单的删除某个分区来实现. 4. 可以将很少用的数据移动到便宜的.转速慢的存储介质上. 分区实现原理 10.x版本之前PG表分区的实现原理:PG中是通过表的继承来实现的,建立一个主表,里面是空的,然后每个分区去继承它.无论何时,该主表里面都必须是空的 官网建议:只有当表本身大小超过了机器物理

  • Mybatis通过数据库表自动生成实体类和xml映射文件

    环境:maven+idea. 1. 需要的jar包 基本的spring和mybatis依赖包就不说了,在pom文件的build->plugins节点下需要添加(两个依赖包也可以直接添加到pom的依赖里面去,这里是为了直接通过maven的插件来生成.如果不是使用maven,自行百度下): <plugin> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-mave

  • VB的32位程序在64位系统中出现文件和注册表自动转向的解决方法

    通常用VB 写的程序在32位系统上可以正常的运行,但到了64位系统中虽然可以运行但是无法修改OEM信息.经过查找原因可以发现,当我们在访问文件夹"SYSTEM32"的时候系统会自动转向到文件夹"SYSWOW64"下,当我们在访问某些注册表键值的时候,和文件转向类似,系统也会自动地把程序的访问转向到Wow6432Node下面. 先来谈谈文件系统的转向,查找了一些资料,并没有VB相关的资料,但是可以查到2个相关函数:Wow64DisableWow64FsRedirect

  • 如何为asp.net网站项目添加子项目

    之前也曾经想过,这些功能根据不同处于项目中的不同文件夹内,比如会员的会在member,后台管理的会在admin文件夹,新闻的会在news文件夹.这样的话如果vs支持按不同文件夹生成不同dll,那我每次修改不同功能的不同文件只要上传对应的dll就可以了,不再需要上传整个website的dll. 不过想法是美好的,事实是残酷的.vs并不支持这样的功能.所以在功能直接交互和文件共享并不多的功能上,我采取了重新建立一个projects的方法,比如把后台管理剥离出来,新建一个admin的website,这

  • Mysqlslap MySQL压力测试工具 简单教程

    MySQL从5.1.4版开始带有一个压力测试工具mysqlslap,通过模拟多个并发客户端访问mysql来执行测试,使用起来非常的简单.通过mysqlslap –help可以获得可用的选项,这里列一些主要的参数,更详细的说明参考官方手册. 复制代码 代码如下: –auto-generate-sql, -a 自动生成测试表和数据 –auto-generate-sql-load-type=type 测试语句的类型.取值包括:read,key,write,update和mixed(默认). –numb

  • SqlServer2005 数据库同步配置图文详解

    <1>软件准备条件 机器A端:SqlServer2005 Management Studio + WinServer 2003 Enterprise (作为发布服务器) 机器B端:Sqlserver2005 Management Studio Express + WinXP(作为订阅服务器) 一:[准备条件] <1>软件准备条件 机器A端:SqlServer2005 Management Studio + WinServer 2003 Enterprise (作为发布服务器) 机器

  • 主键与聚集索引

    主键(PRIMARY KEY ) 来自MSDN的描述: 表通常具有包含唯一标识表中每一行的值的一列或一组列.这样的一列或多列称为表的主键 (PK),用于强制表的实体完整性.在创建或修改表时,您可以通过定义 PRIMARY KEY 约束来创建主键. 一个表只能有一个 PRIMARY KEY 约束,并且 PRIMARY KEY 约束中的列不能接受空值.由于 PRIMARY KEY 约束可保证数据的唯一性,因此经常对标识列定义这种约束. 如果为表指定了 PRIMARY KEY 约束,则 SQL Ser

随机推荐