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 C5FA0D8535 for ; Thu, 1 Sep 2005 16:34:30 -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 99109-08 for ; Thu, 1 Sep 2005 19:34:28 +0000 (GMT) Received: from mail133.pi.net.pl (gips.pi.net.pl [195.116.221.99]) by svr1.postgresql.org (Postfix) with SMTP id 6D335D6FA7 for ; Thu, 1 Sep 2005 16:34:25 -0300 (ADT) Received: (qmail 13306 invoked from network); 1 Sep 2005 19:34:26 -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:34:25 -0000 Message-ID: <431757BE.9000805@epf.pl> Date: Thu, 01 Sep 2005 21:34:22 +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 References: <431739CD.1030906@go2.pl> <4997.1125599176@sss.pgh.pa.us> <431750D4.3050808@go2.pl> <5470.1125601888@sss.pgh.pa.us> In-Reply-To: <5470.1125601888@sss.pgh.pa.us> 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/9 X-Sequence-Number: 18746 Tom Lane napisał(a): >Bartosz Nowak writes: > > >>Tom Lane napisał(a): >> >> >>>You could manually delete either row, probably better to zap the second >>>one: >>>delete from pg_shadow where ctid = '(1,25)'; >>> >>> > > > >>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: >>... >>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 >> >> > >Ugh. That's looking more like you have a transaction ID wraparound >problem. How long has it been since pg_shadow was last vacuumed? > >You could try a "VACUUM FREEZE pg_shadow" and see if the rows act any >more normally after that. (Better take a filesystem-level backup >first, so you can get out of it if that makes things worse.) > > regards, tom lane > > We vacuum whole base regularly, and lately it was about week ago (then the problem occured). Since pg_dump don't work we are making filesystem-level backups, and still im experimenting on local copy of this db for safety. VACUUM FREEZE pg_shadow changed the pg_shadow table: 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,4) | 2 | 0 | 0 | 0 | postgres | 1 | t | t | t | md5c084502ed11efa9d3d96d29717a5e555 | | (2 rows) But rows still act unnormally :[, i.e.: update pg_catalog.pg_shadow set passwd='test' where usename='postgres' and ctid = '(0,1)'; UPDATE 0 delete from pg_shadow where ctid = '(0,1)' ; DELETE 0 delete from pg_shadow where usesysid = 1 and passwd != 'md5c084502ed11efa9d3d96d29717a5e555' ; DELETE 0 delete from pg_shadow where usesysid = 1 ; DELETE 1 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 row) update pg_catalog.pg_shadow set passwd='test' where usename='postgres' ; UPDATE 0 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, encoding, datpath from pg_database where datname = 'alibi' pg_dumpall: pg_dump failed on alibi, exiting Greetings, Bartek -- Pozdrawiam, GRUbY