PostgreSql : ERROR: relation "sequence" does not exist while restoring from dump file

Question

I get the following error while restoring database from dump file on server:

ERROR: relation "table_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('table_id_seq', 362, true);

• my local psql version is 10.2
• server psql version is 9.6.8

Here is my dump command:

pg_dump -U username -h localhost db_name > filename.sql

Here is my restore command on server:

psql -U username -h localhost db_name < filename.sql

Please help, Thanks.

Rutilant

Did you inspect the dump for the sequence? Are there other errors in the output? – Samy

@Samy i'm not sure if my dump file has sequence or not. Could you please help? – Rutilant

Duplicate ? https://mcmap.net/q/1170939/-after-restoring-my-database-serial-removed-from-column-in-postgresql – Feathers

Answer 1

After I got information from @clemens and make some research I found that, in my dump file on section CREATE SEQUENCE table_id_seq has a statement AS integer that why when I restored into new database it did not create the nextval() for the sequence. If I remove the statement AS integer from the CREATE SEQUENCE section it works find.

In my dump file:

CREATE SEQUENCE table_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

Remove AS integer from dump file

CREATE SEQUENCE table_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

Rutilant

the problem was that you generate the dump on postgres 10 and restore it on a lower version where the syntax "AS data_type" is not avalable – Kathernkatheryn

@Kathernkatheryn thank you for your command. I will be careful about version next time. – Rutilant

Answer 2

In my case, the sequence checking is case-sensitive. That's why I was getting the relation error. So maybe it helps some people like me who end up desperately here. I've used a double-quote inside the single quotation mark in the SQL statement.

SELECT nextval('"USER_ID_seq"');

There're some examples in the official documentation: https://www.postgresql.org/docs/9.1/functions-sequence.html

Anthemion

This really did save me a lot of time. Thank you! – Pomiculture

Oh ffs this was the answer for me, thank you brother – Supersensitive

Answer 3

You can open the dump file with any text editor (Notepad, Vim, etc.). Search for table_id_seq. You should find a statement like

CREATE SEQUENCE table_id_seq ...

If it is missing then there is something strange with your dump. You might fix that by adding

CREATE SEQUENCE table_id_seq;

immediately in front of the statement

SELECT pg_catalog.setval('table_id_seq', 362, true);

from the error message.

But this is just a hack. You were supposed to find out why the dump made that mistake. But that requires more information.

Samy

In my dump file has CREATE SEQUENCE table_id_seq ... but it's still error. – Rutilant

@Saly: So you should look into the output if there are any errors. You may add SET client_min_messages TO NOTICEto increase the log level. – Samy