pg_backup.sh

# How to use
# $ cliente_id=521
# $ file="/tmp/teste.sql"
# $ wget -O - https://gist.githubusercontent.com/denoww/0ca4e98fa053a50ebd5ebfa26b7d4cea/raw | bash -s $cliente_id $file


# args
cliente_id=$1 # 521
file=$2 # "/tmp/teste.sql"


# init script

: > $file # limpa arquivo caso já exista

# pg credentials
credentials="-h $DATABASE_HOST -U $DATABASE_USER"
pass=$DATABASE_PASS
psql_exec="psql $credentials -c"


# loop tables
echo "Fazendo backup do banco de dados do cliente $cliente_id"
PGPASSWORD=$pass $psql_exec "SELECT table_name FROM information_schema.tables WHERE table_schema='public'" | while read -a item ; do
  tbl=${item[0]}

  PGPASSWORD=$pass $psql_exec "SELECT attname FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = '$tbl') AND (attname IN ('condominio_id', 'cliente_id'));" | while read -a item_2 ; do
    col=${item_2[0]}
    if [ "$col" == 'condominio_id' ] || [ "$col" == 'cliente_id' ]; then

      # exec backup
      echo
      echo
      echo "=========================================="
      echo "$tbl SCHEMA Backup "
      echo "=========================================="
      PGPASSWORD=$pass pg_dump erp $credentials --table $tbl --schema-only --verbose | sed 's/public.tmp_/public./' >> $file;
      echo
      echo
      echo "=========================================="
      echo "$tbl DATA Backup "
      echo "=========================================="
      PGPASSWORD=$pass $psql_exec "COPY (SELECT * FROM $tbl where $col = $cliente_id) TO STDOUT;"  >> $file
    fi
  done
done

echo
echo
echo "=========================================="
echo "Backup feito em"
echo $file
echo "=========================================="