15

UserX has following grants:

CREATE ROLE "UserX" LOGIN PASSWORD 'pass';
CREATE DATABASE "DBX" WITH OWNER="UserX" ENCODING='UTF8' TABLESPACE=pg_default CONNECTION LIMIT=-1;
GRANT CONNECT ON DATABASE "DBX" TO "UserX";
GRANT USAGE ON SCHEMA public TO "UserX";
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO "UserX";
ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO "UserX";
ALTER DEFAULT PRIVILEGES GRANT ALL ON SEQUENCES TO "UserX";

I get following errors when trying to restore its dump to other database:

pg_restore: creating SEQUENCE "public.tblX_Id_seq"
pg_restore: [archiver (db)] Error from TOC entry 218; 1259 438745 SEQUENCE tblX_Id_seq UserX
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or near "AS"
LINE 2:     AS integer
            ^
    Command was: CREATE SEQUENCE "tblX_Id_seq"
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACH...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "tblX_Id_seq" does not exist
    Command was: ALTER TABLE "tblX_Id_seq" OWNER TO "UserX";


pg_restore: creating SEQUENCE OWNED BY "public.tblX_Id_seq"
pg_restore: [archiver (db)] Error from TOC entry 3569; 0 0 SEQUENCE OWNED BY tblX_Id_seq UserX
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "tblX_Id_seq" does not exist
    Command was: ALTER SEQUENCE "tblX_Id_seq" OWNED BY "tblX"."Id";

...

pg_restore: creating DEFAULT "public.tblX Id"
pg_restore: [archiver (db)] Error from TOC entry 2995; 2604 438750 DEFAULT tblX Id UserX
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "tblX_Id_seq" does not exist
    Command was: ALTER TABLE ONLY "tblX" ALTER COLUMN "Id" SET DEFAULT nextval('"tblX_Id_seq"'::regclass);

...

pg_restore: executing SEQUENCE SET tblX_Id_seq
pg_restore: [archiver (db)] Error from TOC entry 3607; 0 0 SEQUENCE SET tblX_Id_seq UserX
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "tblX_Id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('"tblX_Id_seq"', 1573, true);
                                 ^
    Command was: SELECT pg_catalog.setval('"tblX_Id_seq"', 1573, true);

Any suggestion on what I do wrong?

Babak
  • 3,716
  • 6
  • 39
  • 56

3 Answers3

26

You are trying to restore a dump from a v10 database into an older version of PostgreSQL (CREATE SEQUENCE ... AS is new in v10).

That is not supported and won't work. You can create an SQL script with pg_restore and edit it manually. Or upgrade the destination database.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 3
    It turns out this wasn't all that hard, at least for my particular use case. I got by with: `pg_restore -F c --clean --if-exists dumpfile | sed -e '/AS integer/d' > loadme.sql` Obviously your luck depends on usage of any 10.x specific features, but if you're not... – Dave S. Jul 25 '18 at 02:09
  • Right, that's why it is better to go the supported way and using `pg_dump` from v10 to dump an older database that you want to load into v10. – Laurenz Albe Jul 25 '18 at 06:12
8

I have taken these notes:

SUPPORT ALL COMPRESSED DATABASE

pg_dump -U {usuario} -h {host} -W {database} | gzip -c > archivo.gz

RESTORE FROM BACK GZIP

gunzip -c {archivo.gz} | psql -h {host} -U postgres {database}

RESTORE FROM GZIP BACKUP FROM v10 to v9.x

gunzip -c {archivo.gz} | sed -e '/AS integer/d' | psql -U postgres -h {host} -W {database}

Michael Dautermann
  • 88,797
  • 17
  • 166
  • 215
Francisco
  • 81
  • 1
  • 3
3

I got the solution to restore the database from higher version to a lower version. if sequence error occurs during restore the database just remove As integer from dump.sql file and restore it will solve the sequence error.

For Example:

 CREATE SEQUENCE "tblX_Id_seq"
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACH...

Remove AS integer

CREATE SEQUENCE "tblX_Id_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACH...
Setu Consulting
  • 31
  • 4