无法将PostgreSQL10的转储文件导入到9.6数据库中。

Question

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

谷歌的Cloud SQL运行PostgreSQL 9.6版本，我的数据库从创建以来一直在版本10上运行。

问题出在哪里：当尝试将数据库导入Cloud SQL时，我得到了发生未知错误的致命消息。

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

我尝试使用psql my_96_db < my_10.sql，但会收到大量此类错误：

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

我尝试使用Postgres 9.6的pg_restore命令来恢复我的v10数据库，但它无法成功导入到9.6的数据库中。其中一个错误示例如下：

pg_dump -Fc

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

- jenks

Answer 1

从您展示的错误信息来看，您需要编辑SQL转储文件，并从所有CREATE SEQUENCE语句中删除所有AS integer的出现。

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

- Laurenz Albe

---

为什么没有一个用于向后兼容转储的标志？ - grokpot

@LaurenzAlbe v10应该知道如何转储与v9.x兼容的备份，这是我个人的看法。 - Mirko

@LaurenzAlbe 我明白。但是我不得不把数据转移到旧的数据库太多次了，这次又是其中之一。 - Mirko

我能理解你的痛苦。你可以做到，只是需要更多的手动劳动。 - Laurenz Albe

仅供记录，这也适用于将postgresql 10转嫁到postgresql 9.5。 - luca.vercelli

Answer 2

根据@“Laurenz Albe”的建议，以下是一个Python 3代码片段，可用于将10.x的pg_dump脚本降级为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 ')

- Mario Orlandi

---

感谢您编写这个脚本。它使文件转换变得非常容易。 - Vinay Anantharaman

使用sed可以得到一个更简单的解决方案，命令为 cat test10.sql | sed '/AS integer/d' > test9.sql。该命令将test10.sql文件中所有包含"AS integer"的行删除，并将结果保存在test9.sql文件中。 - zkanda

你真的想在所有地方删除 "as integer" 吗？我的目的是在出现 "CREATE SEQUENCE" 的语句后立即删除该行。 - Mario Orlandi

我可以确认这个脚本也适用于将PostgreSQL 11降级到9.6！感谢分享。 - insideClaw

如果你正在处理Rails的db/structure.sql文件，这里有一个Ruby脚本可以处理一些额外的不兼容性：https://gist.github.com/reedlaw/25e768d0cb853fc40bbdebfaeabaf360 - Reed G. Law

感谢提供脚本。可以将10.13降级到9.6。 - David Lemaitre