PostgreSQL: base de datos de respaldo

Todas las empresas comerciales y corporaciones nunca están 100% libres de amenazas de datos. Es extremadamente importante tener cuidado con escenarios como la corrupción de datos, fallas en el host o la red o incluso la destrucción deliberada de las instalaciones de almacenamiento. Por lo tanto, la copia de seguridad de los datos es una actividad crítica de la que depende cada empresa para garantizar su existencia en el mercado. La idea detrás de las copias de seguridad de los servidores se extiende más allá de la capacidad de competir en el mercado. Aquí hay algunas razones por las que las copias de seguridad se realizan con tanta frecuencia:

1. Prevención de pérdida de datos: en casos de situaciones extremas, como ciberamenazas o catástrofes, los datos deben mantenerse vivos en su forma original.
2. Salud empresarial: todas las empresas deben revisar sus registros para mantenerse confiables y conscientes de todos los niveles de seguridad de la base de datos.
3. Fomentar la responsabilidad: la copia de seguridad de los servidores es la forma más fluida con la que una empresa gana la confianza de los consumidores en cuanto a la custodia de la información.

PostgreSQL tiene una participación destacada en el mercado, dejando de lado a MySQL como líder indiscutible. Como es habitual en cualquier sistema de gestión de bases de datos de tan alto nivel, PostgreSQL proporciona un conjunto de copias de seguridad tanto lógicas como físicas. Para aquellos que no saben la diferencia:

• Las copias de seguridad lógicas contienen información sobre la base de datos, como tablas y esquemas, mientras que las copias de seguridad físicas contienen los archivos y directorios de la información.
• Las copias de seguridad lógicas son de menor tamaño en comparación con las copias de seguridad físicas.

Conceptualmente, hay varios niveles de respaldo (diferencial, completo, incremental, etc.) pero ese es el objetivo de este artículo. Nos ocuparemos de los enfoques que ofrece PostgreSQL a sus usuarios. Aquí nos centraremos en 3 de ellos: 

1. Copias de seguridad a nivel de sistema de archivos
2. Volcados de SQL
3. Archivado continuo o repetitivo

Copia de seguridad a nivel de sistema de archivos

Una copia de seguridad a nivel de sistema de archivos es una copia de seguridad física. La idea es hacer una copia de todos los archivos que crearon la base de datos. Esto significa que estamos viendo grupos de bases de datos y directorios que utiliza PostgreSQL para escribir los datos en la base de datos. Una forma de interpretar esto es visualizándose a sí mismo como el científico que publica un trabajo de investigación y utiliza un montón de trabajo escrito previamente para dar definición a su trabajo. Las obras a las que hace referencia forman un marco y un diseño que lo guían a lo largo de su proceso de escritura. Los archivos que PostgreSQL intenta copiar son el marco y el diseño junto con las pautas que contienen los datos (específicamente un montón de archivos .dat ).

La declaración de copia: 

tar -cf backup.tar /%p $PATH TO DATA FILE/%f $FILENAME
OR
rsync -v /var/lib/pgsql/13/backups/ /Directory 2/

Observe que se crea un archivo .tar , es decir, un archivo de cinta. Mientras tanto, rsync es un comando que SINCRONIZA dos directorios. Entonces, si el Directorio 2 no tiene el mismo contenido que el primer directorio (/var/lib/….), entonces lo tendría después de la ejecución, una forma de copia más cortante.

Sin embargo, debemos habilitar el archivo antes de la ejecución. Obviamente, la simple copia de datos se considera un descuido; las transacciones en curso afectarán el estado inmediato de los archivos que se copian, lo que generará un estado cuestionable de la copia de seguridad y su confiabilidad. 

Nota: Esta es una superposición deliberada con el último enfoque discutido en este artículo; ayuda a mejorar la calidad de la copia de seguridad.

Por lo tanto, comenzamos habilitando primero el archivo WAL. Los archivos WAL son archivos que mantienen todas las modificaciones realizadas por las transacciones para mantener la consistencia de los datos en la base de datos y la memoria de la instancia.

Los archivos WAL son archivos de ‘Registro de escritura anticipada’. Todas las transacciones realizadas se escriben primero en el archivo WAL antes de que esos cambios se apliquen al espacio real del disco. Los archivos WAL son copias de los archivos WAL y existen en un directorio separado para evitar bloqueos plausibles.

Paso 1: Inicie la copia de seguridad conectándose primero a la base de datos como usuario privilegiado y luego ejecutando lo siguiente. /db es el nombre de la base de datos que desea respaldar.

SELECT pg_start_backup('label',false,false);
tar -cf -z backup_name.tar /db $the name of the database you wish to backup

• -cf : esto indica un ‘formato personalizado’. Al incluir esto, el usuario declara que el archivo comprimido tendrá un formato personalizado. Esto introduce flexibilidad al volver a importar el archivo.
• -z : este atributo del comando le indica a postgreSQL que comprima el archivo de salida.

Paso 2: Dígale a la base de datos que finalice el modo de copia de seguridad.

SELECT * FROM pg_stop_backup(false, true);

Lo que también hace este comando es que crea un archivo .backup que ayuda a identificar los primeros y últimos registros de archivo. Esto es importante al restaurar la base de datos.

En el caso muy probable de que el usuario olvide habilitar el archivo WAL, esto se muestra en el shell:

Configure lo siguiente en el archivo postgresql.conf:

-wal_level = replica
-archive_mode = on
-archive_command = 'cp %p /%f'

Esto habilita el archivado WAL. %p es el nombre de la ruta y %f es el nombre del archivo. La configuración de parámetros en el archivo de configuración solo es posible a través de la línea de comandos.

Desventajas de las copias de seguridad a nivel del sistema de archivos:

• Requieren que se realice una copia de seguridad de toda la base de datos. La copia de seguridad de esquemas o solo de tablas específicas no es una opción. Lo mismo se aplica al proceso de restauración, es decir, se debe restaurar toda la base de datos.
• En consecuencia, ocupan más espacio en el almacenamiento por defecto.
• El servidor debe detenerse para obtener una copia de seguridad utilizable. Esto genera gastos generales innecesarios e interrumpe la continuidad de las transacciones comerciales.

Alternativa más cercana:

Otro método para generar una copia de seguridad es mediante pg_basebackup por los siguientes motivos:

• La recuperación desde la copia de seguridad es más rápida y segura
• Es específico de la versión de instalación.
• Las copias de seguridad se realizan a través de protocolos de replicación.

Volcado SQL

Cuando se trata de bases de datos más pequeñas, es mucho más conveniente ejecutar un volcado de SQL. Los volcados de SQL son copias de seguridad lógicas. Esto implica que el método respaldaría todas las instrucciones que se usaron para crear los esquemas y las tablas. Por lo tanto, el archivo que se exporta después de la copia de seguridad es literalmente un archivo lleno de comandos DDL y DML. 

PostgreSQL ofrece dos métodos mediante los cuales se puede realizar un volcado de SQL.

Usando pg_dump

pg_dump es un comando simple que crea una copia de una de las bases de datos en el servidor. Piense en ello como ‘volcar los archivos de objetos de UNA sola base de datos en un archivo nuevo. Al final de este proceso, se crea un nuevo archivo que es interpretable por humanos (lleno de comandos SQL). pg_dump funciona con las siguientes opciones:

• -Fc : exporta el archivo en formato personalizado, explicado anteriormente para copias de seguridad de nivel de sistema de archivos
• -Z : se usa junto con el comando para comprimir los datos. Como era de esperar, esto se usa con grandes bases de datos.
• -j : se usa para habilitar copias de seguridad paralelas usando pg_dump nuevamente
• -a : esta opción especifica que solo se realizará una copia de seguridad de los datos.
• -s : se utiliza cuando solo se requiere realizar una copia de seguridad de los esquemas.
• -t : se utiliza cuando solo se necesita realizar una copia de seguridad de las tablas de la base de datos.
• Aquí está el formato de muestra por el cual un usuario puede crear un volcado de SQL usando pg_dump:

>> C:\Program Files\PostgreSQL\13\bin
>> pg_dump -U username -W -F p database_name > path_to_output_file.sql
>> Password:
>>(For example)
>> pg_dump -U dbuser04 -W -F p My_University > University_backup.sql

El usuario primero debe navegar a la carpeta \bin de la versión de PostgreSQL instalada EN LA TERMINAL o CMD. Una vez hecho esto, se puede ejecutar el comando, como se indica. Se sugiere que el archivo de salida se cree en una ubicación conocida antes de la ejecución (no confíe en PostgreSQL para que lo cree por usted). En el comando anterior, -W es una opción que permite la verificación del usuario a través de la contraseña. -F se usa para especificar el formato del archivo de salida en el siguiente carácter (recuerde cambiar la extensión del archivo de salida). Por lo tanto:

• p = archivo de texto sin formato
• t = archivo tar
• d = archivo de formato de directorio
• c = archivo de formato personalizado

Usando pg_dumpall:

La advertencia aparente es cómo pg_dump está restringido a crear una copia de una sola base de datos a la vez. Para situaciones en las que se deben realizar copias de seguridad de varias bases de datos en el servidor, PostgreSQL pone a disposición el comando pg_dumpall . En su mayor parte, su sintaxis sigue siendo la misma, al igual que las opciones, con la excepción de – W (honestamente, nadie querría escribir la contraseña para cada base de datos de la que hacen una copia de seguridad).

>> C:\Program Files\PostgreSQL\13\bin
>> pg_dumpall -U username -F p > path_to_output_file.sql

En muchos casos, las empresas pueden preferir hacer solo copias de los esquemas o ciertas definiciones de objetos y no realmente los datos. Esto está muy justificado.

>> pg_dumpall --tablespaces-only > path_to_output_file.sql $for only tablespaces
>> pg_dumpall --schema-only > path_to_output_file.sql     $for only schemas

Deméritos del volcado de SQL:

Basado en múltiples facciones de TI de corporaciones que utilizan servidores PostgreSQL para los procedimientos del día a día, las deficiencias de los volcados de SQL brindan fácilmente un incentivo para usar otros enfoques para las copias de seguridad y la recuperación.  

• Como hemos visto, los volcados de SQL recrean literalmente cada variable en función de las instrucciones almacenadas en el archivo de salida. De hecho, también funciona en la reconstrucción de todos los índices. Como resultado, el límite en las velocidades de restauración es obvio.
• Una consecuencia importante de lo anterior son los gastos generales no deseados que las empresas nunca agradecen.
• Técnicamente, con múltiples volcados que ocurren simultáneamente, la inversión de sintaxis es un problema importante.
• Al ser una exportación lógica, los volcados de SQL pueden no ser 100 % portátiles.

Ejemplo:

Considere una base de datos ‘crimedb’ que almacena un registro de todos los delincuentes en el registro policial. Intentaremos realizar una copia de seguridad periódica de todas las tablas.

Archivado continuo

Este es un procedimiento bastante complejo en comparación con los enfoques anteriores discutidos. Sin embargo, los beneficios prevalecen.

El archivado continuo o repetitivo combina las ideas de emitir una copia de seguridad a nivel del sistema de archivos y una copia de seguridad de los archivos WAL (técnicamente, habilitando el archivado WAL por separado). Puede parecer una copia de seguridad a nivel del sistema de archivos de nuevo, pero con el énfasis adicional en retener y no solo archivar los archivos WAL mientras se realiza la copia de seguridad. Esto nos permite tomar ‘instantáneas simultáneas’ de la base de datos cuando está fuera de línea. Otra diferencia es el procedimiento de restauración: la recuperación de las copias de seguridad de Archivado continuo es muy diferente. 

Esta sección trata sobre las copias de seguridad de recuperación de un punto en el tiempo. Esta es una forma de archivado continuo en la que el usuario puede hacer una copia de seguridad y restaurar datos a un cierto punto en el pasado. El juego de palabras se basa en el último archivo WAL creado.

En resumen, el procedimiento es el siguiente: 

1. Habilitar archivo WAL
2. Cree una forma en la que se retengan los archivos WAL.
3. Realice una copia de seguridad a nivel del sistema de archivos (esta vez usaremos pg_basebackup ).
4. Conserve la copia de seguridad para que la base de datos se reproduzca en un punto en el tiempo.

Primero, conectémonos al servidor y ejecutemos lo siguiente en el shell psql:

>> show archive_mode;

Si el archivado está deshabilitado, se muestra lo siguiente:

El primer paso (habilitar el archivado) varía de un sistema a otro según el sistema operativo y, a veces, la versión de Postgres instalada. Todos los pasos restantes siguen un procedimiento común.

Paso 1 (Windows):

Lo primero es lo primero, detenga el servicio de Postgres desde el administrador de servicios de Windows. 

Ahora, diríjase al archivo Postgres.conf en el directorio \data. Por ejemplo , C:\Program Files\PostgreSQL\13\data y realice los siguientes cambios en los parámetros:

>> archive_mode = on
>> archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"'
>> wal_level = replica

Reemplace %p con la ruta del archivo que deseamos archivar, que es pg_wal y reemplace %f con el nombre del archivo. Asegúrese de usar ‘\\’ para los directorios para evitar problemas.

Ahora, inicie los servicios de Postgres.

Paso 1 (Linux):

Siga el procedimiento dictado en el siguiente código:

$ mkdir db_archive
$ sudo chown postgres:postgres db_archive

Ahora, tenemos un directorio creado en el que escribiremos los archivos. Además, le hemos dado permiso al usuario de Postgres para escribir en este directorio. Debemos configurar nuestros parámetros en el archivo postgresql.conf . Busque el archivo en el directorio de datos o en el directorio principal de la versión de PostgreSQL que haya instalado, manualmente. Luego realice los siguientes cambios en el archivo (abra el archivo y edite su contenido):

# uncomment archive parameter and set it to the following:
archive_mode = on
archive_command = 'test ! -f /path/to/db_archive/%f && cp %p /path/to/db_archive/%f'
# change wal_level as well
wal_level = replica

Guardar y salir del archivo. Debe reiniciar el servidor de la base de datos ahora para aplicar estos cambios a la configuración. El equivalente de Linux de esto es:

$ sudo systemctl restart postgresql.service

Pasos 2 – 4 (común para ambas plataformas):

Abra el símbolo del sistema SQL o SQL Shell (psql) en su sistema. Conéctese al servidor. Ahora ejecute la siguiente consulta para hacer una copia de seguridad.

postgres=# SELECT pg_start_backup('label',false,false)
postgres=# pg_basebackup -Ft -D directory_you_wish_to_backup_to
postgres=# SELECT pg_stop_backup(true,false)

pg_start_backup() y pg_stop_backup() pueden omitirse ya que pg_basebackup() es capaz de manejar el control por sí mismo. Una vez que se devuelve el mensaje, los usuarios pueden verificar el directorio en el que se creó la copia de seguridad y estar seguros de encontrar un archivo de copia de seguridad .tar . Depende del usuario especificar el tipo de formato de archivo preferido (p o t o c).

Ahora que tenemos una copia de seguridad a nivel del sistema de archivos y los archivos WAL, podemos ejecutar una recuperación de un punto en el tiempo. Supongamos que nuestra base de datos colapsó y, como resultado, nuestros datos se corrompieron, dañaron o destruyeron. 

Detenga los servicios de la base de datos una vez que se haya creado la copia de seguridad y elimine todos los datos dentro del directorio de datos, ya que los datos ya no se validan debido al bloqueo de la base de datos.

$ sudo systemctl stop postgres.services 
$ or use the windows services panel (if you use Windows)

Esta es la medida en que se puede manejar el archivado continuo (solo estamos creando una copia de seguridad, no la estamos restaurando todavía). Sin embargo, el proceso es bastante simple y directo, ya que todo lo que necesitamos hacer es importar las copias de seguridad a las carpetas originales o las carpetas/directorios predeterminados que PostgreSQL usó inicialmente durante la instalación. A esto le sigue un cambio menor en el archivo postgresql.conf y ¡eso es todo!