Thread: pg_dump warnings

pg_dump warnings

From
v.demartino2@virgilio.it
Date:
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
............................

What should I do to eliminate these warnings?
Being a a posgresql newbye a step by step set of instructions will be highly
appreciated.

Ciao
Vittorio





Re: pg_dump warnings

From
Richard Huxton
Date:
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

Re: pg_dump warnings

From
Vittorio De Martino
Date:
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

Re: pg_dump warnings

From
Tom Lane
Date:
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