Pivotal Knowledge Base

Follow

gpmigrator_mirror failed: psql: FATAL: database "\" does not exist

Problem
As part of the upgrade process steps (i.e. from Pivotal Greenplum database from 4.2.x to 4.3.x), which involves mirrors, you will need to run gpmigrator_mirror as follows.

gpmigrator_mirror $old_gphome $new_gphome

However. sometimes it could fail with error messages like the below example.

0141013:03:17:34:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-**************************************************
20141013:03:17:34:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-**************************************************
20141013:03:17:34:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-Executing state:CatUpgrade
20141013:03:17:34:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-**************************************************
20141013:03:17:34:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-**************************************************
20141013:03:17:34:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-Postmaster not running
20141013:03:17:34:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-Modifying catalog version
20141013:03:17:34:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-To 4.3
20141013:03:17:36:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-Starting new Greenplum postmaster in upgrade mode
20141013:03:17:51:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-Performing catalog transformation
20141013:03:17:51:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-... template1
Traceback (most recent call last):
File "/usr/local/greenplum-db-4.3.3.0/bin/gpmigrator_mirror", line 1097, in CatUpgrade
self.ExecuteSQLFileOnAllDBs(runfile)
File "/usr/local/greenplum-db-4.3.3.0/bin/gpmigrator_mirror", line 687, in ExecuteSQLFileOnAllDBs
transform_db(db, dboid, runfile)
File "/usr/local/greenplum-db-4.3.3.0/bin/gpmigrator_mirror", line 674, in transform_db
raise CmdError(str(cmd), result[0], result[1])
CmdError: psql:/usr/local/greenplum-db-4.3.3.0/share/postgresql/upgrade/upg2_catupgrade.sql:35: NOTICE: language "plpgsql" already exists, skipping
psql:/usr/local/greenplum-db-4.3.3.0/share/postgresql/upgrade/upg2_catupgrade_43.sql:67: ERROR: external table pg_type_load command ended with error. sh: \: command not found (seg6 slice1 sdw2:40000 pid=32345)
DETAIL:

psql: FATAL: database "\" does not exist
sh: line 4: --command: command not found
Command: execute: PGOPTIONS='-c gp_session_role=utility' \\
psql -q --set ON_ERROR_STOP=ON -p $GP_SEG_PORT \\
--command "COPY pg_catalog.pg_type FROM \\
'$GPHOME/share/postgresql/upgrade/pg_type.data' \\
WITH OIDS CSV" $GP_DATABASE $GP_USER && echo "$GP_DATABASE, $GP_SEG_PORT, done";

20141013:03:17:54:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-
20141013:03:17:54:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-**************************************************
20141013:03:17:54:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-**************************************************
20141013:03:17:54:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-Executing state:RevertMasterCopyAndCatVersion
20141013:03:17:54:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-**************************************************
20141013:03:17:54:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-**************************************************
20141013:03:17:54:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-Shutting down new Greenplum postmaster
20141013:03:21:54:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-Modifying catalog version
20141013:03:21:54:005697 gpmigrator_mirror:mdw:gpadmin-[INFO]:-To 4.2

Cause
An external web table called __pg_catalog_gpmigrator.pg_type_load will be created in upg2_catupgrade_43.sql which is executed by gpmigrator_mirror.

CREATE EXTERNAL WEB TABLE
__pg_catalog_gpmigrator.pg_type_load(database text,
port text,
status text)
execute ' PGOPTIONS=''-c gp_session_role=utility'' \\
psql -q --set ON_ERROR_STOP=ON -p $GP_SEG_PORT \\
--command "COPY pg_catalog.pg_type FROM \\
''$GPHOME/share/postgresql/upgrade/pg_type.data'' \\
WITH OIDS CSV" $GP_DATABASE $GP_USER && echo "$GP_DATABASE, $GP_SEG_PORT, done";'
format 'csv';

The "\\" in "execute" section of this external table definition should be interpreted as '\' to make it work. However there is a system parameter named standard_conforming_strings which will prevent the interpretation if it's set to ON. That will leave "\\" in the table definition and mess up command to be executed.
Steps to reproduce

template1=# show standard_conforming_strings ;
standard_conforming_strings
-----------------------------
on
(1 row)

Time: 0.325 ms
template1=# CREATE EXTERNAL WEB TABLE pg_type_load(database text, port text, status text) execute ' PGOPTIONS=''-c gp_session_role=utility'' \\ psql -q --set ON_ERROR_STOP=ON -p $GP_SEG_PORT \\ --command "COPY pg_catalog.pg_type FROM \\ ''$GPHOME/share/postgresql/upgrade/pg_type.data'' \\ WITH OIDS CSV" $GP_DATABASE $GP_USER && echo "$GP_DATABASE, $GP_SEG_PORT, done";' format 'csv';
CREATE EXTERNAL TABLE
Time: 6.257 ms
template1=# \d+ pg_type_load;
External table "public.pg_type_load"
Column | Type | Modifiers | Storage | Description
----------+------+-----------+----------+-------------
database | text | | extended |
port | text | | extended |
status | text | | extended |
Type: readable
Encoding: UTF8
Format type: csv
Format options: delimiter ',' null '' escape '"' quote '"'
Command: PGOPTIONS='-c gp_session_role=utility' \\
psql -q --set ON_ERROR_STOP=ON -p $GP_SEG_PORT \\
--command "COPY pg_catalog.pg_type FROM \\
'$GPHOME/share/postgresql/upgrade/pg_type.data' \\
WITH OIDS CSV" $GP_DATABASE $GP_USER && echo "$GP_DATABASE, $GP_SEG_PORT, done";
Execute on: all segments

template1=# select * from pg_type_load;
ERROR: external table pg_type_load command ended with error. sh: \: command not found (seg0 slice1 mdw:41230 pid=5207)
DETAIL:

psql: FATAL: database "\" does not exist
sh: line 4: --command: command not found
Command: execute: PGOPTIONS='-c gp_session_role=utility' \\
psql -q --set ON_ERROR_STOP=ON -p $GP_SEG_PORT \\
--command "COPY pg_catalog.pg_type FROM \\
'$GPHOME/share/postgresql/upgrade/pg_type.data' \\
WITH OIDS CSV" $GP_DATABASE $GP_USER && echo "$GP_DATABASE, $GP_SEG_PORT, done";

Solution

Disable "standard_conforming_strings" at system level first and restart database before starting the upgrade.

Comments

Powered by Zendesk