Skip site navigation (1) Skip section navigation (2)

Re: pg_dumpall problem - duplicated users

From: GRUbY <grubby(at)epf(dot)pl>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: pg_dumpall problem - duplicated users
Date: 2005-09-01 19:34:22
Message-ID: 431757BE.9000805@epf.pl (view raw or flat)
Thread:
Lists: pgsql-admin
Tom Lane napisał(a):

>Bartosz Nowak <grubby(at)go2(dot)pl> 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


In response to

pgsql-admin by date

Next:From: sandhyaDate: 2005-09-02 04:26:19
Subject: Re: Reg:Connection Object
Previous:From: Tom LaneDate: 2005-09-01 19:11:28
Subject: Re: pg_dumpall problem - duplicated users

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group