2

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.

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

);
Vignesh VS
  • 921
  • 1
  • 14
  • 30
Diego Alves
  • 2,462
  • 3
  • 32
  • 65
  • I notice the problem when my functions started to fail. "produto_id" cannot be null. – Diego Alves Mar 12 '18 at 12:52
  • You are importingto an older version (and probably ignoring some errors). See: https://stackoverflow.com/a/49209191/905902 – wildplasser Mar 12 '18 at 21:17
  • A workaround could be: dump the DDL and the copy/data separately, and do some serious editing.Or: upgrade the target to Postgres-10.x – wildplasser Mar 12 '18 at 21:48

1 Answers1

2

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

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

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.

What is the consequence?

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

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

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263