SafeChildren Banner

Havoc Oracle Solaris Experts

martes, 2 de noviembre de 2010

Copias de Seguridad y Recuperación en PostgreSQL - Parte 1

Introducción
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
Problemas Backup Completo
  • 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
Por ejemplo, podemos realizar un backup completo utilizando
$ pg_dump -f /u07/backups/full/havoctec-db-FULL-02.NOV.2010.dump.gz -Z 9 -o -C havoctec
Restaurar 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.dump
Fijaros 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/data
Nos conectamos a PostgreSQL y crearemos la base de datos <havoctec>
$ psql -U postgres
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)
A 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.

$ time pg_dump -C -f /var/backups/havoctec-db-full.dump -U postgres havoctec
Password:

real    0m3.490s
user    0m0.029s
sys     0m0.012s
Ahora, borramos la base de datos
$ psql -U postgres
Password for user postgres:
psql (8.4.1)
Type "help" for help.

postgres=# drop database havoctec;
DROP DATABASE
postgres=# \q
Por último, restauraremos la base de datos
$ psql -U postgres < /var/backups/havoctec-db-full.dump
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
Comprobamos que tenemos los mismos registros, realizando un simple <SELECT>

$ psql -U postgres -d havoctec
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
Automatizar la Copias usando el <cron>
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:password
Si 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
$ 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
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,
$ 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

17 comentarios:

  1. crear un backup de la base de datos completo

    se 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

    ResponderEliminar
  2. Gracias por el articulo, me fue de gran ayuda, seria bueno que siguieras con el backup parcial

    ResponderEliminar
    Respuestas
    1. Hola,

      La verdad es que lo tengo "medio preparado", pero .. espero tenerlo pronto :D

      Muchas Gracias,

      Eliminar
    2. gracias por la informacion, esta interesante tu blog. te felñicito y espero que sigas adelante con este proyecto.

      SUERTE!!!

      Eliminar
    3. Hola,

      Muchas Gracias a ti y ... en ello estamos!

      ;)

      Eliminar
  3. hola... tengo un backup que no tiene extencion como puedo restaurarlo?... espero tu ayuda gracias!

    ResponderEliminar
    Respuestas
    1. Hola Henry,

      Para 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

      Eliminar
  4. Gracias muy bueno, siempre se tienen que realizar los backups con .dump.

    sirvio en 100% de un windows a Linux Ubuntu 12.01 y Centops 6.3

    ResponderEliminar
    Respuestas
    1. Hola Anónimo,

      Muchas Gracias!

      Me alegro de que sirviese, ... espero que la segunda parte también te ayude!

      Eliminar
  5. Buenas tardes,

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

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

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

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

    ResponderEliminar
    Respuestas
    1. Hola Anónimo,

      La 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

      Eliminar
  8. 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?

    De antemano gracias por tu atencion.

    ResponderEliminar
  9. Hola Chery,

    Gracias 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


    ResponderEliminar