Forma correcta de sacar un respaldo (backup) en PostgreSQL

Esta base de datos es una de las mejores, y a mi en particular me gusta mas que MySQL, pero sigue siendo un dolor de cabeza a la hora de hacer respaldos.

A continuación les enseñare como hacer respaldos de una manera rápida y sencilla. Con este tipo de respaldo no vamos a perder SP, Vistas, tablas, ni nada de data etc.

Vamos hacer de cuenta que la base de datos que queremos hacer respaldo se llama prueba. Yo voy a utilizar el usuario de postgres para trabajar con esto. Pero podrían usar cualquier otro usuario. Yo para “pasarme” al usuario postgres ejecuto el siguiente comando como root:

www:/etc# su postgres
postgres@www:/etc$

Ya siendo usuario postgres no tengo ningún tipo de restricción y puedo ejecutar cualquiera de los comandos mencionados a continuación.

Con el siguiente comando sacaremos respaldo del esquema (schema) de la base de datos:

pg_dump -sv prueba -O > /backup/prueba.schema.sql

Con el siguiente comando sacaremos el repaldo de la DATA de la base de datos:

pg_dump -Fc -f /backup/prueba.data.dump -a –disable-triggers prueba

Estamos usando las opciones -Fc que significa la F de formato y la c de custom, estamos utilizando el formato custom, para así al momento de restaurar la base de datos en otro server o en otra base de datos se nos hará mas fácil porque vamos a utilizar el comando pg_restore.

Les adjunto un poco mas de información en ingles con respecto al dump de postgreSQL en formato “custom”.

c      Output  a  custom  archive  suitable for input into pg_restore. This is the
most flexible format in that it allows reordering of loading data  as  well
as object definitions. This format is also compressed by default.

La opción -f lo uso para especificar el archivo.

La opción -a especificar que quiero solo exportar la data.

La opción  –disable-triggers la uso para que no de problemas a la hora de hacer la restauración.

Estos comandos nos crearan dos archivos, uno con el esquema (schema) y otro con la data de la base de datos.

Para restaurar la información haremos lo siguiente.

Vamos escribir el comando psql y luego enter.

Welcome to psql 8.1.11, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=#

Ahora ya estando dentro de postgres, voy a crear la base de datos con el siguiente comando:

CREATE DATABASE prueba ENCODING ‘UTF8′;

Antes de crear la base de datos verifiquen que tipo de ENCODING usa la base de datos de donde sacaron el respaldo ya que si crean la base de datos con diferente encoding al importar la data el respaldo NO va a funcionar, para saber el encoding de la base de datos lo hacen de la siguiente manera:

psql -l

Y les va a mostrar lo siguiente:

sh-3.1$ psql -l
List of databases
Name    |   Owner   | Encoding
———–+———–+———–
data1 | data1 | SQL_ASCII
data2   | data2    | SQL_ASCII
postgres  | postgres  | SQL_ASCII
template0 | postgres  | SQL_ASCII
template1 | postgres  | SQL_ASCII

El ENCODING de todas las bases de datos en este ejemplo es SQL_ASCII.

luego nos salimos de la consola de postgres con el siguiente comando:

\q y presionamos enter.

Ya estando de vuela en la consola de sistema, ejecutaremos el siguiente comando:

psql prueba < prueba.schema.sql

Con este comando importaremos el schema en la nueva base de datos.

Ahora con el siguiente comando importaremos toda la data:

pg_restore -a -v -e -Fc -O –disable-triggers -d prueba prueba.data.dump

Con este comando insertaremos toda la data en la nueva base de datos.

Recomendaciones:

En este ejemplo sacamos el respaldo con el usuario postgres, recomiendo sacar el respaldo e importar la base de datos con el usuario propietario de la base de datos. Ya que si insertamos la base de datos en otro server cone l usuario postres, al momento de poner a funcionar el respaldo van a haber problemas con los permisos del usuario, ya que el usuario de la base de datos en el anterior server es juan, el usuario en el respaldo va a ser postgres.

Felicitaciones, ha echo un respaldo y ha restaurado una base de datos de postgreSQL, sin perder nada de información me imagino que hay muchas maneras de realizar esto, pero compartí con ustedes la manera como yo hago los respaldos, cualquier comentario o sugerencia es bienvenida.

Etiquetado con: , ,
Publicado en: Bases De Datos
70 comentarios sobre “Forma correcta de sacar un respaldo (backup) en PostgreSQL
  1. JuanS dice:

    Hola

    Que buen aporte pero me surgio una duda realize un backup BD con un simple pg_dump pero el orden que tomo al momento de ir restaurando las tablas fue de manera alfabética, con lo cual la int referencial pues acabo con ella, :(.
    Con esta sintaxis me servira para evitar este problema o necesitaria otra opcion ??

    gracias disculpa pero es que soy nuevo en esto!!!!!!

  2. Arnaldo dice:

    hola quisiera hacerte una pregunta ya que deseo saber si puedo pasar un esquema existente en una base de datos a otra base de datos que tambien exista, a traves de lenguaje sql.

  3. Rolando dice:

    @Arnaldo
    Gracias por visitar mi blog y por exponer tu duda.

    Para sacar el script de un esquema, debes agregar el parametro -n mas el namespace que quieres ( o –schema=schema)

    Ejemplo
    Si tienes las siguientes bases de datos: mibasededatos y minuevabasededatos,
    con los siguientes esquemas

    mibasededatos
    -Public
    -Reports
    -DW
    -Extrenal

    minuevabasededatos
    -Public

    y quieres pasar el esquema de Reports a minuevabasededatos. Debes seguir los siguientes pasos.

    paso 1:
    Hacer dump del esquema Reports de mibasededatos

    Usando el parametro -n
    pg_dump -sv -n Reports minuevabasededatos -O > /backups/reports.mibasededatos.schema.sql

    o

    Usando –schema
    pg_dump -sv –schema=Reports minuevabasededatos -O > /backups/reports.mibasededatos.schema.sql

    Paso 2:

    Estando en consola ejecutamos el siguiente comando

    psql minuevabasededatos < reports.mibasededatos.schema.sql

    Y voilà ! ya tienes el esquema Reports en minuevabasededatos.

  4. Arnaldo dice:

    ok comprobarè a ver si me resulta muchas gracias por tu ayuda estaremos en contacto.

  5. Arnaldo dice:

    Hola Rolando espero que te encuentres bien mi duda es la siguiente probe el backup y todo bien pero como lo restauro en la base de datos que quiero sabes que ahora necesito saber como hacer el restore de un esquema de una base datos.

  6. Arnaldo dice:

    Hola rolando nuevamente te escribo para decirte que me aparece un error en estye script es el siguiente:

    postgres@arnaldo-desktop:/home/arnaldo$ pg_dump -sv -n Reports minuevabasededatos -O > /backups/reports.mibasededatos.schema.sql
    pg_dump: No se encontraron esquemas coincidentes
    pg_dump: *** se abortó a causa de un error
    postgres@arnaldo-desktop:/home/arnaldo$

    muchas gracias

  7. Movorack dice:

    Siempre que uso la opción de backup desde pgadmin me saca el archivo .backup pero cuando restauro la DB pues muchos datos se me han perdido.

    sucede que tengo varios schema y están interconectados y analizando bien el script.. pasa lo que comenta @JuanS…

    me ordena las tablas en orden alfabetico y no en orden de relación… cuando comienza a hacer las inserciones estas fallan.

    y aunque en el script están todos mis datos… en la nueva DB la mayoria de las tablas están vacias.

    ¿En este caso… tendrias alguna sugerencia o comentario?

  8. Arnaldo dice:

    Hola Rolando espero que te encuentres bien te escribo para decirte que probé la solucion que me diste pero me aparecen dos errores los cuales son:

    1) postgres@arnaldo-desktop:/home/arnaldo$ pg_dump -sv -n Reports minuevabasededatos -O> /backups/reports.mibasededatos.schema.sql
    pg_dump: No se encontraron esquemas coincidentes
    pg_dump: *** se abortó a causa de un error

    2) postgres@arnaldo-desktop:/home/arnaldo$ pg_dump -sv -schema=Reports minuevabasededatos -O> /backups/reports.mibasededatos.schema.sql
    pg_dump: [archivador (bd)] falló la conexión a la base de datos «minuevabasededatos»: no se pudo traducir el nombre «ema=Reports» a una dirección: Nombre ó servicio desconocido
    pg_dump: *** se abortó a causa de un error

    como veras probé a realizar el backup a el esquema reports como ejemplo te agradeceria tu ayuda gracias de antemano.

  9. Rolando dice:

    @Arnaldo

    1) El esquema Reports no existe, creo que copiaste exactamente lo que yo te puse en el anterior comentario.
    2) Verifica si al copiar lo que te puse copio los dos guiones –schema

  10. Arnaldo dice:

    Como estas Rolando:

    claro lo hice como prueba, entonces

    luego de las pruebas de ejemplo lo uso en la de verdad, claro que en localhost.

    de hecho ya probe la instruccion de respaldo y funcionó pero estoy probando la de restore y este es el mensaje que muestra:

    postgres@arnaldo-desktop:/home/arnaldo$ psql minuevabasededatos < reports.minuevabasededatos.schema.sql
    bash: reports.minuevabasededatos.schema.sql: No existe el fichero ó directorio
    postgres@arnaldo-desktop:/home/arnaldo$

    se supone que reports.minuevabasededatos.schema.sql es el nombre del archivo que yo le estoy asignando para cuando lo vaya a restaurar el lo encuentre en la carpeta backup pero me dice que no lo encuentra

  11. Arnaldo dice:

    Como estas Rolando te cuento que por fin resolvi el problema de una manera bien extraña te cuento que fue lo que hice en el pg admin 3 hice backup al esquema y luego lo restaure en la otra asi de facil me parece extraño porque lo intente como mi primera opcion y al no concretarse me preocupe de como hacerlo por consola de verdad que fue una tonteria pero pude resolverlo una vez mas muchas gracias por tu ayuda que me sirvio de mucho para resolver esta duda.

    saludos desde Venezuela.!!!

  12. zaira dice:

    hola buenas tardes:

    necesito saber para que se ocupan los siguientes comandos en postgres son los siguientes (-F c -b -v -f)

  13. Gabriel dice:

    Hola Rolando.

    Gracias por el aporte, me sirvió mucho. Quería preguntarte algo. Hay forma de generar el backup de mi BD exceptuando una tabla, o las tablas que empiecen por X por ejemplo…tengo las tablas perro, casa, arbol, perro_tom,perro_jerry, telefono. Podría hacer un backup de todas las tablas excepto las que empiezan por perro_ ?? Gracias por tu tiempo.

  14. Adrian dice:

    Hola Rolando
    Yo soy el mas novato en el mundo en este momento usando Potgres SQL pero bueno todos fuimos asi alguna vez.
    MI duda es si se puede y de ser asi como se hace para crear un backup de mi base de datos de manera automatica y como restaurlarlo. Por favor ayudeme con esto que es muy importante para mi trabajo.

  15. Brandon Calvo Rojas dice:

    Muchas gracias por el aporte Rolando

    Tengo una duda a la hora de ejecutar la linea para el respaldo del DATA me sale el siguiente error me podrías orientar con la solución si me haces el favor.

    NOTA: openfire1 es el nombre de mi base de datos en postgres.

    postgres@sj-jabber-02:/etc$ pg_dump -Fc -f /backup/openfire1.data.dump -a –disable-triggers openfire1
    pg_dump: too many command-line arguments (first is “openfire1″)
    Try “pg_dump –help” for more information.

  16. Jeudy dice:

    Hola,

    Inicialmente me funcionó bien, pero conforme la BD crecia y tenia mas datos, me dio problemas de integridad referencial. Esta otra manera me funcionó mejor:

    http://www.thegeekstuff.com/2009/01/how-to-backup-and-restore-postgres-database-using-pg_dump-and-psql/

    Saludos!

  17. maximi89 dice:

    Hola, muy buena tu guía, me sacaste al toque de las dudas que tenía sobre exportar el modelo y importar el modelo jajaja

    Gracias

  18. ivan dice:

    Rolando en la instruccion:

    pg_dump -Fc -f /backup/prueba.data.dump -a –disable-triggers prueba

    Me arroja el siguiente error:

    pg_dumpopcion invalida –’d’
    Try pg_dump –help for more information

    la verdad no comprendo el error puesto que ‘d’ (sin comillas) no esta puesto como opcion

  19. Jorge Constanzo dice:

    Iván:

    Es porque esos parámetros que son palabras deben ir precedidos por doble guión, vale decir, “–disable-triggers”. Al poner un sólo guión Bash sólo lee la primera letra e interpreta el resto como entrada de ese parámetro. Al no existir el parámetro “d” arroja ese error.

    Un poco tardía la ayuda, pero espero que le sirva al resto también.

    Saludos!

  20. Jorge Constanzo dice:

    Mmmm, no aparecieron los dos guiones antes de “disable-triggers”, bueno pero debe ir con dos guiones. Para mayor información, ver la documentación del comando:

    http://www.postgresql.org/docs/8.4/static/app-pgdump.html

    Saludos!

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=""> <strike> <strong>