pg_dump warnings

v.demartino2@virgilio.it wrote:

Context:Postgresql 7.4.6 under linux gentoo

To move a postgres DB from a FreeBSD box to al linux box I pg_dumped the
archive from the FreeBSD box and pg_restored into the linux box: Now when
I pg_dump the archive I get the following warnings for every table in the
archive:
.........................
pg_dump: WARNING: owner of table "atecu91" appears to be invalid
pg_dump: WARNING: owner of table "letture24btdom" appears to be invalid

Who owns tables "atecu91" and "letture24btdom" on the FreeBSD box?
(tip: "\dt atecu91" will show this in psql)

Does this user exist on the Linux box?
("SELECT * FROM pg_user")

--
Richard Huxton
Archonet Ltd

Alle 11:57, lunedì 14 febbraio 2005, Richard Huxton ha scritto:

v.demartino2@virgilio.it wrote:

Context:Postgresql 7.4.6 under linux gentoo

To move a postgres DB from a FreeBSD box to al linux box I pg_dumped the
archive from the FreeBSD box and pg_restored into the linux box: Now
when I pg_dump the archive I get the following warnings for every table
in the archive:
.........................
pg_dump: WARNING: owner of table "atecu91" appears to be invalid
pg_dump: WARNING: owner of table "letture24btdom" appears to be invalid

Who owns tables "atecu91" and "letture24btdom" on the FreeBSD box?
(tip: "\dt atecu91" will show this in psql)

Does this user exist on the Linux box?
("SELECT * FROM pg_user")

Unfortunately, having only one server, I dumped the db from FreeBSD into the
office LAN via SAMBA then I compiled linux gentoo on the same server
eliminating FreeBSD. Finally I restored the db from the SAMBA share into the
new linux box.
Therefore, what I now see is that \dt atecu91 gives no owner.

How could I set the owner for the tables?

Ciao
Vittorio

Richard Huxton <dev@archonet.com> writes:

v.demartino2@virgilio.it wrote:

pg_dump: WARNING: owner of table "atecu91" appears to be invalid
pg_dump: WARNING: owner of table "letture24btdom" appears to be invalid

Who owns tables "atecu91" and "letture24btdom" on the FreeBSD box?

Evidently nobody. Postgres doesn't currently stop you from dropping a
user who owns tables (Alvaro is working on fixing that for 8.1, though).

These warnings aren't fatal by any means, but if you want to get rid of
them, you can either reassign the tables to some other user (see ALTER
TABLE OWNER), or resurrect the original owner, taking care to give him
the same "usesysid" as before (CREATE USER joe WITH SYSID nnn). The
hard part is to determine what that sysid was; I think you'd need to do
something like SELECT relowner FROM pg_class WHERE relname = 'atecu91'.

regards, tom lane