|
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@xxxxxxxxxxxxxx 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@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] En nombre de Warren Little Enviado el: Friday, March 23, 2007 6:16 PM Para: pgsql-admin@xxxxxxxxxxxxxx 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
|
