全部產品
Search

搜尋本產品

    ：4.3版升級6.0版不相容項檢查參考指南

    文件中心

    ：4.3版升級6.0版不相容項檢查參考指南

    更新時間：Jun 19, 2024

    雲原生資料倉儲AnalyticDB PostgreSQL版（簡稱ADB PG）叢集從4.3版本升級到6.0版本時存在一些不相容項，需要您手工進行預先處理。本文以Linux環境下串連AnalyticDB PostgreSQL 4.3版執行個體為例，介紹了通過執行Shell指令碼檢查常規不相容項的方法。Windows環境可參考指令碼SQL語句執行檢查。

    注意事項

    業務SQL、自訂預存程序或函數、自訂視圖不包含在檢查專案內，您需要根據實際情況在AnalyticDB PostgreSQL 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) 檢查擴充模組
#          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[@]}

unsupport6x_ext="('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_procs()
{
  echo ''
  echo '********** check unsupported procs...'
  count=0
  clause="lower(p.prosrc) like '%pg_stat_activity%'
  and ( lower(p.prosrc) like '%.procpid%' or lower(p.prosrc) like '%.current_query%' or lower(p.prosrc) like '%.waiting%' )
  and n.nspname not in ('gp_toolkit','information_schema')"
  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_proc p join pg_catalog.pg_namespace n on p.pronamespace = n.oid where $clause) to stdout"`
      count=$((count + count1))
      if [[ $count1 -gt 0 ]]; then
        psql -d ${db_names[$i]} -c "select '${db_names[$i]}' dbname,n.nspname schemaname,p.proname from pg_catalog.pg_proc p join pg_catalog.pg_namespace n on p.pronamespace = n.oid where $clause;"
      fi
    done
  fi
  if [[ $count -gt 0 ]]; then
    echo 'WARN: please drop/repair proc/function manually after transferred...'
  else
    echo 'pass......'
  fi
}

# 檢查視圖
check_views()
{
  echo ''
  echo '********** check unsupported views...'
  count=0
  clause="lower(definition) like '%pg_stat_activity%'
  and ( lower(definition) like '%.procpid%' or lower(definition) like '%.current_query%' or lower(definition) like '%.waiting%' )
  and schemaname not in ('gp_toolkit','information_schema')"
  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_views where $clause) to stdout"`
      count=$((count + count1))
      if [[ $count1 -gt 0 ]]; then
        psql -d ${db_names[$i]} -c "select '${db_names[$i]}' schemaname,viewname from pg_catalog.pg_views where $clause;"
      fi
    done
  fi
  if [[ $count -gt 0 ]]; then
    echo 'WARN: please drop useless views manually...'
  else
    echo 'pass......'
  fi
}

check_version
check_libraries
check_table_did
check_table_ftype
check_extensions
check_procs
check_views
    表 1. 參數說明
    參數說明
    <PGHOST>AnalyticDB PostgreSQL 4.3版執行個體的串連地址。
    <PGPORT>AnalyticDB PostgreSQL 4.3版執行個體的連接埠號碼。
    <PGUSER>串連AnalyticDB PostgreSQL 4.3版執行個體的使用者名稱。
    <PGPASSWORD>上述使用者名稱對應的密碼。

    操作步驟

    1. 運行以下命令在Linux裝置中安裝postgresql用戶端。
      sudo yum install postgresql
    2. 查看Linux裝置外網地址。登入AnalyticDB PostgreSQL控制台，將Linux裝置的外網IP地址添加至AnalyticDB PostgreSQL 4.3版執行個體的白名單中，詳情請參見設定白名單。
    3. 使用Linux裝置串連待檢查不相容項的AnalyticDB PostgreSQL 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庫中存在不相容的擴充模組，這些模組不會遷移，如果用到需要考慮修改涉及模組的表/預存程序等，如果沒用可考慮刪除。