通过Shell脚检查ADB PG 6.0版与7.0版不兼容项_云原生数据仓库 AnalyticDB PostgreSQL版(AnalyticDB for PostgreSQL)-阿里云帮助中心

在升级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版库中存在不兼容的扩展模块，这些模块不会迁移。如果用到不兼容的扩展模块，需要考虑修改涉及模块的表或存储过程等。如果没用到，可考虑删除。