Thread: Superuser lost access to particular database
I have one database owned by "user1" which as of 4 days ago the superuser, pgsql, can't see any tables. I noticed I had pg_dumpalls from 4 days ago.. stuck.. upon research I discovered that if I login as the superuser to the problem database that it can not see any of the tables owned by the regular user. The superuser is able to see system tables with \dS, but none of the regular ones with \d If I login as 'user1' all the tables are there. I tried "grant all on pgsql to database <mydb>", but that did not help. Also tried to do a grant for particular table, but got error that it was not found. Tried a pg_dump as the database owner, but it didn't work. Basically I have this database that only the DB owner can use.. and the postgresql superuser can't see any tables and it is freezing the pg_dumpall process.
Francisco Reyes <lists@stringsutils.com> writes:
> I have one database owned by "user1" which as of 4 days ago the superuser,
> pgsql, can't see any tables.
Transaction ID wraparound ... I take it this is not PG 8.1?
As long as it's at least 7.4, vacuuming the system catalogs should help.
regards, tom lane
Tom Lane writes: > Francisco Reyes <lists@stringsutils.com> writes: >> I have one database owned by "user1" which as of 4 days ago the superuser, >> pgsql, can't see any tables. After furhter researching found that I could not see the tables because I have them in diferent schemas.. and the superuser didn't have them in the path. However the pg_dumpall is still freezing in that database. > Transaction ID wraparound ... I take it this is not PG 8.1? 8.1.3 Compiled from ports in FreeBSD 6.1 Stable. select version(); version ---- PostgreSQL 8.1.3 on i386-portbld-freebsd6.0, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518 > As long as it's at least 7.4, vacuuming the system catalogs should help. vacuum the database in question? From that database tried vacuum, vacuum analyze and vacuum full. Tried both as superuser and as the user that owns the database. Also tried running: vacuumdb -azv So the problem is only doing the backup. I am able to see the tables by doing \d <shema>.* The database in question is fairly small so if I could somehow dow a backup I could drop it and reload it. Trying to pg_dump as superuser or as the database owner, freezes.
Simon_Kelly@moh.govt.nz writes: > This may sound a bit weird, but have you tried logging in as user1 and > then granting the permission's to the superuser? Found that superuser could not see them because I had the tables in two schemas which the superuser did not have path to. Doing \dt <schema>.* I was able to see them. Backup still freezing though.
This may sound a bit weird, but have you tried logging in as user1 and then granting the permission's to the superuser? Simon Kelly Java Developer Information Systems Development Information Technology Shared Services Ministry of Health DDI: Mobile: http://www.moh.govt.nz mailto:Simon_Kelly@moh.govt.nz pgsql-general-owner@postgresql.org wrote on 12/09/2006 02:02:12 p.m.: > I have one database owned by "user1" which as of 4 days ago the superuser, > pgsql, can't see any tables. > > I noticed I had pg_dumpalls from 4 days ago.. stuck.. upon research I > discovered that if I login as the superuser to the problem database that it > can not see any of the tables owned by the regular user. The superuser is > able to see system tables with \dS, but none of the regular ones with \d > > If I login as 'user1' all the tables are there. > > I tried "grant all on pgsql to database <mydb>", but that did not help. > Also tried to do a grant for particular table, but got error that it was not > found. > > Tried a pg_dump as the database owner, but it didn't work. > > Basically I have this database that only the DB owner can use.. and the > postgresql superuser can't see any tables and it is freezing the pg_dumpall > process. > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq **************************************************************************** Statement of confidentiality: This e-mail message and any accompanying attachments may contain information that is IN-CONFIDENCE and subject to legal privilege. If you are not the intended recipient, do not read, use, disseminate, distribute or copy this message or attachments. If you have received this message in error, please notify the sender immediately and delete this message. **************************************************************************** ************************************************************************************* This e-mail message has been scanned for Viruses and Content and cleared by the Ministry of Health's Content and Virus Filtering Gateway *************************************************************************************
Francisco Reyes <lists@stringsutils.com> writes:
> Trying to pg_dump as superuser or as the database owner, freezes.
Define "freezes". What happens exactly --- is the pg_dump or its
backend consuming CPU, or just sitting? What do ps and pg_stat_activity
and pg_locks show that it's doing?
(I'm speculating in particular that someone is holding an exclusive lock
on one of the tables to be dumped --- if so pg_locks would tell the tale.)
regards, tom lane
Tom Lane writes: > Define "freezes". What happens exactly doing pg_dump <db> |tee <outfile> Shows nothing. This database is not very big so I would expect it to be done quickly. I tried a couple of other databases and they did the pg_dump without problems. >is the pg_dump or its backend consuming CPU, or just sitting? At 90% of my CPU. > What do ps and pg_stat_activity > and pg_locks show that it's doing? To make sure it was not a locking issue I did pg_ctl restart.. checked that there were no locks or pretty much anything going on.. and then tried again. > (I'm speculating in particular that someone is holding an exclusive lock > on one of the tables to be dumped --- if so pg_locks would tell the tale.) Doing it with a freshly restarted postgresql. I have loggin set log_min_messages = info and log_statement = 'all'. Right after I start the pg_dump there is a flury of activity, which I am putting at http://public.natserv.net/pg_dump_log.txt, but very quickly it stops producing any output to the log. I left it for about 10 minutes and nothing was showing to the log.
Francisco Reyes <lists@stringsutils.com> writes: > Tom Lane writes: >> is the pg_dump or its backend consuming CPU, or just sitting? > At 90% of my CPU. The pg_dump process, or the backend? > I have loggin set log_min_messages = info and log_statement = 'all'. > Right after I start the pg_dump there is a flury of activity, which I am > putting at http://public.natserv.net/pg_dump_log.txt, but very quickly it > stops producing any output to the log. The last query shown is SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' ORDER BY 1,2 so apparently something is fishy about the dependency data. Can you execute this query by hand and get results? It could be that pg_depend is corrupted in a way that locks up the backend trying to read it, or it could be that pg_dump is getting confused and going into a loop trying to process the data. I can't tell from this description. regards, tom lane
Tom Lane writes: > Francisco Reyes <lists@stringsutils.com> writes: >> Tom Lane writes: >>> is the pg_dump or its backend consuming CPU, or just sitting? > >> At 90% of my CPU. > The pg_dump process, or the backend? Backend. pgsql 60769 47.8 1.3 17636 4888 ?? R 11:34AM 761:15.92 postmaster: pgsql pablar [local] SELECT (postgres) > SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' ORDER BY 1,2 > so apparently something is fishy about the dependency data. Can you > execute this query by hand and get results? Nothing happens when I try to run the query. > It could be that pg_depend is corrupted in a way that locks up the > backend trying to read it, or it could be that pg_dump is getting > confused and going into a loop trying to process the data. I can't > tell from this description. What additional info can I provide? Any additional troubleshooting I can try? This one DB is preventing me from doing a pg_dumpall.
Francisco Reyes <lists@stringsutils.com> writes:
> Tom Lane writes:
>> SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' ORDER BY 1,2
>> so apparently something is fishy about the dependency data. Can you
>> execute this query by hand and get results?
> Nothing happens when I try to run the query.
So pg_dump seems off the hook. Can you run the query, attach to the
backend with gdb, and see what it's doing?
regards, tom lane
Tom Lane writes: >> Nothing happens when I try to run the query. > > So pg_dump seems off the hook. Can you run the query, attach to the > backend with gdb, and see what it's doing? Tried to set level debug5 to see if any extra output appeared in log. Nothing. Going to read up on gdb.. tried ktrace, but strangely enough got no output.. Tried several other processes to make sure I had the right syntax.. and every other process I tried produced lots of data with ktrace. Going to try the FreeBSD lists to see why and when a process would not show up in ktrace. Specially one using 90% cpu.
Francisco Reyes <lists@stringsutils.com> writes:
> Going to try the FreeBSD lists to see why and when a process would not
> show up in ktrace. Specially one using 90% cpu.
That's not hard: it's in a loop that doesn't include any kernel calls.
regards, tom lane
Tom Lane writes: > So pg_dump seems off the hook. Can you run the query, attach to the > backend with gdb, and see what it's doing? As stated on another message tried a ktrace and got nothing. Trying with gdb. Is this what you need? (gdb) bt #0 0x0811a0a9 in ExecMakeFunctionResult () #1 0x0811d0d5 in ExecQual () #2 0x0811d573 in ExecScan () #3 0x08123b52 in ExecIndexScan () #4 0x08118ab1 in ExecProcNode () #5 0x081175ac in ExecEndPlan () #6 0x08116a98 in ExecutorRun () #7 0x0819145d in PortalRun () #8 0x0819118c in PortalRun () #9 0x0818d729 in pg_plan_queries () #10 0x0819025d in PostgresMain () #11 0x0816da30 in ClosePostmasterPorts () #12 0x0816d24f in ClosePostmasterPorts () #13 0x0816b55b in PostmasterMain () #14 0x0816aec9 in PostmasterMain () #15 0x08133483 in main () That was right after entering gdb Stepping through produced this: Single stepping until exit from function index_getnext, which has no line number information. 0x08123aec in ExecReScanHashJoin () (gdb) step Single stepping until exit from function ExecReScanHashJoin, which has no line number information. 0x0811d545 in ExecScan () (gdb) step Single stepping until exit from function ExecScan, which has no line number information. 0x081239fc in ExecReScanHashJoin () (gdb) step Single stepping until exit from function ExecReScanHashJoin, which has no line number information. At that point it seemed to freeze (gdb) so I did a ktrace on it. There is a lot of output but some of it is: 47645 gdb RET sigaction 0 47645 gdb CALL wait4(0xffffffff,0xbfbfe538,0,0) 47645 gdb RET wait4 47637/0xba15 47645 gdb CALL sigaction(0x2,0xbfbfe4d0,0xbfbfe4b0) 47645 gdb RET sigaction 0 47645 gdb CALL kill(0xba15,0) 47645 gdb RET kill 0 47645 gdb CALL ptrace(PT_GETREGS,0xba15,0xbfbfe2d0,0) 47645 gdb RET ptrace 0 47645 gdb CALL ptrace(PT_GETDBREGS,0xba15,0xbfbfe440,0) 47645 gdb RET ptrace 0 47645 gdb CALL ptrace(12,0xba15,0xbfbfe280,0) 47645 gdb RET ptrace 0 47645 gdb CALL ptrace(12,0xba15,0xbfbfe280,0) 47645 gdb RET ptrace 0 47645 gdb CALL ptrace(12,0xba15,0xbfbfe280,0) 47645 gdb RET ptrace 0 47645 gdb CALL ptrace(12,0xba15,0xbfbfe280,0) 47645 gdb RET ptrace 0 47645 gdb CALL ptrace(12,0xba15,0xbfbfe280,0) 47645 gdb RET ptrace 0 47645 gdb CALL ptrace(12,0xba15,0xbfbfe280,0) 47645 gdb RET ptrace 0 47645 gdb CALL ptrace(12,0xba15,0xbfbfe280,0) 47645 gdb RET ptrace 0 Is this of any help? Something else I need to try?
Francisco Reyes <lists@stringsutils.com> writes:
> Trying with gdb.
> Is this what you need?
Unfortunately you're not getting anything very sane, which is a common
result with non-debug-enabled builds. Can you rebuild postgres with
--enable-debug (all other options the same) and try again?
regards, tom lane
Tom Lane writes: > Unfortunately you're not getting anything very sane, which is a common > result with non-debug-enabled builds. Can you rebuild postgres with > --enable-debug (all other options the same) and try again? Will do. Should I go with 8.1.4? Since I have to recompile I could install the latest. This particular installation has 8.1.3 I figure if I go with 8.1.4 and the issue goes away then at least we know the problem is solved, however we won't know what it was.
Francisco Reyes <lists@stringsutils.com> writes:
> Should I go with 8.1.4? Since I have to recompile I could install the
> latest. This particular installation has 8.1.3
Sure, use 8.1.4, just be careful not to change any configure options
from what you had.
regards, tom lane
uol2@freenet.de writes: > Francisco, > > I just read your mail in the pgsql archives. > I have the same problem: > On my server, pg_dump crashes the backend with a SELECT statement. > I'm using pgsql 8.1 on FreeBSD 6 > Did you figure out what could be the cause? > > This could be a workaround: > I issued the commands > reindexdb -s -U <SU> <DB> > and > reindexdb -U <SU> <DB> > > and pg_dump worked again. Those commands seem to have fixed my problem too. In particular pg_dumpall works. Also checked and was able to access the trouble DB with the super user. > I found out the following: > The original SELECT command > (SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend > WHERE deptype != 'p' ORDER BY 1,2) > issued by pg_dump lets the backend process eat up CPU time. > The command > (SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend > WHERE ORDER BY 1) > also never gets finished but does NOT use CPU time. > > All other variations of this command work (at least on > my server) if you drop the ORDER clause for column 1. > E.g. > (SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend > WHERE deptype != 'p') > works > and > (SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend > WHERE deptype != 'p' ORDER BY 2) > also works. > > So the "ORDER BY 1" lets postmaster hang and if it is issued together > with the WHERE clause it additionally starts eating CPU time. > > It seems to me that a nightly vacuum script somehow leads to > this bug: When this bug first ocurred, I switched off vacuum. > Quite a long time nothing happened. A few days ago I switched > it back on again and again the nightly pg_dump would fail. It has been working for me the same.. I had a nighly vacuum too. After the reindex I turned it off. I will turn it back on and see if it breaks again. I am using 8.1.3, but plan to upgrade to 8.1.5 soon. Will test with 8.1.3.. if the nighly vacuum breaks the dump, will reindex again.. then try to see if 8.1.5 has the same issue. > If you are interested in these, please let me know. Yes please.