Re: pg_dumpall problem - duplicated users
От | GRUbY |
---|---|
Тема | Re: pg_dumpall problem - duplicated users |
Дата | |
Msg-id | 431757BE.9000805@epf.pl обсуждение исходный текст |
Ответ на | Re: pg_dumpall problem - duplicated users (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-admin |
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
В списке pgsql-admin по дате отправления: