Error: Command was: SELECT pg_catalog.setval

Question

I'm unable to restore my backup DB on heroku DB but I don't have any issue if I restore on my local env and I get no errors.

When I try to restore on heroku connect database I get this following error:

C:\Program Files\PostgreSQL\9.4\bin\pg_restore.exe --host XXXXXX.compute-1.amazonaws.com --port 5432 --username "XXXXokwpyzewicl" --dbname "d9uto7paqXXX" --no-password --verbose

"C:\Users\XXXX\Desktop\loans.backup"

pg_restore: connecting to database for restore pg_restore: implied data-only restore

pg_restore: processing data for table "application_denial_reason_types"

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 5202; 0 16622 TABLE DATA application_denial_reason_types postgres

pg_restore: [archiver (db)] could not execute query: ERROR: relation "application_denial_reason_types" does not exist Command was: COPY application_denial_reason_types (denial_reason_type_id, denial_reason_type_name, description, active, created_by, creat... pg_restore: executing SEQUENCE SET application_denial_reason_types_denial_reason_type_id_seq

pg_restore: [archiver (db)] Error from TOC entry 5672; 0 0 SEQUENCE SET application_denial_reason_types_denial_reason_type_id_seq postgres

pg_restore: [archiver (db)] could not execute query: ERROR: relation "application_denial_reason_types_denial_reason_type_id_seq" does not exist LINE 1: SELECT pg_catalog.setval('application_denial_reason_types_de... ^ Command was: SELECT pg_catalog.setval('application_denial_reason_types_denial_reason_type_id_seq', 1, false);

How do I fix this error?

Vao Tsun Vao Tsun · Accepted Answer · 2016-12-05T12:16:11

It tries to set value of not existing sequence:

pg_restore: [archiver (db)] could not execute query: ERROR: relation "application_denial_reason_types_denial_reason_type_id_seq" does not exist

to understand the reason take alook at the:

pg_restore: connecting to database for restore pg_restore: implied data-only restore

If you check man pg_restore you'll find, that:

   -a
   --data-only
       Restore only the data, not the schema (data definitions). Table data, large objects, and sequence values are restored, if

present in the archive.

So you probably have data structure on your local env, and this is why you set value for existing sequence. And there is not structure on heroku, so you get error.

Remove -a from pg_restore command to run DDL prior to data restore.

Error: Command was: SELECT pg_catalog.setval

1 Answers