gpbackup error : "ERROR: operator is not unique: timestamp without time zone || unknown" on Tanzu Greenplum
search cancel

gpbackup error : "ERROR: operator is not unique: timestamp without time zone || unknown" on Tanzu Greenplum

book

Article ID: 296563

calendar_today

Updated On:

Products

VMware Tanzu Greenplum

Issue/Introduction

gpbackup reports an error:

ERROR: operator is not unique: timestamp without time zone || unknown (SQLSTATE 42725)
or
ERROR: operator is not unique: unknown || oid (SQLSTATE 42725)

Full output from gpbackup:

[gpadmin@mdw log]$ gpbackup --dbname dbname --compression-level 5 --verbose --backup-dir /data1/db_dumps_gpbackup
20220223:11:42:58 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Backup Command: [gpbackup --dbname dbname --compression-level 5 --verbose --backup-dir /data1/db_dumps_gpbackup]
20220223:11:42:58 gpbackup:gpadmin:mdw.gphd.local:788459-[INFO]:-gpbackup version = 1.24.0
20220223:11:42:58 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Initializing 1 worker connections
20220223:11:42:58 gpbackup:gpadmin:mdw.gphd.local:788459-[INFO]:-Greenplum Database Version = 5.28.0 build commit:a832433219ef0159bacde60f00df614c5f31e743 
20220223:11:42:58 gpbackup:gpadmin:mdw.gphd.local:788459-[INFO]:-Starting backup of database dbname
20220223:11:42:58 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Validating Tables and Schemas exist in Database
20220223:11:42:58 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Creating backup directories
20220223:11:42:58 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Getting database size
20220223:11:43:03 gpbackup:gpadmin:mdw.gphd.local:788459-[INFO]:-Backup Timestamp = 20220223114258
20220223:11:43:03 gpbackup:gpadmin:mdw.gphd.local:788459-[INFO]:-Backup Database = dbname
20220223:11:43:03 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Backup Parameters: {compression: gzip, plugin executable: None, backup section: All Sections, object filtering: None, includes statistics: No, data file format: Multiple Data Files Per Segment, incremental: False} 
20220223:11:43:03 gpbackup:gpadmin:mdw.gphd.local:788459-[INFO]:-Gathering table state information 
20220223:11:43:04 gpbackup:gpadmin:mdw.gphd.local:788459-[INFO]:-Acquiring ACCESS SHARE locks on tables 
20220223:11:43:09 gpbackup:gpadmin:mdw.gphd.local:788459-[INFO]:-Gathering additional table metadata 
20220223:11:43:09 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Getting column definitions 
20220223:11:43:17 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Getting distribution policies 
20220223:11:43:19 gpbackup:gpadmin:mdw.gphd.local:788459-[INFO]:-Getting partition definitions 
20220223:11:44:05 gpbackup:gpadmin:mdw.gphd.local:788459-[INFO]:-Getting storage information 
20220223:11:44:06 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Retrieving external table information 
20220223:11:44:07 gpbackup:gpadmin:mdw.gphd.local:788459-[INFO]:-Getting child partitions with altered schema 
20220223:11:44:08 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Constructing table definition map 
20220223:11:44:08 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Skipping data backup of table public.ext_14b9ac9e56a0427a92b4829052d44375 because it is either an external or foreign table.
20220223:11:44:08 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Skipping query for incremental metadata.
20220223:11:44:08 gpbackup:gpadmin:mdw.gphd.local:788459-[INFO]:-Metadata will be written to /data1/db_dumps_gpbackup/gpseg-1/backups/20220223/20220223114258/gpbackup_20220223114258_metadata.sql
20220223:11:44:08 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Writing Session Configuration Parameters to metadata file
20220223:11:44:08 gpbackup:gpadmin:mdw.gphd.local:788459-[INFO]:-Writing global database metadata
20220223:11:44:08 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Writing CREATE RESOURCE QUEUE statements to metadata file
20220223:11:44:08 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Writing CREATE RESOURCE GROUP statements to metadata file
20220223:11:44:08 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Writing CREATE ROLE statements to metadata file
20220223:11:44:08 gpbackup:gpadmin:mdw.gphd.local:788459-[CRITICAL]:-ERROR: operator is not unique: timestamp without time zone || unknown (SQLSTATE 42725)
github.com/greenplum-db/gp-common-go-libs/gplog.FatalOnError /tmp/build/3e49593f/go/pkg/mod/github.com/greenplum-db/[email protected]/gplog/gplog.go:310
github.com/greenplum-db/gpbackup/backup.GetRoles /tmp/build/3e49593f/go/src/github.com/greenplum-db/gpbackup/backup/queries_globals.go:390
github.com/greenplum-db/gpbackup/backup.backupRoles /tmp/build/3e49593f/go/src/github.com/greenplum-db/gpbackup/backup/wrappers.go:457
github.com/greenplum-db/gpbackup/backup.backupGlobals /tmp/build/3e49593f/go/src/github.com/greenplum-db/gpbackup/backup/backup.go:202
github.com/greenplum-db/gpbackup/backup.DoBackup /tmp/build/3e49593f/go/src/github.com/greenplum-db/gpbackup/backup/backup.go:143
main.main.func1 /tmp/build/3e49593f/go/src/github.com/greenplum-db/gpbackup/gpbackup.go:23
github.com/spf13/cobra.(*Command).execute /tmp/build/3e49593f/go/pkg/mod/github.com/spf13/[email protected]/command.go:860
github.com/spf13/cobra.(*Command).ExecuteC /tmp/build/3e49593f/go/pkg/mod/github.com/spf13/[email protected]/command.go:974
github.com/spf13/cobra.(*Command).Execute /tmp/build/3e49593f/go/pkg/mod/github.com/spf13/[email protected]/command.go:902 
main.main /tmp/build/3e49593f/go/src/github.com/greenplum-db/gpbackup/gpbackup.go:27 
runtime.main /usr/local/go/src/runtime/proc.go:255 
runtime.goexit /usr/local/go/src/runtime/asm_amd64.s:1581 
20220223:11:44:09 gpbackup:gpadmin:mdw.gphd.local:788459-[INFO]:-Found neither /usr/local/GP-5.28.0/bin/gp_email_contacts.yaml nor /home/gpadmin/gp_email_contacts.yaml 
20220223:11:44:09 gpbackup:gpadmin:mdw.gphd.local:788459-[INFO]:-Email containing gpbackup report /data1/db_dumps_gpbackup/gpseg-1/backups/20220223/20220223114258/gpbackup_20220223114258_report will not be sent 
20220223:11:44:09 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Beginning cleanup 20220223:11:44:09 gpbackup:gpadmin:mdw.gphd.local:788459-[DEBUG]:-Cleanup complete


This is caused by creating extra "CAST"s.  The "About Implicit Text Casting in Greenplum Database" section in the  Installation Guide refers to Readding implicit casts in PostgreSQL 8.3 to recreate implicit casts.
Some of these casts interfere with the gpbackup tool when trying to backup the roles and role membership.

 

Environment

Product Version: 5.28

Resolution

Workaround

DROP CAST (oid AS text);
DROP CAST (timestamp without time zone AS text);

 

This will need to be done to each database that is reporting the error.

Fix

VMWare are working on improving gpbackup behaviour in future maintenance release.


Powered by Wolken Software