Thread: dump

I'm having a problem dumping a database.  I'm not the original
author of the database, and the schema is a bit unfamiliar.  It's
possible that something is really wonked deep down in the data...

[prompt]$ pg_dump -s smile
NOTICE:  Unrecognized variable client_encoding
getTables(): relation 'users': cannot find function with oid 1644 for
trigger RI_ConstraintTrigger_196192

I understand neither the NOTICE, nor the error (which appears to
be fatal).

The oid does not exist in 'users' (or, indeed, in any other table
in the database).  PostgreSQL appears to be version 7.0.
(SELECT version(); gives
PostgreSQL 7.0.0 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66)

The goal, here, is to get the information and structure from this
installation into another installation; I have PostgreSQL 7.1 and
change installed on another box, and need the database on the new
box.  My thought is that the easiest way to accomplish this is to
pg_dump -d and read it in on the new installation.  If there's a
simpler or better way, please advise.

Indeed, please advise!

Thanks in advance,

  -- Matt

Matt Block writes:

> [prompt]$ pg_dump -s smile
> NOTICE:  Unrecognized variable client_encoding
> getTables(): relation 'users': cannot find function with oid 1644 for
> trigger RI_ConstraintTrigger_196192

The notice indicates that you are using a pg_dump with multibyte support
and a server without.  That's no problem by itself, but it could be a hint
that you have a version mismatch beyond that.

The function with the oid 1644 should be built-in.  Grepping the source
code for the error message I have reasons to believe that you are using
pg_dump from PostgreSQL 6.5, which to no surprise cannot handle
referential integrity (which was added in 7.0).  The solution would be to
use pg_dump from PostgreSQL 7.0.* or 7.1.2 or later (not in between).

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Out of curiosity, and in light of some versioning woes I've
recently experienced, is there a clever way of keeping to
servers with wildly different versions in sync with one
another?  I had thought that I'd hack up something clever
in Perl to do it, and even noticed that such a thing already
exists, when it occurred to me that DBI may be built on
the local pgsql libraries, which may offer different
functionality than the host server.

Is this a concern, or does it matter when the service
is remote?

  -- Matt