☰

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

tag-icon • tag-icon linux database postgresql psql
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-user 对目标数据库的权限：

   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>

答案1

看起来原始转储中确实有一些奇怪的东西。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。

答案2

仅补充一下我的经验：

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

奇怪的是，我只在我的一个数据库中遇到了这个问题，这些数据库（据我所知）都以相同的方式设置，并且大多采用默认设置。这是多次备份的情况。今天第一次看到这种情况，经过几个月的操作后，我删除了一个架构并让它不存在，然后从转储中恢复它。可怕...

答案3

当你尝试备份所有数据库时，值得使用pg_dumpall至于特定的数据库备份 - 当我按照以下方式执行时，我没有遇到任何问题：

  • 创建转储 -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

答案4

检查您用于恢复目的的转储（备份）中的 search_path。当您使用用户（例如：postgresql）进行备份时，备份转储中的 search_path 将类似于 set search_path = postgres,pg_catalog, sys,dbo，并且在恢复时，如果它找不到 postgresql 模式，它将尝试在 pg_catalog 模式中恢复，但会失败。

相关内容