Re: problems restoring 7.2.1 dump to 7.3.2

Ralph Graulich <maillist(at)shauny(dot)de> writes:
> currently I am trying to migrate from postgreSQL 7.2.1 to postgreSQL
> 7.3.2, although I am not having any difficulties with 7.2.1, it's just for
> having the current version to check its new features.
> (1) The 7.2.1 dump was done by:
> pg_dumpall -D > dumpfile

Which version of pg_dump, 7.2 or 7.3?

> and so on. I manually checked the export file and it tries to create views
> on that relation, _before_ the relation is created (the CREATE TABLE
> statement for the relation "dam" is several hundred lines below the CREATE
> VIEW statements on this relation).

Could happen, particularly if you'd used CREATE OR REPLACE VIEW to make
the view refer to a table created later than the view originally was.
pg_dump has never been very good about this sort of thing. Sooner or
later someone will make it use dependency information to choose the
dump order (not that that will help on dumps from pre-7.3 servers :-().
In the meantime, the best available workaround is to pg_dump with -Fc or
-Ft so that you can use pg_restore's facilities for re-ordering the
objects at load time. Or manually edit the dump file (may be easier if
you dump schema and data separately).

> Second, it complains about not being able to create TIMESTAMP(16), as it
> has to be in the range of 0..6. If I look into the export file, there are
> many field definitions reading TIMESTAMP(16), however none of these
> TIMESTAMP fields were created with a length parameter of 16 in the
> original database, which was dumped.

This seems quite strange. I could not duplicate it using either 7.2 or
7.3 pg_dump from a 7.2 server. Do you recall exactly how those fields
were declared? How do they show up in psql \d commands?

regards, tom lane