Thread: pg_user does not exist

pg_user does not exist

From
Bethany A.Benzur
Date:
Hello,
I have been running postgres for quite some time with this problem:
whenever a user logs in or issues almost any command in the "psql"
client, they get this error:

-bash-2.05b$ psql
ERROR:  Relation "pg_user" does not exist

postgres=> \l
ERROR:  Relation "pg_user" does not exist

postgres=> \dt
ERROR:  Relation "pg_user" does not exist

etc...

At first I thought it had something to do with my using ident
authentication, but after some research it seems my "template1"
database is corrupt and missing some pretty essential elements. If I
could just build a new template1 or run initdb again, everything might
be OK... but the problem here is that there isn't much I can do without
being able to use pg_dump to export/backup my databases first!!

I even tried editing the initdb script and the postgres.bki file to
only run certain parts of the init process, like creating the views and
such... which DID eliminate the "pg_user does not exist" error, but
also created big messes with the other databases (like creating
multiple pg_database tables and erasing all other database's data).
Oops! Luckily restoring was as easy as "mv data.bak data" . :)

So, before I spend any more time playing with the my customized initdb
and postgres.bki, does anyone have any better ideas of how I might:

a) rebuild template1 to get to a point where I can pg_dump my
databases, reinstall postgres or re-initdb, then import the databases
-OR-
b) export the databases without having a pg_user view??

The server is running:
Redhat 8.0
postgresql-7.2.4-5.80

I'm not even sure if I'm on the right track here, so any help is much
appreciated!!
B.
--
Bethany A. Benzur
Computer Support Specialist IV
School of Literature, Communication, and Culture at Georgia Institute
of Technology
phone: 404.894.7632
helpdesk: lcc-support@iac.gatech.edu


Re: pg_user does not exist

From
Tom Lane
Date:
"Bethany A.Benzur" <bethany.benzur@oit.gatech.edu> writes:
> I have been running postgres for quite some time with this problem:
> whenever a user logs in or issues almost any command in the "psql"
> client, they get this error:

> -bash-2.05b$ psql
> ERROR:  Relation "pg_user" does not exist

Hmmm ... I'm wondering about version mismatches between your psql and
your backend.  Does "psql -V" agree with the backend version?

> At first I thought it had something to do with my using ident
> authentication, but after some research it seems my "template1"
> database is corrupt and missing some pretty essential elements. If I
> could just build a new template1 or run initdb again, everything might
> be OK... but the problem here is that there isn't much I can do without
> being able to use pg_dump to export/backup my databases first!!

You can rebuild template1 from template0 (... that's pretty much why
template0 exists...).  There are some gotchas in this process, see this
link for a walkthrough:
http://techdocs.postgresql.org/techdocs/pgsqladventuresep1.php

However, I am not totally convinced that that's where your problem is.
Do you see failures when you are connected to other databases besides
template1?

            regards, tom lane

Re: pg_user does not exist

From
Bethany A.Benzur
Date:
> Hmmm ... I'm wondering about version mismatches between your psql and
> your backend.  Does "psql -V" agree with the backend version?

[bj56@steel ]$ psql -V
psql (PostgreSQL) 7.2.4

> However, I am not totally convinced that that's where your problem is.
> Do you see failures when you are connected to other databases besides
> template1?

Yes, the same "pg_user does not exist" occurs with each database (hence
my not being able to dump + restore). I assume that is the case because
each database is based off of template1 when it is created.

I will try reconstructing template1 from template0 - thanks for that
link!

this a great start - thanks!
B.

--
Bethany A. Benzur
Computer Support Specialist IV
School of Literature, Communication, and Culture at Georgia Institute
of Technology
phone: 404.894.7632
helpdesk: lcc-support@iac.gatech.edu


Re: pg_user does not exist

From
Bethany A.Benzur
Date:
Argg... I guess I won't be rebuilding template1 from template0 seeing
as I have no template0!! :(

template1=> UPDATE pg_database SET datallowconn = TRUE
template1-> WHERE datname = 'template0';
UPDATE 0

I'm running out of ideas here... how will I get my databases out and
back in without pg_dump?

On Feb 18, 2004, at 9:10 AM, Bethany A.Benzur wrote:

>> Hmmm ... I'm wondering about version mismatches between your psql and
>> your backend.  Does "psql -V" agree with the backend version?
>
> [bj56@steel ]$ psql -V
> psql (PostgreSQL) 7.2.4
>
>> However, I am not totally convinced that that's where your problem is.
>> Do you see failures when you are connected to other databases besides
>> template1?
>
> Yes, the same "pg_user does not exist" occurs with each database
> (hence my not being able to dump + restore). I assume that is the case
> because each database is based off of template1 when it is created.
>
> I will try reconstructing template1 from template0 - thanks for that
> link!
>
> this a great start - thanks!
> B.
>
> --
> Bethany A. Benzur
> Computer Support Specialist IV
> School of Literature, Communication, and Culture at Georgia Institute
> of Technology
> phone: 404.894.7632
> helpdesk: lcc-support@iac.gatech.edu
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>


Re: pg_user does not exist

From
Tom Lane
Date:
"Bethany A.Benzur" <bethany@rawkawn.com> writes:
> Argg... I guess I won't be rebuilding template1 from template0 seeing
> as I have no template0!! :(

> template1=> UPDATE pg_database SET datallowconn = TRUE
> template1-> WHERE datname = 'template0';
> UPDATE 0

Hm.  I'm beginning to think you've got a transaction counter wraparound
issue.  What filenames do you have in $PGDATA/pg_clog/ ?  How long has
this database been running, and have you been doing full-database
VACUUMs at regular intervals?  (See the "routine maintenance" section of
the Admin Guide for background.)

            regards, tom lane

Re: pg_user does not exist

From
Bethany A.Benzur
Date:
Tom,
I only see this in pg_clog:

[root@steel data]# ls pg_clog/
0000

No, I had not been running Vacuum on a regular interval. :( I just
added "vacuumdb --all" to my daily cron, though...


On Feb 22, 2004, at 6:04 PM, Tom Lane wrote:

> "Bethany A.Benzur" <bethany@rawkawn.com> writes:
>> Argg... I guess I won't be rebuilding template1 from template0 seeing
>> as I have no template0!! :(
>
>> template1=> UPDATE pg_database SET datallowconn = TRUE
>> template1-> WHERE datname = 'template0';
>> UPDATE 0
>
> Hm.  I'm beginning to think you've got a transaction counter wraparound
> issue.  What filenames do you have in $PGDATA/pg_clog/ ?  How long has
> this database been running, and have you been doing full-database
> VACUUMs at regular intervals?  (See the "routine maintenance" section
> of
> the Admin Guide for background.)
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>