-4

Problem:

Getting below mentioned error while importing schema from AWS Postgres to Gcloud postgres.

Error:

Import failed: 
SET 
SET 
SET 
SET 
SET set_config 
------------ 
(1 row) 
SET 
SET 
SET 
CREATE SCHEMA 
SET 
SET 
CREATE TABLE 
ERROR: syntax error at or near "AS" LINE 2: AS integer ^ 
Import error: exit status 3

I used --no-acl --no-owner --format=plain while exporting data from AWS postgres

pg_dump -Fc -n <schema_name> -h hostname -U user -d database --no-acl --no-owner --format=plain -f data.dump

I am able to import certain schemas in gcloud sql exported using same method but getting error for some other similar schemas. Table has geospatial info and postgis is already installed in destination database.

Looking for some quick help here.

deepak shakya
  • 51
  • 1
  • 7
  • 4
    So how does that `CREATE TABLE` look like that fails? Please [edit] your question (by clicking on the [edit] link below it) and add the code for it ([edit] your question do not post code in comments) –  Oct 24 '18 at 15:00
  • CREATE TABLE sample.geofences ( id integer NOT NULL, geofence public.geometry(Polygon,4326), venue_id text ); – deepak shakya Oct 24 '18 at 15:12
  • What's the line under CREATE TABLE, one that contains the word AS? – 404 Oct 24 '18 at 15:32
  • How about sharing that solution? – itsLex Oct 25 '18 at 00:47
  • My solution: Basically, I had a data dump file from postgres 10.0 with tables having 'sequence' for PK . Apparently, the way sequences along with other table data got dumped in file, was not been read properly by Gcloud postgres 9.6. That's where it was giving error "AS integer". Also, finally I did find this express in dump file which I couldn't find earlier. Hence I need to filter out this bit. CREATE SEQUENCE sample.geofences_id_seq AS integer <========= had to remove this bit to get it working START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; – deepak shakya Oct 26 '18 at 11:19

2 Answers2

2

My solution:

Basically, I had a data dump file from postgres 10.0 with tables having 'sequence' for PK . Apparently, the way sequences along with other table data got dumped in file, was not been read properly by Gcloud postgres 9.6. That's where it was giving error "AS integer". Also, finally I did find this express in dump file which I couldn't find earlier. Hence I need to filter out this bit.

CREATE SEQUENCE sample.geofences_id_seq

AS integer  <=====had to filter out this bit to get it working
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

No sure if anyone else faced this issue but i had and this solution worked for me without loosing any functionality.

Happy to get other better solutions here.

deepak shakya
  • 51
  • 1
  • 7
2

The original answer is correct, and similar answers are given for the general case. Options include:

  • Upgrading the target database to 10: this depends on what you are using in GCP. For a managed service like Cloud SQL, upgrading is not an option (though support for 10 is in the works, so waiting may be an option in some cases). It is, if you are running the database inside a Compute instance, or as a container in, e.g., App Engine (a ready instance is available from the Marketplace).
  • Downgrading the source before exporting. Only possible if you control the source installation.
  • Removing all instances of this one line from the file before uploading it. Adapting other responses to modify an already-created dump file, the following worked for me:

cat dump10.sql | sed -e '/AS integer/d' > dump96.sql

MBer
  • 2,218
  • 20
  • 34