Re: trouble migrating large objects from 7.1.3 to 7.3.2

"John M. Layman" <jml(at)frijid(dot)net> writes:
> [ various problems... ]

> pg_restore: [archiver (db)] could not execute query: ERROR: Relation
> "disc_idx_seq" does not exist

I couldn't reproduce this running the dump file you were kind enough to
send me off-list.

> I tried dropping the sequence & re-running the restore, but that just got me
> into more trouble. I'm guessing that I should let pg_restore create the
> database for me (instead of issuing a createdb command),

Not necessarily, but you do usually want to start from an empty database
--- dropping just one object is not going to work.

> but I can't get the -c or -C options to work.

I think this is mostly pilot error, probably compounded by
insufficiently clear explanations in the docs. It might help to think
about what each of these switches actually does. "-c" simply causes
pg_restore to issue a "DROP foo" command immediately before each of its
"CREATE foo" commands. (That's at the individual-object level, I don't
think it applies to the whole database.) This switch strikes me as of
little use --- it's always faster to just drop the whole database before
you start the restore. (I suppose it might come in handy for certain
kinds of database-merge operations, though.) What the "-C" switch does
is cause pg_restore to issue a "CREATE DATABASE dbname" and then
"\connect dbname" before it starts restoring individual objects. The
gotcha here is you cannot say "-d dbname" in the pg_restore command
line, because dbname doesn't exist yet. You have to tell it to
initially connect to some DB that *does* exist, from whence it can issue
the CREATE DATABASE command. So usually "-C" would go along with
"-d template1".

(If you can think of a better way to explain all this, patches for the
documentation would be most welcome.)

> pg_restore: [archiver (db)] connection to database "dbase" failed: FATAL:
> parser zero-length delimited identifier at or near """" at character 47

This error confuses me though --- it doesn't square with the above
considerations, and I couldn't reproduce it with your schema dump.
Could you try it with statement logging enabled, and look to see exactly
what commands pg_restore issued that led up to the failure?

regards, tom lane