Copias de seguridad lógicas y físicas
pg_dump y copias de seguridad base
Copias de seguridad lógicas y físicas es una lección gratuita de SQL Academy en CoddyKit. Esta es la lección 1 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de SQL Academy, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de SQL Academy incluye 4 lecciones en total.
¿Qué es una copia de seguridad de una base de datos?
Una copia de seguridad es una copia de los datos de la base de datos que puede utilizarse para restaurar el sistema después de una pérdida o corrupción de datos, o de un desastre. Sin copias de seguridad fiables, un único fallo de hardware o un DELETE accidental puede destruir permanentemente meses o años de datos.
PostgreSQL ofrece dos grandes categorías de estrategias de copia de seguridad: copias de seguridad lógicas y copias de seguridad físicas. Cada una tiene características, casos de uso y ventajas e inconvenientes distintos que todo DBA debe comprender.
Explicación de las copias de seguridad lógicas
Una copia de seguridad lógica exporta la base de datos como sentencias SQL legibles — CREATE TABLE, INSERT, COPY y comandos similares. La herramienta más habitual para hacerlo en PostgreSQL es pg_dump.
Como el resultado es SQL sin formato, una copia de seguridad lógica es portable: puede restaurarla en otra versión de PostgreSQL, en otro sistema operativo o incluso restaurar de forma selectiva tablas o esquemas individuales. La desventaja es que volcar y restaurar bases de datos grandes puede ser lento.
Usar pg_dump para una copia de seguridad lógica
La utilidad pg_dump se ejecuta desde la línea de comandos, no dentro de SQL. Se conecta a un servidor PostgreSQL en ejecución y exporta la base de datos elegida. Puede generar SQL sin formato, un formato comprimido personalizado o un formato de directorio.
El SQL siguiente simula lo que captura una copia de seguridad lógica: la estructura y los datos de una tabla expresados como sentencias reproducibles.
-- Simulating what pg_dump produces for a table
-- (These statements are written by pg_dump into the backup file)
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer TEXT NOT NULL,
amount NUMERIC(10,2),
created_at TIMESTAMPTZ DEFAULT now()
);
INSERT INTO orders (customer, amount, created_at) VALUES
('Alice', 149.99, '2024-01-15 09:30:00+00'),
('Bob', 89.50, '2024-01-16 14:00:00+00'),
('Carol', 210.00, '2024-01-17 11:15:00+00');Formatos de salida de pg_dump
pg_dump admite cuatro formatos de salida, cada uno adecuado para distintos flujos de restauración:
- plain — un script SQL sin formato, legible en cualquier editor de texto.
- custom — un formato binario comprimido; es el más flexible y admite la restauración en paralelo.
- directory — un archivo por tabla; admite el volcado y la restauración en paralelo.
- tar — un archivo tar del formato de directorio.
El formato custom se recomienda para bases de datos grandes porque pg_restore puede restaurar objetos en paralelo utilizando -j N procesos.
-- Checking which databases exist before choosing what to back up
SELECT datname,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY pg_database_size(datname) DESC;Restaurar una copia de seguridad lógica
Una copia de seguridad lógica en SQL plano se restaura con psql. Una copia de seguridad en formato custom requiere pg_restore. Ambas herramientas vuelven a ejecutar las sentencias SQL para recrear tablas, índices, restricciones y datos.
Como las copias de seguridad lógicas contienen SQL, puede editarlas antes de restaurarlas; por ejemplo, para restaurar una sola tabla o cambiar el nombre de un esquema. Esta flexibilidad es una de las mayores ventajas del enfoque lógico.
-- After restoring a backup, verify row counts match expectations
SELECT
schemaname,
relname AS table_name,
n_live_tup AS estimated_rows
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;Explicación de las copias de seguridad físicas
Una copia de seguridad física (también llamada copia de seguridad base) copia los archivos de datos sin procesar que PostgreSQL utiliza en el disco: las páginas, los segmentos de WAL (Write-Ahead Log) y los archivos de configuración. El resultado es una instantánea binaria de todo el clúster en un momento determinado.
Las copias de seguridad físicas suelen restaurarse mucho más rápido en bases de datos grandes porque no es necesario volver a ejecutar SQL; PostgreSQL simplemente vuelve a colocar los archivos y reproduce el WAL para alcanzar un estado coherente.
pg_basebackup: crear una copia de seguridad física
pg_basebackup es la herramienta estándar de PostgreSQL para las copias de seguridad físicas. Transmite el directorio de datos desde un servidor primario en ejecución a través de una conexión de replicación. Necesita un usuario con privilegios de replicación y wal_level establecido en replica o un nivel superior.
Dentro de la base de datos puede consultar la configuración de replicación para confirmar que el servidor está configurado correctamente antes de intentar crear una copia de seguridad base.
-- Verify WAL level and replication settings before a physical backup
SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
'wal_level',
'max_wal_senders',
'archive_mode',
'archive_command'
)
ORDER BY name;Archivado de WAL y recuperación a un punto en el tiempo
Una copia de seguridad base captura un momento determinado. Para recuperarse hasta cualquier punto posterior a esa copia, PostgreSQL reproduce los segmentos de WAL archivados; esto se denomina recuperación a un punto en el tiempo (PITR).
Cuando archive_mode = on y archive_command están configurados, PostgreSQL copia los segmentos de WAL completados a una ubicación de archivo. Durante la recuperación, restore_command recupera esos segmentos para que el servidor pueda reproducirlos hasta la hora objetivo deseada.
-- Inspect current WAL position and archive status
SELECT
pg_current_wal_lsn() AS current_lsn,
pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file,
archived_count,
failed_count,
last_archived_wal,
last_archived_time
FROM pg_stat_archiver;Comparar copias de seguridad lógicas y físicas
La elección entre copias de seguridad lógicas y físicas depende de sus requisitos:
- Lógica (pg_dump): portable entre versiones, admite restauraciones parciales y es legible, pero es lenta para bases de datos grandes y no ofrece granularidad de subtransacciones.
- Física (pg_basebackup + WAL): permite restaurar rápidamente clústeres grandes, admite PITR, depende de la versión (debe restaurarse en la misma versión principal) y restaura todo el clúster; no puede restaurar una sola tabla.
Los entornos de producción suelen utilizar ambas: copias de seguridad base físicas nocturnas con archivado continuo de WAL, además de volcados lógicos periódicos para facilitar la portabilidad y las restauraciones selectivas.
Verificar la integridad de las copias de seguridad
Una copia de seguridad que nunca se ha probado no es una copia de seguridad: es solo una esperanza. Valide siempre las copias de seguridad restaurándolas en un entorno de prueba y verificando los datos.
En el caso de las copias de seguridad lógicas, una comprobación rápida de integridad consiste en contar las filas y comparar las sumas de comprobación. Para las copias de seguridad físicas, PostgreSQL 14+ introdujo pg_verifybackup, que comprueba el archivo de manifiesto escrito por pg_basebackup.
-- After a test restore, compare row counts across critical tables
SELECT
relname AS table_name,
n_live_tup AS live_rows,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_live_tup DESC
LIMIT 20;Supervisión y programación de copias de seguridad
Automatizar y supervisar las copias de seguridad es tan importante como realizarlas. Registre cuándo se ejecutaron por última vez, cuánto tardaron y si se completaron correctamente. PostgreSQL proporciona metadatos útiles para este fin.
En el caso de las copias de seguridad físicas, pg_stat_archiver muestra el último archivado correcto y los posibles fallos. Para las copias de seguridad lógicas, puede envolver pg_dump en un script que registre la hora de inicio, la hora de finalización, el tamaño del archivo y el código de salida en una tabla de supervisión o un sistema de alertas.
-- Create a simple backup log table to track logical backup runs
CREATE TABLE IF NOT EXISTS backup_log (
id SERIAL PRIMARY KEY,
backup_type TEXT NOT NULL CHECK (backup_type IN ('logical', 'physical')),
started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
finished_at TIMESTAMPTZ,
size_bytes BIGINT,
status TEXT NOT NULL DEFAULT 'running',
notes TEXT
);
-- Record the start of a logical backup job
INSERT INTO backup_log (backup_type, status)
VALUES ('logical', 'running')
RETURNING id, started_at;Lógica frente a física: comprobación rápida
Compruebe su comprensión de las estrategias de copias de seguridad lógicas y físicas en PostgreSQL.
Resumen de la lección: copias de seguridad lógicas y físicas
En esta lección exploró dos estrategias fundamentales de copia de seguridad de PostgreSQL:
- Las copias de seguridad lógicas utilizan
pg_dumppara exportar bases de datos como sentencias SQL. Son portables, legibles y admiten restauraciones parciales, pero pueden ser lentas en bases de datos muy grandes. - Las copias de seguridad físicas utilizan
pg_basebackuppara copiar archivos de datos sin procesar. Combinadas con el archivado de WAL, permiten restauraciones rápidas y recuperación a un punto en el tiempo, aunque dependen de la versión y siempre restauran el clúster completo. - Los sistemas de producción suelen combinar ambas estrategias: copias de seguridad base físicas con archivado de WAL para una recuperación rápida y granular, además de volcados lógicos periódicos para facilitar la portabilidad.
- Pruebe siempre sus restauraciones. Una copia de seguridad no probada no puede considerarse fiable en una situación de desastre real.
Comprender estos dos enfoques es esencial para diseñar un plan sólido de recuperación ante desastres para cualquier implementación de PostgreSQL.
Preguntas frecuentes
¿La lección «Copias de seguridad lógicas y físicas» es gratis?
Sí — el texto completo de «Copias de seguridad lógicas y físicas» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de SQL Academy, actualiza a CoddyKit PRO. El curso de SQL Academy incluye 4 lecciones en total.
¿Qué aprenderé en «Copias de seguridad lógicas y físicas»?
pg_dump y copias de seguridad base Practicas SQL Academy con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.
¿Necesito experiencia previa para empezar SQL Academy?
No se requiere experiencia previa. SQL Academy en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 1 de 4.
¿Cuánto tiempo toma la lección «Copias de seguridad lógicas y físicas»?
La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.
¿Puedo escribir y ejecutar código en esta lección de SQL Academy?
Sí. Cada lección de SQL Academy incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.
Todas las lecciones de este curso
- Copias de seguridad lógicas y físicas
- Recuperación a un momento dado
- Prueba de las restauraciones
- Planificación de recuperación ante desastres