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>

Run the script using `-v ON_ERROR_STOP=1` to stop at the *first* error. — Craig Ringer, Aug 18 '15 at 02:09
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. — Craig Ringer, Aug 18 '15 at 05:58
This is command I used: `root@dbhost:~# su -c "pg_dump db1 > db1.backup" postgres` — Madoc Comadrin, Aug 18 '15 at 06:41
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. — Craig Ringer, Aug 18 '15 at 06:43
Results of 3 commands appear after EDIT 4 on the original post. `grep -i 'create schema' ` finds no results. — Madoc Comadrin, Aug 18 '15 at 06:53

score 2 · Accepted Answer · answered Aug 18 '15 at 05:56

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.

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. — Madoc Comadrin, Sep 01 '15 at 08:00

sudo · Answer 2 · 2016-08-25T05:49:52.783

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...

score 0 · Answer 3 · answered Aug 22 '15 at 05:55

When you trying to backup all database it worth using pg_dumpall, as for specific database backup - I don't have any issue when I do in the following way:

Create dump - pg_dump -o -h <host> -U roleName -d dbName -n "\"SCHEMA\"" > schema_name.dump
Change owner globally in dump file (for small size) vi schema_name.dump then :%s/roleName/newRoleName/g OR just for any size use sed -i -- 's/roleName/newRoleName/g' schema_name.dump
Restore dump psql -U newRoleName newDbName -h <host> < schema_name.dump

score 0 · Answer 4 · answered Dec 07 '15 at 09:13

0

Check for search_path in the dump(backup) which you are using for the restoring purpose. When you take backup using a user for eg:postgresql in the backup dump search_path will be something like set search_path = postgres ,pg_catalog, sys ,dbo and at the time restoration if it can not find the postgresql schema it will try to restore in pg_catalog schema which will get fails.

answered Dec 07 '15 at 09:13

Gowtham

1

Adds nothing, it seems, to the accepted post. – Deer Hunter Dec 07 '15 at 09:26

Postgresql: restoring database from dump fails

4 Answers4