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

Responder a