无法将PostgreSQL10转储导入9.6数据库 [英] Can't import PostgreSQL10 dump into 9.6 database

问题描述

我需要以某种方式将v10转储文件转换为9.6兼容的文件

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

Google的Cloud SQL运行PostgreSQL 9.6版，并且我的数据库自创建以来一直在版本10上运行。

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

问题：当尝试将数据库导入Cloud SQL时，我得到发生未知错误。 死亡消息。

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

我已经尝试在导入Cloud SQL时注释掉我的postgis /其他扩展名，但无济于事

我尝试使用 psql my_96_db< my_10.sql 并获得大量错误，例如：

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
...

我尝试在v10 <$ c $上使用postgres 9.6的pg_restore c> pg_dump -Fc 命令，但不会成功导入9.6数据库。输出中许多失败之一的一个示例是

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

推荐答案

从错误中判断显示的消息，则必须编辑SQL转储，并从所有 CREATE SEQUENCE 中删除​​所有出现的 AS整数

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.

CREATE SEQUENCE的 AS data_type 子句是PostgreSQL v10中的新功能，而较旧的服务器版本将无法理解。

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

这篇关于无法将PostgreSQL10转储导入9.6数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！