pgMigrador.sh

#!/bin/bash
separador="****************************************"

bases=`psql -l -A -F" " -t | awk '{ print $1 }' | grep -v -e ^template -e postgres`
destino="192.168.1.10" # ip destino

for base in $bases; do
        STARTTIME=$(date +%s)

        echo "Migrando $base -- `date`"
        echo "Origen"
        psql -c "\l+ $base" | tail -3 | head -1

        #size de la base
        #el postgres me da lo que pesan los datos, pero el dump contiene sintaxis...
        size=$(psql -c "SELECT pg_database_size('$base')" -t | head -1 | sed 's/ //g')

        #compresion gzip
        pg_dump -c -C $base | gzip -1c | pv --size $size | ssh -o 'Compression no' postgres@$destino "gunzip | psql -q" > migracion_$base.log 2>&1

        #sin compresion
        #quiza tarda menos pero transfiere mas
        #pg_dump -c -C $base | pv --size $size | ssh -o 'Compression no' postgres@$destino "psql -q" > migracion_$base.log 2>&1

        #compresion ssh
        #la peor lejos
        #pg_dump -c -C $base | pv --size $size | ssh postgres@$destino "psql -q" > migracion_$base.log 2>&1

        echo "Destino"
        ssh postgres@$destino "psql -c '\l+ $base'" | tail -3 | head -1

        ENDTIME=$(date +%s)
        echo "Transcurrieron $(($ENDTIME - $STARTTIME)) segundos"
done

for base in $bases; do
        STARTTIME=$(date +%s)

        echo "Vacuum analyze en $base@$destino -- `date`"

        #vacuum analyze
        ssh postgres@$destino "vacuumdb --analyze $base" >> migracion_$base.log 2>&1
        ENDTIME=$(date +%s)
done