From: | tot-to <tot-to(at)tot-to(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Dump file created with pg_dump cannot be restored with psql |
Date: | 2013-08-03 00:03:26 |
Message-ID: | 20130803020326.7472d956@tot-to.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2013-08-03 00:15:28 | Re: Dump file created with pg_dump cannot be restored with psql |
Previous Message | BladeOfLight16 | 2013-08-02 22:04:19 | Re: Re: Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!! (nearly fixed) |