Обсуждение: sanity error with pg_dump on postgresql 7.0.2

I am forwarding this on behalf of bain@tcsn.co.za who is at a client
site and cannot email.  He's found a solution to the problem but
wants to know if there are any other issues.  Hopefully, this will
document the problem/solution in one tidy place.

Please cc any replies to both him and me.

###

Hi there :)

I'm having a problem updating from a default Mandrake 8.0 postresql
(7.0.3) insallation to 7.3. When I run pg_dump/pg_dumpall I get error
"failed sanity check, table trading was not found"

This is an installation I did for a client over a year ago and they
have java apps using it. They also "maintain" the database
accesslists. I only fix things that break.

I did a search and found a mail stating that its possibly related to
a deleted user having ownership of the table.
"http://www.geocrawler.com/mail/msg.php3?msg_id=5653159&list=10" I
checked and indeed the owner that created the table no longer exists.
Unfortunately I have no idea who that user was. I have also tried to
change the pg_user with a new user to match the missing sysid and
pg_tables setting to match a valid user sysid, but cannot seem to
change them.

I desperately need this database dumped and moved. can anybody give
some insign into how I can get around the problem?

Thank you
Henti Smith
bain@tcsn.co.za

------- End of forwarded message -------

Just before I sent this email, Henti told me of this solution which
he arrived at:

###

thanks for dan@langille.org and all the guys on #postgresql
(irc.openprojects.org)
for all their help and suggestions.

The problem is solved.

1.) psql dbname
2.) select * from pg_table;

    this lists all th detaild of the tables in the DB.
    some might have owner set to "unknown (sysid=xx)"
    this is the problem. (note the sysid number)

3.) createuser -i xx (sysid user of the unknown table owner)
4.) pg_dump / pg_dumpall

This solves the "failed sanity check, table XXXXXX was not found"
error when running
pg_dump / pg_dumpall

------- End of forwarded message -------


Feedback on this solution is appreciated.


--
Dan Langille : http://www.langille.org/

"Dan Langille" <dan@langille.org> writes:
> I am forwarding this on behalf of bain@tcsn.co.za who is at a client
> site and cannot email.

> I did a search and found a mail stating that its possibly related to
> a deleted user having ownership of the table.
> "http://www.geocrawler.com/mail/msg.php3?msg_id=5653159&list=10" I
> checked and indeed the owner that created the table no longer exists.
> Unfortunately I have no idea who that user was.

You don't need to know.  Get the old owner's sysid with
    SELECT relowner FROM pg_class WHERE relname = 'busted_table';
then create a user with the appropriate sysid:
    CREATE USER foo WITH SYSID nnn;

Once you've done that, consider upgrading to a more recent Postgres.
It's been a good long while since pg_dump would choke on this situation.

            regards, tom lane

On 7 Jan 2003 at 10:29, Tom Lane wrote:

> "Dan Langille" <dan@langille.org> writes:
> > I am forwarding this on behalf of bain@tcsn.co.za who is at a client
> > site and cannot email.
>
> > I did a search and found a mail stating that its possibly related to
> > a deleted user having ownership of the table.
> > "http://www.geocrawler.com/mail/msg.php3?msg_id=5653159&list=10" I
> > checked and indeed the owner that created the table no longer
> > exists. Unfortunately I have no idea who that user was.
>
> You don't need to know.  Get the old owner's sysid with
>  SELECT relowner FROM pg_class WHERE relname = 'busted_table';
> then create a user with the appropriate sysid:
>  CREATE USER foo WITH SYSID nnn;

I think that's pretty much what he did:

1.) psql dbname
2.) select * from pg_table;

    this lists all th detaild of the tables in the DB.
    some might have owner set to "unknown (sysid=xx)"
    this is the problem. (note the sysid number)

3.) createuser -i xx (sysid user of the unknown table owner)
4.) pg_dump / pg_dumpall

> Once you've done that, consider upgrading to a more recent Postgres.
> It's been a good long while since pg_dump would choke on this
> situation.

FWIW, I think upgrade is the reason for the pg_dump.

--
Dan Langille : http://www.langille.org/

Hello,

I am just wondering why Postgresql allow to drop a user without checking if he has any created tables/objects with him? Is it going to be optimized in the future release?

Regards.

Helen L.

 Dan Langille <dan@langille.org> wrote:

On 7 Jan 2003 at 10:29, Tom Lane wrote:

> "Dan Langille" writes:
> > I am forwarding this on behalf of bain@tcsn.co.za who is at a client
> > site and cannot email.
>
> > I did a search and found a mail stating that its possibly related to
> > a deleted user having ownership of the table.
> > "http://www.geocrawler.com/mail/msg.php3?msg_id=5653159&list=10" I
> > checked and indeed the owner that created the table no longer
> > exists. Unfortunately I have no idea who that user was.
>
> You don't need to know. Get the old owner's sysid with
> SELECT relowner FROM pg_class WHERE relname = 'busted_table';
> then create a user with the appropriate sysid:
> CREATE USER foo WITH SYSID nnn;

I think that's pretty much what he did:

1.) psql dbname
2.) select * from pg_table;

this lists all th detaild of the tables in the DB.
some might have owner set to "unknown (sysid=xx)"
this is the problem. (note the sysid number)

3.) createuser -i xx (sysid user of the unknown table owner)
4.) pg_dump / pg_dumpall

> Once you've done that, consider upgrading to a more recent Postgres.
> It's been a good long while since pg_dump would choke on this
> situation.

FWIW, I think upgrade is the reason for the pg_dump.

--
Dan Langille : http://www.langille.org/


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org