AnalyticDB:Panduan\ untuk\ memeriksa\ ketidakcocokan\ saat\ melakukan\ upgrade\ dari\ versi\ 4\.3\ ke\ 6\.0

Prasyarat

Sebelum memulai, pastikan Anda telah memiliki:

• Perangkat Linux dengan akses jaringan ke instans AnalyticDB for PostgreSQL V4.3

• Titik akhir koneksi, port, username, dan password untuk instans V4.3

• Akses ke Konsol AnalyticDB for PostgreSQL

Skrip pemeriksaan

Skrip ini memeriksa tujuh item kompatibilitas secara berurutan:

1. Versi instans AnalyticDB for PostgreSQL

2. Pustaka yang tidak ditransfer dari V4.3

3. Jenis kunci distribusi tabel yang tidak didukung

4. Jenis bidang tabel yang tidak didukung

5. Ekstensi yang tidak didukung

6. Prosedur tersimpan dan fungsi yang tidak didukung

7. Tampilan yang tidak didukung

#!/bin/bash

#
# Copyright (c) 2020, Alibaba Group, Inc.
#
# Description:
periksa item yang tidak didukung sebelum upgrade instans.
# Usage:  sh 4x_to_6x_check.sh <PGHOST> <PGPORT> <PGUSER> <PGPASSWORD>
# CheckList:
#          1) Periksa versi instans AnalyticDB for PostgreSQL.
#          2) Periksa pustaka yang tidak ditransfer.
#          3) Periksa kunci distribusi tabel yang tidak didukung.
#          4) Periksa jenis bidang tabel yang tidak didukung.
#          5) Periksa ekstensi yang tidak didukung.
#          6) Periksa prosedur tersimpan dan fungsi yang tidak didukung.
#          7) Periksa tampilan yang tidak didukung.
# Notice:        Jika satu atau beberapa pesan error dikembalikan, Anda harus memodifikasi konfigurasi yang sesuai.
#

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')"

# Periksa versi instans AnalyticDB for PostgreSQL.
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
}

# Periksa pustaka yang tidak ditransfer.
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
}

# Periksa kunci distribusi tabel yang tidak didukung.
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
}

# Periksa jenis bidang tabel yang tidak didukung.
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
}

# Periksa ekstensi yang tidak didukung.
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
}

# Periksa prosedur tersimpan dan fungsi yang tidak didukung.
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
}

# Periksa tampilan yang tidak didukung.
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

Parameter

Jalankan pemeriksaan kompatibilitas

1. Instal klien PostgreSQL pada perangkat Linux Anda.

   sudo yum install postgresql

2. Dapatkan alamat IP publik perangkat Linux Anda. Tambahkan ke daftar putih alamat IP untuk instans V4.3 di Konsol AnalyticDB for PostgreSQL. Untuk detailnya, lihat Konfigurasikan daftar putih alamat IP untuk instans AnalyticDB for PostgreSQL.

3. Verifikasi bahwa perangkat Linux dapat terhubung ke instans V4.3.

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

4. Simpan skrip shell sebagai 4x_to_6x_check.sh, lalu jalankan.

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

5. Tinjau output. Jika muncul pesan ERROR atau WARN, selesaikan item yang dilaporkan berdasarkan tabel referensi di bawah ini, lalu jalankan kembali skrip untuk memastikan semua pemeriksaan lolos.

Contoh 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>

Referensi error dan peringatan

Langkah selanjutnya

Setelah semua item pemeriksaan lolos, Anda dapat melanjutkan upgrade instans AnalyticDB for PostgreSQL dari V4.3 ke V6.0.