Postgresql: a restauração do banco de dados do dump falha

Question

Estou usando psql (9.3.9) no Ubuntu 14.04.

Eu criei o dump de um banco de dados usando o comando:

pg_dump db1 > db1.backup

Eu apaguei o banco de dados e o recriou.

Tentar restaurá-lo com psql -d db1 -f /tmp/db1.backup termina com centenas de linhas de erros e não adiciona nada ao banco de dados.

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>

Os primeiros resultados são assim:

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

Outras formas sugeridas de restaurar o despejo falham com os mesmos erros:

postgres=# \i /tmp/db1.backup

Como esse método é sugerido pela documentação oficial:

psql db1 < db1.backup

Usar pg_restore também falha:

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

Qual é a maneira correta de restaurar o banco de dados do despejo?

EDITAR:

Eu descobri os erros reais depois de enviar toda a saída para o arquivo de texto para pesquisar. Parece estar relacionado com as permissões.

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: Com -v ON_ERROR_STOP=1 , consegui obter esta saída:

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.

Eu dei direitos de usuário do postgres para direcionar o banco de dados com comandos:

   grant all privileges on database db1 to postgres;

EDIT3: Este é o comando exato que usei para criar o despejo:

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;

Isso também combinou centenas de linhas de dados no meu banco de dados, tive que deixá-las de fora. Pode ter perdido alguns comandos também:

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>

    

Answer 1

Parece que o despejo original tem algo realmente estranho nele. Deve haver algo como uma instrução CREATE TABLE pg_catalog.attachments no dump. Isso não deve ser possível, pois você não pode criar tabelas em pg_catalog , a menos que a opção de depuração allow_system_table_mods seja on .

A única outra maneira que posso imaginar isso acontecer é se, de alguma forma, o search_path que é configurado pelo script pg_dump é inválido de alguma forma. Por exemplo, se o CREATE SCHEMA myschema não for executado, o search_path poderá ser myschema,pg_catalog , o que, se myschema estiver ausente, resultará em pg_catalog sendo o destino de CREATE TABLE .

Isso não deve ser possível com uma execução normal de pg_dump .

    

Answer 2

Apenas adicionando minha experiência:

Eu tive que criar o (s) esquema (s) que estavam sendo restaurados de antemão. Se o (s) esquema (s) não existia antes de ser restaurado, a restauração falhou dessa maneira. Caso contrário, sem problemas.

Estranhamente, só tive esse problema em um dos meus bancos de dados que estavam (AFAIK) configurados da mesma maneira, todos com configurações padrão. Este foi o caso de vários backups. Primeiro vi isso acontecer hoje depois de meses de operação em que eu deixaria um esquema e deixaria inexistente antes de restaurá-lo de um dump. Assustador ...

    

Answer 3

Quando você tenta fazer backup de todo o banco de dados, vale a pena usar pg_dumpall , como para backup de banco de dados específico - não tenho nenhum problema quando faço da seguinte maneira:

• Criar despejo - pg_dump -o -h <host> -U roleName -d dbName -n "\"SCHEMA\"" > schema_name.dump
• Altere o proprietário globalmente no arquivo de despejo (para tamanho pequeno) vi schema_name.dump then :%s/roleName/newRoleName/g OU apenas para qualquer tamanho use sed -i -- 's/roleName/newRoleName/g' schema_name.dump
• Restaurar o despejo psql -U newRoleName newDbName -h <host> < schema_name.dump

Answer 4

Verifique se há search_path no dump (backup) que você está usando para o propósito de restauração. Quando você faz backup usando um usuário por exemplo: postgresql no backup dump search_path será algo como set search_path = postgres, pg_catalog, sys, dbo e no momento da restauração se ele não puder encontrar o esquema postgresql ele tentará restaurar em pg_catalog esquema que irá falhar.