Check for incompatibility between AnalyticDB for PostgreSQL V4.3 and V6.0 - AnalyticDB

Precautions

The check items do not include SQL statements for your business or custom stored procedures, functions, or views. You can verify the excluded items in AnalyticDB for PostgreSQL V6.0 instances.

Sample shell script

#!/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

Table 1. Parameters
Parameter	Description
<PGHOST>	The endpoint that is used to connect to the AnalyticDB for PostgreSQL V4.3 instance.
<PGPORT>	The port number that is used to connect to the AnalyticDB for PostgreSQL V4.3 instance.
<PGUSER>	The username that is used to connect to the AnalyticDB for PostgreSQL V4.3 instance.
<PGPASSWORD>	The password for the user.

Procedure

Run the following command to install a PostgreSQL client on your Linux device:
```
sudo yum install postgresql
```
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 V4.3 instance in the AnalyticDB for PostgreSQL console. For more information about how to configure an IP address whitelist, see Configure an IP address whitelist for an AnalyticDB for PostgreSQL instance.
Connect to the AnalyticDB for PostgreSQL V4.3 instance by using the Linux device.
```
psql -h <PGHOST> -p <PGPORT> -U <PGUSER>
```
Save the edited shell script as a script file such as 4x_to_6x.sh. In this example, run the following command to run the 4x_to_6x_check.sh script for incompatibility check:
```
sh 4x_to_6x_check.sh <PGHOST> <PGPORT> <PGUSER> <PGPASSWORD>
```
Modify the incompatible items of the AnalyticDB for PostgreSQL V4.3 instance based on returned error messages. After you modify the items, run the script again to verify whether the check items are compatible with AnalyticDB for PostgreSQL V6.0.

Sample check results

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

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

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

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

********** check unsupported table field types...
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 V6.0:


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 message	Modification method
ERROR: please upgrade minor version...	Upgrade the minor version of the AnalyticDB for PostgreSQL V4.3 instance in the AnalyticDB for PostgreSQL console. For more information, see Update the minor kernel version of an AnalyticDB for PostgreSQL instance.
WARN: please transfer libraries manually...	Migrate the libraries that are used in the AnalyticDB for PostgreSQL V4.3 instance.
ERROR: please alter table distributedId types manually...	Modify the incompatible distribution keys in the AnalyticDB for PostgreSQL V4.3 instance.
ERROR: please alter table field types manually...	Modify the incompatible field types in the AnalyticDB for PostgreSQL V4.3 instance.
WARN: please drop useless extensions manually...	If you need to use the tables or stored procedures that are relevant to the incompatible extensions, modify the configurations of the extensions. If the incompatible extensions are not used in AnalyticDB for PostgreSQL V6.0, delete the extensions.