Linux
Postgresql：從轉儲中恢復數據庫失敗
我
psql (9.3.9)在 Ubuntu 14.04 上使用。我使用命令創建了數據庫的轉儲：
pg_dump db1 > db1.backup我刪除了數據庫並重新創建了它。
嘗試以
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). ...其他恢復轉儲的建議方法失敗並出現相同的錯誤：
postgres=# \i /tmp/db1.backup正如官方文件建議的這種方法一樣：
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" ...EDIT2：
-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;EDIT3：這是我用來創建轉儲的確切命令：
root@dbhost:~# su -c "pg_dump db1 > db1.backup" postgres編輯4：
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轉儲中必須有類似聲明的東西。這應該是不可能的，因為pg_catalog除非調試選項allow_system_table_mods是on.我可以想像這種情況發生的唯一另一種方式是，如果腳本
search_path設置的pg_dump以某種方式無效。例如，如果CREATE SCHEMA myschema沒有執行，則search_path可能是myschema,pg_catalog，如果myschema缺少這將導致pg_catalog成為 的目標CREATE TABLE。
pg_dump這在正常執行中是不可能的。