AnalyticDB:Guide to checking for incompatibilities when upgrading from version 4.3 to 6.0

Prerequisites

Before you begin, ensure that you have:

• A Linux device with network access to the AnalyticDB for PostgreSQL V4.3 instance

• The connection endpoint, port, username, and password for the V4.3 instance

• Access to the AnalyticDB for PostgreSQL console

Check script

The script checks seven compatibility items in sequence:

1. AnalyticDB for PostgreSQL instance version

2. Libraries not transferred from V4.3

3. Unsupported distribution key types for tables

4. Unsupported field types for tables

5. Unsupported extensions

6. Unsupported stored procedures and functions

7. Unsupported views

#!/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) Check the version of the AnalyticDB for PostgreSQL instance.
#          2) Check the libraries that are not transferred.
#          3) Check the unsupported distribution keys for tables.
#          4) Check the unsupported field types for tables.
#          5) Check the unsupported extensions.
#          6) Check the unsupported stored procedures and functions.
#          7) Check the unsupported views.
# 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[@]}

unsupport6x_ext="('feature_extractor','varbitx')"
unsupport6x_disted_type="('money','tinterval')"
unsupport6x_type="('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 "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
}

# Check the 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 "WARN: please transfer libraries manually..."
else
    echo 'pass......'
fi
}

# Check the unsupported distribution keys for tables.
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 the unsupported field types for tables.
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 the unsupported extensions.
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 the unsupported stored procedures and functions.
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 the unsupported views.
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

Parameters

Run the compatibility check

1. Install a PostgreSQL client on your Linux device.

   sudo yum install postgresql

2. Get the public IP address of your Linux device. Add it to the IP address whitelist for the V4.3 instance in the AnalyticDB for PostgreSQL console. For details, see Configure an IP address whitelist for an AnalyticDB for PostgreSQL instance.

3. Verify that the Linux device can connect to the V4.3 instance.

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

4. Save the shell script as 4x_to_6x_check.sh, then run it.

   sh 4x_to_6x_check.sh <PGHOST> <PGPORT> <PGUSER> <PGPASSWORD>

5. Review the output. If any ERROR or WARN messages appear, resolve the reported items based on the reference table below, then run the script again to confirm all checks pass.

Sample output

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

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

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

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

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

********** check unsupported procs...
pass......

********** check unsupported views...
pass......

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 and warning reference

What's next

After all check items pass, you can proceed with upgrading your AnalyticDB for PostgreSQL instance from V4.3 to V6.0.