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);

Hui

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.

Renferd

Why is there not a flag for backwards compatibility dumping? – Moravian

@Moravian Huh? you don't need a flag to dump an older PostgreSQL server. And you are not allowed to dump a later PostgreSQL server. That is good, because how should a 9.4 server know how to dump a v10 database correctly? – Renferd

@LaurenzAlbe v10 should know how to dump v9.x compatible backup IMHO – Thorne

@Thorne How should it dump things that didn't exist in an older release? Just silently drop them? Error out? Anyway, you should never have to downgrade, so nobody feels motivated enough to write support for that. It is hard, and it would further complicate pg_dump. – Renferd

@LaurenzAlbe I understand that. Yet too many times I had to transfer data to an older db, this was one of them 😰 – Thorne

I can understand your pain. You can do it, it's just more manual labor. – Renferd

Just for the records, this worked for backporting a dump from postgresql 10 to portgresql 9.5 too. – Vicentevicepresident

Answer 2

Following @"Laurenz Albe" suggestion, here's a python3 snippet can be used to downgrade a 10.x pg_dump script for 9.x:

#!/usr/bin/env python3
import sys

#
#  Downgrades pg_dump 10 script to 9.x
#  removing 'AS integer' from 'CREATE SEQUENCE' statement
#
#  Usage:
#       $ python3 pgdump_10_to_9.py < test10.sql > test9.sql
#  or:
#       $ cat test10.sql | ./pgdump_10_to_9.py > test9.sql
#
#  To obtain a compressed 9.x sql script from a compressed 10 sql script:
#
#       $ gunzip -c test10.sql.gz | ./pgdump_10_to_9.py | gzip > test9.sql.gz
#

inside_create_sequence = False
for row in sys.stdin.readlines():

    if inside_create_sequence and row.strip().lower() == 'as integer':
        pass
    else:
        print(row, end='', flush=True)

    inside_create_sequence = row.strip().startswith('CREATE SEQUENCE ')

Henrion

Thank you for writing this script. It made converting the file super easy. – Crystallography

a less complicated solution is to use sed, cat test10.sql | sed '/AS integer/d' > test9.sql – Cormier

Do you really want to remove "as integer" everywhere ? My purpose was to remove that line right after a "CREATE SEQUENCE" occurrence – Henrion

I can confirm this script works for downgrading PostgreSQL 11 to 9.6 too! Thanks for sharing. – Renown

If you're dealing with a Rails db/structure.sql file, here's a Ruby script that handles some additional incompatibilities: gist.github.com/reedlaw/25e768d0cb853fc40bbdebfaeabaf360 – Mulch

Thanks for the script. Works for downgrading 10.13 to 9.6. – Callable