Crear Backup Automatico con Postgres en Windows

Actualizado [15-04-2016]

A continucación voy a explicar como pude hacer un backup en Postgres y ademas decirle a windows que lo haga periodicamente(via Programador de Tareas).

Primero que todo decir que lo siguiente es la recopilación y lectura de muchos post en español e ingles, igualmente me apoye en la documentación de Postgres 9.5. Algunos métodos no me funcionaron, otros a medias hasta que un poquito de uno y un poco de otro dio resultado acertado. Los ejercicios que haré a continuación se han ejecutado en Windows 7 64 y 32 bits(sirviendo como servidores para PyMes), Windows Server 2008 R2, Windows 10 en modo desarrollo y CentOs 5 y 6.

Comencemos:

Lo primero que hay que tener en cuenta es que para hacer backups en Postgres se utiliza la utilidad pg_dump. Esta utilidad tiene muchas configuraciones para realizar el backup correcpondiente de una base de datos. La siguiente instruccion es la que utilizaremos para realizar el backup, ya que es la que por medio de pg_admin III me ha servido en su totalidad sin generar mas que errores evidentes pero que no tienen nada que ver con los datos o estructura de la base de datos.

pg_dump -i -h <server> -p <puerto> -U <usuario> -F c -b -v -f <nombre_fichero> <bd> 

Tambien hay otra forma que me ha servido y es mas purista en cuanto a la documentacion de Postgres. Las dos me han servido en los sistemas operativos mencionados anteriormente.

pg_dump -h host/server -p puerto -U usuario base > nombre_fichero

Como podemos ver en la instruccion, el signo mayor que(>) nos sirve para decirle donde vamos a guardar el backup.

Para utilizar esta intruccion como debe ser, el directorio bin/ de Postgres tiene que estar en las variables de entorno de entorno de Windows, sino lo hiciéramos, se podría incluir la dirección completa de la ubicación, pero en sistemas de 64 bits molesta bastante debido a los espacios del nombre de las carpetas. Por lo tanto agregaremos al path de Windows, el directorio de postgres.

Para agregar el directorio vamos al siguiente link.

Como ya podemos utilizar pg_dump en el sistema via cmd. Entonces ahora vamos a crear un archivo con extension .bat que se ejecutara y creara nuestro backup. Abrimos nuestro editor preferido e ingresaremos el siguiente codigo.

Forma 1:

@echo off
for /f "tokens=1-3 delims=/ " %%i in ("%date%") do (
    set day=%%i
  set month=%%j
  set year=%%k
 )
 
set datestr=%day%_%month%_%year%
echo datestr is %datestr%
set BACKUP_FILE=G:/mibase_%datestr%.backup
SET PGPASSWORD=14785
echo on
pg_dump -i -h host -p puerto -U usuario -F c -b -v -f %BACKUP_FILE% base
SET PGPASSWORD=

Forma 2:

@echo off
for /f "tokens=1-3 delims=/ " %%i in ("%date%") do (
    set day=%%i
  set month=%%j
  set year=%%k
 )
set datestr=%day%_%month%_%year%
echo datestr is %datestr%
set BACKUP_FILE=G:/mibase_%datestr%.backup
SET PGPASSWORD=14785
echo on
pg_dump -h host -p puerto -U usuario -F c base > %BACKUP_FILE% 
SET PGPASSWORD=

El código anterior es código Batch, si alguien tiene una mejor manera de decirlo, lo agradeceré enormemente. A continuación explico el código.

Línea 1: Cancelamos el echo para que no haya volcado de datos.
Línea 2-6: Creamos un ciclo for /f para asignar en las variables day, month y year la fecha actual vía %date%
Línea 8-9: Creamos una variable datestr en la cual asignaremos la fecha en formato dd_mm_aaaa y estará lista para usarla después
Línea 10: creamos la variable BACKUP_FILE y en esta ingresaremos el lugar (ruta) donde guardaremos nuestro backup. Como pueden ver yo estoy guardando el backup en la raíz de D: con el nombre mibase, guion bajo y %datestr% que es la fecha, por último la extensión .backup.
Línea 11: Set sirve en Batch para mostrar, asignar o eliminar variables de entorno mientras esté abierto la sesión cmd que lo llamo, es decir mientras termina nuestro backup, por lo tanto en esta línea estamos asignando a PGPASSWORD la contraseña del usuario de postres que va a hacer el backup. 
Línea 12: le decimos al echo que puede comenzar desde aquí el volcado de datos, vía instrucción siguiente.
Línea 13: Ejecuta la utilidad pg_dump, los parámetros que deben cambiar son 5432(en caso de que hayan configurado un puerto diferente a postres), usuario(es el usuario que tiene como contraseña la línea 11) y base (nombre de la pase de datos en postres que vamos a hacer el backup). Hay que tener en cuenta que la opcion -F c, significa el formato que utilizara pg_dump para el documento de salida. La c es custom y comprime los datos por defecto. Si le quitamos esto, el documento nos saldra en formato texto plano, es decir SQL puro.
Línea 14: Reasignamos la contraseña del usuario que creo el backup, por seguridad.

Guardamos este script en un lugar que se pueda recordar facilmente y si queremos podemos ejecutarlo via cmd de la siguiente manera para que puedan ver que realmente ya hace el backup.

Ingresamos a cmd y buscamos el archivo. Mi archivo lo guarde en raiz de C, entocnes lo llamo asi:

C:/archivo.bat

De inmediato el archivo se ejecutara y al final nos devolvera a la consola para una nueva intruccion, para verficar que si tenemos bien el script, vamos a la carpeta o ruta donde guardamos el backup y veremos como creo el archivo, en mi caso, D:/mibase_15-07-2014.backup

Con esto ya podemos crear nuestros backups de nuestra base de datos. Para hacerlo automaticamente, es decir que el PC lo haga en una hora determinada cada dia, mensual, semanal debemos de agregar una tarea al programador de tareas de windows y ejecutar este archivo. Para ver este manual da click aqui.

El archivo .backup significa un archivo comprimido diferente a los .sql. Los .sql son archivos planos puros que suelen pesar mucho mas que los comprimidos.

Conclusiones:

Las dos instrucciones funcionan correctamente, lo unico que se cambio es practicamente que el signo mayor que(>) tiene la funcion similar de la opcion -f. 

Como les dije en un momento, esto esta probado, esta en funcionamiento. Si tienen problemas comenten, sino tienen problemas, tambien comenten. Si hay un error en el manual, comenten.