The pg_upgrade checks verify if the source and target clusters are compatible. During the pre-upgrade and initialize phase, the migration scripts highlight and fix some of the incompatibilities that could cause gpupgrade initialize to fail.

The checks include:

Running pg_upgrade checks...                           [IN PROGRESS]
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok
Creating a dump of all tablespace metadata.                 ok
Checking database user is a superuser                       ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for external tables used in partitioning           ok
Checking for non-covering indexes on partitioned AO tables  ok
Checking for heterogeneous partitioned tables               ok
Checking for indexes on partitioned tables                  ok
Checking for foreign key constraints on root partitions     ok
Checking for orphaned TOAST relations                       ok
Checking for gphdfs external tables                         ok
Checking for users assigned the gphdfs role                 ok
Checking for unique or primary key constraints              ok
Checking array types derived from partitions                ok
Checking schemas on partitioned tables                      ok
Checking for large objects                                  ok
Checking for invalid indexes                                ok
Checking for views with lead/lag functions using bigint     ok
Checking for non-dumpable views with anyarray casts         ok
Checking for non-dumpable views with unknown casts          ok
Checking for views referencing deprecated tables            ok
Checking for views referencing deprecated columns           ok
Checking for invalid "name" user columns                    ok
Checking for tsquery user columns                           ok
Checking for contrib/ltree                                  ok
Checking for hash partitioned tables                        ok
Checking for tsvector user columns                          ok
Checking for hash and GIN indexes                           ok
Checking for bpchar_pattern_ops indexes                     ok
Checking for presence of required libraries                 ok

Review the individual descriptions and possible workarounds suggested below.

Cluster versions

The source cluster is not on the latest version of Greenplum 5 or the target cluster is not the latest version of Greenplum 6.

Resolution: Upgrade the source or target Greenplum databases.

Database user is a superuser

The database user is not a superuser, as required for the upgrade.

Resolution: Run gpupgrade as the gpadmin user or another user with superuser privileges.

Database connection settings

The template0 default setting has been altered. Template0 must not allow connections. All other databases must allow access to gpadmin.

Resolution: Run the following transaction in the postgres database to disallow connections to template0:

BEGIN;
set allow_system_table_modes=true;
update pg_database set datallowconn='f' where datname='template0';
COMMIT;

Prepared transactions

This check is not applicable as Greenplum does not support prepared transactions.

Reg* system OID user data types

The source installation contains one of the reg* data types in user tables.

These data types reference system Object Identifiers (OIDs) that are not preserved by pg_upgrade, so this cluster cannot be upgraded in its current state. pg_upgrade does not support database upgrades containing: regproc, regprocedure, regoper, regoperator.

Resolution: Remove columns with these data types or convert them to a different data type. To change the data type on the source database, from reg* to text:

ALTER TABLE <table name> ALTER COLUMN <column name> TYPE TEXT;

See /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/tables_using.txt file for a list of problem columns. After the upgrade, change the data type from text to regproc:

ALTER TABLE <table name> ALTER COLUMN <column name> TYPE regproc USING <column name>::regproc;

Contrib/isn with bigint-passing mismatch

The source installation contains isn module which relies on the bigint data type. The source and target clusters pass bigint values differently, so the source cluster cannot be upgraded.

Resolution: You can manually upgrade the databases that use ISN module data types and remove isn module from the source cluster. Then you may restart the upgrade. See the /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/contrib_isn_and_int8_pass_by_value.txt file for a list of problem functions. Alternatively, you may drop all objects referencing the source ISN library. These objects can be created again in the target cluster, referencing the ISN library in Greenplum 6. If the source cluster has tables with data dependant on the source ISN library, take a backup and restore in the target cluster.

External tables used in partitioning

The source installation contains partitioned tables with external tables as partitions.

Resolution: Use the pre-upgrade migration scripts. These partitions need to be removed from the partition hierarchy before the upgrade. See the /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/external_partitions.txt file for a list of external partitions that need to be removed.

Non-covering indexes on partitioned AO tables

The source installation contains partitioned append-only (AO) tables with an index defined on the partition parent which isn’t present on all partition members.

Resolution: These indexes must be dropped before the upgrade. See the /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/mismatched_aopartition_indexes.txt file for a list of the partitions in question. The indexes can be recreated after the upgrade is complete.

Heterogeneous partitioned tables

The source Greenplum Database contains heterogeneous partitioned tables, which can occur when one or more child partitions either have dropped column references that differ between the sibling tables, or the child partitions are misaligned as compared to the root partition. Upgrade cannot output partition table DDL to preserve the dropped columns for the detected child partitions, as ALTER statements can only be applied from the root partition – which then cascades down the partition hierarchy. Preservation of these columns is necessary for on-disk compatibility of the child partitions.

Resolution: Use the pre-upgrade migration scripts to fix the detected child partition tables listed in heterogeneous_partitioned_tables.txt.

Note that the scripts do not support the case where the child partitions are misaligned from the root in their name, type, or length. To correct the child partitions in this scenario:

  1. Create a new staging table with the same schema as the child partition

  2. Insert the old data into the staging table

  3. Exchange the child partition with the staging table

  4. Drop the staging table

Alternatively, recreate the entire partition table.

Indexes on partitioned tables

The source installation contains partitioned tables with indexes defined on them.

Resolution: Indexes on root partitions, as well as child partitions, must be dropped before upgrade. The indexes can be recreated after the upgrade is complete. See partitioned_tables_indexes.txt for a list of the problem tables.

Foreign key constraints on root partitions

The source cluster contains foreign key constraints on root partition tables.

Resolution: Run the pre-intialize data migration scripts.

Orphaned TOAST relations

The source installation contains orphaned TOAST tables which must be dropped before upgrade.

Resolution: Drop the tables listed in the /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/orphaned_toast_tables.txt file.

Gphdfs external tables

The source installation contains gphdfs external tables.

Resolution: Use the pre-upgrade migration scripts. Drop the tables listed in the /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/gphdfs_external_tables.txt file. Gphdfs was deprecated in Greenplum 5 and removed in Greenplum 6 where the recommendation is to use pxf.

Users assigned the gphdfs role

The gphdfs user role is present in the cluster.

Resolution: Use the pre-upgrade migration scripts. Revoke gphdfs privilege from the roles listed in the /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/gphdfs_user_roles.txt file.

Unique or primary key constraints

The source installation contains unique or primary key constraints on tables.

Resolution: Use the pre-upgrade migration scripts. Drop the constraints and recreate them after the upgrade. See the /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/unique_primary_key_constraint.txt file for a list of those constraints.

Array types derived from partitions

The source installation contains array types derived from partitions of a partitioned table that must not have dependants.

If a partition was exchanged, and an object is created using the type of the partition, then such objects cannot be upgraded. The following example demonstrates this:

CREATE TABLE table_part(a INT, b INT) PARTITION BY RANGE(b) (PARTITION part1 START(0) END(42));
CREATE TABLE replacement(LIKE table_part);
ALTER TABLE table_part EXCHANGE PARTITION part1 WITH TABLE replacement;
CREATE TABLE dependant(d table_part_1_prt_part1[]);

Resolution: In such cases, the dependent objects should be dropped. For instance “drop table dependant”.

Schemas on partitioned tables

The source cluster contains partitioned tables where one or more child partitions are not in the same schema as the root partition.

Resolution: Perform ALTER TABLE ... SET SCHEMA on the child partitions to match them before upgrading. See the /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/mismatched_partition_schemas.txt file for a list of problem tables.

Large objects

Greenplum Database does not support large objects so this error should not occur.

Invalid indexes

The source cluster contains invalid indexes. This can occur with the CREATE INDEX CONCURRENTLY statement, which is not supported in Greenplum.

Resolution: Reindex or drop the indexes specified in invalid_indexes.txt.

Views with lead/lag functions using bigint

The source cluster contains one or more views that were created with lag() or lead() functions that specified a bigint-type offset argument. In Greenplum Database 6.x, the offset argument in these functions is changed to the integer data type.

Resolution: Drop the views listed in the file /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/view_lead_lag_functions.txt file.

Non-dumpable views with anyarray casts

The source cluster contains views with the ::anyarray explicit type cast. These views could have been created due to a server issue generating bad anyarray casts before Greenplum 5.28.6. This is an example view:

CREATE VIEW view_anyarray_cast AS SELECT '{1}'::int[] = '{2}'::int[];
View definition would have erroneously become (the view definition will lead to a failure during schema restore):
SELECT '{1}'::anyarray = '{2}'::anyarray;

Resolution: Drop the views listed in the /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/view_anyarray_casts.txt file. Alternatively, recreate the view in the source cluster without the explicit anyarray type cast before running an upgrade.

Non-dumpable views with unknown casts

The source cluster contains views with the unknown::cstring explicit type cast. Such views cannot be upgraded. These views could have been created due to a server issue generating bad casts before Greenplum 5.28.6. This is an example view:

CREATE VIEW unknown_v1 AS SELECT '2020-12-13'::unknown AS field_unknown;
CREATE VIEW unknown_v2 AS SELECT field_unknown::date FROM unknown_v1;

The view definition for unknown_v2 would have erroneously become:

SELECT unknown_v1.field_unknown::cstring::date AS field_unknown
   FROM unknown_v1;

Resolution: Drop the views listed in the /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/view_unknown_casts.txt. Alternatively, recreate the view in the source cluster without the explicit type casts before running an upgrade.

Views referencing deprecated tables

The source cluster contains views that reference deprecated tables.

Resolution: Drop the views listed in the /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/view_deprecated_tables.txt file.

Views referencing deprecated columns

The source cluster contains views referring to columns that have been deprecated.

Resolution: Drop the views listed in the /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/seg-1/view_deprecated_columns.txt file.

Invalid name or user columns

The source installation contains the name data type in user tables. The internal alignment of this data type changed between source and target clusters, so the source cluster cannot be upgraded.

Resolution: Use the pre-upgrade migration scripts. See the /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/tables_using_name.txt file for a list of the problem columns. If a table contains a column with a name data type, alter the column to varchar(63). Two scenarios are not handled by the migration scripts: - If there is an index defined on a column with name data type, drop the index before running the data migration scripts, or manually alter the columns; otherwise the migration scripts will fail. - If the columns with the name data type are inherited, alter the column in the parent table which will also alter the child tables. The data migration scripts do not handle this scenario.

Example:

ALTER TABLE <tablename> ALTER COLUMN <col_with_name_datatype> TYPE varchar;

tsquery user columns

The source installation contains the tsquery data type. The internal representation of this data type changed between Greenplum Database 5.x and 6.x, so the source cluster cannot be upgraded.

Resolution: Change the tsquery data type to text before proceeding with the upgrade. See the /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/tables_using_tsquery.txt file in your working directory for a list of affected tables and columns. Two scenarios are not handled by the migration scripts: - If there is an index defined on a column with tsquery data type, drop the index before running the data migration scripts, or manually alter the columns; otherwise the migration scripts will fail. - If the columns with the tsquery data type are inherited, alter the column in the parent table which will also alter the child tables. The data migration scripts do not handle this scenario.

Example:

ALTER TABLE <tablename> ALTER COLUMN <col_with_tsquery_datatype> TYPE TEXT;

Contrib/ltree

The source installation contains the ltree data type. This data type changed its internal representation between Greenplum version 5.x and 6.x.

Resolution: Manually upgrade the databases that use the ltree contrib module, remove them from the source cluster and restart the upgrade.

Hash partitioned tables

The source installation contains hash partitioned tables, which are not supported by pg_upgrade.

Resolution: Remove the problem tables and restart the upgrade. See the /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/hash_partitioned_tables.txt file for a list of problem tables. You may also copy the data to a partitioned table based on list or range partitioning and drop the table with hash partition scheme.

Tsvector user columns

The source installation contains tsvector columns. The tsvector internal storage format has changed between Greenplum Database 5.x and Greenplum Database 6.x. The pg_upgrade check highlights this issue with a warning and proceeds with the upgrade.

Resolution: After the finalize phase, use psql to execute the file /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/rebuild_tsvector_tables.sql on the target cluster, which rebuilds all the tables containing tsvector columns.

Hash and GIN indexes

Hash and GIN indexes are not supported on Greenplum 5 so this error is not applicable.

Bpchar_pattern_ops indexes

The source installation contains indexes using bpchar_pattern_ops. These indexes have different internal formats between the source and target clusters.

Resolution: You can drop and reindex these indexes with the REINDEX command.

Presence of required libraries

The source installation references loadable libraries that are missing from the target installation.

Resolution: Add these libraries to the target cluster, or remove the functions that use them from the source cluster. See the /home/gpadmin/gpAdminLogs/gpupgrade/pg_upgrade/<seg#>/loadable_libraries.txt file for a list of the libraries required for the target cluster.

check-circle-line exclamation-circle-line close-line
Scroll to top icon