CODEWITHSUNDEEP
postgresqlpostgresql-9.6pg-restore
Babak
Babak

Reputation: 3976

Error in creating SEQUENCEs when restoring the PostgreSQL database

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?

Upvotes: 15

Views: 18286

Answers (3)

Setu Consulting
Setu Consulting

Reputation: 31

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

Upvotes: 3

Francisco
Francisco

Reputation: 81

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}

Upvotes: 8

Laurenz Albe
Laurenz Albe

Reputation: 246798

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.

Upvotes: 26

Related Questions