Point in Time Recovery (PITR) en PostgreSQL

En la lista de correo de seguidores PostgreSQL Centroamérica dije que compartiría este manualito, y aquí va, no sin antes introducirlo tal como hice en el correo enviado a tal lista.

Por estos lados costarricenses donde me encuentro actualmente (aunque no por mucho tiempo), estoy metiendo PostgreSQL a todo dar en instituciones/empresas actualmente capturadas por otros SGBDs propietarios corporativos de nombres que no mencionaré porque los detesto.

Mi interés particular, aparte de las virtudes evidentes de PostgreSQL como SGBD relacional, es la configuración avanzada para ofrecer GRATIS algunas de esas cositas que en otros ámbitos llaman "herramientas de valor añadido" (es decir, de costo $$,$$$ agregado). Por ejemplo, ya conseguí configurar el respaldo incremental (y/o Point In Time Recovery), elaboré un "howto" al grano en español entendible que copiaré y pegaré a continuación.

Lo siguiente es ver si logro configurar un PGCluster (replicación síncrona) para poder afirmar con rotundidad que la replicación/alta disponibilidad es perfectamente posible y funciona de lo más bien en PgSQL: , pero esto ya es harina de otro costal, muchísimo más enrevesado el asunto que un archive_mode=on y demás.

Y ahora sin más dilación, el manualito del PITR (sobre Debian GNU/Linux Lenny).

---

Deben establecerse en el postgresql.conf (/etc/postgresql/8.3/main/postgresql.conf)

archive_mode = on
archive_command = 'test ! -f /var/backups/postgresql/%f && cp %p /var/backups/postgresql/%f'

# Poner un valor distinto de 0 sólo si necesito forzar la
# creación de un backup incremental cada XXX segundos
archive_timeout = 300

Lo importante es decidir donde van a enviarse los ficheros de respaldo (ojalá un servidor o dispositivo de almacenamiento remoto). Pueden ver que yo indiqué /var/backups/postgresql, pero eso no tiene que ser así en absoluto. El directorio donde vayan a almacenarse (p.e. /var/backups/postgresql) debe pertenecerle al usuario postgres (o éste usuario postgres deberá tener permiso de escritura). Si no es así, o el comando no funciona por algún motivo, PostgreSQL no podrá trasladar los ficheros incrementales y se quedarán acumulados en su lugar por defecto, es decir,
en el pg_xlog hasta que tal circunstancia se resuelva.

Obviamente la partición donde esté el pg_xlog/ podría llenarse. En caso de que eso suceda, PostgreSQL hará un PANIC shutdown, lo que implica que no se pierde ninguna transacción, pero hasta que no haya algo de espacio, la BD no podrá levantarse nuevamente. Si todo va bien, en el pg_xlog/ sólo se mantienen los 9 últimos ficheros.

Cuando algo sucede con el comando de traslado, queda registrado en el log de postgresql, así que un vistazo de vez en cuando no está mal.

Cuando todo está preparado, se puede hacer un restart de postgresql:
/etc/init.d/postgresql-8.3 restart

Hacer un backup

1) conectarse al SGBD con el usuario postgres y ejecutar la sentencia

select * from pg_xlogfile_name_offset(pg_start_backup('/var/backups/postgresql'));

Ojo al nombre del fichero que devuelve, es el fichero que contiene los cambios desde ese instante y deberán guardarse tanto ese como los sucesivos para que el backup esté completo. Los anteriores ficheros pueden ignorarse.

2) salir de la sesión en el SGBD y hacer una copia completa del directorio donde está el cluster

cd /var/lib/postgresql/8.3

tar czf /dondesea/completo.tar.gz main/

(este último comando puede tardar bastante, todo depende de lo gorda que sea la BD)

3) volver a entrar al gestor como usuario postgres y realizar

select * from pg_xlogfile_name_offset(pg_stop_backup());

la salida dice cual es el último fichero con transacciones durante la realización del backup.

Habrá que mover el backup completo a su destino definitivo, así como los ficheros incrementales de /var/backups/postgresql a partir del que dijo el start_backup hasta el que dijo el stop_backup al dispositivo donde se vaya a realizar el backup.

Listo!

Recuperar un backup

Para poder restaurar necesitaremos el backup completo así como los ficheros incrementales que se generaron durante el respaldo (entre el start y el stop) y los posteriores que se hayan ido generando. Yo puedo restaurar hasta un punto dado en el tiempo (de hecho esta virtud es de lo más ideal aparte de reducir el tamaño de los backups), por lo que no hay problema si lo que necesito es recuperar datos borrados antes de un momento en concreto y tengo ficheros incrementales posteriores al desastre.

Éste es el procedimiento:

1) Parar postgres

2) Eliminar todo lo que haya dentro del directorio del cluster (p.e. /var/lib/postgresql/8.3/main)

3) Como usuario postgres, descomprimir el fichero de backup completo

Copiar los ficheros incrementales respaldados en el pg_xlog (p.e. /var/lib/postgresql/8.3/main/pg_xlog). Ojo, todos los ficheros y directorios dentro del main le deben pertenecer a postgres, si hacemos cosas como root, quizá sea adecuado un chown -R postgres main/.

Crear un fichero de instrucciones de restauración denominado "recovery.conf" en el directorio del cluster (p.e. vi /var/lib/postgresql/8.3/main/recovery.conf).

Indicar en él qué se debe hacer para restaurar. Lo más básico es poner donde están los ficheros de restauración y hasta qué momento queremos restaurar, por ejemplo:

restore_command = 'cp /var/lib/postgresql/8.3/main/pg_xlog/%f "%p"'
recovery_target_time='13/05/2009 12:55:47.548867 CST'

4) como root

Antes de arrancar la BD, puede ser importante impedir que se conecte nadie al SGBD para asegurar previamente que todo está en orden: para ello únicamente es necesario modificar el pg_hba.conf.

Para que no haya problemas, antes de reiniciar, es conveniente limpiar el directorio donde se estaban haciendo la transferencia de ficheros incrementales para que no se encuentre con uno que ya exista y quede detenido el traslado.

Entonces podemos ya arrancar el servidor.

Pueden verse en la bitácora /var/log/postgresql/postgresql-8.3-main.log los efectos de la restauración: el servidor procede a leer los ficheros incrementales hasta el punto en tiempo dado.

Una vez concluida la restauración, el recovery.conf es renombrado a recovery.done

Podemos comprobar que todo se restauró como debía hasta el momento que se indicó (es increíble ver como tablas borradas/actualizadas por error vuelven a la vida como si nada).

Lo último que resta es habilitar el acceso de los usuarios en el pg_hba.conf (si es que lo habíamos deshabilitado) y recargar la configuración con un /etc/init.d/postgresql-8.3 reload.

Listo!

LIMITANTE IMPORTANTE: lo que se está resguardando es TODO el cluster, es decir, todas las BDs contenidas en él. Si hacemos una restauración devolveremos TODAS las BDs a ese punto en el tiempo... y lo más probable es que el desastre sólo ocurriera en una de ellas. En tal caso, lo único que se puede hacer es hacer la restauración hasta un punto donde esté la/s tabla/s defenestrada, sacar un copy de dicha tabla a un archivo (o un pg_dump de la BD en cuestión mucho mejor), volver a repetir la restauración sin indicar un punto en el tiempo y entonces, con los datos sacados del copy al archivo o el dump, arreglar la tabla en cuestión (o la BD con un pg_restore).

De donde saqué todo esto? de la doc. oficial, para mi gusto bastante espesa:
http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html