Re: Dump file created with pg_dump cannot be restored with psql

On 08/02/2013 05:03 PM, tot-to wrote:
> I have two installation of postgresql-server-9.2.4 on Gentoo.
>
> I try to just copy database from one to another.
>
> According to the documentation http://www.postgresql.org/docs/9.2/static/backup-dump.html I created dump file:
> psql -U <role> <database> > dumpfile.sql

I would tend to doubt it, I would guess you actually used pg_dump.
Could you show the exact command line you used?

FYI a better source of documentation can be found here:

http://www.postgresql.org/docs/9.2/interactive/app-pgdump.html
http://www.postgresql.org/docs/9.2/interactive/app-pgrestore.html

>
> copied it to another machine, manually created the same role, database and schema (owned by user) as in the first machine and tried to restore:
> psql -f dumpfile.sql -U <role> <database>
>
> It produces a lot of errors starting from the first command in dumpfile:
> DROP TABLE "archive" CASCADE\g
>
> that produces error:
> ERROR: table "archive" does not exist
>
> The second comand is creation of this table:
> CREATE TABLE "archive" (
> "ar_namespace" int NOT NULL DEFAULT '0',
> "ar_title" bytea NOT NULL DEFAULT '',
> "ar_text" bytea NOT NULL,
> "ar_comment" bytea NOT NULL,
> "ar_user" int CHECK ("ar_user" >= 0) NOT NULL DEFAULT '0',
> "ar_user_text" bytea NOT NULL,
> "ar_timestamp" bytea NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
> "ar_minor_edit" smallint NOT NULL DEFAULT '0',
> "ar_flags" bytea NOT NULL,
> "ar_rev_id" int CHECK ("ar_rev_id" >= 0) DEFAULT NULL,
> "ar_text_id" int CHECK ("ar_text_id" >= 0) DEFAULT NULL,
> "ar_deleted" smallint CHECK ("ar_deleted" >= 0) NOT NULL DEFAULT '0',
> "ar_len" int CHECK ("ar_len" >= 0) DEFAULT NULL,
> "ar_page_id" int CHECK ("ar_page_id" >= 0) DEFAULT NULL,
> "ar_parent_id" int CHECK ("ar_parent_id" >= 0) DEFAULT NULL,
> "ar_sha1" bytea NOT NULL DEFAULT '',
> "ar_content_format" bytea DEFAULT NULL,
> "ar_content_model" bytea DEFAULT NULL
> ) ;
>
> it produces error:
> ERROR: invalid input syntax for type bytea
>
> and so on... a lot of error relation/table does not exist.
>
> In the initial database field "ar_title" in table "archive" has type "text", not "bytea".
>
> At the end I have only 45 tables of 51 in my new database.
>
> In the man page of pg_dump I can see option -c, --clean that as far as I understand should activate existance of DROP TABLE commands in dump. I didn't add this option but have such commands. Why?

Probably depends on the switches you gave to pg_dump and how you wrote
them out. That is why the exact command you gave to create the dump is
necessary.

>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com