Backup y restore en SQLite: .backup y VACUUM INTO

Por qué no basta con un cp del archivo

Una base de datos SQLite es un único fichero, así que la tentación de copiarla con cp está ahí. A veces funciona. Muchas otras, no.

Hay dos cosas que pueden salir mal:

• Otra conexión está escribiendo justo cuando copias. El fichero de destino se queda con una transacción a medias y, al abrirlo, está corrupto.
• La base de datos está en modo WAL (lo habitual en la mayoría de aplicaciones modernas). Los cambios recientes viven en un fichero aparte, database.db-wal. Si copias solo el principal, pierdes datos sin enterarte.

SQLite trae sus propias herramientas para esto. Gestionan los bloqueos, el contenido del WAL y las escrituras concurrentes sin sorpresas. Tira de ellas en vez de cp.

El comando .backup de SQLite

La forma más rápida de hacer una copia de seguridad de SQLite desde la CLI es el dot command .backup:

sqlite3 app.db
sqlite> .backup backup.db
sqlite> .quit

Eso escribe una copia íntegra de app.db en backup.db. Funciona aunque haya otros procesos leyendo o escribiendo la base de datos: la API de backup toma varios bloqueos pequeños en lugar de uno grande, copia las páginas de forma incremental y reintenta aquellas que se modifican durante la copia.

El resultado es una base de datos SQLite totalmente funcional. Ábrela como cualquier otra:

sqlite3 backup.db
sqlite> .tables

También puedes hacerlo todo en un solo comando, que es justo como acaban viéndose la mayoría de los cron jobs:

sqlite3 app.db ".backup '/var/backups/app-$(date +%Y%m%d).db'"

Un fichero entra, un fichero sale. Sin pasar por un dump/restore, sin parsear SQL: simplemente se copian las páginas a nivel de almacenamiento.

VACUUM INTO para una copia compactada

VACUUM INTO es una herramienta parecida, pero con otro propósito. Escribe una copia recién construida de la base de datos en un fichero nuevo:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('primero'), ('segundo'), ('tercero');
DELETE FROM notes WHERE id = 2;

VACUUM INTO 'notes-backup.db';

SELECT 'copia de seguridad creada' AS status;

El resultado es la misma base de datos lógica, pero reescrita desde cero: cada página queda compactada al máximo, sin fragmentación ni páginas libres residuales de filas eliminadas. Eso hace que el fichero de backup ocupe lo mínimo posible.

Cuándo usar cada uno:

• .backup — copias de seguridad rutinarias y frecuentes. Más rápido, convive bien con escrituras concurrentes y es fiel byte a byte.
• VACUUM INTO — snapshots periódicos cuando además te interesa un fichero limpio y del menor tamaño posible. Es más lento porque reescribe todo, y mantiene un bloqueo de escritura sobre la base de datos origen mientras dura la operación.

Ambos generan un fichero .db válido que puedes abrir al instante.

La Online Backup API desde el código de la aplicación

Dentro de una aplicación no vas a invocar sqlite3 por línea de comandos. Lo que se hace es usar la online backup API que expone tu driver. En el módulo sqlite3 de la librería estándar de Python, eso se traduce en Connection.backup:

import sqlite3

source = sqlite3.connect("app.db")
dest = sqlite3.connect("backup.db")

with dest:
    source.backup(dest)

source.close()
dest.close()

El método backup copia páginas desde source hacia dest mientras otras conexiones siguen trabajando con normalidad. También puedes pasar pages= para copiar por bloques y progress= para recibir un callback — muy útil en bases de datos grandes cuando quieres regular el ritmo de copia o mostrar el progreso.

La mayoría de drivers en otros lenguajes exponen la misma API en C (sqlite3_backup_init, _step, _finish) con un nombre parecido. El esquema siempre es el mismo: abrir el origen, abrir el destino, recorrer las páginas paso a paso y cerrar.

Copias de seguridad SQLite en caliente

Aquí es donde SQLite brilla sin hacer ruido. Tanto .backup como la API de backup online están pensadas para copias en caliente: la base de datos de origen puede estar abierta y en uso durante todo el proceso.

Lo que ocurre por dentro:

1. El backup toma un lock compartido y empieza a copiar páginas.
2. Si un escritor modifica una página que aún no se ha copiado, el backup lo detecta y la vuelve a leer.
3. La copia termina cuando todas las páginas son consistentes entre sí.

No hace falta parar la aplicación, expulsar conexiones ni programar ventanas de mantenimiento. En una base de datos con mucha carga, el backup puede tardar algún ciclo extra en converger, pero acaba convergiendo. El fichero resultante es una instantánea consistente de un momento concreto en el tiempo.

Un detalle importante: si usas modo WAL, ejecuta PRAGMA wal_checkpoint(TRUNCATE); de vez en cuando para que el fichero WAL no crezca sin control. El backup en sí trata el WAL correctamente — esto es simplemente higiene general del WAL.

Restaurar una base de datos SQLite

Restaurar una base de datos SQLite es sorprendentemente aburrido, y eso es justo lo bueno. El fichero de backup es la base de datos. Para usarlo, basta con abrirlo:

sqlite3 backup.db
sqlite> SELECT COUNT(*) FROM notes;

Para restaurar sobre una base de datos en uso —por ejemplo, al recuperarte tras una pérdida de datos— la secuencia segura es esta:

1. Detén todos los procesos que tengan la base de datos abierta.
2. Borra los ficheros app.db, app.db-wal y app.db-shm que existan. Si dejas restos de WAL/SHM de la base anterior junto al fichero principal restaurado, SQLite se va a hacer un lío.
3. Copia tu backup en su sitio: cp backup.db app.db.
4. Vuelve a arrancar la aplicación.

Los ficheros -wal y -shm son clave. Si te saltas el paso 2, SQLite puede intentar aplicar un WAL obsoleto sobre el fichero principal recién restaurado, y acabarás con corrupción o con datos mezclados de forma rarísima.

Desde la propia CLI también tienes el comando .restore, que es el espejo de .backup:

sqlite3 app.db
sqlite> .restore backup.db
sqlite> .quit

Esto sobrescribe el contenido de la base de datos conectada con el contenido de backup.db. Usa la misma API de backup online, pero en sentido inverso.

.dump es otra herramienta distinta

En tutoriales antiguos verás menciones a .dump. No es una copia de seguridad en el mismo sentido: lo que hace es generar un fichero de texto SQL con sentencias CREATE e INSERT:

sqlite3 app.db .dump > app.sql

Para restaurar, vuelves a ejecutar el SQL:

sqlite3 new.db < app.sql

Esto resulta útil para migrar entre versiones de SQLite, comparar esquemas en git o mover datos a otro motor de base de datos. Eso sí, es más lento, ocupa más y pierde información respecto a .backup (las collations personalizadas, las columnas generadas y algunos pragmas pueden requerir cuidado extra). Si lo que quieres es una copia de seguridad real de una base de datos en producción, mejor tira de .backup o VACUUM INTO.

Una rutina de backup sensata

Para la mayoría de aplicaciones, esta combinación funciona muy bien:

• Una ejecución programada de .backup — cada hora, cada día, lo que tu tolerancia a la pérdida de datos permita. Barato, rápido y en caliente.
• Un VACUUM INTO semanal a una ruta distinta. Detecta desviaciones, te da un snapshot compactado y ejercita otra ruta de código.
• Una política de retención: guarda los últimos N backups diarios y los últimos M semanales. Las bases de datos SQLite comprimen muy bien, así que pasarles un gzip backup.db después merece la pena.
• De vez en cuando, restaura uno y lanza unas cuantas consultas contra él. Un backup sin probar es una esperanza, no una copia de seguridad.

# Diariamente, en cron:
sqlite3 /var/lib/app/app.db ".backup '/var/backups/app-$(date +%F).db'"
gzip "/var/backups/app-$(date +%F).db"

# Semanalmente:
sqlite3 /var/lib/app/app.db "VACUUM INTO '/var/backups/app-weekly-$(date +%F).db'"

Ambos comandos se pueden ejecutar de forma segura mientras la aplicación está atendiendo peticiones.

Siguiente: configuración con PRAGMA

Las copias de seguridad son una de las preocupaciones operativas; ajustar el comportamiento en tiempo de ejecución es otra. SQLite expone sus parámetros a través de sentencias PRAGMA: modo de journal, nivel de sincronización, tamaño de caché, control de claves foráneas. La siguiente página repasa las que vale la pena conocer.