PostgreSQL: 数据迁移之序列问题

在数据库管理工作中,数据迁移是 DBA 平常而又重要的工作,数据迁移的背景可以有很多,简单的说,需要将一个地方的数据迁移到另一个地方,这里需要说明的是,任何时候的数据迁移,如果涉及到序列,请注意序列问题,哪怕只有一个序列,也要注意。为什么序列对数据迁移如此重要,因为在数据迁移过程中,很容易碰到序列和表数据不匹配的情况,如表的 id ( 这里假设 id 字段是主键,且用序列填充) 最大值大于序列的 next 值,这种情况将造成数据插入不进的情况,从而影响了应用的正常运行。

当然,对于数据为冷备份迁移方式和停业务方式导出,导入的方式出现序列不一致的情况会稍微少些,但在完成数据迁移后,检查下重要核心表的序列是必要的;有一类情况更加需要检查序列,例如,某些情况,先将表定义导入到目标库,并且也建好了序列,之后再导数据,这种情况下,在业
务开启之前,务必需要检查序列,说不准会有序列 next 值小于表的 id 最大值的情况,从而导入数据无法插入。

接下来简单介绍下序列的一些操作,虽然比较简单:

创建序列

1
2
skytf=> create sequence seq_test_1 INCREMENT by 1 MINVALUE 1 NO MAXVALUE start with 1 ;  
CREATE SEQUENCE

查看序列属性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
skytf=> \d seq_test_1  
Sequence "skytf.seq_test_1"
Column | Type | Value
---------------+---------+---------------------
sequence_name | name | seq_test_1
last_value | bigint | 1
start_value | bigint | 1
increment_by | bigint | 1
max_value | bigint | 9223372036854775807
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 0
is_cycled | boolean | f
is_called | boolean | f

查看序列 next 值

1
2
3
4
5
6
7
8
9
10
skytf=> select nextval('seq_test_1');  
nextval
---------
1
(1 row)
skytf=> select nextval('seq_test_1');
nextval
---------
2
(1 row)

查看序列最近使用值

1
2
3
4
5
6
7
8
9
10
skytf=> select currval('seq_test_1');  
currval
---------
2
(1 row)
skytf=> select currval('seq_test_1');
currval
---------
2
(1 row)

接下来介绍下序列重置,这个操作比较常见,也比较重要,有两种方法。

序列重置方式一

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
skytf=> select setval('seq_test_1',100);  
setval
--------
100
(1 row)
skytf=> select currval('seq_test_1');
currval
---------
100
(1 row)
skytf=> select nextval('seq_test_1');
nextval
---------
101
(1 row)

备注:将序列当前值设置为 100。

序列重置方式二

skytf=> alter sequence seq_test_1 restart with 200;  
ALTER SEQUENCE

skytf=> select nextval('seq_test_1');  
nextval  
---------  
 200  
(1 row)

skytf=> select nextval('seq_test_1');  
nextval  
---------  
 201  
(1 row)

skytf=> \d seq_test_1  
 Sequence "skytf.seq_test_1"  
 Column | Type | Value  
---------------+---------+---------------------  
sequence_name | name | seq_test_1  
last_value | bigint | 201  
start_value | bigint | 200  
increment_by | bigint | 1  
max_value | bigint | 9223372036854775807  
min_value | bigint | 1  
cache_value | bigint | 1  
log_cnt | bigint | 31  
is_cycled | boolean | f  
is_called | boolean | t

备注:将序列起始值设置成 200。

总结

之所以写下这篇Blog,是因为自己曾经在序列问题上疏忽过,写下这篇提醒自己,也提醒大家。

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%