From: GRUbY <grubby@epf.pl>
To: pgsql-admin@postgresql.org
Subject: Re: pg_dumpall problem - duplicated users
Date: Thu, 01 Sep 2005 21:34:22 +0200
Message-ID: <431757BE.9000805@epf.pl> (raw)
In-Reply-To: <5470.1125601888@sss.pgh.pa.us>
References: <431739CD.1030906@go2.pl>
<4997.1125599176@sss.pgh.pa.us>
<431750D4.3050808@go2.pl>
<5470.1125601888@sss.pgh.pa.us>
Tom Lane napisał(a):
>Bartosz Nowak <grubby@go2.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: grubby@epf.pl
Subject: Re: pg_dumpall problem - duplicated users
In-Reply-To: <431757BE.9000805@epf.pl>
References: <431739CD.1030906@go2.pl> <4997.1125599176@sss.pgh.pa.us> <431750D4.3050808@go2.pl> <5470.1125601888@sss.pgh.pa.us> <431757BE.9000805@epf.pl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox