Thread: pg_user does not exist
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
"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
> 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
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 >
"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
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 >