Обсуждение: 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
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now