Pivotal Greenplum: 4.3.x
OS: RHEL 6.x
When trying to backup a database that has a S3 external table, the following error message is seen when restoring the database.
ERROR: protocol ""s3" does not exist.
It was found that double quotes were being inserted by the backup process either by way of pg_dump or gpcrondump. It is currently unknown why the double quotes are being inserted. Internal engineering bug ticket MPP-26763 was opened to track the investigation of this backup issue.
[gpadmin@mdw ~]$ pg_dump -s -t ext_testtable s3 -- -- Greenplum Database database dump -- SET statement_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = off; SET check_function_bodies = false; SET client_min_messages = warning; SET escape_string_warning = off; SET default_with_oids = false; -- -- Name: s3; Type: PROTOCOL; Schema: -; Owner: gpadmin -- CREATE PROTOCOL s3 ( readfunc = 'read_from_s3'); ALTER PROTOCOL s3 OWNER TO gpadmin; SET search_path = public, pg_catalog; SET default_tablespace = ''; -- -- Name: ext_testtable; Type: EXTERNAL TABLE; Schema: public; Owner: gpadmin; Tablespace: -- CREATE EXTERNAL TABLE ext_testtable ( a integer, b integer ) LOCATION ( '"s3://s3-us-east-1.amazonaws.com/gpdb/s3gpdb/test.txt config=/home/gpadmin/s3/s3.conf"' ) FORMAT 'text' (delimiter E'|' null E'' escape E'\\') ENCODING 'UTF8'; ALTER EXTERNAL TABLE public.ext_testtable OWNER TO gpadmin; -- -- Greenplum Database database dump complete -- [gpadmin@mdw ~]$
The only workaround is to manually remove the double quotes from the DDL until a fix is developed for the issue. The issue reported here is fixed in Greenplum Database 126.96.36.199.