Re: problems restoring 7.2.1 dump to 7.3.2

Hi Tom,

> > pg_dumpall -D > dumpfile
> Which version of pg_dump, 7.2 or 7.3?

As stated in the documentation I read carefully, I used the pg_dump
version freshly compiled from 7.3.2 (dump from older database using
current pg_dump version of the version you want to restore the dump).

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

You mean creating or replacing views during the building process of the
database? I can recall the whole process of creating the database as I
stored all the necessary statements in a large SQL file. I just checked it
and the tables were always created befor anything else needing the tables
(e.g. sequences first -> tables -> views -> procedures).

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

I will look into this later this evening, as I am currently upgrading my
development machine (good luck I didn't immediately upgrade the production
machine *g*). So I have plenty of time on my hands to figure it out and
come back with more questions for sure.

[Timestamp problem]

> 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?

Double checked the table definitions in the reactivated 7.2.1 version:

\d dam

[...]
lastchanged | timestamp(13) without time zone |
[...]

Saving everything from the start, I was able to get the original CREATE
TABLE statement out of the backup storage:

--
-- create table dam
--
-- audit trail
--
-- 18-JUL-2002 rg first version
[...]
CREATE TABLE dam
(
rowid BIGINT DEFAULT nextval('sq_dam_rowid'),
[...] lots more fields
lastchanged TIMESTAMP(13) WITHOUT TIME ZONE,
createdate DATE NOT NULL DEFAULT now()
);

Didn't complain about anything being wrong. However this field gets dumped
as TIMESTAMP(16), which 7.3.2 can't import. This is not the only table
containing this TIMESTAMP field. All other tables contain this field
accordingly for version history purposes.

Kind regards
... Ralph ... still puzzled