恢复我的数据库序列后从Postgresql中的列中删除 [英] After restoring my database serial removed from column in Postgresql

问题描述

Postgresql在还原后丢失了自动增量功能。我的数据库是在Windows 10（v 10.1）上创建的，我已将其还原到Ubuntu（v 9.6）上的Postgresql。现在，我发布了一个问题，我看到版本有所不同。我没有使用任何晦涩的功能，只有表格，函数和带有序列号的列。另外，还原过程中没有任何抱怨。我检查了转储选项，但找不到导致问题的任何东西。

Postgresql lost the autoincrement feature after a restore. My database was created on Windows 10 (v 10.1) and I restored it to Postgresql on Ubuntu (v 9.6). Now that I posted the question I saw that the versions are different. I didn't use any obscure feature, only tables, functions, and columns with serials. Also, the restore process didn't complain about anything. I checked the dump options but I couldn't find anything that caused the problem.

使用Pgadmin右键单击表>脚本>在原始表上创建脚本，

With Pgadmin right-clicking the table > scripts > create a script on my original table gives this:

CREATE TABLE public.produto
(
    produto_id integer NOT NULL DEFAULT nextval('produto_produto_id_seq'::regclass),
    ...

);

在我的服务器中，已还原的数据库。

In my server, the restored database. It seems it lost the feature.

CREATE TABLE public.produto
(
        produto_id integer NOT NULL,
        ...

);

推荐答案

在恢复还原过程中没有检查错误数据库;

You didn't check for errors during restore of the database; there should have been a few.

像表这样的表转储在PostgreSQL v10中看起来是这样的（这是10.3，在10.1中看起来有些不同，但这是

A dump of a table like yours will look like this in PostgreSQL v10 (this is 10.3 and it looks slightly different in 10.1, but that's irrelevant to this case):

CREATE TABLE public.produto (
    produto_id integer NOT NULL
);

ALTER TABLE public.produto OWNER TO laurenz;

CREATE SEQUENCE public.produto_produto_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.produto_produto_id_seq OWNER TO laurenz;

ALTER SEQUENCE public.produto_produto_id_seq
    OWNED BY public.produto.produto_id;

ALTER TABLE ONLY public.produto
    ALTER COLUMN produto_id
    SET DEFAULT nextval('public.produto_produto_id_seq'::regclass);

现在的问题是 AS整数在PostgreSQL v10中引入到 CREATE SEQUENCE 中，因此该语句将因9.6中的语法错误而失败。

Now the problem is that AS integer was introduced to CREATE SEQUENCE in PostgreSQL v10, so that statement will fail with a syntax error in 9.6.

什么是

• 表的创建与第一个语句类似。

• The table is created like in the first statement.

创建该序列的第三个语句失败。

The third statement creating the sequence fails.

以下所有需要该序列的语句也会失败。

All the following statements that require the sequence will also fail.

注意：不支持通过转储和还原来降级PostgeSQL。

Note: It is not supported to downgrade PostgeSQL with dump and restore.

解决方案是手动编辑转储，直到工作为止，特别是您必须删除 AS整数或 创建序列中的AS bigint 子句。

The solution is to manually edit the dump until it works, in particular you'll have to remove the AS integer or AS bigint clause in CREATE SEQUENCE.

这篇关于恢复我的数据库序列后从Postgresql中的列中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！