X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0C85AD86D7 for ; Thu, 1 Sep 2005 16:09:01 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 93080-08 for ; Thu, 1 Sep 2005 19:08:59 +0000 (GMT) Received: from mail133.pi.net.pl (gips.pi.net.pl [195.116.221.99]) by svr1.postgresql.org (Postfix) with SMTP id 53C15D86BC for ; Thu, 1 Sep 2005 16:08:54 -0300 (ADT) Received: (qmail 6835 invoked from network); 1 Sep 2005 19:08:53 -0000 Received: from unknown (HELO ?81.168.138.82?) (grubby@81.168.138.82) by 195.205.153.133 with SMTP; 1 Sep 2005 19:08:53 -0000 Message-ID: <431751C6.9040902@epf.pl> Date: Thu, 01 Sep 2005 21:08:54 +0200 From: GRUbY User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050727) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: pg_dumpall problem - duplicated users Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.024 required=5 tests=[RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200509/8 X-Sequence-Number: 18745 Tom Lane napisał(a): > Bartosz Nowak writes: > > >> postgres=> select ctid,xmin,xmax,cmin,xmax,* from pg_shadow where >> usename = 'postgres'; >> ctid | xmin | xmax | cmin | xmax | usename | usesysid | >> usecreatedb | usesuper | usecatupd | >> passwd | valuntil | useconfig >> --------+------------+-------+-------+-------+----------+----------+-------------+----------+-----------+-------------------------------------+----------+----------- >> > > > > >> (0,1) | 1 | 50469 | 50469 | 50469 | postgres | 1 | >> t | t | t >> | | | >> (1,25) | 2559800612 | 0 | 0 | 0 | postgres | 1 | >> t | t | t | >> md5c084502ed11efa9d3d96d29717a5e555 | | >> (2 rows) >> > > > Hmm --- clearly, that second xmin is corrupt. I'd venture that this > is the result of a rolled-back (crashed?) ALTER USER SET PASSWORD > operation, in which somehow the new tuple's xmin got clobbered ... and > more than likely, its XMIN_COMMITTED bit got set at the same time. > > You could manually delete either row, probably better to zap the second > one: > delete from pg_shadow where ctid = '(1,25)'; > and then things should be OK. A dump and reload wouldn't be a bad idea > though, since there may be other corruption elsewhere that you haven't > noticed yet. > > regards, tom lane > > Heh... i wish it was that easy - i tried it already :] When i delete the 'second' postgres user (with passwd set) PG is acting like there is no postgres account at all: pg_dumpall -i -U mw > test.sql pg_dump: SQL command failed pg_dump: Error message from server: ERROR: pg_class_aclcheck: invalid user id 1 pg_dump: The command was: SELECT (SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, pg_encoding_to_char(encoding) as encoding, datpath FROM pg_database WHERE datname = 'alibi' pg_dumpall: pg_dump failed on database "alibi", exiting pg_dumpall -i -U postgres > test.sql pg_dumpall: could not connect to database "template1": FATAL: user "postgres" does not exist psql -U postgres psql: FATAL: user "postgres" does not exist And i cannot modify row of 'first' postgres user (without passwd set) with or without 'second' present: mw=> delete from pg_shadow where ctid = '(0,1)' ; DELETE 0 mw=> update pg_catalog.pg_shadow set passwd='test' where usename='postgres' and ctid = '(0,1)'; UPDATE 0 So further help will be welcomed :) And thank U for trying to help me. Greetings, Bartek