在升級AnalyticDB PostgreSQL 6.0版為AnalyticDB PostgreSQL 7.0版時，需要您手工預先處理兩個版本存在的不相容項。本文以Linux環境下串連AnalyticDB PostgreSQL 6.0版執行個體為例，介紹通過執行Shell指令碼檢查常規不相容項的方法。
注意事項
業務SQL、自訂預存程序、自訂函數和自訂視圖不包含在檢查專案內，您需要根據實際情況在AnalyticDB PostgreSQL 7.0版中進行驗證。
Shell指令碼參考
#!/bin/bash
#
# Copyright (c) 2023, Alibaba Group, Inc.
#
# Description: check unsupported items before upgrade instance.
# Usage: sh 6x_to_7x_check.sh <PGHOST> <PGPORT> <PGUSER> <PGPASSWORD>
# CheckList:
# 1) 檢查執行個體版本
# 2) 檢查libraries
# 4) 檢查表欄位類型
# 5) 檢查oss_ext外表
# 6) 檢查擴充模組
# 7) 檢查預存程序/函數
# Notice: 如果提示錯誤資訊，請人工修改資料庫對應項。
#
if [[ $# -lt 4 ]]; then
echo "Usage: $0 <PGHOST> <PGPORT> <PGUSER> <PGPASSWORD>"
exit 1
fi
export PGHOST=$1
export PGPORT=$2
export PGUSER=$3
export PGPASSWORD=$4
db_ver=`psql -d postgres -c "copy (select version()) to stdout"`
db_names=`psql -d postgres -c "copy (select sodddatname from gp_toolkit.gp_size_of_database) to stdout"`
db_names=(${db_names})
db_len=${#db_names[@]}
unsupport7x_ext="('adbpg_desensitization', 'adbpg_hardware_bench', 'address_standardizer', 'address_standardizer_data_us', 'auto_partition', 'automerge_status', 'diskquota', 'fastann', 'hyjal_pb_formatter', 'madlib', 'morton_code', 'multi_master', 'multicorn', 'open_analytic', 'oss_ext', 'pljava', 'plpython2u', 'plpythonu', 'querycache', 'redis_fdw')"
unsupport7x_type="('abstime', 'reltime', 'tinterval', 'unknown')"
# 檢查執行個體版本
check_version()
{
echo ''
echo $db_ver
echo ''
echo '********** check base version...'
base_time=`date -d "2023-01-31" +%s`
db_verdate=${db_ver##*compiled on}
seconds=`date -d "$db_verdate" +%s`
if [[ $seconds -lt $base_time ]]; then
echo 'ERROR: please upgrade minor version...'
else
echo 'pass......'
fi
}
# 檢查libraries
check_libraries()
{
echo ''
echo '********** check untransferred libraries...'
count=`psql -d postgres -c "copy (select count(1) from pg_catalog.pg_library) to stdout"`
if [[ $count -gt 0 ]]; then
psql -d postgres -c "select name,lanname language from pg_catalog.pg_library;"
echo "ERROR: please drop unsupported libraries manually..."
else
echo 'pass......'
fi
}
# 檢查表欄位類型
check_table_ftype()
{
echo ''
echo '********** check unsupported table field types...'
count=0
if [[ $db_ver == *9.4*6.* ]]; then
for ((i=0; i<$db_len; ++i)); do
sql="select count(1) from pg_catalog.pg_class c,pg_catalog.pg_attribute a,pg_catalog.pg_type t where
a.atttypid=t.oid and a.attrelid=c.oid and a.attnum>0 and c.relnamespace <> 11 and t.typname in $unsupport7x_type"
count1=`psql -d ${db_names[$i]} -c "copy ($sql) to stdout"`
count=$((count + count1))
if [[ $count1 -gt 0 ]]; then
sql="select '${db_names[$i]}' dbname,n.nspname schema,c.relname table_name,a.attname field_name,t.typname field_type from
pg_catalog.pg_namespace n,pg_catalog.pg_class c,pg_catalog.pg_attribute a,pg_catalog.pg_type t
where a.atttypid=t.oid and n.oid=c.relnamespace and a.attrelid=c.oid and a.attnum>0 and c.relnamespace <> 11 and t.typname in $unsupport7x_type order by schema,table_name;"
psql -d ${db_names[$i]} -c "$sql"
fi
done
fi
if [[ $count -gt 0 ]]; then
echo 'ERROR: please alter table field types manually...'
else
echo 'pass......'
fi
}
#檢查oss_ext外部表格
check_oss_ext()
{
echo ''
echo '********** check oss external table...'
count=0
if [[ $db_ver == *9.4*6.* ]]; then
for ((i=0; i<$db_len; ++i)); do
count1=`psql -d ${db_names[$i]} -c "copy (select count(*) from pg_class c join pg_exttable e on c.oid = e.reloid where c.relstorage = 'x' and e.urilocation[1] like '%oss://%') to stdout"`
count=$((count + count1))
if [[ $count1 -gt 0 ]]; then
psql -d ${db_names[$i]} -c "select '${db_names[$i]}' dbname, c.relname from pg_class c join pg_exttable e on c.oid = e.reloid where c.relstorage = 'x' and e.urilocation[1] like '%oss://%';"
fi
done
fi
if [[ $count -gt 0 ]]; then
echo 'WARN: please drop oss external table manually...'
echo 'HINT: oss external table is unsupported, please change to oss foreign table'
else
echo 'pass......'
fi
}
# 檢查擴充模組
check_extensions()
{
echo ''
echo '********** check unsupported extensions...'
count=0
if [[ $db_ver == *9.4*6.* ]]; then
for ((i=0; i<$db_len; ++i)); do
count1=`psql -d ${db_names[$i]} -c "copy (select count(1) from pg_catalog.pg_extension where extname in $unsupport7x_ext) to stdout"`
count=$((count + count1))
if [[ $count1 -gt 0 ]]; then
psql -d ${db_names[$i]} -c "select '${db_names[$i]}' dbname,extname,extversion from pg_catalog.pg_extension where extname in $unsupport7x_ext;"
fi
done
fi
if [[ $count -gt 0 ]]; then
echo 'WARN: please drop useless extensions manually...'
echo 'HINT: 1. please change plpythonu to plpython3u(include related functions) 2. oss_ext is unsupported, please change to oss_fdw'
echo 'REF DROP EXTENSION SQL: drop extension <name> '
else
echo 'pass......'
fi
}
check_version
check_libraries
check_table_ftype
check_oss_ext
check_extensions參數說明如下：
參數 | 說明 |
<PGHOST> | AnalyticDB PostgreSQL 6.0版執行個體的串連地址。 |
<PGPORT> | AnalyticDB PostgreSQL 6.0版執行個體的連接埠號碼。 |
<PGUSER> | 串連AnalyticDB PostgreSQL 6.0版執行個體的資料庫帳號。 |
<PGPASSWORD> | 帳號對應的密碼。 |
操作步驟
執行以下命令在Linux裝置中安裝postgresql用戶端。
sudo yum install postgresql
查看Linux裝置外網地址。登入AnalyticDB PostgreSQL控制台，將Linux裝置的外網IP地址添加至AnalyticDB PostgreSQL 6.0版執行個體的白名單中，詳情請參見設定白名單。
使用Linux裝置串連待檢查不相容項的AnalyticDB PostgreSQL 6.0版執行個體。
psql -h <PGHOST> -p <PGPORT> -U <PGUSER>
編輯保持Shell指令碼，如6x_to_7x.sh的指令檔，運行以下命令執行檢查不相容項的指令檔6x_to_7x_check.sh。
sh 6x_to_7x_check.sh <PGHOST> <PGPORT> <PGUSER> <PGPASSWORD>根據提示資訊，修改資料庫中對應的不相容項，修改完畢後，請再次執行指令碼查看結果是否通過檢查。
檢查結果參考
檢查通過情況（運行指令碼後全部顯示“pass”，或者不包含“ERROR”資訊）
********** check base version...
pass......
********** check untransferred libraries...
pass......
********** check unsupported table field types...
pass......
********** check oss external table...
pass......
********** check unsupported extensions...
pass......存在不相容項的檢查情況（提示ERROR資訊的必須修改）
PostgreSQL 9.4.26 (Greenplum Database 6.6.0 build dev) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 9.2.1 20200522 (Alibaba 9.2.1-3 2.17), 64-bit compiled on Dec 15 2023 16:44:16
********** check base version...
ERROR: please upgrade minor version...
********** check untransferred libraries...
name | language
----------+----------
select_1 | plpgsql
(1 row)
WARN: please transfer libraries manually...
********** check unsupported table field types...
dbname | schema | table_name | field_name | field_type
--------+--------+------------+------------+------------
adbpg | public | test2 | name | unknown
(1 row)
ERROR: please alter table field types manually...
********** check oss external table...
dbname | relname
--------+----------
testdb | testoss
testdb | testoss2
testdb | testoss3
(3 rows)
WARN: please drop oss external table manually...
HINT: oss external table is unsupported, please change to oss foreign table
********** check unsupported extensions...
dbname | extname | extversion
--------+---------+------------
adbpg | fastann | 1.0
(1 row)
WARN: please drop useless extensions manually...
REF DROP EXTENSION SQL: drop extension <name> 提示資訊 | 修改方式 |
ERROR: please upgrade minor version... | 升級執行個體核心小版本，詳情請參見版本升級。 |
WARN: please transfer libraries manually... | 提示AnalyticDB PostgreSQL 6.0版庫中用到了library，這些library不會自動遷移，升級後您需要手工進行遷移。 |
ERROR: please alter table field types manually... | 提示AnalyticDB PostgreSQL 6.0版庫表存在不相容的欄位類型，需要在AnalyticDB PostgreSQL 6.0版庫中手動修改。 |
WARN: please drop oss external table manually... | 提示AnalyticDB PostgreSQL 6.0版庫使用了OSS External TABLE，在AnalyticDB PostgreSQL 7.0版中不再支援，需要轉換為OSS Foreign TABLE。 |
WARN: please drop useless extensions manually... | 提示AnalyticDB PostgreSQL 6.0版庫中存在不相容的擴充模組，這些模組不會遷移。
|