AnalyticDB:Check for incompatibilities between AnalyticDB for PostgreSQL V6.0 and V7.0

Precautions

Service SQL statements, custom stored procedures, custom functions, and custom views are not checked by the shell script. You need to verify these check items after you upgrade your instance to AnalyticDB for PostgreSQL V7.0 based on your business requirements.

Sample shell script

#!/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) Check the version of the AnalyticDB for PostgreSQL instance.
#          (2) Check for libraries that are not transferred.
#          (3) Check for unsupported field types for tables.
#          (4) Check for unsupported OSS external tables.
#          (5) Check for unsupported extensions.
#          (6) Check for unsupported stored procedures and functions.
# Notice: If one or more error messages are returned, you must modify the corresponding configurations. 
#

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 the version of the AnalyticDB for PostgreSQL instance.
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
}

# Check for libraries that are not transferred.
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 for unsupported field types for tables.
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
}

# Check for unsupported OSS external tables.
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 for unsupported extensions.
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

The following table describes the parameters in the preceding script.

Procedure

1. Run the following command to install a PostgreSQL client on your Linux device:

   sudo yum install postgresql

2. View the public IP address of the Linux device. Add the public IP address of the Linux device to an IP address whitelist for the AnalyticDB for PostgreSQL V6.0 instance in the AnalyticDB for PostgreSQL console. For more information about how to configure an IP address whitelist, see Configure an IP address whitelist.

3. Connect to the desired AnalyticDB for PostgreSQL V6.0 instance by using the Linux device.

   psql -h <PGHOST> -p <PGPORT> -U <PGUSER>

4. Save the shell script as a script file such as 6x_to_7x.sh. In this example, run the following command to execute the 6x_to_7x_check.sh script file for an incompatibility check:

   sh 6x_to_7x_check.sh <PGHOST> <PGPORT> <PGUSER> <PGPASSWORD>

5. Modify the incompatible configurations of the AnalyticDB for PostgreSQL V6.0 instance based on returned error messages. After you modify the configurations, execute the script again to check whether the check items are compatible with AnalyticDB for PostgreSQL V7.0.

Sample check results

A "pass" message without "ERROR" indicates that the check items are compatible with AnalyticDB for PostgreSQL V7.0. The following code shows that all check items are compatible with AnalyticDB for PostgreSQL V7.0:

********** check base version...
pass......

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

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

********** check oss external table...
pass......

********** check unsupported extensions...
pass......

If the results contain one or more error messages, you must modify the incompatible configurations. The following code shows that all check items are incompatible with AnalyticDB for PostgreSQL V7.0:

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>