Обсуждение: database owner gone?
we have a problem here at a large database using postgresql.
i used it with no problems yesterday and suddenly today all
the databases were no longer visible in phppgadmin.
using pgsql -U admin <database> does give me access.
so i thought something was wrong with the usersettings. checking
these settings with \d reveales that the table no longer had
an owner.
select * from pg_user gives me only 1 user (postgres). pg_shadow
also gives 1 user. so i tried recreating the admin user with
createuser admin (as postgres user) but that failed saying admin
allready exists! so where is it now? and how do i get access
back to my database using pg_dump and phppgadmin?
thanx for any help,
Evert
"Evert Daman" <e.daman@student.tudelft.nl> writes:
> select * from pg_user gives me only 1 user (postgres). pg_shadow
> also gives 1 user. so i tried recreating the admin user with
> createuser admin (as postgres user) but that failed saying admin
> allready exists! so where is it now? and how do i get access
> back to my database using pg_dump and phppgadmin?
What PG version is this? How long has it been since you vacuumed
pg_shadow, or the other catalogs for that matter?
regards, tom lane
"Evert Daman" <e.daman@wanadoo.nl> writes:
> we had never used the vacuum before (database was up
> for about two months). after doing that too things were
> speeding up again. but that couldn't be the cause of the
> lost user could it?
It could be, if you'd managed to execute 2 billion transactions in
two months ... which seems a bit implausible. But in any case,
please read the documentation about required routine maintenance.
regards, tom lane
it was postgresql version 7.3.10 and we recovered access by dumping and recreating (new initdb) the database. everything is allright now. we had never used the vacuum before (database was up for about two months). after doing that too things were speeding up again. but that couldn't be the cause of the lost user could it? ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Evert Daman" <e.daman@student.tudelft.nl> Cc: <pgsql-admin@postgresql.org> Sent: Friday, April 07, 2006 8:39 PM Subject: Re: [ADMIN] database owner gone? > "Evert Daman" <e.daman@student.tudelft.nl> writes: > > select * from pg_user gives me only 1 user (postgres). pg_shadow > > also gives 1 user. so i tried recreating the admin user with > > createuser admin (as postgres user) but that failed saying admin > > allready exists! so where is it now? and how do i get access > > back to my database using pg_dump and phppgadmin? > > What PG version is this? How long has it been since you vacuumed > pg_shadow, or the other catalogs for that matter? > > regards, tom lane > >