作为DBA,经常会涉及到数据导入导出问题,以前用pg_dump导出数据后,用psql进行导入,当时觉得sql中已经存在操作序列的语句,所以没有对应更新序列最大值。最近频繁出现序列问题。
为什么序列对数据迁移如此重要,因为在数据迁移过程中,很容易碰到序列和表数据不匹配的情况,如表的 id ( 这里假设 id 字段是主键,且用序列填充) 最大值大于序列的 next 值,这种情况将造成数据插入不进的情况,从而影响了应用的正常运行。
针对这个问题,我查了一下资料,自己写了一个批量执行的脚本,贴上来方便大家参考(水平有限,大家共同进步)。
####################这段代码稍微有点麻烦,请看下面优化版本#########################
#!/bin/bash
# author:Jeff Yuan
# date:2016-03-29
IFS=$'\n'
# 读取并生成rwnas序列更新语句存入变量,可以在下面语句中指定对应schema来操作固定schema下面的序列
update_sequence_array=$(psql -h 127.0.0.1 -p 5432 -d test -U test -c "select 'select setval(\''||table_sequence||'\',max(id)) from '||table_schema||'.'||table_name||';' from (SELECT table_schema,table_name, column_name, column_default, substring(column_default,position('(\'' in column_default)+2,position('\'::regclass' in column_default)-10) as table_sequence from information_schema.columns where column_default <> '' and table_name not like '%_1_prt_m%' and column_name = 'id' and table_schema not like '%_bak') t;")
#定义循环更新函数
function update_sequence(){
for i in $update_sequence_array
do
echo $i
echo "$(psql -h 127.0.0.1 -p 5432 -d test -U test -c "$i")"
done
}
#执行函数
update_sequence
##############下面这段代码里面做了一点优化,目前使用脚本可以传入数据库参数,提高了通用性#################
#!/bin/bash
# author:Jeff Yuan
# create date:2016-03-29
# audit date:2016-05-30
# 使用:请在脚本名后面加上要更新序列的数据库名,例如:update_sequence.sh test
IFS=$'\n'
# 参数定义
db=$1
# 读取并生成序列更新语句存入变量
update_sequence_array=$(psql -h 127.0.0.1 -p 5432 -d $db -t -c "select 'select setval('''||table_sequence||''',max(id))'||' from '||table_name||';' from (SELECT table_schema||'.'||table_name as table_name, split_part(column_default,'''',2) as table_sequence from information_schema.columns where column_default <> '' and table_name not like '%_1_prt%' and column_name = 'id') t;")
#定义循环更新函数
function update_sequence(){
for i in $update_sequence_array
do
echo $i
echo "$(psql -h 127.0.0.1 -p 5432 -d $db -c "$i")"
done
}
#执行函数
update_sequence