Una copia diaria te devuelve la base de datos tal como estaba anoche, pero si alguien ejecuta un DELETE sin WHERE a las 17:42, perderías todo lo escrito durante el día. La recuperación a un momento específico (PITR, point-in-time recovery) combina una copia base con el registro de transacciones que la base de datos ya genera, y reproduce ese registro hasta justo antes del error. En este tutorial configurarás PITR en Ubuntu 24.04 para MySQL 8.0, con los logs binarios, y para PostgreSQL 16, con el archivado de WAL, y harás una recuperación completa en cada uno.

Requisitos previos

Para seguir esta guía necesitas:

  • Un servidor con Ubuntu 24.04 LTS, por ejemplo un VPS de CubePath, con MySQL 8.0 (mysql-server) o PostgreSQL 16 (postgresql) instalados desde los repositorios de Ubuntu.
  • Un usuario no root con privilegios sudo.
  • Espacio en disco para la copia base y para los registros de transacciones de, al menos, el periodo entre dos copias base.
  • Idealmente, un servidor de pruebas donde ensayar la recuperación antes de necesitarla en producción.

Las secciones de MySQL y PostgreSQL son independientes: sigue solo la del motor que uses.

Cómo funciona PITR

Cualquier recuperación a un momento concreto tiene tres piezas:

  • Copia base: una copia consistente de toda la base de datos en un instante conocido.
  • Registro de transacciones: los logs binarios en MySQL o los segmentos WAL en PostgreSQL, que contienen cada cambio posterior a la copia base.
  • Objetivo de recuperación: el punto (una hora o una posición en el log) hasta el que se reproducen los cambios.

Si el error ocurrió a las 17:42 y la copia base es de las 03:00, restauras la copia de las 03:00 y reproduces los cambios de 03:00 a 17:41:59. Por eso es imprescindible conservar, sin huecos, todos los registros de transacciones desde la última copia base.

MySQL 8.0 con logs binarios

Paso 1: Configurar los logs binarios

MySQL 8.0 activa los logs binarios por defecto, en formato ROW, con archivos binlog.NNNNNN en /var/lib/mysql/ y una retención de 30 días. Comprueba el estado actual:

sudo mysql -e "SHOW VARIABLES WHERE Variable_name IN ('log_bin','binlog_format','binlog_expire_logs_seconds','server_id','gtid_mode');"

+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
| binlog_format              | ROW     |
| gtid_mode                  | OFF     |
| log_bin                    | ON      |
| server_id                  | 1       |
+----------------------------+---------+

Si log_bin está en OFF, algún archivo de configuración lo desactiva con skip-log-bin o disable-log-bin. Busca esa línea en /etc/mysql/mysql.conf.d/mysqld.cnf, elimínala y reinicia MySQL.

La retención debe cubrir con holgura el intervalo entre copias base. Para dejarla explícita en 14 días, edita el archivo de configuración:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Añade al final de la sección [mysqld]:

server-id                  = 1
binlog_expire_logs_seconds = 1209600
sync_binlog                = 1

sync_binlog = 1 (el valor por defecto en 8.0) fuerza la escritura a disco en cada commit, así que no perderás transacciones confirmadas si el servidor se apaga de golpe. Reinicia y comprueba que MySQL arranca:

sudo systemctl restart mysql
sudo mysql -e "SHOW BINARY LOGS;"

Paso 2: Crear la copia base

Crea un directorio para las copias:

sudo install -d -m 0700 /var/backups/mysql

Haz un volcado consistente de todas las bases de datos. --single-transaction lo hace sin bloquear tablas InnoDB, --flush-logs rota el log binario para que el siguiente archivo empiece justo después de la copia, y --source-data=2 anota como comentario el archivo y la posición del log en ese instante:

sudo sh -c 'mysqldump --all-databases --single-transaction --flush-logs --source-data=2 --routines --events --triggers | gzip > /var/backups/mysql/base-$(date +%F-%H%M).sql.gz'

Consulta las coordenadas guardadas en el volcado:

zcat /var/backups/mysql/base-*.sql.gz | grep -m1 'SOURCE_LOG_FILE'

-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000004', SOURCE_LOG_POS=157;

Guarda estos dos valores: son el punto desde el que tendrás que reproducir los logs.

Paso 3: Guardar los logs binarios fuera del servidor

Los logs en /var/lib/mysql/ no sirven de nada si pierdes el disco. Copia periódicamente la copia base y los logs a otra máquina. Primero cierra el log actual para que quede completo:

sudo mysql -e "FLUSH BINARY LOGS;"

Después sincronízalos con tu servidor de backups (sustituye backup_user y your_backup_server):

sudo rsync -a /var/lib/mysql/binlog.[0-9]* /var/backups/mysql/ backup_user@your_backup_server:/srv/backups/mysql-host/

Programa estos dos comandos cada 15 minutos (con cron o un temporizador de systemd) y tu pérdida máxima de datos ante un fallo total del servidor será de unos 15 minutos.

Paso 4: Recuperar MySQL a un momento concreto

Supón que a las 17:42 se borró la tabla orders de la base de datos appdb. Primero, detén las escrituras de la aplicación para que no se sigan generando cambios.

Copia los logs a un lugar seguro antes de tocar nada, porque la restauración generará actividad nueva en el servidor:

sudo install -d -m 0700 /root/pitr
sudo cp /var/lib/mysql/binlog.[0-9]* /root/pitr/

Localiza la sentencia dañina en el log que estaba activo a esa hora (normalmente el más reciente; ls -l /root/pitr muestra la fecha de cada archivo). --base64-output=DECODE-ROWS -v muestra los cambios en un formato legible:

sudo mysqlbinlog --base64-output=DECODE-ROWS -v --start-datetime="2026-09-25 17:30:00" /root/pitr/binlog.000005 | grep -B 12 'DROP TABLE'

...
# at 48213
#260925 17:42:08 server id 1  end_log_pos 48361 CRC32 0x5f1c2a7e 	Query	thread_id=812	exec_time=0	error_code=0	Xid = 23117
use `appdb`/*!*/;
SET TIMESTAMP=1790350928/*!*/;
DROP TABLE `orders` /* generated by server */

La línea # at 48213 es la posición de binlog.000005 en la que empieza el evento dañino. Esa será la posición de parada: se aplica todo lo anterior y nada a partir de ella.

Restaura la copia base sin escribir en el log binario, para no mezclar la restauración con el historial original:

zcat /var/backups/mysql/base-2026-09-25-0300.sql.gz | sudo mysql --init-command="SET SESSION sql_log_bin=0"

Reproduce los logs desde la posición anotada en el paso 2 hasta justo antes del error. --start-position se aplica al primer archivo y --stop-position al último:

sudo mysqlbinlog --start-position=157 --stop-position=48213 /root/pitr/binlog.000004 /root/pitr/binlog.000005 | sudo mysql --init-command="SET SESSION sql_log_bin=0"

Si prefieres parar por hora, usa --stop-datetime="2026-09-25 17:42:00" en lugar de --stop-position. La posición es más precisa: dentro del mismo segundo puede haber transacciones buenas y malas.

Verifica el resultado:

sudo mysql -e "SELECT COUNT(*), MAX(created_at) FROM appdb.orders;"

La tabla debe existir y la última fila debe ser de poco antes de las 17:42. Cuando lo confirmes, reactiva la aplicación y haz una copia base nueva.

PostgreSQL 16 con archivado de WAL

Paso 1: Activar el archivado de WAL

PostgreSQL escribe cada cambio en el WAL (write-ahead log) y recicla los segmentos cuando ya no los necesita. Para PITR hay que copiar cada segmento completo a un archivo antes de que se recicle. Crea el directorio del archivo:

sudo install -d -m 0700 -o postgres -g postgres /var/lib/postgresql/wal_archive

En producción, este directorio debería estar en otro disco o sincronizarse con otra máquina. Crea un archivo de configuración propio; Ubuntu carga automáticamente todo lo que hay en conf.d:

sudo nano /etc/postgresql/16/main/conf.d/archiving.conf

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'
archive_timeout = 300

archive_command se niega a sobrescribir un segmento ya archivado, y archive_timeout = 300 fuerza el cambio de segmento cada 5 minutos aunque haya poca actividad, lo que limita la pérdida de datos. archive_mode requiere reiniciar:

sudo systemctl restart postgresql@16-main

Fuerza un cambio de segmento y comprueba que se archiva:

sudo -u postgres psql -c "SELECT pg_switch_wal();"
sudo -u postgres psql -c "SELECT archived_count, last_archived_wal, failed_count FROM pg_stat_archiver;"

 archived_count |    last_archived_wal     | failed_count
----------------+--------------------------+--------------
              1 | 000000010000000000000003 |            0

Si failed_count sube, revisa /var/log/postgresql/postgresql-16-main.log: normalmente es un problema de permisos del directorio.

Paso 2: Crear la copia base

Crea el directorio de copias y lanza pg_basebackup. -X stream incluye en la copia el WAL necesario para que sea consistente por sí sola, y -c fast fuerza un checkpoint inmediato:

sudo install -d -m 0700 -o postgres -g postgres /var/lib/postgresql/basebackups
sudo -u postgres pg_basebackup -D /var/lib/postgresql/basebackups/base-$(date +%F-%H%M) -Fp -X stream -c fast -P

Verifica la copia con pg_verifybackup, que compara cada archivo con el manifiesto generado:

sudo -u postgres /usr/lib/postgresql/16/bin/pg_verifybackup /var/lib/postgresql/basebackups/base-2026-09-25-0300

backup successfully verified

Haz una copia base nueva cada día o cada semana. Los segmentos WAL anteriores a la copia base más antigua que conserves ya no son necesarios y puedes borrarlos del archivo.

Paso 3: Recuperar PostgreSQL a un momento concreto

Supón de nuevo un borrado accidental a las 17:42. Detén la aplicación y después el clúster:

sudo systemctl stop postgresql@16-main

Aparta el directorio de datos actual en lugar de borrarlo; si algo sale mal, podrás volver a él:

sudo mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main.broken

Copia la copia base en su lugar con los permisos que exige PostgreSQL:

sudo cp -a /var/lib/postgresql/basebackups/base-2026-09-25-0300 /var/lib/postgresql/16/main
sudo chown -R postgres:postgres /var/lib/postgresql/16/main
sudo chmod 700 /var/lib/postgresql/16/main

Configura el objetivo de recuperación en un archivo aparte, para poder eliminarlo al terminar:

sudo nano /etc/postgresql/16/main/conf.d/pitr.conf

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-09-25 17:42:00+02'
recovery_target_action = 'promote'

Indica siempre la zona horaria en recovery_target_time. recovery_target_action = 'promote' hace que, al llegar al objetivo, el servidor termine la recuperación y acepte escrituras. Si quieres revisar los datos antes de promocionar, usa 'pause'.

Crea el archivo recovery.signal, que indica a PostgreSQL que arranque en modo recuperación:

sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal

Arranca el clúster y sigue el log:

sudo systemctl start postgresql@16-main
sudo tail -n 20 /var/log/postgresql/postgresql-16-main.log

LOG:  starting point-in-time recovery to 2026-09-25 17:42:00+02
LOG:  restored log file "000000010000000000000005" from archive
...
LOG:  recovery stopping before commit of transaction 91822, time 2026-09-25 17:42:08.114+02
LOG:  redo done at 0/7A1C3F8
LOG:  selected new timeline ID: 2
LOG:  database system is ready to accept connections

Comprueba que el servidor ya no está en recuperación y que los datos son los esperados:

sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
sudo -u postgres psql -d appdb -c "SELECT COUNT(*), MAX(created_at) FROM orders;"

pg_is_in_recovery debe devolver f. Elimina la configuración de recuperación para que no se aplique en el próximo arranque, y haz una copia base nueva, ya que el servidor ha empezado una línea temporal nueva:

sudo rm /etc/postgresql/16/main/conf.d/pitr.conf
sudo -u postgres pg_basebackup -D /var/lib/postgresql/basebackups/base-$(date +%F-%H%M) -Fp -X stream -c fast -P

Cuando todo funcione, borra /var/lib/postgresql/16/main.broken.

Solución de problemas

MySQL: ERROR 1062 Duplicate entry al reproducir los logs. Has empezado en una posición anterior a la copia base, o has aplicado dos veces el mismo archivo. Usa exactamente el archivo y la posición de SOURCE_LOG_FILE y SOURCE_LOG_POS del volcado.

MySQL con GTID activado (gtid_mode = ON). Los eventos reproducidos se saltan porque sus GTID ya constan como ejecutados. Antes de reproducir, ejecuta RESET MASTER en el servidor restaurado (solo si no tiene réplicas) o reproduce con mysqlbinlog --skip-gtids.

PostgreSQL: recovery ended before configured recovery target was reached. Falta algún segmento WAL en el archivo o el objetivo es posterior al último WAL archivado. Comprueba que no hay huecos en /var/lib/postgresql/wal_archive/ y que failed_count de pg_stat_archiver era 0.

PostgreSQL: el objetivo se ignora y recupera hasta el final. Falta recovery.signal en el directorio de datos o pitr.conf no está en conf.d. Revisa el log al arrancar: debe aparecer starting point-in-time recovery to ....

Conclusión

Con los logs binarios de MySQL o el archivado de WAL de PostgreSQL, tu pérdida de datos máxima pasa de "desde la última copia" a unos minutos, y puedes deshacer un error humano sin perder el resto del día. Como siguientes pasos, envía los logs y el WAL fuera del servidor de forma continua, automatiza las copias base con un temporizador de systemd y ensaya la recuperación en un servidor de pruebas cada cierto tiempo. Para PostgreSQL en producción, herramientas como pgBackRest o Barman gestionan la retención y la compresión por ti.