From e.daman@student.tudelft.nl Fri Mar 31 11:16:11 2006 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id B46CC11F68F7 for ; Fri, 31 Mar 2006 07:16:11 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 91666-01 for ; Fri, 31 Mar 2006 07:16:05 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 7458D11F68DA for ; Fri, 31 Mar 2006 07:16:03 -0400 (AST) Received: from mailservice.tudelft.nl (mailservice.tudelft.nl [130.161.131.5]) by svr4.postgresql.org (Postfix) with ESMTP id E66BB5AF068 for ; Fri, 31 Mar 2006 11:16:03 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by rav.antivirus (Postfix) with ESMTP id D89A822F01C for ; Fri, 31 Mar 2006 13:15:59 +0200 (CEST) Received: from smtp-a.tudelft.nl (smtp-a.tudelft.nl [130.161.129.18]) by mx1.tudelft.nl (Postfix) with ESMTP id 7FF8722EF7F for ; Fri, 31 Mar 2006 13:15:59 +0200 (CEST) Received: from B100 (cable-175-135.zeelandnet.nl [82.176.175.135]) (using TLSv1 with cipher RC4-MD5 (128/128 bits)) (No client certificate requested) by smtp-a.tudelft.nl (Postfix) with ESMTP id 671DBF1A37 for ; Fri, 31 Mar 2006 13:15:59 +0200 (CEST) Message-ID: <002801c654b4$816eb340$91512b0a@B100> From: "Evert Daman" To: Subject: database owner gone? Date: Fri, 31 Mar 2006 13:16:06 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0025_01C654C5.44BA5330" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2670 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670 X-Virus-Scanned: by amavisd-new at tudelft.nl X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200604/71 X-Sequence-Number: 21240 This is a multi-part message in MIME format. ------=_NextPart_000_0025_01C654C5.44BA5330 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 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 ------=_NextPart_000_0025_01C654C5.44BA5330 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
we have a problem here at a large = database using=20 postgresql.
i used it with no problems yesterday = and suddenly=20 today all
the databases were no longer visible in = phppgadmin.
 
using pgsql -U admin <database> = does give me=20 access.
so i thought something was wrong with = the=20 usersettings. checking
these settings with \d reveales that = the table no=20 longer had
an owner.
 
select * from pg_user gives me only 1 = user=20 (postgres). pg_shadow
also gives 1 user. so i tried = recreating the admin=20 user with
createuser admin (as postgres user) but = that failed=20 saying admin
allready exists! so where is it now? = and how do i=20 get access
back to my database using pg_dump and=20 phppgadmin?
 
thanx for any help,
Evert
 
 
------=_NextPart_000_0025_01C654C5.44BA5330-- From tgl@sss.pgh.pa.us Fri Apr 7 18:39:36 2006 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 3B9BD11F6C3B for ; Fri, 7 Apr 2006 15:39:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20261-05 for ; Fri, 7 Apr 2006 15:39:31 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id BFFAE11F6C2E for ; Fri, 7 Apr 2006 15:39:31 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k37IdVfA015204; Fri, 7 Apr 2006 14:39:31 -0400 (EDT) To: "Evert Daman" cc: pgsql-admin@postgresql.org Subject: Re: database owner gone? In-reply-to: <002801c654b4$816eb340$91512b0a@B100> References: <002801c654b4$816eb340$91512b0a@B100> Comments: In-reply-to "Evert Daman" message dated "Fri, 31 Mar 2006 13:16:06 +0200" Date: Fri, 07 Apr 2006 14:39:31 -0400 Message-ID: <15203.1144435171@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.131 required=5 tests=[AWL=0.131] X-Spam-Score: 0.131 X-Spam-Level: X-Archive-Number: 200604/74 X-Sequence-Number: 21243 "Evert Daman" 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 From e.daman@wanadoo.nl Fri Apr 7 22:00:32 2006 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 08D8111F677C for ; Fri, 7 Apr 2006 19:00:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 18300-09 for ; Fri, 7 Apr 2006 19:00:27 -0300 (ADT) X-Greylist: delayed 00:29:26.086596 by SQLgrey- Received: from smtp04.wanadoo.nl (smtp04.wanadoo.nl [194.134.35.144]) by postgresql.org (Postfix) with ESMTP id CAC0F11F676D for ; Fri, 7 Apr 2006 19:00:29 -0300 (ADT) Received: from beneden (s559361db.adsl.wanadoo.nl [85.147.97.219]) by smtp4.wanadoo.nl (Postfix) with SMTP id 5C9C641798; Fri, 7 Apr 2006 23:31:00 +0200 (CEST) Message-ID: <005401c65a8a$908e2820$0b01a8c0@beneden> From: "Evert Daman" To: "Tom Lane" Cc: References: <002801c654b4$816eb340$91512b0a@B100> <15203.1144435171@sss.pgh.pa.us> Subject: Re: database owner gone? Date: Fri, 7 Apr 2006 23:31:00 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1506 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.479 required=5 tests=[DNS_FROM_RFC_ABUSE=0.479] X-Spam-Score: 0.479 X-Spam-Level: X-Archive-Number: 200604/92 X-Sequence-Number: 21261 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" To: "Evert Daman" Cc: Sent: Friday, April 07, 2006 8:39 PM Subject: Re: [ADMIN] database owner gone? > "Evert Daman" 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 > > From tgl@sss.pgh.pa.us Fri Apr 7 21:34:44 2006 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 781BB11F66CE for ; Fri, 7 Apr 2006 18:34:44 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 66394-07 for ; Fri, 7 Apr 2006 18:34:39 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id D46C711F666F for ; Fri, 7 Apr 2006 18:34:41 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k37LYfAc016868; Fri, 7 Apr 2006 17:34:41 -0400 (EDT) To: "Evert Daman" cc: pgsql-admin@postgresql.org Subject: Re: database owner gone? In-reply-to: <005401c65a8a$908e2820$0b01a8c0@beneden> References: <002801c654b4$816eb340$91512b0a@B100> <15203.1144435171@sss.pgh.pa.us> <005401c65a8a$908e2820$0b01a8c0@beneden> Comments: In-reply-to "Evert Daman" message dated "Fri, 07 Apr 2006 23:31:00 +0200" Date: Fri, 07 Apr 2006 17:34:41 -0400 Message-ID: <16867.1144445681@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.13 required=5 tests=[AWL=0.130] X-Spam-Score: 0.13 X-Spam-Level: X-Archive-Number: 200604/77 X-Sequence-Number: 21246 "Evert Daman" 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