Re: Dump file created with pg_dump cannot be restored with psql - Mailing list pgsql-general

Oh, sorry. I mixed up dumps...

I am migrating from mysql and by mistake I tried to apply dump from "mysqldump --compat=postgresql" that was named very
similarto dump of finally converted database produced by pg_dump (for the purpose of copy from test to main server).
Bashcomletitions and then bash history (when I was reproducing problem before mailing here) made malicious joke on me.
:)

Sorry for inattention. And thank you for your answer. I found my mistake when I was looking for the exact command.

On Fri, 02 Aug 2013 17:15:28 -0700
Adrian Klaver <adrian.klaver@gmail.com> wrote:

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