Postgresql: restoring database from dump fails

Question

I am using psql (9.3.9) on Ubuntu 14.04.

I created dump of a database using command:

pg_dump db1 > db1.backup

I deleted the db and recreated it.

Trying to restore it with psql -d db1 -f /tmp/db1.backup ends with hundreds of lines of errors and does not add anything back to the db.

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>

The first outputs look like this:

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

Other suggested ways of restoring the dump fail with same errors:

postgres=# \i /tmp/db1.backup

As does this method suggested by official documentation:

psql db1 < db1.backup

Using pg_restore fails as well:

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

What is the correct way of restoring the database from the dump?

EDIT:

I found out the actual erros after sending all output to text file for researching. It seems to be related to the permissions.

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: With -v ON_ERROR_STOP=1 I was able to get this output:

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.

I gave postgres-user rights to target database with commands:

   grant all privileges on database db1 to postgres;

EDIT3: This is the exact command I used to create the dump:

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;

This also matched hundreds of lines data in my db, I had to leave those out. Might have missed some commands as well:

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>

Solution 1

It looks like the original dump has something really weird in it. There must be something like a CREATE TABLE pg_catalog.attachments statement in the dump. This shouldn't be possible, since you can't create tables in pg_catalog unless the debug option allow_system_table_mods is on.

The only other way I can imagine this happening is if, somehow, the search_path that gets set up by the pg_dump script is invalid in some way. For example, if the CREATE SCHEMA myschema doesn't get run, the search_path might be myschema,pg_catalog, which if myschema is missing would result in pg_catalog being the target of CREATE TABLE.

That shouldn't be possible with a normal pg_dump run.

Solution 2

Just adding my experience:

I had to create the schema(s) that were being restored beforehand. If the schema(s) didn't exist before being restored, the restore failed in this way. Otherwise, no problems.

Strangely, I only had this problem on one of my databases that were (AFAIK) all set up the same way, all with mostly default settings. This was the case with multiple backups. First saw this happen today after months of operation where I'd drop a schema and leave it nonexistent before restoring it from a dump. Scary...

Comments

[Craig Ringer]

Run the script using -v ON_ERROR_STOP=1 to stop at the first error.

[Madoc Comadrin]

I have added results to the original post.

[Craig Ringer]

Are you sure your dump was just created with pg_dump? I'm wondering if perhaps it was created with -t options to filter only a subset of the database, and the tables dumped are in a non-default schema.

[Madoc Comadrin]

This is command I used: root@dbhost:~# su -c "pg_dump db1 > db1.backup" postgres

[Craig Ringer]

Please show the output of head -50 db1.backup, grep search_path db1.backup, grep -i 'create schema' db1.backup, and grep attachments db1.backup. Edit the question, then comment here when done.

[Madoc Comadrin]

Results of 3 commands appear after EDIT 4 on the original post. grep -i 'create schema' finds no results.

[Madoc Comadrin]

I re-created the dump at later day using the same command and it worked. The first dump I tried must have gotten corrupted at some point.

[Deer Hunter]

Adds nothing, it seems, to the accepted post.