# rm-rf.es | Administración de sistemas

Blog de un SysAdmin Unix, Gnu/Linux, Windows y lo que haga falta.

PostgreSQL: cómo crear y restaurar backups de bases de datos


PostgreSQLEn 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

Categoría: Bases de datos | Etiquetas: , , , ,

12 Comentarios

  1. h3x dice:

    Muy bueno tu post, se podria convertir en una guia obligada para los nuevos en postgre si dieras algunos ejemplos de cada metodo!!

  2. nelson dice:

    Compadre aplico esto
    psql basededatos < /root/fichero.sql

    pero como respuesta el sistema me entrega

    /root/fichero.sql: Permiso Denegado

    Sabes a que se debe y como solucionarlo ???

  3. sorel dice:

    a mi me pide una contraseña después de hacer esto

    psql basededatos < /root/fichero.sql

    le doy la contraseña que se supone que es la del usuario postgres pero me manda un error que no se pudo identificar el usuario "Sorel" no comprendo debería ser postgres no?

    • Cazapata dice:

      Sorel, cuando ejecutas un comando a nivel de shell para el motor de base de datos postgres, pues si no le especificas el usuario de base de datos, toma el que tiene en el entorno tu shell. Asi, si yo tengo en mi cuenta el siguiente prompt:

      [cazapata@localhost ~]$

      y ejecuto una instrucción en sql como por ejemplo listar las bases de datos existentes:

      [cazapata@localhost ~]$ psql -l
      psql: FATAL: no pg_hba.conf entry for host “[local]”, user “cazapata”, database “postgres”, SSL off
      [cazapata@localhost ~]$

      pues se genera un error porque trata de conectarse con el usuario “cazapata” a la base de datos, en este caso al template1. Por lo tanto yo debería escribir el comando de la siguiente forma:

      [cazapata@fullengine ~]$ psql -l -U postgres
      List of databases
      Name | Owner | Encoding
      —————+——————+———-
      template1 | postgres | LATIN1
      temporal | postgres | LATIN1
      tiernet | utiernetdb | LATIN1
      (3 rows)

      [cazapata@locahost ~]$

      Si tienes luego problemas con la contraseña, modifica el archivo pg_hba.conf que por lo general esta en /var/lib/pgsql/data y crea una línea que diga:

      local all postgres trust

  4. luis eduardo dice:

    buenos dias

    tengo mi backup betatonio.backup como hago para exportarlo a postgres??

    agradezco su ayuda :)

  5. felix dice:

    Buenas amigos como estan… saludos cordiales… tengo una preguntica…
    Tengo un sistema web de carnetizacion corriendo bajo ambiente windows con el servidor virutal xampp y la base de datos en postgresql… la tarjeta madre se me quemo, y la informacion que contiene el disco duro la visualizo correctamente del xampp, pero la de la base de datos no encuentro como sacarla sin que arranque el equipo, osea de esclavo en otra maquina… quisiera ver donde guarda la base de datos postgre para ver si la puedo restaurar en otro equipo… ya he carnetizado a varios por esa razon deseo recuperarlos…

  6. diana dice:

    Alguien que me ayude con un programa en shell urg comuniquense 042840229 plis

  7. jose luis dice:

    Ejecuta esto
    #su -l postgres -s /bin/sh -c “/usr/bin/pg_dump BASEDATOS” > archivo.sql

  8. carlos dice:

    como hago para restaurar una base de datos dond un amigo quien me hizo el backup desde su pc tiene puerto distinto al mio…

  9. Jose dice:

    Buenas tardes,

    Alguien que me diga como le saco un backup a los usuarios creados en postgres, es que al hacer un backup me lo hace solo de las tablas y vistas, pero no de los usuario.

    Agradezco cualquier ayuda y si pueden dejar un ejemplo mejor.

    Saludos

  10. murpy dice:

    Como puedo restaurar una base de datos de esta forma ??

    database.tar.bz2

    entiendo que esta comprimida al descomprimir me genera varios archivos comprimidos en .gz

    intente con este comando : pg_restore -v -C -O -d template1 database.tar.bz2

    despues de 25 hora no genero nada.

Deja una respuesta