#!/usr/bin/env python
import os
import time
import pysftp
import sys

#BD Config
username = 'postgres_username'
defaultdb = 'postgres'
port = '5432'

#SFTP Config
sftp_host="remote_host"
sftp_username="remote_user"
sftp_password="pass"

#Folder Config
backupfolder='/var/www/backup_pgsql/'

# Making the backup folder
date = time.strftime('%d%m%Y_%H%M%S')
backupdir=backupfolder + date
if not os.path.exists(backupdir):
	os.mkdir(backupdir)

# Locate all databases
get_db_names="psql -U%s -d%s -p%s --tuples-only -c '\l' | awk -F\| '{ print $1 }' | grep -E -v '(           |           |template0|template1|^$)'" % (username, defaultdb, port)
print "Obteniendo Bases de Datos"
print get_db_names

# Making Backup of all databases
filename_system = "%s/system_%s.sql.gz" % (backupdir, date)
pg_dumpall="pg_dumpall -p%s | gzip -9 -c > %s" % (port, filename_system)
print "Haciendo Backup del Sistema Completo"
print pg_dumpall
os.popen(pg_dumpall)
print "Haciendo el Backup por BD"

# Making Backup for BD
for base in os.popen(get_db_names).readlines():
        base = base.strip()
        fulldir = backupdir + "/" + base
        if not os.path.exists(fulldir):
                os.mkdir(fulldir)
        filename = "%s/%s-%s.sql.gz" % (fulldir, base, date)
        print base
        pg_dump = "nice -n 19 pg_dump -C -F c -U%s -p%s %s | gzip > %s" % (username, port, base, filename)
        os.popen(pg_dump)
        print pg_dump

# Compresing
comprimir = "cd %s && tar -czvf backup_pg_%s.tar.gz *" % (backupdir, date)
print "Comprimiendo Archivos"
print comprimir
os.system(comprimir)

# Sending to SFTP Server
remote_file = "%s/backup_pg_%s.tar.gz" % (backupdir, date)
srv = pysftp.Connection(host=sftp_host, username=sftp_username, password=sftp_password)
print "Enviando al servidor: " + sftp_host
print remote_file
srv.put(remote_file)
srv.close()
print "Listo!"