#!/bin/bash -l

# Open SSH tunnel to remote database
# Make sure, that passwordless authentication is enabled
ssh -C -N -L 15432:livedb2:5432 username@live.site.com &
sleep 10s

# Set source PostgreSQL connection parameters
# Make sure that PostgreSQL tools (psql, pg_dump) can connect with these parameters without password
# if necessary use ~/.pgpass file
export PGDATABASE=db_live
export PGHOST=localhost
export PGPORT=15432
export PGUSER=exportuser

# Generate temporary filename
SCHEMAFILE=`mktemp`

# Dump the data
pg_dump -t srv.stat_export -t disp.trip --file=$SCHEMAFILE -O --no-acl
RESULT=$?

# Kill SSH tunnel
kill %+

# If export was successfull, then start importing
if [ $RESULT -eq 0 ]; then
  # Replace source schema name with destination schema name
  sed -i '/^SET search_path = /s/disp,\|srv,/etl,/g' $SCHEMAFILE

  # Set destination PostgreSQL connection parameters
  # Make sure that PostgreSQL tools (psql, pg_dump) can connect with these parameters without password
  export PGDATABASE=db_dev
  export PGHOST=localhost
  export PGPORT=5432
  export PGUSER=postgres

  # Drop old tables
  echo 'drop table etl.stat_export; drop table etl.trip; ' | psql -q
  # Load new tables
  psql -q < $SCHEMAFILE
  # Fix access privileges
  echo 'alter table etl.stat_export owner to etl; alter table etl.trip owner to etl;' | psql -q
fi

# Remove temporary file
rm $SCHEMAFILE