Usando PITR - Point in Time Recovery

servidor

PITR - Point in Time Recovery es un tipo de backup avanzado utilizado en sistemas PostgreSQL que trabajan con datos importantes los cuales no pueden perderse en caso de fallo.

Este artículo es un poco avanzado, largo y no muy interesante para pequeños sistemas sin grandes requerimientos de seguridad en lo concerniente a la perdida de datos por problemas de hardware. Necesitais conocimientos de administración de sistemas Linux/Unix y como trabajar con LVM (Linux Volume Manager) para administrar vuestros discos y particiones.

La teoria es fácil de entender, pero la práctica y la administración de este tipo de copias de seguridad es complicado y engorroso si queremos estar seguros de la calidad y utilidad de las mismas. PostgreSQL nos proporciona la infraestructura necesaria para poder realizar backups del tipo PITR, pero somos nosotros los que debemos de crear todo lo necesario para ponerla en uso.

Introducción

PITR no es otra cosa que el almacenamiento y copia continua de todas las transacciones producidas por PostgreSQL desde el ultimo backup realizado a nivel de sistema de fichero. Esta copias de seguridad se podran usar en caso de un fallo grave de hardware con perdida de la zona de datos, ó de necesidad de restaurar nuestra base de datos en un determinado momento del pasado.

PITR funciona de la siguiente manera cuando está activado:

  • PostgreSQL almacena todos los ficheros WAL (Write Ahead Log - información sobre las transacciones realizadas) generados por el sistema. Este almacenamiento es continuo y no parará una vez activado
  • Cada cierto tiempo debemos de realizar la denominada copia de seguridad base. Esta copia de seguridad se realiza a nivel del sistema de ficheros, sin apagar PostgreSQL y sin procuparnos de lo inconsistente que sea por ello.
  • Una vez terminada la copia de seguridad base habrá que borrar todos ficheros WAL antiguos que no se necesiten.
  • En caso de catástrofe, podremos utilizar nuestra copia de seguridad inconsistente, copia de seguridad base, más todos los ficheros WAL archivados desde el termino de esta copia hasta el momento del fallo, para restaurar nuestra base de datos a un estado consistente y sin perdida de datos.
  • De la misma manera se puede devolver a la base de datos al estado en que se encontraba en un determinado momento, de ahi que se llame "recuperacion a un punto del tiempo"

Implementando PITR en nuestro sistema

A continuación vamos a ver como se puede administrar todo esto, las reglas a seguir y lo que necesitamos hacer para poder empezar a utilizar PITR en nuestro sistema:

  • Todos los datos se basan en la versión 8.3.x de PostgreSQL
  • Las particiones de datos/WAL y las de backup tienen que encontrarse en discos diferentes. Esto es importantisimo. Los sistemas donde se utiliza PITR, suelen estar conectados a cabinetes externos de discos totalmente independientes unos de otros, con redundancia de componentes a diferentes niveles y RAID. El mínimo absoluto que se necesita para garantizar los datos si utilizais un servidor con discos internos es que los datos/WALs y los backups esten en dos discos independientes.
  • Vamos a utilizar la funcionalidad LVM-snapshot proveida por Linux Volume Manager para crear las copia de seguridad base y minimizar la inconsistencia de esta copia. Esto suele ayudar bastante en sistemas con bases de datos grandes y actualizadas constantemente.

    El grupo de volúmenes (VG) utilizado para albergar el volumen lógico (LV) utilizado para la partición de datos, debe de tener espacio libre suficiente para poder realizar un LVM-snapshot del LV de datos. Cuanto espacio extra libre se necesitará, dependerá de lo mucho que cambien vuestros datos en el LV mientras que el LVM-snapshot esta activo. Con un 25% de espacio libre en el VG deberiais de tener más que suficiente. El informe mandado al final por pitr_basebackup.sh incluye información sobre cuanto espacio se ha utilizado, el atributo con esta informacion es Allocated to snapshot

  • archive_wal.sh se va a utilizar por PostgreSQL para archivar continuamente todos los ficheros WAL generados.
  • archive_last_wal.sh se va a ejecutar via cron para archivar el último fichero WAL en uso residente en el directorio pg_xlog y que todavia no se ha archivado por PostgreSQL
  • pitr_basebackup.sh se va a ejecutar via cron para crear una copia de seguridad de la partición de datos a nivel de sistema de ficheros, y para borrar todos los ficheros WAL archivados que ya no se necesitan

Un sistema de almacenamiento ideal para nuestro sistema podria tener esta configuración:

Dependiendo de la complejidad y redundancia de nuestro sistema este esquema puede variar. El más simple de todos solo tendria dos discos, uno para datos/WAL y otro para backup/logs, no tendria RAID y solamente un VG con un LV para la particion de datos y espacio libre para el snapshot.

Scripts de administración de PITR

Para administrar PITR vamos a utilizar 3 scripts en BASH, pitr_basebackup.sh, archive_wal.sh y archive_last_wal.sh. Los podeis grabar en un directoria que este definido en vuestro PATH, por ejemplo /usr/local/bin

Estos 3 scripts han estado en uso en la Universidad de Oslo durante varios años realizando sus trabajo sin problemas. Para que os hagais una idea, durante el último año ejecutamos pitr_basebackup.sh aproximádamente unas 3.650 veces, archive_last_wal.sh unas 5.256.000 y archive_wal.sh unas 500.000 veces. Muchas de las comprobaciones que hacen son para asegurarse que todo funciona sin problemas y para mandar informes despues de haber descubierto problemas y fallos durante situaciones no planeadas (discos llenos, etc).

Al final de este artículo teneis los tres scripts a modo de ejemplos para que veais como están implementados. Si quereis utilizarlos vais a tener que modificar algunas de las variables para adaptarlos a vuestro sistema. Las versiones de estos scripts que nosotros tenemos en uso som más completas ya que están integradas en nuestro sistema de administración y entre otras cosas graban el estatus de las ejecuciones en una base de datos interna.

A continuación teneis una descripción de las tareas que estos scripts realizan:

archive_wal.sh

  • Comprobar que la partición para archivar ficheros WAL existe
  • Comprobar que la partición donde se generan ficheros WAL existe
  • Comprobar que el fichero WAL que se intenta archivar no está ya archivado
  • Archivar el fichero WAL definido en la partición para archivar ficheros WAL

archive_last_wal.sh

  • Borrar los archivos WAL obsoletos grabados en la partición para archivar el último fichero WAL en uso, residente en el directorio pg_xlog
  • Archivar el último fichero WAL en uso residente en el directorio pg_xlog y que todavia no se ha archivado

pitr_basebackup.sh

  • Comprobar que la partición para archivar el fichero con copia de seguridad base existe
  • Estimar el espacio necesario para crear copia de seguridad base y parar el script para evitar problemas si estimamos que la partición se va a llenar durante le ejecución de este script.
  • Comprobar que no se esta generando una copia de seguridad base
  • Registrar el comienzo de la copia de seguridad base con pg_start_backup()
  • Crear un LVM-snapshot de la partición de datos
  • Montar el volumen que contien el LVM-snapshot
  • Crear un fichero tar de todo el contenido en el volumen LVM-snapshot
  • Desmontar el volumen que contien el LVM-snapshot
  • Destruir el contenido del LVM-snapshot
  • Registrar el final de la copia de seguridad base con pg_stop_backup()
  • Archivar el último fichero WAL en uso residente en el directorio pg_xlog y que todavia no se ha archivado
  • Borrar los archivos WAL obsoletos
  • Borrar los ficheros con copia de seguridad base obsoletos
  • Generar informe de la ejecución

Usando los scripts de administración de PITR

archive_last_wal.sh se ejecutará cada minuto por el usuario 'postgres' desde cron. Para ello teneis que actualizar el fichero cron del usuario postgres con esta linea:

*  *  *  *  *   /usr/local/bin/archive_last_wal.sh -S hostname

archive_wal.sh se ejecutará automaticamente por postgreSQL cuando lo necesite. Para que esto ocurra tendreis que activar PITR en el fichero postgresql.conf definiendo estas dos lineas:

archive_mode = on           
archive_command = '/usr/local/bin/archive_wal.sh -P %p -F %f -S hostname'

pitr_basebackup.sh se tiene que ejecutar tambien por el usuario 'postgres' desde cron. Dependiendo de lo grande que sea vuestra instalación PostgreSQL y lo mucho que los datos cambien en la misma, tendreis que ejecutar este script más ó menos a menudo. Si dejais pasar mucho tiempo entre cada ejecución y teneis una instalación que genere muchos ficheros WAL, tardareis mucho en restaurar el sistema si teneis que hacer esto alguna vez.

Yo suelo ejecutarlo una vez al dia por la noche. Podeis por ejemplo actualizar el fichero cron del usuario postgres con esta linea:

01  03  *  *  *  /usr/local/bin/pitr_basebackup.sh -S hostname -c t 

Como veis, todos estos scripts se ejecutan por el usuario postgres. Por ello tendreis que actualizar el fichero /etc/sudoers con la siguiente linea:

postgres  ALL = NOPASSWD: /usr/sbin/lvcreate, /usr/sbin/lvremove, 
/usr/sbin/lvdisplay, /usr/sbin/vgdisplay, /bin/mount, /bin/umount

El usuario postgres tiene que poder conectarse al cluster PostgreSQL via sockets y sin clave. Más información sobre como configurar la cuanta de administrador postgres, se puede encontrar en el artículo Asegurando la cuenta de administrador "postgres".

Restaurar datos

Si tuvieramos que restaurar los datos de nuestro cluster PostgreSQL a partir de las copias de seguridad realizadas por estos scripts tendriamos que realizar las siguientes operaciones:

  • Si existe algún fichero en $PG_DATA_PARTITION, borrarlo.
  • Si existe algún fichero en $PG_ARCH_PARTITION, borrarlo.
  • Descomprimir en $PG_DATA_PARTITION la copia de seguridad base (fichero tar) grabado en $PG_BACKUP_PITR_DATA
  • Asegurarse que $PG_DATA_PARTITION y $PG_ARCH_PARTITION y sus contenidos tienen al usuario postgres como dueño
  • Copiar a $PG_BACKUP_PITR_WAL el fichero WAL grabado en $PG_BACKUP_PITR_LAST
  • Cambiar el nombre del directorio $PG_BACKUP_PITR_WAL a ${PG_BACKUP_PITR_WAL]_restore
  • Crear un directorio con el nombre $PG_BACKUP_PITR_WAL
  • Asegurarse que todos los ficheros WAL en ${PG_BACKUP_PITR_WAL]_restore tienen permisos de escritura para el usuario postgres
  • Borrar los ficheros postmaster.pid y postmaster.opts en $PG_DATA_PARTITION
  • Crear en $PG_DATA_PARTITION un fichero llamado recovery.conf con esta linea:
    restore_command = 'cp ${PG_BACKUP_PITR_WAL]_restore/%f %p'
    
  • Cambiar archive_mode a 'off' y listen_addresses a '' en el archivo postgresql.conf
  • Arrancar PostgreSQL
  • Esperar a que termine el proceso de restauración. Podeis comprobar cuando ha terminado cuando estas dos lineas aparezcan en el fichero log de PostgreSQL:
    LOG: archive recovery complete
    LOG: database system is ready
    
  • Cambiar archive_mode a 'on' y listen_addresses a su valor original
  • Apagar y arrancar PostgreSQL
Los valores de las variables en esta lista están definidas en pitr_globalconf.sh.

Actualización 10.2011

  • Versiones actualizadas de los scripts se encuentran disponibles en GITHUB
  • A partir de la versión 9.0 hay que definir un parametro más (wal_level) en el fichero postgresql.conf
    wal_level = archive
    archive_mode = on           
    archive_command = '/usr/local/bin/archive_wal.sh -P %p -F %f -S hostname'
    
  • Un nuevo script pitr_globalconf.sh contiene todas las funciones y variables globales usadas en el resto de scripts. De esta manera solo hay que modificar este fichero.

Scripts

En GITHUB teneis los tres scripts de los que hablamos en este artículo liberados bajo la licencia GPLv3. Los podeis encontrar en:

https://github.com/rafaelma/pitr_scripts

Tendreis que actualizar las variables definidas en pitr_globalconf.sh para adaptarlas a vuestros sistemas. Es importante que tengais la configuración LVM de vuestro sistema correctamente configurada para que estos scripts funcionen. Mas información en el fichero README que encontrareis en GITHUB.

Más información

Comentarios

Opciones de visualización de comentarios

Seleccione la forma que prefiera para mostrar los comentarios y haga clic en «Guardar las opciones» para activar los cambios.
Imagen de rafaelma

Nueva version

Estamos preparando una nueva version de estos scripts que usa un fichero global de configuración para evitar configuración redundante.

Sugerencias

Tratándose de sistemas linux, esta bastante correcto, pero tratando de sistemas como freebsd o solaris, basta con definir 2 raidz en mirrors (zfs) o 2 raidz2(doble paridad), y tienes asegurada la integridad de los datos, igual con freebsd con unas cuantas clases de geom se puede conseguir lo mismo, por lo que teniendo un mirror de un raidz y ademas siempre tener una copia diaria de la db en otro disco tienes solucionado el problema. Ademas de que no hace falta que postgresql registre en disco datos adicionales. siempre tendra la ultima db consistente, a pesar de fallo de hardware o algún error de usuario.

Imagen de rafaelma

RAID y PITR

El uso de PITR es independiente de los mecanismos de seguridad y redundantes que tengas instalados a nivel del sistema de almacenamiento.

Los ejemplos que pones de diversos tipos de raid en otros sistemas operativos no anulan la necesidad de archivar las transacciones a nivel de base de datos (oracle y otras bases de datos funcionan igual).

Si no tienes activado el archivo continuo de transacciones (PITR) perderas todos los datos desde el momento del ultimo backup (consistente, por supuesto) hasta el momento en que un fallo grave de hardware con perdida de datos te oblige a restaurar tu base de datos desde una copia de seguridad.

PITR puede activarse/usarse independientemente de si usas o no un sistema raid (no importa el tipo o el sistema operativo)

RAID y PITR

Si bueno, tienes la razón, lo que quise decir es que, freebsd/solaris integran sistemas que prevén esos fallos graves de hardware, por lo que es un plus, nunca quise decir que no hace falta PITR, siento la confusión.

Compatible

Disculpa se que fuiste muy especifico con en decir los requerimientos y las caracteristicas del sistema y alli nos dices que todos los datos estan basados en la version 8.3.

Yo manejo una base de datos bastante grande, bajo un SO Debian etch y Postgresql 8.1, mi inquitud es si basicamente esot tambien puede ser implantado bajo este ambiente y si los script que estan aqui publicados pueden tomarse como base para la implantacion es decir si son (compatibles) en este ambiente.

Imagen de rafaelma

Re: Compatible

No deberias de tener problemas usando los scripts con la version 8.1.

Es mas, si recuerdo bien, versiones anteriores a estos scripts fueron utilizados en los sistemas que administro cuando utilizaban la version 8.1.

Las actualizaciones de estos scripts desde que los usabamos con la version 8.1 han sido para hacerlos mas robustos y no por incompatibilidades entre versiones de postgreSQL.

PD.- Si recuerdo bien, el parametro archive_mode = on en postgresql.conf solo existe a partir de la version 8.3. Antes de esta version habia que comentar o quitar el comentario al parametro archive_command para activar/desactivar PITR.

--
Rafael Martinez
Webmaster

Imagen de JF Aspal

Archiving

Efectivamente en el postgres 8.1 no existe en el postgresql.conf esa opcion la que existe es la de archive comand y por lo que he investigado para activarla se descomenta y ademas podrias definirle un directorio donde guardaria todos los log generados por los WAL, (Abajo te muestro mi configuracion para ese parametro); he seguido los pasos que se mencionan en el tutorial publicado de PITR ya instale la utilidad de LMV y me quede alli bueno estoy configurando algo ahi del LMV, para que este operable en realidad no se si basta solo con haberlo instalado y ya ando investigando, espero este dia terminar de leerlo todo y probar, cualquier cosa te avisare este tema es bien interesante y de gran utilidad en donde estoy trabajando por eso me tomare mi tiempo y de antemano gracias por todo el apoyo.

Archiving

archive_command = 'cp "%p" /mnt/server/archivedir/"%f"'
# command to use to archive a logfile

Ademas configure el Log_filename y lo configure con la informacion que crei pertinente para ubicar cuales son los archivos necesarios a la hora de hacer un Recovery de alguna base de datos en especifico ya que note que en el directorio asignado al Archive_command, es el destino para todos los archivos WAL de un Cluster que puede y de seguro debe tener varias bases de datos, asi que creo que un prefijo en el nombre del (log_filename) con una informacion suficiente nos facilitaria la tarea de los recovery en especifico de alguna base de dato... ahora tambien disculpa pues estoy hablando sin fundamento pues creo que eso es configurable ya en el PITR pero es pensando a futuro y asumiendo algunas cosas.

log_filename = 'Database: %d -Usuario: %u -Host: %r -Proceso: %p -Duracion: %t -SQL: %i ' # Log file name pattern.

Esta configuracion me da el nombre de la
Base de datos
Usuario
Host
Proceso
Duracion de la ejecucion

De ante manos muchas gracias por la ayuda ofrecida.

pd: yo postee el comentario de Compatibilidad.

Jose Francisco Palmero Asuaje

Problema

ya estoy implantando en mi sistema este script y lo he modificado y ajustado al ambiente adecuandolo, para que el script pueda funcionar adecuadamente ahora bien al momento de ejecutarlo en este momento se esta generando un error que no logro solucionar, si alguien me puede ayudar se los agradeceria...

el error es el siguiente
let FIVE_PORCENT_PR_DATA_DISK=`$DF $PG_BACKUP_PARTITION | $EGREP "([0-9])%" | $AWK -F' ' '{print $1}'`*5/100

esta sobre Debian
postgres 8.1
lvm2
2 particiones sda 6 y sda 7 con formato para lvm

Re: Problema


el error es el siguiente
let FIVE_PORCENT_PR_DATA_DISK=`$DF $PG_BACKUP_PARTITION | $EGREP "([0-9])%" | $AWK -F' ' '{print $1}'`*5/100

Esta linea es parte del código, ¿Cual es el mensaje de error total que te da?

Problema

Este es el problema en especifico en el 1º se refiere como a que existe un problema al calcular el 5% del PITR_DATA_DISK, ahi tengo una duda porque si tengo esa particion con ese nombre sda 5, pero las que tengo asignadas para LVM es sda6 y sda7 asi que no se si esto lo afecte o tenga algo que ver y como modificarlo, y con respecto al segundo error tiene como raiz al 1ro puesto a que este hace referencia del dato que deberia arrojar el 1º.

1º) /usr/local/bin/pitr_basebackup.sh:
line 268: let: FIVE_PORCENT_PITR_DATA_DISK=/dev/sda5*5/100:
syntax error: operand expected (error token is "/dev/sda5*5/100")

2º)/usr/local/bin/pitr_basebackup.sh: line 272: [: -10720194: unary operator expected

cualquier ayuda se los agradeceria, tengo varios dias que no salgo de este problema...