En esta entrada voy a hacer una guía muy básica sobre la gestión de copias de seguridad de bases de datos PostgreSQL. La más común es mediante volcados SQL, aunque hay otras como la copia a nivel de fichero.

Backups mediante volcado (SQL Dump con pg_dump)

Si conocéis mysqldump veréis que el funcionamiento es similar. Se trata de volcar en un fichero de texto que contendrá los comandos y datos SQL necesarios para restaurar una copia exacta de la base de datos en el momento de la copia. La herramienta a utilizar es pg_dump.

La forma más sencilla de hacer un backup de una base de datos completa es la siguiente:

# pg_dump basededatos > fichero.sql

En el caso de tener que especificar un host remoto o puerto de conexión distinto usaríamos los parámetros correspondientes, -h para el host y -p para el puerto, lo mismo se tenemos que especificar usuario, clave, etc.

La restauración del backup es el mismo proceso pero a la inversa:

# psql basededatos < fichero.sql

Especificando parámetros:

# psql -U usuario -d basededatos -p 5432 -h 192.168.0.111 < fichero.sql

Mediante el frontend de PostgreSQL psql volcamos el contenido del fichero SQL en la base de datos a restaurar. Al igual que con la exportación se pueden especificar un gran número de parámetros para personalizar la restauración (revisad la ayuda).

Backup de todas las bases de datos del servidor PostgreSQL

Mediante pg_dumpall podemos hacer con un único comando una copia de seguridad de todas las bases de datos del servidor:

# pg_dumpall > backup_server.sql

Posteriormente para restaurar todas las bases de datos:

# psql -f backup_server.sql postgres

Backup a nivel de ficheros

Este método implica copiar directamente los ficheros de la base de datos o comprimirlos a modo de copia de seguridad. El mayor inconveniente de este método es que obligatoriamente el servidor PostgreSQL debe estar apagado.

Si quisiéramos hacer un backup comprimido de todas las bases de datos del servidor, suponiendo que se encuentran en /var/pgsql/data sería así (recordad que el servidor PostgreSQL debe estar parado):

# tar -czvf backup.tar.gz /var/pgsql/data

Restaurarlo sería tan sencillo como parar el servicio, mover los ficheros a su ruta y levantarlo de nuevo. Es un sistema rápido pero que implica más espacio en disco utilizado que el volcado SQL. Otra opción podría ser usar rsync para sincronizar remotamente las bases de datos, aunque también tendría que estar parado el servicio.

Archivado continuo y puntos de recuperación (PITR)

PostgreSQL, al igual que otros motores SQL guarda un log (WAL) que almacena todos los cambios que se realizan contra las bases de datos, algo similar al log binario de MySQL. Esto nos puede permitir en caso de fallo recuperar datos de una base de datos desde el punto concreto que necesitemos. En la documentación de PostgreSQL recomiendan combinar este sistema con el de copiado a nivel físico para una estrategia compleja de backups.

Si estáis interesados en este sistema os recomiendo leer directamente la documentación de PostgreSQL sobre este tema ya que es algo extenso y ahí está perfectamente explicado.

basededatos

Para conseguir acceder remotamente a un servidor PostgreSQL es necesario, además de lógicamente permitir el acceso en el firewall o las medidas de seguridad en capas anteriores al servidor (comunmente puerto 5432), configurar el servidor PostgreSQL para aceptar dichas conexiones. Para ello, hay que realizar dos tareas:

1. Editar el fichero  postgresql.conf:
   Por defecto, seguramente tu servidor PostgreSQL solamente escuchará en localhost, para añadir más IP’s permitidas, o todas (wildcard), modificaremos la siguiente directiva:

   listen_addresses = '*'

   De la forma que está arriba, cualquier dirección IP puede conectarse remotamente al servidor PostgreSQL, si quisieramos restringir a determinadas IPs, simplemente cambiamos el wildcard * por IPs separadas por comas.
   Una vez modificado, si reiniciáis PostgreSQL veréis con el comando netstat que ya está escuchando en las IPs que hayáis indicado.

2. Editar el fichero pg_hba.conf:

   En este fichero podemos especificar varios parámetros, como por ejemplo qué host tiene acceso a qué base de datos, los permisos que asignamos al usuario, el tipo de autenticación…
   Básicamente tendremos que añadir líneas con la siguiente estructura:

   # local      database  user  CIDR  auth-method  [auth-options]
   # Ejemplo
   host mibasededatos miusuario 192.168.0.0/24 md5
   

Una vez realizados estos pasos correctamente, si reiniciamos el servicio PostgreSQL ya deberíamos poder acceder remotamente a la administración de las bases de datos especificadas.

A continuación os indico el modo de acceder a la shell de PostgreSQL (psql) sin necesidad de escribir la contraseña, os será útil a la hora de utilizar la shell unix + psql para la creación de scripts.

Para acceder a PSQL sin clave tenéis que crear un fichero llamado .pgpass en vuestra home (/home/usuario, /root/,…). El contenido del fichero es el siguiente:

hostname:port:database:username:password

Podéis utilizar el * en cualquiera de los puntos para por ejemplo, tener acceso a todas las bases de datos. Posteriormente simplemente cambiad cada parámetro por el que deseéis y finalmente asignadle los siguientes permisos:

chmod 0600 ~/.pgpass