云原生数据仓库AnalyticDB PostgreSQL(简称ADB PG)集群从4.3版本升级到6.0版本时存在一些不兼容项,需要您手工进行预处理。本文以Linux环境下连接ADB PG 4.3版实例为例,介绍了通过执行shell脚本检查常规不兼容项的方法。Windows环境可参考脚本SQL语句执行检查。

注意事项

业务SQL、自定义存储过程或函数、自定义视图不包含在检查项目内,您需要根据实际情况在ADB PG 6.0实例中进行验证。

shell脚本参考

#!/bin/bash

#
# Copyright (c) 2020, Alibaba Group, Inc.
#
# Description:  check unsupported items before upgrade instance.
# Usage:        sh 4x_to_6x_check.sh <PGHOST> <PGPORT> <PGUSER> <PGPASSWORD>
# CheckList:
#          1) 检查实例版本
#          2) 检查libraries
#          3) 检查表分布键
#          4) 检查表字段类型
#          5) 检查扩展模块
# 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[@]}

unsupport6x_ext="('fastann','feature_extractor','varbitx')"
unsupport6x_disted_type="('money','tinterval')"
unsupport6x_type="('unknown')"

# 检查实例版本
check_version()
{
  echo ''
  echo $db_ver
  echo ''
  echo '********** check base version...'
  base_time=`date -d "2020-08-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 "WARN: please transfer libraries manually..."
  else
    echo 'pass......'
  fi
}

# 检查表分布键
check_table_did()
{
  echo ''
  echo '********** check unsupported table distributedId types...'
  count=0
  if [[ $db_ver == *8.2*4.3* ]]; 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,pg_catalog.gp_distribution_policy p where
      a.atttypid=t.oid and a.attrelid=c.oid and p.localoid=c.oid and a.attnum=any(p.attrnums) and a.attnum>0 and t.typname in $unsupport6x_disted_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 distributed_field,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,pg_catalog.gp_distribution_policy p
        where a.atttypid=t.oid and n.oid=c.relnamespace and a.attrelid=c.oid and p.localoid=c.oid and a.attnum=any(p.attrnums) and a.attnum>0 and t.typname in $unsupport6x_disted_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 distributedId types manually...'
  else
    echo 'pass......'
  fi
}

# 检查表字段类型
check_table_ftype()
{
  echo ''
  echo '********** check unsupported table field types...'
  count=0
  if [[ $db_ver == *8.2*4.3* ]]; 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 t.typname in $unsupport6x_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 t.typname in $unsupport6x_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
}

# 检查扩展模块
check_extensions()
{
  echo ''
  echo '********** check unsupported extensions...'
  count=0
  if [[ $db_ver == *8.2*4.3* ]]; 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 $unsupport6x_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 $unsupport6x_ext;"
      fi
    done
  fi
  if [[ $count -gt 0 ]]; then
    echo 'WARN: please drop useless extensions manually...'
    echo 'REF DROP EXTENSION SQL: drop extension <name> '
  else
    echo 'pass......'
  fi
}

check_version
check_libraries
check_table_did
check_table_ftype
check_extensions
表 1. 参数说明
参数 说明
<PGHOST> ADB PG 4.3版实例的连接地址
<PGPORT> ADB PG 4.3版实例的端口号
<PGUSER> 连接ADB PG 4.3版实例的用户名
<PGPASSWORD> 上述用户名对应的密码

操作步骤

  1. 运行以下命令在Linux设备中安装postgresql客户端。
    sudo yum install postgresql
  2. 查看Linux设备外网地址。登录AnalyticDB PostgreSQL控制台,将Linux设备的外网IP地址添加至ADB PG 4.3版实例的白名单中,详情请参见设置白名单
  3. 使用Linux设备连接待检查不兼容项的ADB PG 4.3版实例。
    psql -h <PGHOST> -p <PGPORT> -U <PGUSER>
  4. 将shell脚本编辑保存为如4x_to_6x.sh的脚本文件,运行以下命令执行检查不兼容项的脚本文件4x_to_6x_check.sh。
    sh 4x_to_6x_check.sh <PGHOST> <PGPORT> <PGUSER> <PGPASSWORD>
  5. 根据提示信息,修改数据库中对应的不兼容项,修改完毕后,请再次执行脚本查看结果是否通过检查。

检查结果参考

检查通过情况(运行脚本后全部显示“pass”,或者不包含“ERROR”信息)
********** check base version...
pass......

********** check untransferred libraries...
pass......

********** check unsupported table distributedId types...
pass......

********** check unsupported table field types...
pass......

********** check unsupported extensions...
pass......
存在不兼容项的检查情况(提示ERROR信息的必须修改)

PostgreSQL 8.2.15 (Greenplum Database 4.3.99.00 build dev) compiled on May 2 2020 09:35:15

********** 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 distributedId types...
 dbname | schema | table_name | distributed_field | field_type 
--------+--------+------------+-------------------+------------
 adbpg  | public | test1      | id                | money
(1 row)

ERROR: please alter table distributedId types 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 unsupported extensions...
 dbname | extname | extversion 
--------+---------+------------
 adbpg  | varbitx | 1.0
(1 row)

WARN: please drop useless extensions manually...
REF DROP EXTENSION SQL: drop extension <name> 
提示信息 修改方式
ERROR: please upgrade minor version... 登录AnalyticDB PostgreSQL控制台将实例进行小版本升级,详情请参见版本升级
WARN: please transfer libraries manually... 提示4.3库中用到了library,这些library不会自动迁移,升级后您需要手工进行迁移。
ERROR: please alter table distributedId types manually... 提示4.3库中存在不兼容的表分布键,需要您在4.3库中手动修改。
ERROR: please alter table field types manually... 提示4.3库表存在不兼容的字段类型,需要在4.3库中手动修改。
WARN: please drop useless extensions manually... 提示4.3库中存在不兼容的扩展模块,这些模块不会迁移,如果用到需要考虑修改涉及模块的表/存储过程等,如果没用可考虑删除。