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

16 comentarios en “PostgreSQL: cómo crear y restaurar backups de bases de datos

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

  2. 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. 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?

    • 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. 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…

  5. 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

  6. 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.

  7. Buenas necesito alguien me ayude con un problems que tengo para colocar un server De RF Online es que no me compaguina la game cp con el juego los item que compras en la game cp no me llegan all juego alguien podria ayudarme a solucionar este problema me pueden add all whatsaap 7863576373 gracias

  8. hola estoy creando una base de datos llamada producción con dos tablas
    ya creada la base de datos como hago un backup con esa base de datos

Deja un comentario

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

Puedes usar las siguientes etiquetas y atributos HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>