PostgreSQL: Restaurar a un momento en el tiempo desde el archivado de WAL

Descripción del Escenario

Partimos de una instalación del PostgreSQL 9.2.18 con archivado de WAL comprimido sobre Centos7 utilizando únicamente los tablespaces por defecto. Las principales rutas son las siguientes:

• Directorio Data: /var/lib/pgsql/data
• Directorio Archivado de WAL comprimido: /var/lib/pgsql/PostgresqlWalCompr
• Directorio para almacenar los Backups: /var/lib/pgsql/PostgresqlBackup

La configuración del archivado de WAL en el fichero postgresql.conf es la siguiente:

Sobre este escenario, vamos a realizar las pruebas que detallamos a continuación.

Preparando el Backup

Lo primero de todo vamos a hacer un Backup del filesystem a un fichero comprimido (data.tar.gz) utilizando pg_start_backup y pg_stop_backup, como ya explicamos en el anterior artículo PostgreSQL: Hot Physical Backup and Restore.

Realizado esto, vamos a añadir en un cron una llamada a psql cada 15 minutos para insertar filas con el valor de current_timestamp en una tabla denominada test_table localizada en una base de datos llamada guillesql, para así tener información de prueba, y poder comprobar a qué momento del tiempo hemos restaurado nuestra base de datos. A continuación se muestra la sentencia psql que hemos utilizado.

También vamos a forzar manualmente, de vez en cuando, el rotado/archivado de los WAL, para así garantizar que tenemos diferentes WAL archivados y que en el directorio pg_xlog no queda ningún WAL, sólo los más recientes, para así probar la recuperación del archivado de WAL. Para forzar el rotado de WAL ejecutaremos lo siguiente cada vez que lo queramos forzar:

Más tarde vamos a realizar otro backup del filesystem, igual que el anterior, pero en esta ocasión contra otro fichero (data2.tar.gz), seguiremos forzando rotados/archivados de WAL, incluso algún otro insert.

Con todo esto, tenemos completamente listo nuestro escenario para el comenzar con el restore.

Realizar el Restore a un momento del tiempo desde el Archivado de WAL

Ahora llega el momento más divertido, el de hacer el restore a un momento en el tiempo, recuperando de un Backup de filesystem y de los archivados de WAL. En nuestro caso de ejemplo, que hicimos dos Backups de filesystem en dos momentos del tiempo (a las 11:30 y a las 12:45 aprox), vamos a recuperar del primer Backup, y después vamos a recuperar las transacciones desde el archivado de WAL hasta un momento del tiempo posterior al segundo Backup del filesystem, en particular, hasta las 14:05 (imaginemos que por un problema no tenemos disponible el último Backup de filesystem… pues recuperamos del anterior y aplicamos todas las transacciones necesarias hasta el momento del tiempo al que deseamos recuperar).

Lo primero, vamos a parar PostgreSQL y a hacer un Backup del Archivado de WAL a un tar comprimido (archive.tar.gz) para garantizar que podemos volver al momento más reciente. Con esto, ya tenemos tres tar comprimidos, dos de los backups del filesystem y otro con todo el archivado de WAL (antes de parar PostgreSQL, vamos a forzar un archivado de WAL, para tener todas las transacciones hasta el último momento).

Para continuar, tenemos que eliminar el directorio data (antes de recuperarlo del Backup de filesystem), aunque si es posible, deberíamos moverlo o hacer un Backup previo a un tar comprimido, para poder dar la marcha atrás si fuera necesario (es decir, tener un Backup en frío, con PostgreSQL parado, justo antes del Restore). Seguidamente, vamos a descomprimir el Backup de filesystem que queremos recuperar (el de las 11:30), y vamos a eliminar el contenido del directorio pg_xlog. Hecho esto, creamos el fichero recovery.conf, que es la madre del cordero.

En el fichero recovery.conf, deberemos especificar el comando de recuperación, es decir, que comando tiene que ejecutar PostgreSQL para recuperar un WAL del Archivado (copiar, copiar y descomprimir, etc.), dependerá de que estemos haciendo para el archivado (el parámetro archive_command del fichero de configuración postgresql.conf). También especificaremos a qué momento del tiempo deseamos recuperar, en nuestro caso de ejemplo, a las 14:05.

Hecho esto, ya solo queda arrancar PostgreSQL, que comprobará la existencia del fichero recovery.conf, se iniciará en modo recuperación, recuperará al momento del tiempo que le hemos indicado desde el archivado del WAL, y al finalizar renombrará el fichero recovery.conf como recovery.done y comenzará a aceptar peticiones y dar servicio. En nuestro caso de ejemplo tardó sobre un minuto. Seguidamente, comprobaremos que hemos conseguido recuperar al momento del tiempo deseado, que en nuestro caso de ejemplo, lo podemos hacer ejecutando una select contra la tabla en la que insertamos la fecha y hora cada quince minutos con el cron.

A continuación se muestra el resultado de ejecución. Objetivo conseguido.

Si comprobamos el contenido del directorio data, podremos ver que el fichero recovery.conf ha sido renombrado como recovery.done al finalizar la recuperación, y también podemos observar el fichero backup_label.old que si lo visualizamos nos permitirá conocer la información básica del Backup de filesystem que hemos recuperado.

Si revisamos el LOG de PostgreSQL, podremos observar una secuencia de mensajes como la siguiente:

Poco más por hoy. Como siempre, confío que la lectura resulte de interés.