恢复PostgreSQL数据库时创建SEQUENCE时出错

本文介绍了恢复PostgreSQL数据库时创建SEQUENCE时出错的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

UserX 有以下授予：

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?

推荐答案

您正在尝试将v10数据库中的转储还原到旧版本的PostgreSQL（ CREATE SEQUENCE ... AS 是

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.