Cómo respaldar y restaurar bases de datos usando pg_dump y pg_restore

Cómo Realizar Copias de Seguridad y Restaurar Bases de Datos Usando pg_dump y pg_restore

En el mundo de la gestión de bases de datos, las estrategias sólidas de copia de seguridad y restauración no solo son recomendables; son absolutamente críticas. La pérdida de datos puede deberse a diversas fuentes, incluyendo fallos de hardware, errores humanos o ataques maliciosos. PostgreSQL, una potente base de datos relacional de código abierto, proporciona utilidades esenciales de línea de comandos, pg_dump y pg_restore, para garantizar la integridad y la recuperabilidad de sus datos. Este tutorial lo guiará a través de las mejores prácticas para utilizar estas herramientas para crear copias de seguridad fiables y realizar restauraciones seguras de sus bases de datos PostgreSQL.

Comprender estas utilidades es fundamental para cualquier administrador o desarrollador de PostgreSQL responsable de la protección de datos. pg_dump se utiliza para extraer una base de datos PostgreSQL en un archivo de script u otro archivo de archivo, mientras que pg_restore puede interpretar dichos archivos para reconstruir la base de datos. Dominarlas le permite salvaguardar su información valiosa y mantener la continuidad del negocio.

Comprensión de pg_dump

pg_dump es una utilidad para crear copias de seguridad de una base de datos PostgreSQL. Genera un archivo que contiene comandos SQL que se pueden usar para recrear los objetos de la base de datos (tablas, funciones, índices, etc.) y poblarlos con datos. pg_dump funciona conectándose a la base de datos y luego generando la salida. Puede generar la salida en varios formatos, cada uno con sus propias ventajas.

Formatos de Salida

pg_dump admite múltiples formatos de salida:

Script SQL de texto plano (-Fp o predeterminado): Este es el formato más simple. Genera un archivo que contiene una serie de comandos SQL. Este formato es legible por humanos y fácilmente programable mediante scripts, pero puede ser grande y más lento de restaurar para bases de datos muy grandes.
Archivo Personalizado/Custom Archive (-Fc): Este formato crea un archivo de archivo personalizado y comprimido. Generalmente es el formato recomendado para copias de seguridad, ya que está comprimido por defecto, admite la restauración paralela (pg_restore) y generalmente es más flexible.
Formato de Directorio (-Fd): Esto crea un directorio que contiene múltiples archivos, uno para cada tabla y otros objetos. También admite la restauración paralela y es útil para bases de datos muy grandes.
Formato Tar (-Ft): Esto crea un archivo tar. Es similar al formato de directorio pero es un único archivo. Es útil para la compatibilidad con otras herramientas que manejan archivos tar.

Opciones Comunes de pg_dump

Aquí están algunas de las opciones más utilizadas para pg_dump:

-h <hostname>: Especifica el nombre de host de la máquina donde se está ejecutando el servidor.
-p <port>: Especifica el puerto TCP en el que el servidor está escuchando las conexiones.
-U <username>: Conectarse como el usuario especificado.
-W: Fuerza a pg_dump a solicitar una contraseña antes de conectarse.
-f <filename>: Especifica el nombre del archivo de salida. Si se omite, pg_dump escribe en la salida estándar.
-F <format>: Especifica el formato del archivo de salida (p, c, d, t).
-Z <0-9>: Especifica el nivel de compresión para formatos comprimidos (ej., Fc).
--exclude-table=<table_name>: Excluye la tabla especificada del volcado (dump).
--jobs=<number>: Número de trabajos paralelos a usar al hacer el volcado (solo para formato de directorio).
--clean: Incluye comandos para eliminar (drop) objetos de la base de datos antes de crearlos. Esto es útil para restauraciones completas para asegurar un estado limpio.
--create: Incluye un comando para crear la base de datos misma. Útil al restaurar a un servidor nuevo.

Ejemplo: Copia de Seguridad Básica de Texto Plano

Para crear una copia de seguridad de texto plano de una base de datos llamada mydatabase propiedad del usuario myuser y guardarla en mydatabase_backup.sql:

pg_dump -U myuser -f mydatabase_backup.sql mydatabase

Si necesita especificar un host y puerto:

pg_dump -h localhost -p 5432 -U myuser -f mydatabase_backup.sql mydatabase

Este comando solicitará la contraseña para myuser.

Ejemplo: Copia de Seguridad en Formato Personalizado (Recomendado)

Generalmente se prefiere el uso del formato de archivo personalizado por su flexibilidad y compresión. Para crear una copia de seguridad personalizada comprimida de mydatabase:

pg_dump -U myuser -Fc -f mydatabase_backup.dump mydatabase

Esto creará un archivo llamado mydatabase_backup.dump que está comprimido y en un formato adecuado para pg_restore.

Ejemplo: Copia de Seguridad en Formato de Directorio con Trabajos Paralelos

Para bases de datos muy grandes, el volcado paralelo puede acelerar significativamente el proceso de copia de seguridad. Esto requiere el formato de directorio.

pg_dump -U myuser -Fd -j 4 -f mydatabase_backup_dir mydatabase

Este comando creará un directorio llamado mydatabase_backup_dir que contiene múltiples archivos, utilizando 4 trabajos paralelos.

Comprensión de pg_restore

pg_restore es una utilidad para restaurar una base de datos PostgreSQL a partir de un archivo creado por pg_dump en uno de los formatos personalizado, directorio o tar. No se utiliza para volcados SQL de texto plano (que se restauran utilizando el comando psql).

Opciones Comunes de pg_restore

-h <hostname>: Especifica el nombre de host de la máquina donde se está ejecutando el servidor.
-p <port>: Especifica el puerto TCP en el que el servidor está escuchando las conexiones.
-U <username>: Conectarse como el usuario especificado.
-W: Fuerza a pg_restore a solicitar una contraseña antes de conectarse.
-d <database>: Especifica el nombre de la base de datos a la que restaurar. Esto es obligatorio.
-v: Modo detallado (verbose).
-c o --clean: Limpiar (eliminar/drop) objetos de la base de datos antes de recrearlos.
--create: Crear la base de datos antes de restaurar en ella. Requiere conectarse inicialmente a una base de datos diferente (ej., postgres).
-j <number>: Número de trabajos paralelos a utilizar para la restauración. Esto acelera significativamente la restauración, especialmente para formatos personalizados y de directorio.
-F <format>: Especifica el formato del archivo (c, d, t). Generalmente, pg_restore puede detectarlo automáticamente.

Restauración desde Copias de Seguridad SQL de Texto Plano

Si creó una copia de seguridad SQL de texto plano usando pg_dump (ej., mydatabase_backup.sql), la restaura usando psql:

psql -U myuser -d mydatabase -f mydatabase_backup.sql

Si el archivo de copia de seguridad incluye comandos CREATE DATABASE y \connect, es posible que deba conectarse a una base de datos diferente (como postgres) y omitir el argumento -d mydatabase:

psql -U myuser -f mydatabase_backup.sql postgres

Ejemplo: Restauración desde un Formato Personalizado

Para restaurar la copia de seguridad de formato personalizado mydatabase_backup.dump en una base de datos llamada restored_db:

pg_restore -U myuser -d restored_db mydatabase_backup.dump

Si la base de datos de destino restored_db no existe, puede usar la opción --create. En este caso, debe conectarse a una base de datos diferente (como postgres) ya que la base de datos de destino será creada.

pg_restore -U myuser --create -d restored_db mydatabase_backup.dump

Este comando creará la base de datos restored_db y luego restaurará los datos en ella.

Ejemplo: Restauración Paralela

Para una restauración más rápida, especialmente con grandes conjuntos de datos, use la opción -j:

pg_restore -U myuser -d restored_db -j 4 mydatabase_backup.dump

Esto intentará restaurar la base de datos utilizando 4 trabajos paralelos.

Ejemplo: Restauración desde Copia de Seguridad en Formato de Directorio

Para restaurar una copia de seguridad creada en formato de directorio (ej., mydatabase_backup_dir):

pg_restore -U myuser -d restored_db -j 4 mydatabase_backup_dir

Tenga en cuenta que el último argumento es la ruta del directorio.

Mejores Prácticas para Copias de Seguridad

Programación Regular: Automatice sus copias de seguridad usando cron (Linux/macOS) o el Programador de Tareas (Windows) para que se ejecuten a intervalos regulares (ej., diarios, por hora).
Elija el Formato Correcto: Para la mayoría de los casos de uso, se recomienda el formato personalizado (-Fc) debido a su compresión y compatibilidad con las funciones paralelas de pg_restore. Utilice el formato de directorio (-Fd) para bases de datos extremadamente grandes donde el volcado/restauración paralela es crucial.
Pruebe sus Copias de Seguridad: Una copia de seguridad solo es útil si se puede restaurar. Pruebe regularmente su proceso de restauración restaurando en un entorno de ensayo o desarrollo para garantizar la integridad de los datos y la validez de sus archivos de copia de seguridad.
Almacene las Copias de Seguridad Fuera del Sitio (Offsite): Mantenga copias de sus copias de seguridad en una ubicación física separada de su servidor de base de datos principal para protegerse contra desastres a nivel de sitio.
Monitoree los Trabajos de Copia de Seguridad: Implemente monitoreo para asegurar que sus trabajos de copia de seguridad programados se estén ejecutando exitosamente y para que le avisen si fallan.
Considere la Compresión: Use las opciones de compresión de pg_dump o canalice la salida a través de herramientas de compresión externas (gzip, zstd) si no está utilizando formatos que se comprimen automáticamente.
Use --clean y --create con Cuidado: Al realizar una restauración completa en un entorno existente, --clean puede ser muy útil. Al pasar a un nuevo entorno o asegurar un estado completamente fresco, --create es esencial. Tenga cuidado con estas opciones en sistemas de producción.
Control de Versiones de Copias de Seguridad: Implemente una estrategia para gestionar la retención de copias de seguridad, incluyendo cuánto tiempo se mantienen y cómo se purgan las antiguas.

Conclusión

pg_dump y pg_restore son herramientas indispensables para salvaguardar sus datos de PostgreSQL. Al comprender sus diversas opciones, formatos de salida y al implementar una estrategia consistente de copia de seguridad y restauración con pruebas regulares, puede mitigar significativamente el riesgo de pérdida de datos y garantizar la resiliencia de sus sistemas de bases de datos. Convierta estas utilidades en una piedra angular de sus prácticas de administración de bases de datos.