En esta ocasión vamos a ver cómo realizar un procedimiento de backup utilizando las herramientas que nos proporciona PostgreSQL. En estas primeras partes, veremos cómo podemos utilizar <pg_dump> y las principales opciones.
Posteriormente, veremos cómo utilizar PostgreSQL PITR -Point In-Time Recovery- y qué ventajas nos ofrece este sistema de respaldo.
Tipos de Backups
En PostgreSQL existen diferentes tipos de Copias de Seguridad -Backups-: Completos y Parciales.
En esta primera parte, veremos los backups completos y cómo podemos realizarlos, dejaremos los backups parciales para las siguientes entregas.
Backup Completo -Full Backup-
Este tipo de copia de seguridad es un volcado completo de nuestra base de datos en un instante "t", y como es natural, contiene la información hasta el instante que comenzamos el proceso de backup.
Para realizar este backup utilizaremos el comando <pg_dump> y, es posible realizar esta operación en caliente, es decir, podemos tener nuestra base de datos abierta.
Ventajas Backup Completo
- Sencillo de realizar
- Se puede realizar en caliente
- Backup "consistente"
- En bases de datos pequeñas en muy rápido
- Se restaura "todo" o "nada"
- Consumo de I/O elevado
- En bases de datos grandes puede no ser efectivo
Realizar un Backup
Ya hemos hablado que para realizar un "full backup" utilizaremos el comando <pg_dump> cuyo formato es el siguiente:
pg_dump [OPCIONES] [DATABASE]Donde las opciones más importantes son
- -f, Nombre del archivo de salida donde vamos a guardar nuestro backup
- -Z, Salida comprimida con GZip. Por defecto el nivel de compresión es normal, pero podemos utilizar la compresión máxima si ponemos -Z 9
- -a, Sólo datos, nada de estructura.
- -b, Incluye objetos grandes en el volcado (blobs)
- -c, Añade comandos de borrado (clean) antes de cada objeto, por ejemplo, "DROP SCHEMA ..."
- -C, Añade los comandos de creación (create) antes de cada objecto, por ejemplo, "CREATE SCHEMA ..."
- -E, Utiliza el tipo de caracteres indicado en el volcado, por ejemplo, "-E UTF8"
- -n, Vuelca sólo el schema indicado, por ejemplo, "-n public"
- -N, Vuelca todos los schemas menos el indicado, por ejemplo, "-N private"
- -o, Incluye los OIDs en el volcado
- -O, No restaura los propietarios a los objetos
- -s, Vuelca sólo estructura nada de datos
- -t, Vuelca sólo la tabla indicada, por ejemplo, "-t havoctec.users"
- -T, Vuelca todas las tablas menos la indicada, por ejemplo "-T havoctec.options"
- -X, No vuelva los privilegios, es decir, los GRANT y REVOKE
$ pg_dump -f /u07/backups/full/havoctec-db-FULL-02.NOV.2010.dump.gz -Z 9 -o -C havoctecRestaurar un Full Backup
La restauración de un backup realizado con <pg_dump> es realmente sencilla, debemos tener en cuenta que al ser un backup completo vamos a "volcar todos los datos". En función de las opciones que hemos utilizado en el volcado, deberemos realizar algunos pasos diferentes, por ejemplo, si hemos utilizado la opción -C <opción Create>, dentro del propio dump tendremos los comandos SQL para la creación de la base de datos, y por lo tanto no será necesario crearla. Si no hemos utilizado esa opción, deberemos crear la base de datos primero -a no ser que queramos restaurar sobre otra base de datos diferente-
Para realizar la restauración de un volcado completo, utilizaremos el comando <psql> y como entrada nuestro archivo de backup, por ejemplo
$ psql -U postgres < /u07/backups/full/havoctec-db-FULL-02.NOV.2010.dumpFijaros que no he puesto el archivo gzip (.gz) ya que el comando <psql> cree que lo que viene son instrucciones SQL en texto plano, si no queremos descromprimirlo podemos utilizar
$ gzip -dc /u07/backups/full/havoctec-db-FULL-02.NOV.2010.dump | psql -U postgres
Un Ejemplo Completo
En este ejemplo vamos a crear una base de datos llamada "havoctec" y en ella una tabla donde introduciremos varios datos. Posteriormente realizaremos una copia de seguridad y la restauraremos.
Recordar que si no tenemos asignado el valor de <PGDATA> deberemos exportarlo antes de poder trabajar con PostgreSQL.
$ export PGDATA=/var/postgres/8.4/dataNos conectamos a PostgreSQL y crearemos la base de datos <havoctec>
$ psql -U postgresA continuación, realizaremos una copia de seguridad utilizando la opción <-C> (create) para poder restaurar en la misma base de datos con el mismo nombre.
Password for user postgres:
psql (8.4.1)
Type "help" for help.
postgres=# create database havoctec;
CREATE DATABASE
postgres=# \c havoctec;
psql (8.4.1)
You are now connected to database "havoctec".
havoctec=# create table urls(url_id serial, url_name character varying);
NOTICE: CREATE TABLE will create implicit sequence "urls_url_id_seq" for serial column "urls.url_id"
CREATE TABLE
havoctec=# insert into urls(url_name) values('http://www.havoctec.com/');
INSERT 0 1
havoctec=# insert into urls(url_name) values('http://www.sfchildren.com/');
INSERT 0 1
havoctec=# insert into urls(url_name) values('http://sparcki.blogspot.com/');
INSERT 0 1
havoctec=# select * from urls;
url_id | url_name
--------+------------------------------
1 | http://www.havoctec.com/
2 | http://www.sfchildren.com/
3 | http://sparcki.blogspot.com/
(3 rows)
$ time pg_dump -C -f /var/backups/havoctec-db-full.dump -U postgres havoctecAhora, borramos la base de datos
Password:
real 0m3.490s
user 0m0.029s
sys 0m0.012s
$ psql -U postgresPor último, restauraremos la base de datos
Password for user postgres:
psql (8.4.1)
Type "help" for help.
postgres=# drop database havoctec;
DROP DATABASE
postgres=# \q
$ psql -U postgres < /var/backups/havoctec-db-full.dumpComprobamos que tenemos los mismos registros, realizando un simple <SELECT>
Password for user postgres:
SET
SET
SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "havoctec".
SET
SET
SET
SET
SET
SET
CREATE LANGUAGE
ALTER LANGUAGE
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
setval
--------
3
(1 row)
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
$ psql -U postgres -d havoctecAutomatizar la Copias usando el <cron>
Password for user postgres:
psql (8.4.1)
Type "help" for help.
havoctec=# select * from urls;
url_id | url_name
--------+------------------------------
1 | http://www.havoctec.com/
2 | http://www.sfchildren.com/
3 | http://sparcki.blogspot.com/
(3 rows)
havoctec=# \q
Por último, podemos utilizar nuestro <crontab> para realizar una copia de seguridad completa diariamente, y así, evitar sorpresas de última hora.
El problema que nos podemos encontrar es, principalmente, que el comando <pg_dump> nos solicite una contraseña y no podamos automatizar el proceso.
No hay problema, para ello está el archivo <pgpass> PostgreSQL Password File, en el cual declararemos los usuarios y contraseñas necesarios para el acceso sin password. El formato del archivo es el siguiente:
maquina:puerto:base de datos:usuario:passwordSi utilizamos un "*" en el campo de <base de datos> se podrán acceder a todas la bases de datos, por ejemplo, si queremos que el usuario backups pueda acceder a cualquier base de datos, ponemos
Ahora PostgreSQL no nos ha solicitado la contraseña, porque la ha encontrado en el archivo, y por lo tanto, la ha introducido por nosotros, así que ya podemos añadir nuestra entrada al cron para que haga copias de seguridad completas todos los días a las 02:00h, por ejemplo,$ echo "db1.havoctec.com:*:backups:password" > $HOME/.pgpass$ chmod 600 $HOME/.pgpass$ pg_dump -U backups -Z9 -C -f /var/backups/havoctec-db-full.dump.gz havoctec
$ crontab -e
00 02 * * * /u01/app/postgres/8.4/bin/pg_dump -U backups -Z9 -C -f /var/backups/havoctec-db-full.dump.gz havoctec
:wq
Conclusiones
Hemos visto un primer acercamiento a las opciones en cuanto a Backup y Restore que nos ofrece PostgreSQL, y además hemos definido una política básica de copias utilizando el cron del sistema, para, al menos, tener una copia diaria de nuestra base de datos.
En las siguientes entregas veremos cómo podemos crear una política de Backup y Restore Eficiente y Segura en PostgreSQL
Referencias
crear un backup de la base de datos completo
ResponderEliminarse debe pasar a lo ultimo el comando "-U postgres" para lograr
hacer la conexion a la base de datos, y especificar la ruta en donse se guardará
la informacion de la base de datos pero antes de esto se debe crear una carpeta
que se llamara backup en el disco C o donde tu quieras en mi caso la ruta seria
C:\backup = RUTA EN DISCO DONDE SE GUARDARA EL BACKUP
sencilla = esto es la base de dato a la cual le haremos el backup
C:\Archivos de programa\PostgreSQL\9.1\bin>
pg_dump -f C:\backups\sencilla-db-FULL-02.NOV.2010.dump.gz -Z 9 -o -C -U postgres
Gracias por el articulo, me fue de gran ayuda, seria bueno que siguieras con el backup parcial
ResponderEliminarHola,
EliminarLa verdad es que lo tengo "medio preparado", pero .. espero tenerlo pronto :D
Muchas Gracias,
gracias por la informacion, esta interesante tu blog. te felñicito y espero que sigas adelante con este proyecto.
EliminarSUERTE!!!
Hola,
EliminarMuchas Gracias a ti y ... en ello estamos!
;)
Genial !
ResponderEliminarhola... tengo un backup que no tiene extencion como puedo restaurarlo?... espero tu ayuda gracias!
ResponderEliminarHola Henry,
EliminarPara saber qué tipo de archivo es, puedes hacer un "file NOMRE_ARCHIVO" y saber qué tipo es, por ejemplo,
havoc@h200:~$ /u01/app/postgres/9.0/db/bin/64/pg_dump -s -f backup-sin-extension -U postgres postgres
Password:
havoc@h200:~$ file backup-sin-extension
backup-sin-extension: English text
havoc@h200:~$ /u01/app/postgres/9.0/db/bin/64/pg_dump -s -f backup-sin-extension-comprimido -Z9 -U postgres postgres
Password:
havoc@h200:~$ file backup-sin-extension-comprimido
backup-sin-extension-comprimido: gzip compressed data - deflate method
muchas gracias!!
EliminarGracias muy bueno, siempre se tienen que realizar los backups con .dump.
ResponderEliminarsirvio en 100% de un windows a Linux Ubuntu 12.01 y Centops 6.3
Hola Anónimo,
EliminarMuchas Gracias!
Me alegro de que sirviese, ... espero que la segunda parte también te ayude!
Buenas tardes,
ResponderEliminarYo quiero sacar un backup de los usuarios que he creado, y que los pueda seguir usando en otro equipo.
Es que necesito conservar el Oid
Agradezco la ayuda.
Hola soy reynaldo y tengo un problema con una base d datos que quiero sacar el backup sin codigo, el nombre de la base es Imcruz
ResponderEliminareste es el error q sale...
ERROR: database "Imcruz" already exists
SQL state: 42P04
Ahora me podrias explicar pero de la manera facil para sacar el backup y donde lo guarda por defecto. Agradeceria la ayuda..
Hola trabajo en una empresa donde tengo acceso a la base de datos porque me dieron el usuario y contraseña. Mi pregunta es la siguiente.. al sacar un backup en postgres de una bd con todos sus esquemas.. pueden darse cuenta que lo hice?? tal vez los dbas??
ResponderEliminarHola Anónimo,
EliminarLa verdad es que ... depende un poco de cómo esté configurado el sistema. Si existe auditoría de comandos, el DBA podrá ver que se ha ejecutado un pg_dump o se ha realizado una copia de la DB.
Además, esto supone un delito, así que yo no te recomiendo hacerlo.
Un Saludo,
Urko
Hola Urko, me parecio muy interesante tu blog, tengo una consulta para ver si me puedes ayudar, hago un backup diario con una tarea programada en windows y utilizo un archivo .bat pero en el archivo debo colocar la clave del postgres o un usuario administrador, hay alguna forma de hacer este backup sin colocar la clave visible?
ResponderEliminarDe antemano gracias por tu atencion.
Hola Chery,
ResponderEliminarGracias por tus comentarios,
Sobre lo que me preguntas, yo no utilizo una arquitectura Windows, pero entiendo que será un procedimiento igual que en UNIX.
Lo que necesitas es crear un archivo OCULTO y PROTEGIDO llamado "pgpass" y ahí declaras que usuario, password y a que db puede acceder.
En el BAT, deberás exportar la variable de entorno haciendo referencia a este archivo, así
SET %PGPASSFILE% = C:\MI_DIRECTORIO\pgpass
Y luego, ya puedes lanzar el comando y no te pedirá la contraseña.
Un Saludo,
Urko