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.
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.
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.
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;
This check is not applicable as Greenplum does not support prepared transactions.
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;
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.
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.
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.
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:
Create a new staging table with the same schema as the child partition
Insert the old data into the staging table
Exchange the child partition with the staging table
Drop the staging table
Alternatively, recreate the entire partition table.
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.
The source cluster contains foreign key constraints on root partition tables.
Resolution: Run the pre-intialize data migration scripts.
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.
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.
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.
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.
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”.
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.
Greenplum Database does not support large objects so this error should not occur.
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.
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.
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.
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.
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.
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.
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;
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;
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.
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.
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 are not supported on Greenplum 5 so this error is not applicable.
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.
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.
