良好的数据库维护不仅包括执行标准添加、更新和删除记录,等等,但定期编辑表模式。操作,比如添加、编辑和删除表列也是今天的生命周期的一部分现实不断添加新功能。
安静,少要求,可以侥幸通过执行影响最小的模式更新操作在低负荷时间。然而,正如所有的数据库系统已经变得更加关键业务的底线,维护窗口,不得已,更小更合理的时间安排。
这个问题是问:一个人怎么能更新一个tb表接近零停机时间?
寻找深入Postgres的组合,我们不是看,而是最近的和先进的功能,我们利用一个非常古老的功能的Postgres的一部分,因为当它第一次被作为一个发布开源项目,即其对象关系能力的实现继承。
用例
在进入解决方案的细节让我们定义正是我们正在努力解决的。
严格地说;有两种用例使用继承时想到的' ETL数据迁移机制:
- 删除表oid,例如当搬到Postgres 12和更大的版本。
- 执行DML / DDL操作包括:
- 更新数据
- 添加或删除表列
生活开始变得复杂处理等问题时更新数据类型。但我们会谈论这个的博客。
关于继承
与面向对象编程语言,孩子从父母继承属性,在Postgres的父母从孩子有能力继承(如果这是真的在现实生活中;-))。因此从一个孩子一个表列有潜力成为可用的父母关系。
考虑下面的代码片段:两个父母和三个孩子创建和填充记录:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
开始;
下降
表
如果
存在
的父亲,妈妈。
级联;
创建
表
父亲(c1
int,c2
int,c3
int);
创建
表
母亲(c1
int,c2
int,c4
int);
——注意到尽管不是宣布从母亲列“c4”添加到这些表
创建
表
儿子(c1
int,c2
int,c3
int)
继承了
(父亲、母亲);
创建
表
女儿(c2
int,c4
int,c5
int)
继承了
(父亲、母亲);
——这只表继承那些列在“父亲”
创建
表
表兄(c1
int,c2
int,c3
int,c6
int)
继承了
(父亲);
提交;
|
|
1
2
3
4
5
6
7
|
开始;
插入
成
儿子
值(1,1,1,1);
插入
成
女儿
值(2、2、2、2、2);
插入
成
表兄
值(3,3,3,3);
插入
成
父亲
值(10、10、10);
插入
成
妈妈。
值(11、11、11);
提交;
|
列中声明的父母必须因此存在于孩子,即它们合并。但要注意那些列的孩子不一定是传播到父母:
|
1
2
3
4
5
6
7
|
表“public.father”
专栏| |类型排序| Nullable |违约
- - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - -
c1整数| | | |
c2整数| | | |
c3整数| | | |
子表的数量:3(用d +列表。)
|
|
1
2
3
4
5
6
7
|
表“public.mother”
专栏| |类型排序| Nullable |违约
- - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - -
c1整数| | | |
c2整数| | | |
c4整数| | | |
子表的数量:2(用d +列表。)
|
|
1
2
3
4
5
6
7
8
9
|
表“public.son”
专栏| |类型排序| Nullable |违约
- - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - -
c1整数| | | |
c2整数| | | |
c3整数| | | |
c4整数| | | |
继承:爸爸,
妈妈。
|
|
1
2
3
4
5
6
7
8
9
10
|
表“public.daughter”
专栏| |类型排序| Nullable |违约
- - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - -
c1整数| | | |
c2整数| | | |
c3整数| | | |
c4整数| | | |
c5整数| | | |
继承:爸爸,
妈妈。
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20.
21
|
表“public.cousin”
专栏| |类型排序| Nullable |违约
- - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - -
c1整数| | | |
c2整数| | | |
c3整数| | | |
c6整数| | | |
继承:父亲
< br / >
< p >即使记录填充在父母的孩子可以看到相反的是不正确的,当记录填充到父母和不被孩子:< / p >
< br / >
< pre类= "朗:pgsql解码:真正的“> db02 = # select * from父亲;
c1 | c2 | c3
- - - - - + - - - + - - - - -
10 | | 10
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
|
|
1
2
3
4
5
6
|
db02 = #
选择
*
从
母亲;
c1|c2|c4
- - - - - + - - - + - - - - -
11|11|11
1|1|1
2|2|2
|
|
1
2
3
4
|
db02 = #
选择
*
从
的儿子,
c1|c2|c3|c4
- - - - - + - - - + - - - + - - - - -
1|1|1|1
|
|
1
2
3
4
|
db02 = #
选择
*
从
女儿;
c1|c2|c3|c4|c5
- - - - - + - - - + - - - + - - - + - - - - -
2|2|2|2|2
|
|
1
2
3
4
|
db02 = #
选择
*
从
表弟;
c1|c2|c3|c6
- - - - - + - - - + - - - + - - - - -
3|3|3|3
|
发展ETL /迁移模型
在生产条件下进行数据迁移应该考虑这些四(4)不同的查询操作:
- 选择目标和源表在同一时间。
- 更新和/或删除记录的目标和源表。
- 将新记录插入到目标表。
- 将数据从源移动到目标表。
为了讨论我们将演示使用源和目标从一个表分别继承父:
|
1
2
3
|
创建
表
父母(c1
int
主
关键,c2
int,c3
int);
创建
表
源(就像
父
包括
所有)
继承了
(父);
创建
表
目标(就像
父
包括
所有)
继承了
(父);
|
查询目标和源表
继承使SELECT查询一个简单的操作。这个查询检查所有表的查询记录(s):
|
1
|
解释
选择
*
从
父母;
|
|
1
2
3
4
5
6
|
查询计划
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
追加(成本= 0.00 . . 81.21行宽度= 4081 = 12)
- > Seq扫描父parent_1(成本= 0.00 . . 0.00行= 1宽度= 12)
- > Seq扫描源parent_2(成本= 0.00 . . 30.40行宽度= 2040 = 12)
- > Seq扫描目标parent_3(成本= 0.00 . . 30.40行宽度= 2040 = 12)
|
更新和/或删除记录
类似于SELECT查询,不需要担心编辑现有应用程序的DML操作(s)在执行更新和删除。再次注意到源和目标表查询以及家长:
|
1
|
解释
更新
父
集
c2=0
在哪里
c1=1;
|
|
1
2
3
4
5
6
7
8
9
10
11
12
|
查询计划
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
更新父(成本= 0.00 . . 16.34行宽度= 3 = 18)
更新父
更新源
更新目标
- > Seq扫描在父(成本= 0.00 = 1 . . 0.00行宽度= 18)
过滤器(c1 = 1):
- >索引扫描使用source_pkey源(成本= 0.15 = 1 . . 8.17行宽度= 18)
气孔导度指数(c1 = 1):
- >索引扫描使用target_pkey目标(成本= 0.15 = 1 . . 8.17行宽度= 18)
气孔导度指数(c1 = 1):
|
将新记录插入到目标表
关于插入的事情要记住的是没有重定向机制所有记录被插入到父母。
因为所有人都已经知道触发器,我认为会很有趣使用重写规则:
|
1
2
3
4
5
|
创建
规则
child_insert
作为
在
插入
来
父
做
而不是
插入
成
目标
值
(新. *);
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
表“public.parent”
专栏| |类型排序| Nullable | | | |存储统计目标描述违约
- - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
c1整数| | | not null | |平原| |
平原c2整数| | | | | | |
平原c3整数| | | | | | |
索引数量:
“parent_pkey”主键,btree (c1)
规则:
child_insert作为
父母代替插入插入目标(c1, c2, c3)
(新值。c1,新的。c2,new.c3)
子表:源,
目标
|
这是我们的验证;注意插入重定向父来目标:
|
1
|
解释
插入
成
父(c1, c2, c3)
值
(1,1,1);
|
|
1
2
3
4
|
查询计划
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
插入目标(成本= 0.00 = 1 . . 0.01行宽度= 12)
- >结果(成本= 0.00 = 1 . . 0.01行宽度= 12)
|
源表和目标表之间移动记录
是时候介绍最后一个需要执行实际的数据迁移机制。从本质上讲,数据批量移动,否则,如果你买得起的停机时间记录在一个非常大的交易,这盛大表演有点多余。
在现实世界中我们需要预见到多个进程同时试图独占锁。如果一个或多个记录被另一个操作下面的示例演示了如何可以简单地跳过在他们:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
- - -
(例:插入单个记录在表“源”
- - -
插入
成
源(c1, c2, c3)
值
(2,2,2);
——移动1000条记录
——从表“源”到“目标”
- - -
与
一个
作为
(选择
*
从
源
为
更新
跳过锁着的
限制
1000年),
b
作为
(删除
从
源
使用
一个
在哪里
c1=a.c1
返回
source.c1,
source.c2,
source.c3)
插入
成
目标
选择
*
从
b;
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
查询计划
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
插入目标(成本= 27.92 . . 41.52行宽度= 680 = 12)(实际时间= 0.082 = 0 . . 0.082行循环= 1)
CTE b
- >删除源(成本= 9.42 . . 27.92行= 680宽度= 6)(实际时间= 0.050 = 1 . . 0.053行循环= 1)
- >堆扫描源位图宽度(成本= 9.42,27.92行= 680 = 6)(实际时间= 0.021 = 1 . . 0.023行循环= 1)
复核电导率(c1 > = 0):
堆:确切= 1
source_pkey - >位图索引扫描(成本= 0.00,9.25行= 680 = 0)宽度(实际时间= 0.010 = 1 . . 0.011行循环= 1)
气孔导度指数(c1 > = 0):
- > CTE扫描b(成本= 0.00 . . 13.60行宽度= 680 = 12)(实际时间= 0.054 = 1 . . 0.057行循环= 1)
计划时间:0.237毫秒
执行时间:0.173 ms
|
把它放在一起
是时候来演示使用pgbench概念验证。
设置
初始化数据库db02:
|
1
2
3
|
dropdb
- - -
如果
- - - - - -
存在
db02
createdb
db02
pgbench
- - - - - -
年代
10
- - - - - -
我
db02
|
|
1
2
3
4
5
6
7
|
的关系列表
模式| | |类型名称所有者| |大小描述
- - - - - - - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - -
公共| pgbench_accounts表| | postgres | | 128 MB
公共| pgbench_branches表| | postgres 40 kB | |
公共| pgbench_history表| | postgres | | 0字节
公共| pgbench_tellers表| | postgres 40 kB | |
|
创建表父和孩子。
请注意:为了证明数据迁移从弃用表,表中pgbench_accounts通过添加oid是改变。
|
1
2
3
4
5
6
7
|
创建
表
父(就像
pgbench_accounts
包括
所有)
没有
oid;
创建
表
孩子(就像
pgbench_accounts
包括
所有)
继承了
(父)
没有
oid;
改变
表
pgbench_accounts
集
与
oid,
继承
父母;
改变
表
pgbench_accounts
重命名
来
pgbench_accounts_deprecated;
改变
表
父
重命名
来
pgbench_accounts;
|
测试
这个查询的方法是解决方案的核心。任何完全锁定的记录,它试图移动自动跳过和新的尝试可以下次调用这个脚本。
|
1
2
3
4
5
6
7
8
9
10
|
与
一个
作为
(选择
*
从
pgbench_accounts_deprecated
订单
通过
1
为
更新
跳过锁着的
限制
10),
b
作为
(删除
从
pgbench_accounts_deprecated
使用
一个
在哪里
pgbench_accounts_deprecated.aid=a.aid
返回
pgbench_accounts_deprecated.aid,
pgbench_accounts_deprecated.bid,
pgbench_accounts_deprecated.abalance,
pgbench_accounts_deprecated.filler)
插入
成
孩子
选择
*
从
b;
|
验证
|
1
|
解释
分析
选择
*
从
pgbench_accounts
订单
通过
1
限制
13;
|
|
1
2
3
4
5
6
7
8
9
10
|
查询计划
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
限制(成本= 0.72,1.45行= 13宽度= 97)(实际时间= 0.012,0.016行= 13循环= 1)
- >合并附加(成本= 0.72,56212.42行= 1000211 = 97)宽度(实际时间= 0.011,0.013行= 13循环= 1)
排序关键字:pgbench_accounts.aid
- >索引扫描使用parent_pkey pgbench_accounts(成本= 0.12 = 1 . . 8.14行宽度= 352)(实际时间= 0.002 = 0 . . 0.002行循环= 1)
- >索引扫描使用pgbench_accounts_pkey pgbench_accounts_deprecated(成本= 0.42,43225.43行= 1000000 = 97)宽度(实际时间= 0.006 = 3 . . 0.007行循环= 1)
- >索引扫描使用child_pkey孩子(成本= 0.14,51.30行= 210 = 352)宽度(实际时间= 0.002 = 10 . . 0.003行循环= 1)
计划时间:0.084毫秒
执行时间:0.030 ms
|
迁移脚本
查询已纳入这个脚本移动1000条记录每5秒。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20.
21
22
23
24
25
26
27
28
29日
30.
31日
32
|
# ! / bin / bash
集
- - - - - -
e
出口
PGHOST
=
/
tmp
PGPORT
=
10011年
PGDATABASE
=
db02
PGUSER
=
postgres
睡眠
=
5
矩形
=
1000年
SQL
=”
与
一个
作为
(
选择
*
从
pgbench_accounts_deprecated
订单
通过
1
为
更新
跳过
锁着的
限制
REC美元
)
,
b
作为
(
删除
从
pgbench_accounts_deprecated
使用
一个
在哪里
pgbench_accounts_deprecated
.aid
=
一个
.aid
返回
pgbench_accounts_deprecated
.aid
,
pgbench_accounts_deprecated
.bid
,
pgbench_accounts_deprecated
.abalance
,
pgbench_accounts_deprecated
.filler
)
插入
成
孩子
选择
*
从
b
;
与
一个
(
count_child
)
作为
(
选择
数
(
*
)
从
孩子
)
,
b
(
count_accounts
)
作为
(
选择
数
(
*
)
从
pgbench_accounts_deprecated
)
选择
一个
.count_child
,
b
.count_accounts
从
一个
,
b
;
”
而
真正的
做
回声
”——美元(日期):执行查询,移动矩形记录现在美元……推荐- - - - - -”
psql
< < <
“$ SQL”
回声
“睡眠:睡眠美元秒……”
& &
睡眠
美元的睡眠
完成
|
基准测试
所有的活跃pgbench上述脚本运行时的水准。
|
1
2
3
4
|
#
#不会阻止
#
pgbench
- - - - - -
c
2
- - - - - -
j
4
- - -
协议
=
简单的
- - - - - -
T
120年
db02
|
警告
这是一个简单而强大的方法,但有局限性:而常见的表之间的列必须是相同的数据类型。
例如,如果列c1表中源的数据类型int你想迁移数据到表中目标用相同的列c1但数据类型长整型数字然后这个方法行不通。然而另一种解决方案可以利用可更新视图,您可以阅读更多关于在这里和使用适当的触发器和重写规则。
雷竞技下载官网Percona分布PostgreSQL提供最好的和最重要的开源社区的企业组件,在一个单一的分布,设计和测试一起工作。
想要每周更新清单的最新博客文章?
现在订阅,我们将送你一个更新每周五下午1点等。
相关的博客文章
推荐的文章
2023年7月3日,
Neha Korukula
PostgreSQL分区使用传统方法
2023年6月30日
谢尔盖Pronin
宣布Percona算子的一般可用性为PostgreSQL版本2雷竞技下载官网
2023年6月29日
Sagar Jadhav
在PostgreSQL找到犯下数之间的两个部分
最受欢迎文章
2023年3月15日
谢尔盖Pronin和针对枪
自动化Kubernetes MongoDB的物理备份
2023年1月17日
谢尔盖Pronin
保持你的数据库安全Percona顾问雷竞技下载官网
2023年2月10日
马塞洛•阿尔特曼
雷竞技下载官网Percona XtraBackup现在支持我实例配置文件