Hola Alvaro y lista
Como para no perder el impulso y poder definir el procedimiento apropiado
para subsanar el bug y agradeciendo su tiempo y apoyo, tengo dos
situaciones:
1 Cluster de base de datos PostgreSQL con no muchas transacciones, la
carpeta /opt/PostgreSQL/9.3/data/pg_multixact/offsets/ tiene solo un
archivo (0000):
[root@mpc1-bd offsets]# ls -lah
total 116K
drwx------ 2 postgres postgres 4,0K feb 12 2014 .
drwx------ 4 postgres postgres 4,0K feb 12 2014 ..
-rw------- 1 postgres postgres 144K jun 1 18:50 0000
Sobre una de las bases de este cluster ejecuto
SELECT relminmxid FROM pg_class;
lo exporte a cvs y lo cargue en excel para ordenarlo
los 5 primeros son:
0112723157441587615907
Descartando 0 y 1 seleccionaría 12723 y ese debería ser el valor con el que
debo setear en pg_database:
update pg_database set datminmxid='12723'::xid where datname='bd_principal'
and oid='16436'::oid.
antes de ejecutar el UPDATE para validar y/o confirmar hago un hexdump
del archivo:
[root@mpc100-bd offsets]# hexdump -C 0000 | more
00000000 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
|................|
*
0000c6c0 00 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00
|................|
0000c6d0 03 00 00 00 05 00 00 00 07 00 00 00 09 00 00 00
|................|
0000c6e0 0b 00 00 00 0d 00 00 00 0f 00 00 00 11 00 00 00
|................|
0000c6f0 13 00 00 00 15 00 00 00 17 00 00 00 19 00 00 00
|................|
0000c700 1b 00 00 00 1e 00 00 00 20 00 00 00 23 00 00 00 |........
...#...|
0000c710 25 00 00 00 27 00 00 00 29 00 00 00 2b 00 00 00
|%...'...)...+...|
0000c720 2d 00 00 00 2f 00 00 00 31 00 00 00 33 00 00 00
|-.../...1...3...|
0000c730 35 00 00 00 37 00 00 00 39 00 00 00 3b 00 00 00
|5...7...9...;...|
0000c740 3d 00 00 00 3f 00 00 00 41 00 00 00 43 00 00 00
|=...?...A...C...|
0000c750 45 00 00 00 47 00 00 00 49 00 00 00 4b 00 00 00
|E...G...I...K...|
0000c760 4d 00 00 00 4f 00 00 00 51 00 00 00 53 00 00 00
|M...O...Q...S...|
0000c770 55 00 00 00 57 00 00 00 59 00 00 00 5b 00 00 00
|U...W...Y...[...|
0000c780 5d 00 00 00 5f 00 00 00 61 00 00 00 63 00 00 00
|]..._...a...c...|
0000c790 65 00 00 00 67 00 00 00 69 00 00 00 6b 00 00 00
|e...g...i...k...|
0000c7a0 6d 00 00 00 6f 00 00 00 71 00 00 00 73 00 00 00
|m...o...q...s...|
0000c7b0 75 00 00 00 77 00 00 00 79 00 00 00 7b 00 00 00
|u...w...y...{...|
0000c7c0 7d 00 00 00 7f 00 00 00 81 00 00 00 83 00 00 00
|}...............|
0000c7d0 85 00 00 00 87 00 00 00 89 00 00 00 8b 00 00 00
|................|
0000c7e0 8d 00 00 00 8f 00 00 00 91 00 00 00 93 00 00 00
|................|
0000c7f0 95 00 00 00 97 00 00 00 99 00 00 00 9b 00 00 00
|................|
0000c800 9d 00 00 00 9f 00 00 00 a1 00 00 00 a3 00 00 00
|................|
0000c810 a5 00 00 00 a7 00 00 00 a9 00 00 00 ab 00 00 00
|................|
0000c820 ad 00 00 00 af 00 00 00 b1 00 00 00 b3 00 00 00
|................|
0000c830 b5 00 00 00 b7 00 00 00 b9 00 00 00 bb 00 00 00
|................|
0000c840 bd 00 00 00 bf 00 00 00 c1 00 00 00 c3 00 00 00
|................|
0000c850 c5 00 00 00 c7 00 00 00 c9 00 00 00 cb 00 00 00
|................|
0000c860 cd 00 00 00 cf 00 00 00 d1 00 00 00 d3 00 00 00
|................|
0000c870 d5 00 00 00 d7 00 00 00 d9 00 00 00 db 00 00 00
|................|
0000c880 dd 00 00 00 df 00 00 00 e1 00 00 00 e3 00 00 00
|................|
0000c890 e5 00 00 00 e7 00 00 00 e9 00 00 00 eb 00 00 00
|................|
0000c8a0 ed 00 00 00 ef 00 00 00 f1 00 00 00 f3 00 00 00
|................|
0000c8b0 f5 00 00 00 f7 00 00 00 f9 00 00 00 fb 00 00 00
|................|
0000c8c0 fd 00 00 00 ff 00 00 00 01 01 00 00 03 01 00 00
|................|
más le soy sincero: no sé que debo interpretar del mismo o donde debo ver,
trate de buscar el mismo valor que me arrojo en el paso anterior pero no lo
vi.
2. Cluster de base de datos PostgreSQL con muchas transacciones, la
carpeta /opt/PostgreSQL/9.3/data/pg_multixact/offsets/ tiene 13 archivos:
[root@Mpc2-bd offsets]# ls -alh
total 3,2M
drwx------ 2 postgres postgres 4,0K may 29 09:39 .
drwx------ 4 postgres postgres 4,0K feb 16 2014 ..
-rw------- 1 postgres postgres 256K mar 4 2014 0008
-rw------- 1 postgres postgres 256K abr 9 2014 0009
-rw------- 1 postgres postgres 256K may 24 2014 000A
-rw------- 1 postgres postgres 256K jul 8 2014 000B
-rw------- 1 postgres postgres 256K ago 19 2014 000C
-rw------- 1 postgres postgres 256K sep 26 2014 000D
-rw------- 1 postgres postgres 256K nov 1 2014 000E
-rw------- 1 postgres postgres 256K dic 5 12:04 000F
-rw------- 1 postgres postgres 256K ene 9 13:29 0010
-rw------- 1 postgres postgres 256K feb 12 14:47 0011
-rw------- 1 postgres postgres 256K mar 25 08:06 0012
-rw------- 1 postgres postgres 256K may 5 12:47 0013
-rw------- 1 postgres postgres 184K jun 2 08:05 0014
Sobre una de las bases de este cluster ejecuto
SELECT relminmxid FROM pg_class;
lo exporte a csv y lo cargue en excel para ordenarlo
los 5 primeros son:
01539897540437562499562988
Descartando 0 y 1 seleccionaría 539897y ese debería ser el valor con el que
debo setear en pg_database:
update pg_database set datminmxid='539897'::xid where
datname='bd_secundaria' and oid='16431'::oid.
antes de ejecutar el UPDATE para validar y/o confirmar hago un hexdump
del archivo :
[root@Mpc72-BD offsets]# hexdump -C 0008 | more
00000000 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
|................|
*
0000f3e0 00 00 00 00 01 00 00 00 03 00 00 00 05 00 00 00
|................|
0000f3f0 07 00 00 00 09 00 00 00 0b 00 00 00 0d 00 00 00
|................|
0000f400 0f 00 00 00 11 00 00 00 13 00 00 00 15 00 00 00
|................|
0000f410 17 00 00 00 19 00 00 00 1b 00 00 00 1d 00 00 00
|................|
0000f420 1f 00 00 00 21 00 00 00 23 00 00 00 25 00 00 00
|....!...#...%...|
0000f430 27 00 00 00 29 00 00 00 2b 00 00 00 2d 00 00 00
|'...)...+...-...|
0000f440 2f 00 00 00 31 00 00 00 33 00 00 00 35 00 00 00
|/...1...3...5...|
0000f450 37 00 00 00 39 00 00 00 3b 00 00 00 3d 00 00 00
|7...9...;...=...|
0000f460 3f 00 00 00 41 00 00 00 43 00 00 00 45 00 00 00
|?...A...C...E...|
0000f470 47 00 00 00 49 00 00 00 4b 00 00 00 4d 00 00 00
|G...I...K...M...|
0000f480 4f 00 00 00 51 00 00 00 53 00 00 00 55 00 00 00
|O...Q...S...U...|
0000f490 57 00 00 00 59 00 00 00 5b 00 00 00 5d 00 00 00
|W...Y...[...]...|
0000f4a0 5f 00 00 00 61 00 00 00 63 00 00 00 65 00 00 00
|_...a...c...e...|
0000f4b0 67 00 00 00 69 00 00 00 6b 00 00 00 6d 00 00 00
|g...i...k...m...|
0000f4c0 6f 00 00 00 71 00 00 00 73 00 00 00 75 00 00 00
|o...q...s...u...|
0000f4d0 77 00 00 00 79 00 00 00 7b 00 00 00 7d 00 00 00
|w...y...{...}...|
0000f4e0 7f 00 00 00 81 00 00 00 83 00 00 00 85 00 00 00
|................|
0000f4f0 87 00 00 00 89 00 00 00 8b 00 00 00 8d 00 00 00
|................|
0000f500 8f 00 00 00 91 00 00 00 93 00 00 00 95 00 00 00
|................|
0000f510 97 00 00 00 99 00 00 00 9b 00 00 00 9d 00 00 00
|................|
0000f520 9f 00 00 00 a1 00 00 00 a3 00 00 00 a5 00 00 00
|................|
0000f530 a7 00 00 00 a9 00 00 00 ab 00 00 00 ad 00 00 00
|................|
0000f540 af 00 00 00 b1 00 00 00 b3 00 00 00 b5 00 00 00
|................|
0000f550 b7 00 00 00 b9 00 00 00 bb 00 00 00 bd 00 00 00
|................|
0000f560 bf 00 00 00 c1 00 00 00 c3 00 00 00 c5 00 00 00
|................|
0000f570 c7 00 00 00 c9 00 00 00 cb 00 00 00 cd 00 00 00
|................|
0000f580 cf 00 00 00 d1 00 00 00 d3 00 00 00 d5 00 00 00
|................|
0000f590 d7 00 00 00 d9 00 00 00 db 00 00 00 dd 00 00 00
|................|
0000f5a0 df 00 00 00 e1 00 00 00 e3 00 00 00 e5 00 00 00
|................|
0000f5b0 e7 00 00 00 e9 00 00 00 eb 00 00 00 ed 00 00 00
|................|
0000f5c0 ef 00 00 00 f1 00 00 00 f3 00 00 00 f5 00 00 00
|................|
0000f5d0 f7 00 00 00 f9 00 00 00 fb 00 00 00 fd 00 00 00
|................|
0000f5e0 ff 00 00 00 01 01 00 00 03 01 00 00 05 01 00 00
|................|
0000f5f0 07 01 00 00 09 01 00 00 0b 01 00 00 0d 01 00 00
|................|
0000f600 0f 01 00 00 11 01 00 00 13 01 00 00 15 01 00 00
|................|
0000f610 17 01 00 00 19 01 00 00 1b 01 00 00 1d 01 00 00
|................|
0000f620 1f 01 00 00 21 01 00 00 23 01 00 00 25 01 00 00
|....!...#...%...|
Pero pasa lo mismo, no se que debo interpretar aqui... inlcuso tengo duda
del valor que me arrojo el query (539897) por lo que menciona Alvaro (cito)
"El problema es que si has consumido una gran cantidad de multiacts,
podrían haber valores que estén pasados del punto medio de wraparound, o
haber dado la vuelta completa..."
Entonces tengo la duda que procedimiento debo seguir en estos casos, mil
Gracias Alvaro.
El 1 de junio de 2015, 4:35 p. m., Alvaro Herrera<alvhe...@2ndquadrant.com>
escribió:
> Hellmuth Vargas escribió:
> > Hola Alvaro
> >
> > Desarrolle este pequeño script para actualizar en cada base los valores
> de
> > datminmxid,
> >
> > select oid, datminmxid , datname from pg_database;
> >
> > update pg_database as x
> > set datminmxid=y.nuevo
> > from (
> > select relminmxid as nuevo from pg_class where (cast(cast(relminmxid AS
> > text) AS bigint))<>0 and (cast(cast(relminmxid AS text) AS bigint))<>1
> > order by (cast(cast(relminmxid AS text) AS bigint)) asc limit 1
> > ) as y
> > where x.datname='crm_seguro' and x.oid='16438'::oid;
> >
> > Está bien? Puedo dejar este valor así?
>
> Ni idea. ¿por qué no muestras un select oid, relminmxid from pg_class
> where relminmxid <> '0', a ver si tiene sentido? El problema es que si
> has consumido una gran cantidad de multixacts, podrían haber valores que
> estén pasados el punto medio de wraparound, o haber dado la vuelta
> completa, y las comparaciones normales < y > podrían no tener sentido ...
>
> > Lo estoy ejecutando y no genera error sin embargo lo estoy haciendo
> > sobre las bases que se pueden recuperar fácil de un backup....
>
> Me parece sensato!
>
> > De antemano muchas gracias.. Me surge una duda adicional.. Que mas
> > debería uno validar? .. A qué me refiero: pues si no se hubiese
> > presentando el problema seguramente se hubiese presentando después con
> > peores consecuencias..
>
> Bueno, a algunos ya se les presentaron con peores consecuencias. En
> realidad, el bug de ahora surgió a partir de la corrección de un bug más
> serio que causaba pérdidas de datos en ciertos casos. Para saber más
> habría que darle una mirada al pg_controldata y el listado de archivos
> en pg_multixact/offset, y ver si son consistentes.
>
> > Donde se puede revisará o validar los valores correctos que debe tiene
> > una base en sus diccionario de sistema. Existe algún script o
> > herramienta que haga este diagnóstico?
>
> No tenemos nada aún.
>
> --
> Álvaro Herrera http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
--
Cordialmente,
Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet
Oracle Database 10g Administrator Certified Associate
EnterpriseDB Certified PostgreSQL 9.3 Associate
