"[CRITICAL]:-ERROR: operator family "xxxx" for access method "btree" already exists" error when performing a database restore to a new database in VMware Tanzu Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

Running gprestore to restore a full backup to a new database results in the this error:

20210510:15:29:25 gprestore:gpadmin:gpm-ipbl-etl101.gpdata.rws.local:110005-[INFO]:-gprestore version = 1.20.2 20210510:15:29:25 gprestore:gpadmin:gpm-ipbl-etl101.gpdata.rws.local:110005-[INFO]:-Greenplum Database Version = 6.13.0 build commit:4f1adf8e247a9685c19ea02bcaddfdc200937ecd 20210510:15:29:26 gprestore:gpadmin:gpm-ipbl-etl101.gpdata.rws.local:110005-[INFO]:-Creating database 
20210510:15:29:54 gprestore:gpadmin:gpm-ipbl-etl101.gpdata.rws.local:110005-[INFO]:-Database creation complete for: aoa_air_test_restore 20210510:15:29:54 gprestore:gpadmin:gpm-ipbl-etl101.gpdata.rws.local:110005-[INFO]:-Restoring pre-data metadata Pre-data objects restored: 25 / 3790 [>---------------------------------------------------] 0.66% 20210510:15:30:45 gprestore:gpadmin:gpm-ipbl-etl101.gpdata.rws.local:110005-[CRITICAL]:-ERROR: operator family "btree_geography_ops" for access method "btree" already exists (SQLSTATE 42710) 
20210510:15:30:45 gprestore:gpadmin:gpm-ipbl-etl101.gpdata.rws.local:110005-[INFO]:-Found neither /usr/local/greenplum-db-6.13.0/bin/gp_email_contacts.yaml nor /home/gpadmin/gp_email_contacts.yaml 20210510:15:30:45 gprestore:gpadmin:gpm-ipbl-etl101.gpdata.rws.local:110005-[INFO]:-Email containing gprestore report /greenplum-backup/db_dumps/gpm-ipbl-etl101/aoa_air_test/gpseg-1/backups/20210510/20210510020120/gprestore_20210510020120_20210510152925_report will not be sent 10/May/2021 15:30:45 Restore failed with return code: 2

This is caused by a duplicate entry after the creation of the database.

Environment

Product Version: 6.13

Resolution

Check for duplicates in metadata.sql file

Check the metadata.sql file for the backup and ensure that there are no duplicate entries. There may be several "CREATE OPERATOR FAMILY" entries. Verify there are are no duplicates.

CREATE OPERATOR FAMILY public.btree_geography_ops USING btree;
ALTER OPERATOR FAMILY public.btree_geography_ops USING btree OWNER TO gpadmin;

Check for PostGIS extensions

If there are create operator entries and a create PostGIS extension entry. These may conflict with each other and stop the restore process. The PostGIS extension will be created first with all the operators below.

CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public; 

The operators created by the extension call above are given below and should not be in metadata.sql:

CREATE OPERATOR FAMILY public.btree_geography_ops USING btree;
CREATE OPERATOR FAMILY public.btree_geometry_ops USING btree; 
CREATE OPERATOR FAMILY public.gist_geography_ops USING gist; 
CREATE OPERATOR FAMILY public.gist_geometry_ops_2d USING gist; 
CREATE OPERATOR FAMILY public.gist_geometry_ops_nd USING gist; 

If the backup contains the PostGIS extension and the operators, then add "--on-error-continue" during the gprestore to ignore these CREATE OPERATOR commands and continue with the restore.

0210512:17:01:26 gprestore:gpadmin:gpm-ipbl-etl101.gpdata.rws.local:100023-[DEBUG]:-Error encountered when executing statement: CREATE OPERATOR FAMILY public.btree_geography_ops USING btree; Error was: ERROR: operator family "btree_geography_ops" for access method "btree" already exists (SQLSTATE 42710)
20210512:17:01:26 gprestore:gpadmin:gpm-ipbl-etl101.gpdata.rws.local:100023-[DEBUG]:-Error encountered when executing statement: CREATE OPERATOR FAMILY public.btree_geometry_ops USING btree; Error was: ERROR: operator family "btree_geometry_ops" for access method "btree" already exists (SQLSTATE 42710)
20210512:17:01:26 gprestore:gpadmin:gpm-ipbl-etl101.gpdata.rws.local:100023-[DEBUG]:-Error encountered when executing statement: CREATE OPERATOR FAMILY public.gist_geography_ops USING gist; Error was: ERROR: operator family "gist_geography_ops" for access method "gist" already exists (SQLSTATE 42710)
20210512:17:01:26 gprestore:gpadmin:gpm-ipbl-etl101.gpdata.rws.local:100023-[DEBUG]:-Error encountered when executing statement: CREATE OPERATOR FAMILY public.gist_geometry_ops_2d USING gist; Error was: ERROR: operator family "gist_geometry_ops_2d" for access method "gist" already exists (SQLSTATE 42710)
20210512:17:01:26 gprestore:gpadmin:gpm-ipbl-etl101.gpdata.rws.local:100023-[DEBUG]:-Error encountered when executing statement: CREATE OPERATOR FAMILY public.gist_geometry_ops_nd USING gist; Error was: ERROR: operator family "gist_geometry_ops_nd" for access method "gist" already exists (SQLSTATE 42710)

Check relations created with the DB

Check the operators or objects created during the creation of the database. Specifically, check if the database inherited objects from template0.

CREATE DATABASE aoa_air_test TEMPLATE template0;

Note: Connecting to template0 may cause errors. Create a test database and check if there are any operators already created.

gpadmin=# \c template0 
FATAL: database "template0" is not currently accepting connections
Previous connection kept 

gpadmin=# create database test;
CREATE DATABASE

gpadmin=# \c test
You are now connected to database "test" as user "gpadmin".
test=#

test=# SELECT * FROM pg_opfamily where opfname = 'btree_geography_ops';
 opfmethod |       opfname       | opfnamespace | opfowner 
-----------+---------------------+--------------+----------
       403 | btree_geography_ops |         2200 |       10
(1 row)

If operators exist during the creation of the test database, then alter or recreate template0 so that these operators do not exist.