Can't import PostgreSQL10 dump into 9.6 database

Question

I need to somehow convert a v10 dump file into one which is 9.6 compatible

Google's Cloud SQL runs PostgreSQL version 9.6 and my database has been running on version 10 since its creation.

THE ISSUE: When trying to import the database into Cloud SQL, I get the an unknown error has occurred. message of death.

I have already tried commenting out my postgis / other extensions when importing to Cloud SQL but, to no avail.

I have tried using using psql my_96_db < my_10.sql and get tons of errors like this:

...
CREATE TABLE
ERROR:  syntax error at or near "AS"
LINE 2:     AS integer
            ^
ERROR:  relation "authentication_phonecontact_id_seq" does not exist
CREATE TABLE
...

I have tried using postgres 9.6's pg_restore on my v10 pg_dump -Fc command, but it will not successfully import into a 9.6 database. An example of one of the many failures in the output is

pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.authentication_referral_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public.authentication_referral_id_...
                                 ^
    Command was: SELECT pg_catalog.setval('public.authentication_referral_id_seq', 1, false);

Answer 1

Judging from the error messages you show, you'll have to edit the SQL dump and remove all occurrences of AS integer from all CREATE SEQUENCE statements.

The AS data_type clause of CREATE SEQUENCE is new in PostgreSQL v10, and older server versions will not understand it.