Backup en PostgreSQL + Optimizaciones

Bokudan BBDD, Linux

Introducción

La verdad que nunca había trabajado con bases de datos PostgreSQL y he empezado relativamente hace poco por necesidades del trabajo. Pues bien en este caso nos vamos a centrar en una problemática que nos ha pasado y es realizar backup + restore.

Os preguntaréis, ¿Por qué tienes problemas al hacerlo? pues bien el problema viene dado porque si es una base de datos grande se puede llevar horas o día el proceso. Por lo tanto en mi cabeza lo primero que pasó fue un ¿Se puede optimizar los tiempos? ¿Cuál es la mejor manera de hacerlo? etc…

Así que en este artículo nos vamos a centrar en los distintos métodos que existen para realizar los backups y además optimizaciones siempre que se pueda.

ÍNDICE

Métodos de Backup/Restore

Revisando un poco por la red me encontré con varios métodos, por lo tanto los iré añadiendo en este apartado con sus optimizaciones y sus pros/contra.

Método #1: pg_dump, pg_dumpall y pg_restore

Estas son las herramientas oficiales que los propios creadores de PostgreSQL nos facilitan. Por lo tanto es un buen inicio de nuestra guía.

Siempre que vayamos hacer dump desde la propia máquina debemos realizar estas labores desde el usuario «postgres» o el que se haya definido para ello.

# Realizar un backup de una base de datos completa
pg_dump nombre_db > nombre_db.sql

# Realizar un restore de una base de datos
psql nombre_db < nombre_db.sql

# Realizar un backup de una base de datos a otra en remoto
pgdump -h server_host1 nombre_db | psql -h server_host2 nombre_db

# Por defecto aunque haya errores sigue, con esto se detiene
psql --set ON_ERROR_STOP=on nombre_db < nombre_db.sql

Cuando usamos pg_dump no nos guarda información extra acerca de los roles o tablespaces. Si queremos hacerlo completo debemos usar el comando pg_dumpall.

# Realizar un dump de todas las bases de datos
pg_dumpall > bases_de_datos.sql

# Realizar un restore de las bases de datos
psql -f bases_de_datos.sql postgres

A continuación vamos a mostrar unos cuantos ejemplos que pueden ser útiles en vuestro día a día.

# Realiza un fichero custom comprimido y nos permite la capacidad de actualizar lo que queramos y ordenar como queremos el restore.
pg_dump -Fc nombre_db > nombre_db-compress

# Obteniendo solo la estructura
pg_dump -s nombre_db > nombre_db_estructura.sql

# Obteniendo solo los datos
pg_dump -a nombre_db > nombre_db_datos.sql

# Dump remoto de una base de datos
pg_dump -Fc -v -U USUARIO -p PUERTO -h HOST nombre_db > nombre_db-compress.sql

# Restore
# -d sirve para asignar la base de datos (si viene de un nombre distinto)
# -v modo verbose
# -c al tener esta opción entendemos que viene de un dump custom comprimido
pg_restore -U postgres -d heos -c -v nombre_db-compress.sql

Si queremos más información acerca de las opciones podemos revisar la documentación oficial. Para ello seleccionad la versión y listo. Yo os la pondré de la 9.2.

Método #2: Backup a nivel de ficheros o file system

La principal diferencia, generalmente, es que para tener un backup consistente de la base de datos debe estar detenida. Pero si está en producción ya esto no nos gusta mucho.

2 condiciones son imprescindibles:

  1. La base de datos debe ser detenida (deshabilitar todas las conexiones no vale).
  2. No se puede restaurar el contenido de ciertas tablas ya que no habría consistencia.

Una alternativa para evitar detener la base de datos PostgreSQL es la de realiza un frozen snapshot del directorio. Pero ten en cuenta que al restaurar de esta manera tomará como si no hubiera cerrado correctamente y revisará el WAL LOG.

Otra alternativa es usando el comando RSYNC:

  1. Realizamos el primer RSYNC mientras la base de datos está activa o corriendo.
  2. Detenemos la base de datos.
  3. Ejecutamos de nuevo el RSYNC

La ventaja que tiene esto es que al ejecutar el segundo RSYNC tardará mucho menos (se entiende que menos ficheros han cambiado).

Para finalizar este punto solo tenemos que tener pendiente en donde estamos guardando los datos. Generalmente suele ser en:

/usr/local/pgsql/data

Método #3: Parallel Loader (python)

Este es un script que aprovecha las optimizaciones oficiales para mejorar el rendimiento a la hora de realizar un volcado de una tabla muy grande. En resumidas cuentas lo que hace:

  • Lecturas y escrituras simultáneas de una tabla.
  • Posibilidad de usar el multihilo en un dump/restore de una tabla.

En resumidas cuentas como veis es un script que se centra solo en una tabla y como dije antes en las tablas que son muy grandes.

Página oficial del script en GitHub

Pre-requisitos/Recomendaciones

  • Debe de llevar la columna id en la tabla. Se recomienda que dicho parámetro lleve índices.
  • Se recomienda que los servidores estén en la misma región (cloud) o la misma red con baja latencia.
  • No hay que olvidar reiniciar la secuencias de la tabla en la base de datos destino. No se reinician en la tabla de la base de datos destino.

Ejemplo de cómo funciona:

python parallel_migrate.py "host=test_src.postgres.database.azure.com port=5432 dbname=postgres user=test@test_src password=xxxx sslmode=require" test_table "host=test_dest.postgres.database.azure.com port=5432 dbname=postgres user=test@test_dest password=xxxx sslmode=require" test_table 8 411187501

Lo ideal en estos casos sería realizar un backup/restore con pg_dump y pg_restore de casi todas las tablas y luego las tablas más grandes realizarlos a través de dicho script.

Optimizaciones Backup/Restore

Antes de empezar hablar sobre las optimizaciones para ganar tiempo debemos tener en cuenta que todo tiene sus pros y sus contras. Por lo tanto se debe juzgar según el caso y necesidad de las mismas.

Optimizando los Backups

La forma de optimizar los backups usando pg_dump son los siguientes:

  • Usar el método custom comprimido. Es decir las opciones ( -Fc ).
    • Aparte de comprimirnos el fichero nos da la posibilidad de restaurar parte del fichero.
    • Ahorraremos espacio y flexibilidad a la hora de realizar el restore.
  • Usar -Z [0-9] para el nivel de compresión
    • Siendo 0 sin compresión y 9 el máximo. Por defecto cuando se comprime (punto anterior) es moderado.
    • A mayor compresión usa más CPU así que tenedlo en cuenta.
  • Usar -j [Número de Cores]
    • Esto permite realizar el dump de manera paralela. Dependiendo de cada core que asignemos.
    • Es importante recordar que a mayores core mayor carga en la máquina.
    • Si se comparte con otros recursos tened cuidado a la hora de asignar cores.
    • Está disponible desde la versión 9.3

Optimizando los Restores

La forma de optimizar los restores usando pg_restore son los siguientes:

  • Usar -j [Número de Cores]
    • Esto permite realizar el dump de manera paralela. Dependiendo de cada core que asignemos.
    • Es importante recordar que a mayores core mayor carga en la máquina.
    • Si se comparte con otros recursos tened cuidado a la hora de asignar cores.
    • Está disponible desde la versión 9.3

MIX

Como comentamos en el método 3 de backup, lo ideal sería una mezcla de pg_dump/pg_restore y el script de Parallel Loader. Con esto podemos realizar algún tipo de política que nos permita mover las tablas de mayor tamaño a través de este y excluir esas tablas en el dump.

Un ejemplo de sintaxis:

# Creamos un backup remoto comprimido excluyendo datos de información y tablas
pg_dump -h HOST -p PORT -U USER -Fc -v --exclude-table-data "test*" \
--exclude-table-data "tabla-grande1" --exclude-table-data "tabla-grande2" \
--exclude-table="llamadas*" -f "RUTA_FICHERO" BBDD

Ahora solo nos queda realizar una migración de las dos tablas «tabla-grande1» y «tabla-grande2» a través del script del método 3 de backup.

Conclusiones

Con esto hemos aprendido a realizar backups de nuestras bases de datos PostgreSQL y como mejorar el rendimiento para no morir en el intento cuando hacemos los dumps.

Siendo honestos la única forma que he probado el funcionamiento es realizando pg_dump y pg_restore. Creedme cuando os digo que el hecho de usar el «-j» y aumentando los recursos de las máquinas (siendo cloud es más fácil) mejoramos los tiempos una barbaridad.

No he usado el script de Parallel Loader. Sin embargo la información la vi de una web fiable.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *