我有一个使用以下命令完成的 PostgreSQL 13 转储文件:
$ sudo -u postgres pg_dump --no-privileges -Fc -n my_schema --file="/path/to/my_schema.sql" my_database
--clean --if-exists
但是,当我尝试将其恢复到新的数据库(PG15)时,由于该选项,我遇到了一个意想不到的问题:
$ sudo -u postgres pg_restore --single-transaction --clean --if-exists --schema=my_schema --no-owner --role="app-user" --dbname=my_database /path/to/my_schema.sql
pg_restore: error: could not execute query: ERROR: schema "my_schema" does not exist
LINE 1: CREATE TABLE my_schema.my_table (
^
Command was: CREATE TABLE my_schema.my_table (
id integer NOT NULL,
name character varying(128),
description text
);
在实际创建表之前不应该重新创建模式吗?
如果这有帮助,这里是转储文件的相关标题(抱歉,这是一种自定义二进制格式):
$ head -n27 my_schema.sql
PGDMP0my_database13.16 (Debian 13.16-0+deb11u1)13.16 (Debian 13.16-0+deb11u1)-=0ENCODINENCODINGSET client_encoding = 'UTF8';
false=00
STDSTRINGS
STDSTRINGS(SET standard_conforming_strings = 'on';
false=00
SEARCHPATH
SEARCHPATH8SELECT pg_catalog.set_config('search_path', '', false);
false=126217698my_databasDATABASEgCREATE DATABASE my_database WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'en_US.UTF-8';
"DROP DATABASE my_database;
postgresfalse2615200013my_schemaSCHEMACREATE SCHEMA my_schema;
DROP SCHEMA my_schema;
app-userfalseo12592000138my_tableTABLECREATE TABLE my_schema.my_table (
id integer NOT NULL,
name character varying(128),
description text
);
!DROP TABLE my_schema.my_table;
my_schemaheaapp-userfalse14p12592000144my_table_id_seSEQUENCE}CREATE SEQUENCE my_schema.my_table_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
+DROP SEQUENCE my_schema.my_table_id_seq;
my_schemaapp-userfalse62314=00my_table_id_seqSEQUENCE OWNED BYMALTER SEQUENCE my_schema.my_table_id_seq OWNED BY my_schema.my_table.id;
我能做什么吗?为什么会出现这个错误?
--schema=my_schema
省略带有 的选项pg_restore
。该选项仅导致恢复架构中的对象,而不是架构本身。