9

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.

clemens
  • 16,716
  • 11
  • 50
  • 65
Saly
  • 1,446
  • 3
  • 11
  • 18

3 Answers3

14

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;
Saly
  • 1,446
  • 3
  • 11
  • 18
  • 7
    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 – gagalago Jul 31 '18 at 13:00
  • @gagalago thank you for your command. I will be careful about version next time. – Saly Aug 01 '18 at 04:12
4

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.

clemens
  • 16,716
  • 11
  • 50
  • 65
  • In my dump file has CREATE SEQUENCE table_id_seq ... but it's still error. – Saly Mar 19 '18 at 09:03
  • @Saly: So you should look into the output if there are any errors. You may add `SET client_min_messages TO NOTICE`to increase the log level. – clemens Mar 19 '18 at 17:12
4

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

Fırat Küçük
  • 5,613
  • 2
  • 50
  • 53