Load census tables from Prod S3 to production database

[rocheller123]

09/12/24 - Load census historical tables to Prod - (Sudha, Matt, Rochelle)

# To load census tables from Prod S3 into Prod DB
# Note:  The census_historical tables mentioned here are currently empty in Prod


# cf login -a https://api.fr.cloud.gov/ --sso
# Select Production

cf t -s production

cf ssh gsa-fac
/tmp/lifecycle/shell
source tools/setup_env.sh
setup_env

set +e
aws --help

echo $https_proxy


curl -x $https_proxy -L "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
unzip awscliv2.zip && rm awscliv2.zip
./aws/install -i ~/usr -b ~/bin

export PATH=/home/vcap/deps/0/apt/usr/lib/postgresql/15/bin:$PATH
export PATH=/home/vcap/app/bin:$PATH

export db_name=$(echo $VCAP_SERVICES | jq -r '.["aws-rds"][] | select(.name=="fac-db") | .credentials.name')
export username=$(echo $VCAP_SERVICES | jq -r '.["aws-rds"][] | select(.name=="fac-db") | .credentials.username')
export host=$(echo $VCAP_SERVICES | jq -r '.["aws-rds"][] | select(.name=="fac-db") | .credentials.host')
export port=$(echo $VCAP_SERVICES | jq -r '.["aws-rds"][] | select(.name=="fac-db") | .credentials.port')
export password=$(echo $VCAP_SERVICES | jq -r '.["aws-rds"][] | select(.name=="fac-db") | .credentials.password')
export PGPASSWORD=$password
export S3CREDS="$(echo $VCAP_SERVICES|jq -r '.s3')"
export FACPRIVATE="$(echo $S3CREDS|jq '.[]|select(.name=="fac-private-s3")'|jq '.credentials')"
export AWS_ACCESS_KEY_ID="$(echo "$FACPRIVATE"|jq -r '.access_key_id')"
export AWS_SECRET_ACCESS_KEY="$(echo "$FACPRIVATE"|jq -r '.secret_access_key')"
export BUCKET_NAME="$(echo "$FACPRIVATE"|jq -r '.bucket')"
export AWS_DEFAULT_REGION='us-gov-west-1'

echo $https_proxy
unset https_proxy
echo $https_proxy

aws s3 ls s3://${BUCKET_NAME}/census-historical/


psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_elecueis" 
aws s3 cp s3://${BUCKET_NAME}/census-historical/census_historical_migration_elecueis.pgsql - | psql  -h $host -p $port -U $username -d $db_name
psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_elecueis" 


psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_eleccpas"
aws s3 cp s3://${BUCKET_NAME}/census-historical/census_historical_migration_eleccpas.pgsql - | psql  -h $host -p $port -U $username -d $db_name
psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_eleccpas" 


psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_eleceins" 
aws s3 cp s3://${BUCKET_NAME}/census-historical/census_historical_migration_eleceins.pgsql - | psql  -h $host -p $port -U $username -d $db_name
psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_eleceins" 


psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_elecauditfindings"
aws s3 cp s3://${BUCKET_NAME}/census-historical/census_historical_migration_elecauditfindings.pgsql - | psql  -h $host -p $port -U $username -d $db_name
psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_elecauditfindings" 


psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_eleccaptext" 
aws s3 cp s3://${BUCKET_NAME}/census-historical/census_historical_migration_eleccaptext.pgsql - | psql  -h $host -p $port -U $username -d $db_name
psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_eleccaptext" 


psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_elecfindingstext"
aws s3 cp s3://${BUCKET_NAME}/census-historical/census_historical_migration_elecfindingstext.pgsql - | psql  -h $host -p $port -U $username -d $db_name
psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_elecfindingstext" 


psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_elecnotes" 
aws s3 cp s3://${BUCKET_NAME}/census-historical/census_historical_migration_elecnotes.pgsql - | psql  -h $host -p $port -U $username -d $db_name
psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_elecnotes" 


psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_elecpassthrough"
aws s3 cp s3://${BUCKET_NAME}/census-historical/census_historical_migration_elecpassthrough.pgsql - | psql  -h $host -p $port -U $username -d $db_name
psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_elecpassthrough" 


psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_elecauditheader"
aws s3 cp s3://${BUCKET_NAME}/census-historical/census_historical_migration_elecauditheader.pgsql - | psql  -h $host -p $port -U $username -d $db_name
psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_elecauditheader" 


psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_elecaudits" 
aws s3 cp s3://${BUCKET_NAME}/census-historical/census_historical_migration_elecaudits.pgsql - | psql  -h $host -p $port -U $username -d $db_name
psql  -h $host -p $port -U $username -d $db_name -c "SELECT count(*) FROM public.census_historical_migration_elecaudits" 


psql  -h $host -p $port -U $username -d $db_name -c "ANALYZE public.census_historical_migration_elecueis"
psql  -h $host -p $port -U $username -d $db_name -c "ANALYZE public.census_historical_migration_eleccpas"
psql  -h $host -p $port -U $username -d $db_name -c "ANALYZE public.census_historical_migration_eleceins"
psql  -h $host -p $port -U $username -d $db_name -c "ANALYZE public.census_historical_migration_elecauditfindings"
psql  -h $host -p $port -U $username -d $db_name -c "ANALYZE public.census_historical_migration_eleccaptext"
psql  -h $host -p $port -U $username -d $db_name -c "ANALYZE public.census_historical_migration_elecfindingstext"
psql  -h $host -p $port -U $username -d $db_name -c "ANALYZE public.census_historical_migration_elecnotes"
psql  -h $host -p $port -U $username -d $db_name -c "ANALYZE public.census_historical_migration_elecpassthrough"
psql  -h $host -p $port -U $username -d $db_name -c "ANALYZE public.census_historical_migration_elecauditheader"
psql  -h $host -p $port -U $username -d $db_name -c "ANALYZE public.census_historical_migration_elecaudits"
psql  -h $host -p $port -U $username -d $db_name -c "ANALYZE public.census_historical_migration_reportmigrationstatus"

ls

exit
exit

[gsa-suk]