Обсуждение: problems restoring 7.2.1 dump to 7.3.2

Hi,

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

    (2) 7.3.2 was installed, initdb was run and the restore was
started as in the documentation:

psql -d template1 -f dumpfile

The restore process complains about non existing relations, e.g.

---
psql:dumpfile:30: ERROR:  Relation "dam" does not exist
psql:dumpfile:38: ERROR:  Relation "dam" does not exist
---

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

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.

Any help appreciated.

Kind regards
... Ralph ...

Ralph Graulich <maillist@shauny.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

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

Ralph Graulich <maillist@shauny.de> writes:
>>> [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:
>  lastchanged   | timestamp(13) without time zone |

Hm.  I created a table in a 7.2.4 server:

ts=# create table zit(lastchanged     TIMESTAMP(13) WITHOUT TIME ZONE);
CREATE

and dumped it with 7.3.2 pg_dump:

CREATE TABLE zit (
    lastchanged timestamp(13) without time zone
);

No (16) anywhere.  And I see nothing in the CVS logs that looks like a
relevant patch between 7.2.1 and 7.2.4.  So I'm mystified why you're
seeing (16).

Now, the (13) version is still going to fail in 7.3, because we
tightened the allowed range of timestamp precisions:

ERROR:  TIMESTAMP(13) precision must be between 0 and 6

I wonder whether we should reduce that ERROR to a WARNING, and
substitute the max allowed precision instead of failing out.
As-is, it's going to be painful to load dump files containing
what had been a perfectly legitimate declaration in 7.2.
Comments anyone?

            regards, tom lane

I think the warning idea is sound.

Tom Lane wrote:
> Ralph Graulich <maillist@shauny.de> writes:
>
>>>>[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:
>> lastchanged   | timestamp(13) without time zone |
>
>
> Hm.  I created a table in a 7.2.4 server:
>
> ts=# create table zit(lastchanged     TIMESTAMP(13) WITHOUT TIME ZONE);
> CREATE
>
> and dumped it with 7.3.2 pg_dump:
>
> CREATE TABLE zit (
>     lastchanged timestamp(13) without time zone
> );
>
> No (16) anywhere.  And I see nothing in the CVS logs that looks like a
> relevant patch between 7.2.1 and 7.2.4.  So I'm mystified why you're
> seeing (16).
>
> Now, the (13) version is still going to fail in 7.3, because we
> tightened the allowed range of timestamp precisions:
>
> ERROR:  TIMESTAMP(13) precision must be between 0 and 6
>
> I wonder whether we should reduce that ERROR to a WARNING, and
> substitute the max allowed precision instead of failing out.
> As-is, it's going to be painful to load dump files containing
> what had been a perfectly legitimate declaration in 7.2.
> Comments anyone?
>
>             regards, tom lane
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

Hi Tom,


> CREATE TABLE zit (
>     lastchanged timestamp(13) without time zone
> );

I checked that issue again and compared each table where this error occurs
with each table where the error doesn't occur. Now listing what all the
affected tables have in common:

(1) contain more than one field of the type TIMESTAMP
(2) contain a primary key field generated by sequence
(3) at least on of their TIMESTAMP field is a foreign key for other tables
(4) there are views on the affected table which implicit convert the
timestamp field to another format

However I don't know how those common thing could affect pg_dump. Even
restarted the database without allowing any other connections than mine,
just to be sure it's not a problem coming from table locks or things like
that.

One other thing to mention: I created the test table you mentioned above
with only one single field and this particular table gets dumped
correctly, as gets a table dumped correctly if I recreate it from scratch.

Hummmm... any ideas what else I could check?


> Now, the (13) version is still going to fail in 7.3, because we
> tightened the allowed range of timestamp precisions:
> ERROR:  TIMESTAMP(13) precision must be between 0 and 6

So the only difference between TIMESTAMP(13) and TIMESTAMP(6) is the
the precision of the second's fraction, which means it holds only
",xxxxxx" six digits or a maximum precision of 1/999999 second?


> I wonder whether we should reduce that ERROR to a WARNING, and
> substitute the max allowed precision instead of failing out.

[x] ACK - and maybe having a switch to silently convert it on the fly
while reloading the export file.


> As-is, it's going to be painful to load dump files containing
> what had been a perfectly legitimate declaration in 7.2.
> Comments anyone?

It's really painful considering you have to edit it manually after
exporting the database, which is 481 TIMESTAMP fields in my case. Oh man,
did I mention how much I love "sed" and regular expressions? ;-)


Kind regards
... Ralph ... (now on his way to check the -Fc option and pg_restore!)

Ralph Graulich wrote:
> Hi Tom,
>
<snip>
> Hummmm... any ideas what else I could check?

send your tables/data to tom

Hi Tom,

[...]
> 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).

I read about that option and tried a few combinations, but still I can't
it the export file to be restored without any error messages. Maybe I am
doing something basic wrong or is it still necessary to manually edit the
export file and rearrange the parts' order?

Kind regards
... Ralph ...

On Thu, May 01, 2003 at 10:34:55PM +0200, Ralph Graulich wrote:
> doing something basic wrong or is it still necessary to manually edit the
> export file and rearrange the parts' order?

Yes, I believe what Tom was suggesting is that you'll have to
re-order the restore.  You can do this with the binary formats.  You
need to generate a catalogue from the dump file, and then edit that
by hand.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Hi Andrew,

> Yes, I believe what Tom was suggesting is that you'll have to
> re-order the restore.  You can do this with the binary formats.  You
> need to generate a catalogue from the dump file, and then edit that
> by hand.

Thanks for your input. Finally I managed to do a full export old from old
version - import in new version cycle without any significant errors.
Although it was a tedious work, sorting depending relations (e.g. sequence
-> table -> triggers -> views -> pl/pgsql-procedures - failing, depending
on another table, so next sequence, next table, trigger, former
pl/pgsql-procedure, ah, needs another trigger it's referring to, etc.).

It took me a whole three days to rearrange about nearly 1.400 database
objects, couting sequences, triggers, tables, views, rules and pl/pgsql
procedures. So, one thing I learned: Before extending my project, first
accomplish a working upgrade path by writing down all dependencies ;-)


Kind regards and thanks for your help again, Tom and Andrew
... Ralph ...

On Mon, May 05, 2003 at 11:05:41AM +0200, Ralph Graulich wrote:
> It took me a whole three days to rearrange about nearly 1.400 database
> objects, couting sequences, triggers, tables, views, rules and pl/pgsql
> procedures. So, one thing I learned: Before extending my project, first
> accomplish a working upgrade path by writing down all dependencies ;-)

I wonder if it would be possible to create script that would take a guess at
all the dependancies, feed that to tsort and somehow get pg_restore to
restore the DB in the right order.

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington