Thread: Relation 'pg_user' does not exist
I get the following error trying to make my backups: !2188 marauder@bofh:~$ pg_dump -h tweedledee -D ds >ds_1108.sql getTypes(): SELECT failed. Explanation from backend: 'ERROR: Relation 'pg_user' does not exist How can I fix the error without losing any of our data, or at least get my dumps working again? -- Matt Beauregard Information Technology Operations, DesignScape Ph: +61 2 9361 4233 Fx: +61 2 9361 4633
Matt Beauregard <matt@designscape.com.au> writes:
> I get the following error trying to make my backups:
> !2188 marauder@bofh:~$ pg_dump -h tweedledee -D ds >ds_1108.sql
> getTypes(): SELECT failed. Explanation from backend: 'ERROR:
> Relation 'pg_user' does not exist
Hmm, did you do something silly like delete pg_user?
It's only a view, so you could recreate it if so:
CREATE VIEW pg_user AS
SELECT
usename,
usesysid,
usecreatedb,
usetrace,
usesuper,
usecatupd,
'********'::text as passwd,
valuntil
FROM pg_shadow
regards, tom lane
On Wed, Nov 08, 2000 at 12:57:50AM -0500, Tom Lane wrote: > Matt Beauregard <matt@designscape.com.au> writes: > > I get the following error trying to make my backups: > > !2188 marauder@bofh:~$ pg_dump -h tweedledee -D ds >ds_1108.sql > > getTypes(): SELECT failed. Explanation from backend: 'ERROR: > > Relation 'pg_user' does not exist > > Hmm, did you do something silly like delete pg_user? No... I have no idea where the table went. Which database do I need to connect to in order to recreate it? I tried recreating it in template1 and got a set of interesting errors about also missing pg_views. -- Matt Beauregard Information Technology Operations, DesignScape Ph: +61 2 9361 4233 Fx: +61 2 9361 4633
Matt Beauregard <matt@designscape.com.au> writes:
>> Hmm, did you do something silly like delete pg_user?
> No... I have no idea where the table went. Which database do I need
> to connect to in order to recreate it? I tried recreating it in
> template1 and got a set of interesting errors about also missing
> pg_views.
Ugh, something's more hosed than I thought, then. What do you get
from "select * from pg_class" in template1?
regards, tom lane
On Wed, Nov 08, 2000 at 06:45:15PM -0500, Tom Lane wrote:
> Matt Beauregard <matt@designscape.com.au> writes:
> >> Hmm, did you do something silly like delete pg_user?
>
> > No... I have no idea where the table went. Which database do I need
> > to connect to in order to recreate it? I tried recreating it in
> > template1 and got a set of interesting errors about also missing
> > pg_views.
>
> Ugh, something's more hosed than I thought, then. What do you get
> from "select * from pg_class" in template1?
65 rows of stuff, including
pg_user | 0 | 70 | 0 |
0 | 0 | 0 | f | f | r |
8 | 0 | 0 | 0 | 0 | 0 | f
| t |
pg_rules | 0 | 70 | 0 |
0 | 0 | 0 | f | f | r |
3 | 0 | 0 | 0 | 0 | 0 | f
| t |
pg_views | 0 | 70 | 0 |
0 | 0 | 0 | f | f | r |
3 | 0 | 0 | 0 | 0 | 0 | f
| t |
--
Matt Beauregard
Information Technology Operations, DesignScape
Ph: +61 2 9361 4233 Fx: +61 2 9361 4633
Matt Beauregard <matt@designscape.com.au> writes:
>> Ugh, something's more hosed than I thought, then. What do you get
>> from "select * from pg_class" in template1?
> 65 rows of stuff, including
> pg_user | 0 | 70 | 0 |
> 0 | 0 | 0 | f | f | r |
> 8 | 0 | 0 | 0 | 0 | 0 | f
> | t |
> pg_rules | 0 | 70 | 0 |
> 0 | 0 | 0 | f | f | r |
> 3 | 0 | 0 | 0 | 0 | 0 | f
> | t |
> pg_views | 0 | 70 | 0 |
> 0 | 0 | 0 | f | f | r |
> 3 | 0 | 0 | 0 | 0 | 0 | f
> | t |
The plot thickens ... what did you say the error message was exactly?
Actually, what'd be really useful here is to see where the error is
being reported. Try this:
Window 1: start psql in template1
Window 2: find out PID of backend connected to psql; then, as postgres
user, run "gdb /path/to/postgres/executable" and do this:
attach PID-of-backend
break elog
cont
Window 1: issue failing query (whatever will provoke the error about
pg_user not existing)
Window 2: should get a breakpoint response. Say
bt
quit
y
and send the output from the bt command ...
regards, tom lane
On Wed, Nov 08, 2000 at 07:13:30PM -0500, Tom Lane wrote:
> The plot thickens ... what did you say the error message was
exactly?
!759 marauder@bofh:~$ pg_dump -h tweedledee ds
getTypes(): SELECT failed. Explanation from backend: 'ERROR:
Relation 'pg_user' does not exist
When trying to recreate pg_user using your command:
template1=> CREATE VIEW pg_user AS
template1-> SELECT
template1-> usename,
template1-> usesysid,
template1-> usecreatedb,
template1-> usetrace,
template1-> usesuper,
template1-> usecatupd,
template1-> '********'::text as passwd,
template1-> valuntil
template1-> FROM pg_shadow
template1-> ;
ERROR: Relation 'pg_user' already exists
(which is oddly different from the error I got the first time)
When trying to do something with pg_user in template1:
template1=> select * from pg_user;
NOTICE: mdopen: couldn't open pg_user: No such file or directory
NOTICE: mdopen: couldn't open pg_user: No such file or directory
NOTICE: mdopen: couldn't open pg_user: No such file or directory
NOTICE: mdopen: couldn't open pg_user: No such file or directory
NOTICE: mdopen: couldn't open pg_user: No such file or directory
ERROR: cannot open relation pg_user
template1=> \d pg_user
NOTICE: mdopen: couldn't open pg_views: No such file or directory
NOTICE: RelationIdBuildRelation: smgropen(pg_views): No such file or directory
NOTICE: mdopen: couldn't open pg_views: No such file or directory
NOTICE: mdopen: couldn't open pg_views: No such file or directory
NOTICE: mdopen: couldn't open pg_views: No such file or directory
ERROR: cannot open relation pg_views
template1=> \dt pg_user
NOTICE: mdopen: couldn't open pg_user: No such file or directory
NOTICE: mdopen: couldn't open pg_views: No such file or directory
NOTICE: mdopen: couldn't open pg_views: No such file or directory
NOTICE: mdopen: couldn't open pg_user: No such file or directory
NOTICE: mdopen: couldn't open pg_user: No such file or directory
NOTICE: mdopen: couldn't open pg_views: No such file or directory
NOTICE: mdopen: couldn't open pg_views: No such file or directory
NOTICE: mdopen: couldn't open pg_user: No such file or directory
NOTICE: mdopen: couldn't open pg_views: No such file or directory
NOTICE: mdopen: couldn't open pg_views: No such file or directory
ERROR: cannot open relation pg_views
> Actually, what'd be really useful here is to see where the error is
> being reported. Try this:
Tried it, couldn't get the breakpoint to trigger the first time,
restarted postgres, got a different set of messages again.
template1=> select * from pg_user;
ERROR: Relation 'pg_user' does not exist
(bt:)
#0 0x812db0c in elog ()
#1 0x806dbae in heap_openr ()
#2 0x80c0088 in addRangeTableEntry ()
#3 0x80bac8d in transformTableEntry ()
#4 0x80bacc7 in transformTableEntry ()
#5 0x80ba824 in makeRangeTable ()
#6 0x80b18d7 in parse_analyze ()
#7 0x80b0260 in parse_analyze ()
#8 0x80aff2a in parse_analyze ()
#9 0x80ba3dd in parser ()
#10 0x80f4ddb in pg_parse_and_rewrite ()
#11 0x80f50a3 in pg_exec_query_dest ()
#12 0x80f5069 in pg_plan_query ()
#13 0x80f6172 in PostgresMain ()
#14 0x80dea6c in PostmasterMain ()
#15 0x80de5a8 in PostmasterMain ()
#16 0x80dd789 in PostmasterMain ()
#17 0x80dd186 in PostmasterMain ()
#18 0x80afe8f in main ()
#19 0x80639f9 in _start ()
(then:)
template1=> CREATE VIEW pg_user AS
template1-> SELECT
template1-> usename,
template1-> usesysid,
template1-> usecreatedb,
template1-> usetrace,
template1-> usesuper,
template1-> usecatupd,
template1-> '********'::text as passwd,
template1-> valuntil
template1-> FROM pg_shadow
template1-> ;
ERROR: Illegal class name 'pg_user'
The 'pg_' name prefix is reserved for system catalogs
Dammit, the error messages won't even stay constant...
--
Matt Beauregard
Information Technology Operations, DesignScape
Ph: +61 2 9361 4233 Fx: +61 2 9361 4633
Matt Beauregard <matt@designscape.com.au> writes:
>> The plot thickens ... what did you say the error message was
> exactly?
> !759 marauder@bofh:~$ pg_dump -h tweedledee ds
> getTypes(): SELECT failed. Explanation from backend: 'ERROR:
> Relation 'pg_user' does not exist
> NOTICE: mdopen: couldn't open pg_user: No such file or directory
This is strange in itself. There should be an (empty) file named
pg_user in your $PGDATA/base/template1 directory --- is there?
What about pg_views?
> Tried it, couldn't get the breakpoint to trigger the first time,
> restarted postgres, got a different set of messages again.
> template1=> select * from pg_user;
> ERROR: Relation 'pg_user' does not exist
> (bt:)
> #0 0x812db0c in elog ()
> #1 0x806dbae in heap_openr ()
Hmm. Tracing this back makes it appear that the pg_user row in pg_class
isn't being found by an index scan. Since the row clearly is there when
you do a sequential scan, this suggests that the pg_class_relname_index
index is corrupted. If so, how'd it get that way? Is there a
pg_class_relname_index file in template1? How big is it?
One thing I am wondering about, since you mention restarting the
postmaster, is whether you're being careful to start the postmaster
in a consistent environment --- in particular, with consistent
LOCALE-related environment variable values. A number of people have
managed to produce corrupted or corrupted-acting indexes by making
entries with different LOCALE values at different times. That affects
the sort order of the index and can produce inconsistent index ordering,
thereby preventing some entries from being found. This doesn't seem
like a very likely theory for template1, since it normally is readonly
after initdb and usually hasn't got any non-ASCII names in it anyway,
but the inconsistent behavior is hard to explain without some such
factor...
regards, tom lane
On Wed, Nov 08, 2000 at 08:40:08PM -0500, Tom Lane wrote: > Matt Beauregard <matt@designscape.com.au> writes: > >> The plot thickens ... what did you say the error message was > > exactly? > > > !759 marauder@bofh:~$ pg_dump -h tweedledee ds > > getTypes(): SELECT failed. Explanation from backend: 'ERROR: > > Relation 'pg_user' does not exist > > > NOTICE: mdopen: couldn't open pg_user: No such file or directory > > This is strange in itself. There should be an (empty) file named > pg_user in your $PGDATA/base/template1 directory --- is there? > What about pg_views? Neither are there. > Hmm. Tracing this back makes it appear that the pg_user row in pg_class > isn't being found by an index scan. Since the row clearly is there when > you do a sequential scan, this suggests that the pg_class_relname_index > index is corrupted. If so, how'd it get that way? Is there a > pg_class_relname_index file in template1? How big is it? There, 16384 bytes. > One thing I am wondering about, since you mention restarting the > postmaster, is whether you're being careful to start the postmaster > in a consistent environment --- in particular, with consistent > LOCALE-related environment variable values. A number of people have That doesn't sound like something we'd ever change... I've never explicitly been careful about the environment but I can't think of a reason for it not to be the same between starts. -- Matt Beauregard Information Technology Operations, DesignScape Ph: +61 2 9361 4233 Fx: +61 2 9361 4633
Matt Beauregard <matt@designscape.com.au> writes:
>>>> NOTICE: mdopen: couldn't open pg_user: No such file or directory
>>
>> This is strange in itself. There should be an (empty) file named
>> pg_user in your $PGDATA/base/template1 directory --- is there?
>> What about pg_views?
> Neither are there.
Hmm. *Something's* been tromping on your database, then. Hard to tell
what happened from the information at hand ... but I'll bet the
corrupted index on pg_class is related somehow.
Since these are only views, the datafiles underlying them would be empty
anyway --- so you can recreate the datafiles just by doing
"touch $PGDATA/base/template1/pg_user" etc.
You could probably rebuild the corrupted index using REINDEX. I haven't
had to do that myself so I'm not sure about the procedure.
I'd definitely recommend a full dump, initdb, restore once you have
things working well enough to allow a dump. Whatever happened here,
you probably haven't seen all the effects yet :-(
regards, tom lane
On Wed, Nov 08, 2000 at 10:03:05PM -0500, Tom Lane wrote: > Matt Beauregard <matt@designscape.com.au> writes: > >>>> NOTICE: mdopen: couldn't open pg_user: No such file or directory > >> > >> This is strange in itself. There should be an (empty) file named > >> pg_user in your $PGDATA/base/template1 directory --- is there? > >> What about pg_views? > > > Neither are there. > > Hmm. *Something's* been tromping on your database, then. Hard to tell > what happened from the information at hand ... but I'll bet the > corrupted index on pg_class is related somehow. > > Since these are only views, the datafiles underlying them would be empty > anyway --- so you can recreate the datafiles just by doing > "touch $PGDATA/base/template1/pg_user" etc. I've touched pg_user and pg_group but the db still won't find them. Is it normal to not be able to create things beginning with pg_ inside template1, or has template1 lost its magic? > You could probably rebuild the corrupted index using REINDEX. I haven't > had to do that myself so I'm not sure about the procedure. I had a go at that but it doesn't seem to have fixed things. > I'd definitely recommend a full dump, initdb, restore once you have > things working well enough to allow a dump. Whatever happened here, > you probably haven't seen all the effects yet :-( Is there any way to get pg_dump to dump the data but nothing else? The databases in their current state work fine for normal select/insert/update, just not for dumping. -- Matt Beauregard Information Technology Operations, DesignScape Ph: +61 2 9361 4233 Fx: +61 2 9361 4633
Matt Beauregard <matt@designscape.com.au> writes:
>> Since these are only views, the datafiles underlying them would be empty
>> anyway --- so you can recreate the datafiles just by doing
>> "touch $PGDATA/base/template1/pg_user" etc.
> I've touched pg_user and pg_group but the db still won't find them.
> Is it normal to not be able to create things beginning with pg_ inside
> template1, or has template1 lost its magic?
Well, there's more than one "thing" involved here --- a table or view
has dozens of entries in different system catalogs, as well as the
physical file. Touching the physical file should eliminate that `mdopen'
complaint you exhibited before, but I have a bad feeling that extensive
damage has been done to your system catalogs as well.
> Is there any way to get pg_dump to dump the data but nothing else?
pg_dump uses the system catalogs to *find* the data, so there's not
much hope it will work with damaged system catalogs. You might consider
trying manual COPY commands to dump out the data from your user tables.
regards, tom lane