Postgresql：从转储恢复数据库失败

我在Ubuntu 14.04上使用psql (9.3.9) 。

我使用命令创build了一个数据库转储：

 pg_dump db1 > db1.backup

我删除了数据库，并重新创build它。

尝试使用psql -d db1 -f /tmp/db1.backup来恢复它，并以数百行错误结束，并且不会将任何内容添加回数据库。

 Query buffer reset (cleared). psql:/tmp/db1.backup:19658: invalid command \n Query buffer reset (cleared). psql:/tmp/db1.backup:19659: invalid command \n* Query buffer reset (cleared). psql:/tmp/db1.backup:19660: invalid command \<text data from my db> Query buffer reset (cleared). psql:/tmp/db1.backup:19662: invalid command \n<text data from my db> Query buffer reset (cleared). psql:/tmp/db1.backup:19663: invalid command \n<more text data from my db>

第一个输出如下所示：

 SET SET SET SET SET SET CREATE EXTENSION COMMENT SET SET SET Query buffer reset (cleared). Query buffer reset (cleared). Query buffer reset (cleared). ...

其他build议的恢复转储的方式失败，同样的错误：

 postgres=# \i /tmp/db1.backup

正如文件所build议的这种方法一样：

 psql db1 < db1.backup

使用pg_restore失败了：

  pg_restore -d db1 /tmp/db1.backup pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

什么是从转储恢复数据库的正确方法？

编辑：

将所有输出发送到文本文件进行研究后，我发现了实际的错误。这似乎与权限有关。

 psql:/tmp/db1:50: ERROR: permission denied to create "pg_catalog.attachments" DETAIL: System catalog modifications are currently disallowed. psql:/tmp/db1:53: ERROR: schema "public" does not exist psql:/tmp/db1:64: ERROR: permission denied to create "pg_catalog.attachments_id_seq" DETAIL: System catalog modifications are currently disallowed. psql:/tmp/db1:67: ERROR: schema "public" does not exist psql:/tmp/db1:73: ERROR: relation "attachments_id_seq" does not exist psql:/tmp/db1:97: ERROR: permission denied to create "pg_catalog.auth_sources" DETAIL: System catalog modifications are currently disallowed. psql:/tmp/db1:100: ERROR: schema "public" does not exist psql:/tmp/db1:111: ERROR: permission denied to create "pg_catalog.auth_sources_id_seq" DETAIL: System catalog modifications are currently disallowed. psql:/tmp/db1:114: ERROR: schema "public" does not exist psql:/tmp/db1:120: ERROR: relation "auth_sources_id_seq" does not exist psql:/tmp/db1:137: ERROR: permission denied to create "pg_catalog.boards" ...

编辑2：与-v ON_ERROR_STOP=1我能得到这个输出：

 postgres@dbhost:$ psql -d db1 -v ON_ERROR_STOP=1 < db1.backup SET SET SET SET SET SET CREATE EXTENSION COMMENT SET SET SET ERROR: permission denied to create "pg_catalog.attachments" DETAIL: System catalog modifications are currently disallowed.

我给了postgres用户的权利，以命令的目标数据库：

  grant all privileges on database db1 to postgres;

编辑3：这是我用来创build转储的确切命令：

 root@dbhost:~# su -c "pg_dump db1 > db1.backup" postgres

EDIT4：

 postgres@dbhost:/ head -50 db1.backup -- -- PostgreSQL database dump -- SET statement_timeout = 0; SET lock_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; -- -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -- CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; -- -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; SET search_path = public, pg_catalog; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: attachments; Type: TABLE; Schema: public; Owner: mydbuser; Tablespace: -- CREATE TABLE attachments ( id integer NOT NULL, container_id integer, container_type character varying(30) DEFAULT NULL::character varying, filename character varying(255) DEFAULT ''::character varying NOT NULL, disk_filename character varying(255) DEFAULT ''::character varying NOT NULL, filesize integer DEFAULT 0 NOT NULL, content_type character varying(255) DEFAULT ''::character varying, digest character varying(40) DEFAULT ''::character varying NOT NULL, downloads integer DEFAULT 0 NOT NULL, author_id integer DEFAULT 0 NOT NULL, created_on timestamp without time zone, description character varying(255), disk_directory character varying(255) ); id integer NOT NULL,

–

 postgres@dbhost:/$ grep search_path db1.backup SET search_path = public, pg_catalog;

这也匹配我的数据库中的数百行数据，我不得不离开这些。可能也错过了一些命令：

 postgres@dbhost:/$ grep attachments db1.backup -- Name: attachments; Type: TABLE; Schema: public; Owner: dbuser; Tablespace: CREATE TABLE attachments ( ALTER TABLE public.attachments OWNER TO dbuser; -- Name: attachments_id_seq; Type: SEQUENCE; Schema: public; Owner: dbuser CREATE SEQUENCE attachments_id_seq ALTER TABLE public.attachments_id_seq OWNER TO dbuser; -- Name: attachments_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: dbuser ALTER SEQUENCE attachments_id_seq OWNED BY attachments.id; ALTER TABLE ONLY attachments ALTER COLUMN id SET DEFAULT nextval('attachments_id_seq'::regclass); -- Data for Name: attachments; Type: TABLE DATA; Schema: public; Owner: dbuser COPY attachments (id, container_id, container_type, filename, disk_filename, filesize, content_type, digest, downloads, author_id, created_on, description, disk_directory) FROM stdin; -- Name: attachments_id_seq; Type: SEQUENCE SET; Schema: public; Owner: dbuser

–

 root@dbhost:~# grep -i 'create schema' db1.backup <no results>

它看起来像原来的转储有一些非常奇怪的东西。转储中必须具有像CREATE TABLE pg_catalog.attachments语句之类的内容。这应该是不可能的，因为除非debugging选项allow_system_table_mods on否则您不能在pg_catalog创build表。

我可以想象的唯一的另一种方式是，如果，某种程度上，由pg_dump脚本设置的search_path无效。例如，如果CREATE SCHEMA myschema没有运行， search_path可能是myschema,pg_catalog ，如果myschema丢失，则会导致pg_catalog成为CREATE TABLE的目标。

这不应该是正常的pg_dump运行。

只需添加我的经验：

我必须事先创build正在恢复的模式。如果架构在还原之前不存在，则以这种方式还原失败。否则，没有问题。

奇怪的是，我只有一个我的数据库（AFAIK）都设置了相同的方式，所有的大部分默认设置这个问题。这是多个备份的情况。首先看到这发生在经过几个月的操作之后，我放弃了一个模式，并从一个转储中恢复它之前不存在。害怕…

当你尝试使用pg_dumpall来备份所有的数据库时，对于特定的数据库备份 – 当我按照以下方式进行时，我没有任何问题：

创build转储 – pg_dump -o -h <host> -U roleName -d dbName -n "\"SCHEMA\"" > schema_name.dump
在转储文件中全局更改所有者（对于较小的大小） vi schema_name.dump然后:%s/roleName/newRoleName/g或者仅用于任何大小使用sed -i -- 's/roleName/newRoleName/g' schema_name.dump
还原转储psql -U newRoleName newDbName -h <host> < schema_name.dump

检查您正在用于恢复目的的转储（备份）中的search_path。当你使用备份用户例如：postgresql在备份转储search_path将类似于设置search_path = postgres，pg_catalog，sys，dbo和在恢复的时候，如果它找不到postgresql模式，它将尝试在pg_catalog中恢复将会失败的模式。