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_ASCIIEl 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.
Temas que te pueden interesar:
46 Comentarios en “Forma correcta de sacar un respaldo (backup) en PostgreSQL”
Deja tu Comentario
Gracias me funciono muy bien
el esquema lo saco sin ningun problema, pero la data me sale un mensaje
could not change directory to /root
pg_dump: opcion invalida — l
cual debe ser la causa, gracias
Jimmy, tu problema es que has de tener una version de pg_dump, ya sea mas moderna, o mas vieja, puedes ejecutar el comando pg_dump –help para que el comando para exportar la data que yo menciono en este articulo sea compatible con tu version, ojala te funcione, saludos!
hola esoty empezando en postgres, y necesito hacer un backup de la base mediante sentencia.
Utilice esto
pg_dump datos > /backup/ss.sql
de la base datos hago un backup en ss.sql
pero me da este error:
ERROR: syntax error at or near “pg_dump”
SQL state: 42601
Character: 1
Desde ya muchas gracias
Paola, porque no tratas de hacerlo como explico en este articulo? Me podrias explicar mejor a que te refieres con “mediante sentencia” saludos
estoy por terminar un sistema, pero me falta hacer el respaldo para la db de postgres utilizo so windows xp, necesito hacer el respaldo con php por medio de una pagina web, si alguien tiene un script que me pudiera ayudar………se los agradeceria….
Estuve intentando lo mismo pero desde php y usando pg_dump pero como exec o system desde windows y no me funciona, alguno de ustedes me podria ayudar? o sabe como seria la linea correcta.
Joel, no entendi mucho tu pregunta, usas el pg_dump desde un Windows? este tutorial aplica a Postgres instalado en un servidor linux, porque no ves la documentacion para postgres en Windows, te dejo una direccion en la cual puedes encontrar ayuda, esta en ingles:
http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows
Saludos!
Hola soy nuevo en esto.
instalé red hat 5 en un servidor e instalé el postgres, trato de ejecutar una prueba de respaldo con la base de datos template1, pero me sale este error:
pg_dump: [archiver (db)] connection to database “template1″ failed: could not connect to server: No such file or directory
Is the server running locally and accepting
pero no se que debo hacer….
GRACIAS.
Gustavo,
Existe la base de datos template1?
UMMMM..PUES NO SE, SEGÚN LA DOCUMENTACIÓN DEL RED HAT SI, HAY ALGÚN COMANDO QUE ME DIGA LAS BD
Si lo haces dentro de psql con el comando \l
postgres@db:/home/rolando$ psql
Welcome to psql 8.1.15, 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=# \l
List of databases
Name | Owner | Encoding
————+————+———-
affiliated | coreg | UTF8
Te recomiendo que leas esta pagina para que aprendas un poco mas de los comandos de postgres:
http://www.yolinux.com/TUTORIALS/LinuxTutorialPostgreSQL.html
creo que el problema está en que no está bien instalado el postgres en mi linux, pues trato de levantar el servicio como lo indica el tutorial que me diste, pero me da failed…
para todo da el error de conexion.
Gustavo,
Como lo instalaste? me imagino que bajaste un rpm ?
Ya no me acuerdo mucho de redhat pero creo que bajas el archivo y lo instalas con el siguiente comando:
rpm -ivh archivo.rpm.
Te recomiendo que pruebes con una version mas reciente de linux, si no estoy mal la version que estas usando salio hace mas de 8 años.
ummm, no esta versión es la 5 de 64 bits la descargue el año pasado…….
el mismo red hat trae postgres a la hora de instalarlo, uno habilíta la opción…….
Gustavo, tienes razon, cometi un error con la version.
Te recomiendo que desinstales postgres y lo vuelvas a instalar bajando el rpm desde redhat.
Saludos.
si eso voy a hacer…gracias…
muy buena tu página….ahí nos estamos hablando…..gracias
hola…..
ya tengo bien el postgres…gracias.
pero tengo otra duda….
cuando hago un respaldo de la bd, para restaurarla en otro servidor para probar si está bien el respaldo, tengo que hacer algo previo o qué instrcción debo usar???
Gracias.
Gustavo,
Tienes que hacer todo lo que dice esta guia a partir de:
Para restaurar la información haremos lo siguiente.
Para restaurar.
Saludos.
Hola nuevamente.
ya pude realizar el respaldo y la restauración de la DB pero trato de probarla y me da un error: “Error: Database connection failed.
It is possible that the database is overloaded or otherwise not running properly.
The site administrator should also check that the database details have been correctly specified in config.php
ya revise archivos conf y php, pero me sigue dando el error…
Tendrás alguna idea qué puede ser??
Gracias.
hola
me podrias ayudar sabes yo al igual que paola estoy tratando de hacer backup pero con sentencias sql porque tengo mi programa en visual.net y desde ahi debo escribir una sentencia para que mi postgres pueda hacer lo que le pido sin el promp con comandos pero de la forma como explicas me sale con el promp pero desde mi visual con postgresql no…!! ayudame si..
Hola, mi preguta es sencilla, aunque soy nuevo en linux y postgresql no puedo haer un backup “desatendido”, que quiero decir con esto:
Instale un linux Ubuntu 8.10, para cambiarme al usuario “postres”, tengo que hacer el comando “sudo su postgres”, porque de otra manera pg, no me deja trabajar.
El problema es que “sudo su postgres” me pide contraseña.
he bajado varios scripts de backup pero ninguno me funciona porque posgres no reconoce el usuario que ejecuta el script, simple.
Ahora como puedo hacer un backup desatendido, es decir que lo coloque en el cron, y todas las noches haga un .sql , despues de ahi yo me arreglo y lo hago gz y lo subo por ftp a un servidor ….
Desde ya muchas gracias.
Nicolas,
Cuando ejecutas el comando sudo su postgres te pide clave, pero es la clave de tu cuenta, no la de postgres. Por ejemplo si tu usuario es nicolas, y tu clave hola123, al ejecutar el comando sudo postgres te va a pedir clave y ahí le pones hola123 que es la clave de tu usuario. Para que sudo funcione necesitas que tu usuario este listado en el archivo /etc/sudoers.
Con respecto a de agregar un backup desatendido en el cron, yo lo he echo de la siguiente manera:
Creo un archivo llamado backup.sh y le doy permisos de ejecucion con el comando chmod +x backup.sh, dentro de este archivo pongo lo siguiente:
La base de datos a la que le voy a hacer el respaldo se llama Orders.
echo initiating backup…
pg_dump -sv Orders -O > /backup/Orders.schema.sql
pg_dump -Fc -f /backup/Orders.data.dump -a –disable-triggers Orders
echo postgresql backup done…
Grabo el archivo y lo ejecuto con el comando ./backup.sh, en mi caso puse que el backup se grabara en la carpeta /backup.
En el archivo /etc/crontab agrego la siguiente linea al final:
00 21 * * * postgres /bin/sh /backup/backup.sh
Con este comando ejecutaremos el comando backup.sh con el usuario postgres, a las 9 pm todos los días.
Saludos Nicolas, espero que esto te sirva.
Gracias por la pronta respuesta
voy a probar la secuencia, me parece que mi version de Linux no soporta en el cron el usuario postgres, es decir, intente otra funcion de backup y el cron dio un error de que no existia el comando postgres ….
seguire intentando ..
gracias igualmente
PD: si sabia lo del sudo, quiza me exprese mal.
si en Ubuntu hago $ su postgres, me pide la contraseña de postgres, porque el perfil root esta deshabilitado, por lo tanto soy un “soldado raso”, ahora con el sudo puedo ser root por un ratito, asi que juntando los dos comandos, logro el objetivo, pero si me pide mi propia contraseña.
Saludos
Clara y Paola,
No tengo mucha experiencia con lo que les voy a recomendar, pero amigos que trabajan de manera similar que ustedes utilizan este programa para hacer los backups, pruebenlo:
http://www.pgadmin.org/download/windows.php
Saludos.
Nicolas,
Te sugiero que copias alguna entrada de crontab similar a lo que tu quieres asi ves como es el comando en Ubunto, los comandos que yo te envie son los que yo uso en debian.
Saludos.
Creo que lo tengo, use tus entradas de pg_dump en un archivo:
me quedo asi:
#!/bin/bash
DIR=/home/operador/bkppg
DB=cgpq_dev
[ !$DIR ] && mkdir -p $DIR || :
echo initiating backup…
pg_dump -sv $DB -O -i -x -O | gzip -c > $DIR/$DB.schema.sql.gz
pg_dump $DB -i -x -O -R -a –disable-triggers | gzip -c > $DIR/$DB.data.dump.gz
echo postgresql backup done…
echo .. cambiando permisos…
chmod 666 $DIR/$DB.schema.sql.gz
chmod 666 $DIR/$DB.data.dump.gz
echo … fin backup …
—
Como veras los hice .gz ( los queria hacer tar, pero da lo mismo)
entonces hice sudo su postgres y en el terminal hice crontab -e, para editar las entradas del cron de postgres …
por las dudas el directorio donde se hace el backup todos tiene permiso de escritura ….
Mañana te aviso si funciono !!
gracias
Hola! alguien me puede decir como se hace el respaldo de la base de datos en postgres desde una pagina web hecha con php? de antemano, muchas gracias!!
Darling,
Te dejo este enlace:
http://archives.postgresql.org/pgsql-php/2005-12/msg00023.php
Lo vas a tener que modificar para que cumpla tus necesidades. Saludos!
no se como hacer el respaldo de mi bd mi version de postgresql es v 1.2.0 y la tengo en un sistema operativo server 2003 intento todas las sentencias que me indican pero no hace nada algun otro comentario que me puedan ofrecer para mi respaldo gracias…
otra pregunta por ahi comentaron que: “No tengo mucha experiencia con lo que les voy a recomendar, pero amigos que trabajan de manera similar que ustedes utilizan este programa para hacer los backups, pruebenlo:
http://www.pgadmin.org/download/windows.php ” ese programa si hace respaldos de la base de datos o solo actualiza mi pgadminIII tools, porque ya lo descarge y me dice que va instalar un pgadmin3 tools y tengo miedo, por no saber, de que va a pasar si lo instalo o mejor no lo instalo?.
mil gracias.
saludos¡¡¡¡¡¡¡¡¡¡
Dan,
Si lo puedes instalar, es totalmente seguro, no esta demas que siempre tengas un antivirus instalado en tu computadora.
Saludos.
Hola, he probado de la forma que aparece en esta pagina, pero me dice Permiso denegado. Otra forma que encontre en la red funciona pero me pide contraseña. Habra una forma de hacerlo como dice por aqui con una sola instruccion, sin que me pida contraseña o pasandola como parametro dentro del mismo comando o algo asi? Muchas Gracias por la ayuda!
hola
gracias rolando ya me salió de la forma que dijiste y solo que al ejecutarlo me aparece la contraseña para que yo la introduzca y luego ya me hace el backup y esta todo bien……
@clara
Suele pasar, justo el dia de hoy movi de servidor una base de datos y me sucedio lo mismo. Pero todo esta bien. Saludos.
Amigo quisiera que me ayudaras soy nueva en postgre y quisiera saber como utilizo el pg_dump en Postgre bajo ambiente Windows para realizar un respaldo de las bases de datos. Gracias por su colaboracion
Hola Gaby para usar el pg_dump bajo netornos windows lo puedes hacer de la misma forma que lo haces en entonos Linux/Unix mediante una consola de simbolo del sistema….unicamente debes tener en cuentaque los comandos los debes ejecutar desde la carpeta /bin de tu instalacion por ejemplo: C:\Program Files\PostgreSQL\8.3\bin>
o si lo prefieres puedes agregar esta ruta a la variable de entorno PATH y asi los podrás ejecutar desde donde quieras
Hola cordial saludo,
Me puedes guiar en como sacar un BK completo con motor, data y todo, tanto en sistema Linux como en Windows.
Soly nuevo en esto. Mil graicas y quedo atento
Hola. estoy iniciando con POSTGRESQL y pues estoy realizando una base de datos en bloc de notas con codigo SQL pero ahora quiero migrarlo a Postgres , como podria hacerlo?
Ademas si existe la posibilidad de migrar una tabla de excel a Postgresql?
Te lo agradeceria mucho….
Hola un comentario haz intentado aparte de comprimirlo al sacar el bk comprimir aun mas este backup?.. con un gzip por ejemplo y cargarlo despues de haberlo descomprimido…
Rolando, muy bueno tu articulo, ahora te cuento yo tengo una base de datos con pocas tablas pero muy, muy grandes, la base ocupa un lugar de 30 gigas, y hay una tabla en particular de 10 gigas, lo unico que se me ocurrio es hacer backup por tablas, porque se me hacia complicado restaurar una tabla, porque el archivo sql es muy grande, haciendo un dump por tabla, a vos que te parece, tambien lei por ahi que el postgres comprime los datos, hay que hacer algo para que los comprima. gracias
Buenas estoy probando con los backup, pero en el pgadmin III se guinda (no hace nada) y dandole la instruccion me dice que no tengo prmisologia. al darle su postgres me pide el password y se lo doy y no lo reconoce (le doy el password de postgres) tambien he intentando con el password de linux y nada.
Gracias
@Miguel
Prueba ejecutar el backup desde la linea de comando (pg_dump) desde el usuario postgres.
hola, gracias por la sugerencia, lo que quiero que me ayuden es como importar datos desde microsoft Excel a mi base de datos en postgre, porfa.
pg_dump -U postgres -O circuitcity > circuitcity.backup
bueno esta es la forma que se hace la restauracion todo sale bn cuidense
@ervin sanchez
Gracias por compartir, saludos.