pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedURGENT TABLE PG_SHADOW CORRUTEP
6+ messages / 3 participants
[nested] [flat]
* URGENT TABLE PG_SHADOW CORRUTEP
@ 2007-03-25 02:56 Daniel Ricardo Medina <dmedina@computec.com.co>
2007-03-25 18:51 ` Re: URGENT TABLE PG_SHADOW CORRUTEP Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Daniel Ricardo Medina @ 2007-03-25 02:56 UTC (permalink / raw)
To: Daniel Ricardo Medina <dmedina@computec.com.co>; pgsql-admin
help please
when I execute this query
select * from pg_shadow;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig
-----------+----------+-------------+----------+-----------+----------+----------+-----------
postgres | 1 | t | t | t | ******** | |
postgres | 1 | t | t | t | ******** | |
userradar | 100 | f | f | f | ******** | |
(3 rows)
the result are once tuples with the same username "postgres" but the first don´t have password. in additon, when I execute pg_dump the result is
pg_dump: saving encoding
pg_dump: saving database definition
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: more than one row returned by a subq
uery used as an expression
pg_dump: The command was: SELECT (SELECT usename FROM pg_user WHERE usesysid = d
atdba) as dba, pg_encoding_to_char(encoding) as encoding, datpath FROM pg_databa
se WHERE datname = 'radar'
pg_dump: *** aborted because of error
because there are two users with SYSID = 1, the result is in red
I have try to run this command in single user mod
select * from pg_shadow;
delete from pg_shadow;
CREATE USER postgres WITH CREATEDB CREATEUSER PASSWORD '***' SYSID 1;
CREATE USER userradar PASSWORD '***'
but the result is the same.....
thanks.....
Daniel Ricardo Medina
Ing. de Desarrollo
Computec S.A.
Tel 260 71 11 Ext 1640
________________________________
De: Daniel Ricardo Medina
Enviado el: Friday, March 23, 2007 6:38 PM
Para: pgsql-admin@postgresql.org
Asunto: AYUDA URGENTE CON TABLA PG_SHADOW
Ayuda por favor
Cuando ejecuto este comando me sale una doble tupla del usuario postgres, esto me error cuando ejecuto el comando Pg_dump para realizar el respectivo backup..
radar=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig
-----------+----------+-------------+----------+-----------+----------+----------+-----------
postgres | 1 | t | t | t | ******** | |
postgres | 1 | t | t | t | ******** | |
userradar | 100 | f | f | f | ******** | |
(3 rows)
He intentado borrar los usuario directamente de la tabla subiendo postgres com postgres -O -P -D directorio
Y corriendo estos comandos..
select * from pg_shadow;
delete from pg_shadow;
CREATE USER postgres WITH CREATEDB CREATEUSER PASSWORD '***' SYSID 1; CREATE USER userradar PASSWORD '***'
Pero me crea los mismos tres usuarios en la tabla pg_shadow, el problema es que no he podido hacer el backup y ya no tengo espacio en la particion donde tengo montada la base
Ayuda por favor
Cuando ejecuto este comando me sale una doble tupla del usuario postgres, esto me error cuando ejecuto el comando Pg_dump para realizar el respectivo backup..
radar=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig
-----------+----------+-------------+----------+-----------+----------+----------+-----------
postgres | 1 | t | t | t | ******** | |
postgres | 1 | t | t | t | ******** | |
userradar | 100 | f | f | f | ******** | |
(3 rows)
He intentado borrar los usuario directamente de la tabla subiendo postgres com postgres -O -P -D directorio
Y corriendo estos comandos..
select * from pg_shadow;
delete from pg_shadow;
CREATE USER postgres WITH CREATEDB CREATEUSER PASSWORD '***' SYSID 1; CREATE USER userradar PASSWORD '***'
Pero me crea los mismos tres usuarios en la tabla pg_shadow, el problema es que no he podido hacer el backup y ya no tengo espacio en la particion donde tengo montada la base
Daniel Ricardo Medina
Ing. de Desarrollo
Computec S.A.
Tel 260 71 11 Ext 1640
________________________________
De: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] En nombre de Warren Little
Enviado el: Friday, March 23, 2007 6:16 PM
Para: pgsql-admin@postgresql.org
Asunto: [ADMIN] trying to run PITR recovery
Hello,
I'm testing my PITR recovery procedures and something doesn't look right.
The following is from the logs upon starting postgres with recovery.conf file in place
2007-03-23 05:56:00 MDTLOG: database system was interrupted at 2007-03-18 05:09:15 MDT
@ 2007-03-23 05:56:00 MDTLOG: starting archive recovery
@ 2007-03-23 05:56:00 MDTLOG: restore_command = "cp /data/pgLocal/archive/WAL_restore/%f "%p""
cp: cannot stat `/data/pgLocal/archive/WAL_restore/00000001.history': No such file or directory
@ 2007-03-23 05:56:00 MDTLOG: restored log file "000000010000011A000000EE.004E0060.backup" from archive
@ 2007-03-23 05:56:00 MDTLOG: restored log file "000000010000011A000000EE" from archive
@ 2007-03-23 05:56:00 MDTLOG: checkpoint record is at 11A/EE4E0060
@ 2007-03-23 05:56:00 MDTLOG: redo record is at 11A/EE4E0060; undo record is at 0/0; shutdown FALSE
@ 2007-03-23 05:56:00 MDTLOG: next transaction ID: 2415965426; next OID: 81701223
@ 2007-03-23 05:56:00 MDTLOG: next MultiXactId: 43380; next MultiXactOffset: 92368
@ 2007-03-23 05:56:00 MDTLOG: automatic recovery in progress
@ 2007-03-23 05:56:01 MDTLOG: redo starts at 11A/EE4E00B0
@ 2007-03-23 05:56:15 MDTLOG: restored log file "000000010000011A000000EF" from archive
@ 2007-03-23 05:56:27 MDTLOG: restored log file "000000010000011A000000F0" from archive
@ 2007-03-23 05:56:31 MDTLOG: restored log file "000000010000011A000000F1" from archive
@ 2007-03-23 05:56:34 MDTLOG: restored log file "000000010000011A000000F2" from archive
@ 2007-03-23 05:56:40 MDTLOG: restored log file "000000010000011A000000F3" from archive
@ 2007-03-23 05:56:50 MDTLOG: restored log file "000000010000011A000000F4" from archive
@ 2007-03-23 05:57:02 MDTLOG: restored log file "000000010000011A000000F5" from archive
@ 2007-03-23 05:57:07 MDTLOG: restored log file "000000010000011A000000F6" from archive
@ 2007-03-23 05:57:11 MDTLOG: restored log file "000000010000011A000000F7" from archive
@ 2007-03-23 05:57:14 MDTLOG: restored log file "000000010000011A000000F8" from archive
@ 2007-03-23 05:57:18 MDTLOG: restored log file "000000010000011A000000F9" from archive
@ 2007-03-23 05:57:21 MDTLOG: restored log file "000000010000011A000000FA" from archive
@ 2007-03-23 05:57:27 MDTLOG: restored log file "000000010000011A000000FB" from archive
@ 2007-03-23 05:57:29 MDTLOG: restored log file "000000010000011A000000FC" from archive
@ 2007-03-23 05:57:33 MDTLOG: restored log file "000000010000011A000000FD" from archive
@ 2007-03-23 05:57:35 MDTLOG: incorrect resource manager data checksum in record at 11A/FD492B20
@ 2007-03-23 05:57:35 MDTLOG: redo done at 11A/FD492210
@ 2007-03-23 05:57:36 MDTLOG: restored log file "000000010000011A000000FD" from archive
@ 2007-03-23 05:57:36 MDTLOG: archive recovery complete
@ 2007-03-23 05:57:36 MDTLOG: could not truncate directory "pg_multixact/members": apparent wraparound
@ 2007-03-23 05:59:00 MDTLOG: database system is ready
@ 2007-03-23 05:59:00 MDTLOG: transaction ID wrap limit is 3065701724, limited by database "postgres"
@ 2007-03-23 12:14:04 MDTLOG: autovacuum: processing database "postgres"
My concern is that there were many more logfiles to be played following "00000010000011A000000FD"
(ie 000000010000011E0000005C) yet it appears the recovery stop at that point and called it good.
I would assume all WAL logs would be restored.
The recovery.conf was simple:
restore_command = 'cp /data/pgLocal/archive/WAL_restore/%f "%p"'
The backup file 000000010000011A000000EE.004E0060.backup" contained:
START WAL LOCATION: 11A/EE4E0060 (file 000000010000011A000000EE)
STOP WAL LOCATION: 11A/EFF68AB8 (file 000000010000011A000000EF)
CHECKPOINT LOCATION: 11A/EE4E0060
START TIME: 2007-03-17 20:29:16 MDT
LABEL: 076_pgdata.tar
STOP TIME: 2007-03-18 05:16:17 MDT
Does the line: incorrect resource manager data checksum in record at 11A/FD492B20
mean there is a corrupted WAL log file?
Any insight here would be helpful
version PG 8.1.2 64 bit Linux
thanks
Warren Little
Chief Technology Officer
Meridias Capital Inc
ph 866.369.7763
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: URGENT TABLE PG_SHADOW CORRUTEP
2007-03-25 02:56 URGENT TABLE PG_SHADOW CORRUTEP Daniel Ricardo Medina <dmedina@computec.com.co>
@ 2007-03-25 18:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-26 00:27 ` Re: URGENT TABLE PG_SHADOW CORRUTEP Peter Koczan <pjkoczan@gmail.com>
2007-03-26 13:52 ` Re: URGENT TABLE PG_SHADOW CORRUTEP Daniel Ricardo Medina <dmedina@computec.com.co>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2007-03-25 18:51 UTC (permalink / raw)
To: Daniel Ricardo Medina <dmedina@computec.com.co>; +Cc: pgsql-admin
"Daniel Ricardo Medina" <dmedina@computec.com.co> writes:
> I have try to run this command in single user mod
>
> select * from pg_shadow;
> delete from pg_shadow;
> CREATE USER postgres WITH CREATEDB CREATEUSER PASSWORD '***' SYSID 1;
> CREATE USER userradar PASSWORD '***'
> but the result is the same.....
What do you see if you look in pg_shadow right after doing the DELETE?
What PG version is this?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: URGENT TABLE PG_SHADOW CORRUTEP
2007-03-25 02:56 URGENT TABLE PG_SHADOW CORRUTEP Daniel Ricardo Medina <dmedina@computec.com.co>
2007-03-25 18:51 ` Re: URGENT TABLE PG_SHADOW CORRUTEP Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-03-26 00:27 ` Peter Koczan <pjkoczan@gmail.com>
2007-03-26 13:58 ` Re: URGENT TABLE PG_SHADOW CORRUTEP Daniel Ricardo Medina <dmedina@computec.com.co>
1 sibling, 1 reply; 6+ messages in thread
From: Peter Koczan @ 2007-03-26 00:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Daniel Ricardo Medina <dmedina@computec.com.co>; pgsql-admin
I saw something along these lines recently when I was running stuff from
postgres 7.4.7 (we recently upgraded to 8.2.3). Basically, a unique index
somehow fails and allows duplicate rows...but I don't know why.
Deleting doesn't work because postgres only looks for the record shown by
the index (though this may have been fixed in more recent releases). At
least this is the behaviour I observed.
The way I fixed it was to drop the unique index, remove the offending rows,
reinsert only one of the offending rows (so things would once again be
unique), and remake the index. It worked.
There are two things that concern me:
1. Trying to do this on a system table is likely ill-advised at best, and
dangerous at worst.
2. If the table only has 3 rows in it and it got corrupted, there's
something really wrong. My table in question was a user table that had
almost a million rows.
On 3/25/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Daniel Ricardo Medina" <dmedina@computec.com.co> writes:
> > I have try to run this command in single user mod
> >
> > select * from pg_shadow;
> > delete from pg_shadow;
> > CREATE USER postgres WITH CREATEDB CREATEUSER PASSWORD '***' SYSID 1;
> > CREATE USER userradar PASSWORD '***'
>
> > but the result is the same.....
>
> What do you see if you look in pg_shadow right after doing the DELETE?
> What PG version is this?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: URGENT TABLE PG_SHADOW CORRUTEP
2007-03-25 02:56 URGENT TABLE PG_SHADOW CORRUTEP Daniel Ricardo Medina <dmedina@computec.com.co>
2007-03-25 18:51 ` Re: URGENT TABLE PG_SHADOW CORRUTEP Tom Lane <tgl@sss.pgh.pa.us>
2007-03-26 00:27 ` Re: URGENT TABLE PG_SHADOW CORRUTEP Peter Koczan <pjkoczan@gmail.com>
@ 2007-03-26 13:58 ` Daniel Ricardo Medina <dmedina@computec.com.co>
0 siblings, 0 replies; 6+ messages in thread
From: Daniel Ricardo Medina @ 2007-03-26 13:58 UTC (permalink / raw)
To: Peter Koczan <pjkoczan@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
ok thank, in single mode i try to erase two unique index on pg_shadow but postgres show this messages
this index is used by system postgres... can´t erase it....
Daniel Ricardo Medina
Ing. de Desarrollo
Computec S.A.
Tel 260 71 11 Ext 1640
________________________________
De: Peter Koczan [mailto:pjkoczan@gmail.com]
Enviado el: Sunday, March 25, 2007 7:28 PM
Para: Tom Lane
CC: Daniel Ricardo Medina; pgsql-admin@postgresql.org
Asunto: Re: [ADMIN] URGENT TABLE PG_SHADOW CORRUTEP
I saw something along these lines recently when I was running stuff from postgres 7.4.7 (we recently upgraded to 8.2.3). Basically, a unique index somehow fails and allows duplicate rows...but I don't know why.
Deleting doesn't work because postgres only looks for the record shown by the index (though this may have been fixed in more recent releases). At least this is the behaviour I observed.
The way I fixed it was to drop the unique index, remove the offending rows, reinsert only one of the offending rows (so things would once again be unique), and remake the index. It worked.
There are two things that concern me:
1. Trying to do this on a system table is likely ill-advised at best, and dangerous at worst.
2. If the table only has 3 rows in it and it got corrupted, there's something really wrong. My table in question was a user table that had almost a million rows.
On 3/25/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Daniel Ricardo Medina" <dmedina@computec.com.co> writes:
> I have try to run this command in single user mod
>
> select * from pg_shadow;
> delete from pg_shadow;
> CREATE USER postgres WITH CREATEDB CREATEUSER PASSWORD '***' SYSID 1;
> CREATE USER userradar PASSWORD '***'
> but the result is the same.....
What do you see if you look in pg_shadow right after doing the DELETE?
What PG version is this?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: URGENT TABLE PG_SHADOW CORRUTEP
2007-03-25 02:56 URGENT TABLE PG_SHADOW CORRUTEP Daniel Ricardo Medina <dmedina@computec.com.co>
2007-03-25 18:51 ` Re: URGENT TABLE PG_SHADOW CORRUTEP Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-03-26 13:52 ` Daniel Ricardo Medina <dmedina@computec.com.co>
2007-03-26 13:56 ` Re: URGENT TABLE PG_SHADOW CORRUTEP Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Daniel Ricardo Medina @ 2007-03-26 13:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
After delete all, postgres said no exist the user id 1...
The version is 7.4.8
Daniel Ricardo Medina
Ing. de Desarrollo
Computec S.A.
Tel 260 71 11 Ext 1640
-----Mensaje original-----
De: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Enviado el: Sunday, March 25, 2007 1:52 PM
Para: Daniel Ricardo Medina
CC: pgsql-admin@postgresql.org
Asunto: Re: [ADMIN] URGENT TABLE PG_SHADOW CORRUTEP
"Daniel Ricardo Medina" <dmedina@computec.com.co> writes:
> I have try to run this command in single user mod
>
> select * from pg_shadow;
> delete from pg_shadow;
> CREATE USER postgres WITH CREATEDB CREATEUSER PASSWORD '***' SYSID 1;
> CREATE USER userradar PASSWORD '***'
> but the result is the same.....
What do you see if you look in pg_shadow right after doing the DELETE?
What PG version is this?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: URGENT TABLE PG_SHADOW CORRUTEP
2007-03-25 02:56 URGENT TABLE PG_SHADOW CORRUTEP Daniel Ricardo Medina <dmedina@computec.com.co>
2007-03-25 18:51 ` Re: URGENT TABLE PG_SHADOW CORRUTEP Tom Lane <tgl@sss.pgh.pa.us>
2007-03-26 13:52 ` Re: URGENT TABLE PG_SHADOW CORRUTEP Daniel Ricardo Medina <dmedina@computec.com.co>
@ 2007-03-26 13:56 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2007-03-26 13:56 UTC (permalink / raw)
To: Daniel Ricardo Medina <dmedina@computec.com.co>; +Cc: pgsql-admin
"Daniel Ricardo Medina" <dmedina@computec.com.co> writes:
> After delete all, postgres said no exist the user id 1...
Maybe reindexing pg_shadow would help.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-03-26 13:58 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-25 02:56 URGENT TABLE PG_SHADOW CORRUTEP Daniel Ricardo Medina <dmedina@computec.com.co>
2007-03-25 18:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-26 00:27 ` Peter Koczan <pjkoczan@gmail.com>
2007-03-26 13:58 ` Daniel Ricardo Medina <dmedina@computec.com.co>
2007-03-26 13:52 ` Daniel Ricardo Medina <dmedina@computec.com.co>
2007-03-26 13:56 ` Tom Lane <tgl@sss.pgh.pa.us>
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