Matías Neiff's Blog

Alguna vez se preguntaron cual sería el último recurso si por error (o una falla de disco) se borra nuestro directorio postgres corrompiendo totalmente la base de datos?, bueno, les puedo asegurar que a esta altura les encantaría tener backups, pero un poco aquello de que normalmente a uno lo contratan cuando el sistema es un desastre y por el otro lado “desasperación es la mejor de las maestras”. Por A o por B me tocó hacer esto, que no es más que el último recurso cuando por error o una falla de disco se borro la base de datos. Ojalá lo disfruten ya que es una de las cosas más difíciles que hice hasta ahora, en el top 3 digamos.

Bien lo primero que se le ocurre a cualquier hijo de cristiano que tuvo la desgracia de usar DOS es hacer el famoso, conocido, populuar y nunca bien ponderado undelete de microsoft, la versión de linux para ext4 sería extundelete y anda muy muy bien. Sería algo así:

extundelete --restore-all /dev/XXXN

Nos ceará un directorio llamado RECOVERY_FILES con todo lo que encontró, ahí podemos intentar hacer un REINDEX y VACUUM FULL de cada tabla, quizás también usando un zero_damaged_pages. El problema amigos, es que para tablas grandes nada de esto funcionaa, al menos no para mi. Y toca caer en el data carving, que no es más que cavar en el disco buscando pedazos de lo que necesitamos e intentar reconstruirlo. Si bien hay aplicaciones que hacen esto por nosotros para determinado tipo de archivos, como PhotoRec (http://www.cgsecurity.org/wiki/PhotoRec_Data_Carving) el gran problema es que nosotros no estamos buscando fotos, o archivos de texto, así que estos programas no hará nada por nosotros… necesitamos poder poner nuestros headers y ahí es cuando choca los cinco y entra a jugar foremost (http://foremost.sourceforge.net) este a diferencia de los anteriores podemos poner nuestros propios headers así que allá vamos, primero a buscar nuestros headers…

Buscamos nuestra tabla de postgres,

SELECT pg_relation_filepath('gis_gps');
  pg_relation_filepath  
------------------------
 base/45608546/45611313

Muy bien, ahora saquemos el header

hexdump -C postgresindoe | head
00000000  00 00 00 00 b8 2d 40 0f  00 00 00 00 a0 01 e8 01  |.....-@.........|
00000010  00 20 04 20 00 00 00 00  b0 9f a0 00 70 9f 78 00  |. . ........p.x.|
00000020  30 9f 80 00 f0 9e 80 00  a0 9e a0 00 58 9e 88 00  |0...........X...|

Luego de correrle este procedimiento con varias tablas se darán cuenta de que hay un patrón, no sé si será el mismo para todas las arqs y versiones de postgres, pero para mi fue este que agregué a mi foremost.conf:
—– foremost.conf extract—–
# case size header footer
#extension sensitive
NONE y 900000000000 ?\x00\x00\x00????\x00\x00\x00\x00?\x00??\x00\x20\x04\x20\x00\x00\x00\x00
——————————–

Muy bien ya estamos como para empezar a hurgar en nuestro disco…

foremost -d -i /dev/xXXXN -o /recover/output/

Ahora viene uno de nuestros grandes problemas, el header que nos inventamos se repite mucho dentro de una tabla, porque es el header del heap page, así que si teníamos un archivo como de 1GB nos hará varios archivos de 97m (por ejemplo). Así que nos toca trabajar con esto. Primero que nada hay que modificar el postgres (yo usé la versión 9.5) para agregar la funcion heap_page_tuples_attrs. Al parche lo pueden conseguir de acá:
A mi me tocó modificarlo y aplicarlo a mano, suerte con eso, pero no me quedé con una copia limpia como para darles. Pueden bajarse el original de este post.

Ahora podemos crear nuestra tabla con un create en limpio y reemplazar el filenode con uno que recuperó foremost, claro, nada funcionará excepto esto:

select * from heap_page_tuples_attrs('tabla',0);

Eureca!!!! un poco de luz al final del camino, pero, sin ánimos de desanimarlos… falta muuuuchoooo. Sigamos un poco más.

Por cada tipo de datos necesitaremos una función, les dejo acá las que usé para varchar, fecha, hora, real, y double.
El tema con los datos numéricos (y los de tiempo) es que en mi caso estaban en big endian, lo que significa que hay que cambiar todo el orden de los bytes primeros antes de poder trabajar. Luego hay que seguir el estándar IEEE 754 1985, sí, ese que aprendiste en la facultad de potencia y mantisa que siempre pensaste, esto no me va a servir para nada en la put@ vida… bienvenido a la put@ vida. Acá les dejo el link al estándar, por si les hiciera falta una ayuda de memoria.

Acá mis babys:

CREATE OR REPLACE FUNCTION getMaxPage(tabe text) RETURNS int AS $$
DECLARE
lp int;
BEGIN
FOR i IN REVERSE 1000..1 LOOP
--RAISE NOTICE 'i %',i;
BEGIN
lp:= (select lp_len from heap_page_tuples_attrs(tabe, i) limit 1);
if lp > 16 and lp < 500 then
return i;
end if;
EXCEPTION
            WHEN data_corrupted THEN --RAISE NOTICE 'tranqui';
			when internal_error THEN --RAISE NOTICE 'tranqui';
end;
end loop;
END;
$$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION real2date(tiempo bytea) RETURNS text AS $$
DECLARE
BEGIN

return  date('2000-01-01')+(floor(round((bit2real(tiempo)-1)*100000000000000)/11920929))::int+1;
	
END;
$$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION real2hhmmss(tiempo bytea) RETURNS text AS $$
DECLARE
msegundos bigint;
tmpseg bigint;
segundos text;
minutos text;
hora text;
BEGIN
msegundos:=trunc((bit2double(tiempo)-1)*10000000000000);
if msegundos::text = '-10000000000000' then
return '00:00:00';
end if;
tmpseg:=msegundos/2220.45;
--RAISE NOTICE 'msegundos %',msegundos;
--RAISE NOTICE 'tmpseg %',tmpseg;
hora = floor(tmpseg / 3600);
if char_length(hora)=1 then
hora := '0' || hora;
end if;
--RAISE NOTICE 'hora %',hora;
minutos = floor(tmpseg / 60)::int %60;
if char_length(minutos)=1 then
minutos := '0' || minutos;
end if;
--RAISE NOTICE 'minutos %',minutos;
segundos = tmpseg::int %60;
if char_length(segundos)=1 then
segundos := '0' || segundos;
end if;
--RAISE NOTICE 'segundos %',segundos;

return hora||':'||minutos||':'||segundos;	
	
END;
$$
LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION bit2real(doblehex bytea) RETURNS double precision AS $$
DECLARE
sign bigint;
binary_value text;
exponent text;
mantissa text;
byte_array bytea[4];
mantissa_index int;
exp_index int;
exp int;
potencia int;
vbdoble varbit;
result real;
BEGIN
vbdoble := get_byte(doblehex,3)::bit(8) || get_byte(doblehex,2)::bit(8) || get_byte(doblehex,1)::bit(8) || get_byte(doblehex,0)::bit(8);
IF vbdoble = '00000000000000000000000000000000' OR vbdoble = '10000000000000000000000000000000' THEN -- IEEE754-1985 Zero
        return 0.0;
END IF;

sign := substring(vbdoble from 1 for 1);
exponent := substring(vbdoble from 2 for 8);
mantissa := substring(vbdoble from 10);
exp_index:=1;
potencia=7;
-- RAISE NOTICE 'bin exponente %',substring(vbdoble from 2 for 11);
-- RAISE NOTICE 'bin mantisa %',substring(vbdoble from 13);
exp:=0;
WHILE exp_index < 12 LOOP
        IF substring(exponent from exp_index for 1) = '1' THEN
            exp := exp + power(2, potencia);
        END IF;
        exp_index := exp_index + 1;
		potencia := potencia -1;
END LOOP;
--RAISE NOTICE 'exponente %',exp;

IF exp > 126 THEN
   exp := exp - 127;
  ELSE
   exp:= -exp;
  END IF;


mantissa_index:=1;
result:=0;
WHILE mantissa_index < 52 LOOP
        IF substring(mantissa from mantissa_index for 1) = '1' THEN
            result := result + power(2, -(mantissa_index));
        END IF;
        mantissa_index := mantissa_index + 1;
END LOOP;
-- RAISE NOTICE 'mantissa %',result;
result := (1+ result) * power(2, exp);

IF(sign = '1') THEN
	result = -result;
END IF;

return result;	
	
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION bit2double(doblehex bytea) RETURNS double precision AS $$
DECLARE
sign bigint;
binary_value text;
exponent text;
mantissa text;
byte_array bytea[8];
mantissa_index int;
exp_index int;
exp int;
potencia int;
vbdoble varbit;
result double precision;
BEGIN
vbdoble := get_byte(doblehex,7)::bit(8) || get_byte(doblehex,6)::bit(8) || get_byte(doblehex,5)::bit(8) || get_byte(doblehex,4)::bit(8) || get_byte(doblehex,3)::bit(8) || get_byte(doblehex,2)::bit(8) || get_byte(doblehex,1)::bit(8) || get_byte(doblehex,0)::bit(8);
IF vbdoble = '0000000000000000000000000000000000000000000000000000000000000000' OR vbdoble = '1000000000000000000000000000000000000000000000000000000000000000' THEN -- IEEE754-1985 Zero
        return 0.0;
END IF;

sign := substring(vbdoble from 1 for 1);
exponent := substring(vbdoble from 2 for 11);
mantissa := substring(vbdoble from 13);
exp_index:=1;
potencia=10;
-- RAISE NOTICE 'bin exponente %',substring(vbdoble from 2 for 11);
-- RAISE NOTICE 'bin mantisa %',substring(vbdoble from 13);
exp:=0;
WHILE exp_index < 12 LOOP
        IF substring(exponent from exp_index for 1) = '1' THEN
            exp := exp + power(2, potencia);
        END IF;
        exp_index := exp_index + 1;
		potencia := potencia -1;
END LOOP;
RAISE NOTICE 'exponente %',exp;

IF exp > 1022 THEN
   exp := exp - 1023;
  ELSE
   exp:= -exp;
  END IF;


mantissa_index:=1;
result:=0;
WHILE mantissa_index < 52 LOOP
        IF substring(mantissa from mantissa_index for 1) = '1' THEN
            result := result + power(2, -(mantissa_index));
        END IF;
        mantissa_index := mantissa_index + 1;
END LOOP;
-- RAISE NOTICE 'mantissa %',result;
result := (1+ result) * power(2, exp);

IF(sign = '1') THEN
	result = -result;
END IF;

return result;	
	
END;
$$
LANGUAGE plpgsql;

Bueno... seguro que ya se deben hacer una idea de lo que toca hacer ahora. Necesitamos un script que:
1) copie los archivos a las tablas "rotas"
2) reinicie el postgres
3) llame a un procedimiento que arregle todas las tablas

Bueno, primero el procedimiento que cura las tablas, sería así para mi caso, tómenlo de ejemplo solamente:

CREATE OR REPLACE FUNCTION migratabla() RETURNS void AS $$
DECLARE
t text;
tablas text[];
page int;
BEGIN
tablas:=ARRAY['db_satguard','db_sisspa','db_tech','db_teleplus','db_waytrack','db_worldtrack'];
FOReach t IN  ARRAY tablas LOOP


BEGIN
page:=getMaxPage(t);
if(page)>0 then
RAISE NOTICE 't %',t;
	FOR i IN REVERSE page..1 LOOP
	execute 'INSERT into ' || t || '_gis (gps_id_dispositivo, gps_vehiculo,gps_fecha,gps_hora,gps_status,gps_speed,gps_course,gps_magn_variation,gps_magn_var_direction,gps_event,gps_tstamp,gps_ubicacion,gps_latitud,gps_longitud,the_geom,gps_send_event) select 
substring(encode(t_attrs[2],''escape'') from 2),
substring(encode(t_attrs[3],''escape'') from 2),
real2date(t_attrs[4])::date,
real2hhmmss(t_attrs[5])::time,
substring(encode(t_attrs[6],''escape'') from 2),
bit2real(t_attrs[7]),
bit2real(t_attrs[8]),
bit2real(t_attrs[9]),
substring(encode(t_attrs[10],''escape'') from 2),
substring(encode(t_attrs[11],''escape'') from 2),
(real2date(t_attrs[4]) || '' '' || real2hhmmss(t_attrs[5]))::timestamp,
substring(encode(t_attrs[13],''escape'') from 2),
bit2double(t_attrs[14]),
bit2double(t_attrs[15]),
ST_geomfromtext(''POINT(''|| bit2double(t_attrs[15]) || '' '' || bit2double(t_attrs[14]) ||'')''),
bit2real(t_attrs[17])
from heap_page_tuples_attrs(''' || t || ''', ' || i || ' )
ON CONFLICT DO NOTHING
;';
	end loop;
end if;
EXCEPTION
            WHEN function_executed_no_return_statement THEN --RAISE NOTICE 'tranqui';
			when internal_error THEN --RAISE NOTICE 'tranqui';
end;
end loop;
END;
$$
LANGUAGE plpgsql;

Ahora mi script de bash:

for i in *; do 
cp $i /recover/5435/base/16384/17832; 
cp $i /recover/5435/base/16384/17797; 
cp $i /recover/5435/base/16384/17782; 
cp $i /recover/5435/base/16384/17768; 
cp $i /recover/5435/base/16384/17835; 
/usr/lib/postgresql/9.3/bin/pg_ctl -D /recover/5435/ -l /recover/5435/logfile restart -m inmediate;
sleep 0.5s;
echo "select migratabla();"| psql -p 5432 -h 127.0.0.1 recover; 
echo listo $i; 
mv $i pasados/
done;

Bueno, no está todo lo explicado que me gustaría, pero acabo de terminar de hacerlo y dejé el script corriendo, quería poner todo antes de que me olvide de algo. Esto básicamente funciona porque estamos usando postgres para dumpear el raw data de las páginas, y de ahí las parseamos en su formato. Esto es lo más bajo nivel que se puede llegar a trabajar en el disco, así que si con esto no salvan sus datos... lo siento, pero sus datos no están.

Espero que le salve la vida a mas de un admin, y bueno, si no me llaman que intento ayudarlos.
Saludos ?