k8s-cluster-config/scripts/migrate_postgres.sh

#!/bin/bash
export PGHOST=$OLD_PGHOST
export PGPASSWORD=$OLD_PGPASSWORD
export PGDATABASE=$OLD_PGDATABASE
export PGPORT=$OLD_PGPORT
export PGUSER=$OLD_PGUSER
DUMP_FILE=/tmp/$PGDATABASE.dump
#pg_dump $PGDATABASE --no-owner --no-privileges -Fc -f $DUMP_FILE -vvv
#
#export PGHOST=$NEW_PGHOST
#export PGPASSWORD=$NEW_PGPASSWORD
#export PGDATABASE=$NEW_PGDATABASE
#export PGPORT=$NEW_PGPORT
#export PGUSER=$NEW_PGUSER
#pg_restore --no-owner --no-privileges -d $PGDATABASE -Fc $DUMP_FILE -vvv
#
#psql -c "GRANT ALL PRIVILEGES ON DATABASE \"${PGDATABASE}\" to \"${PGDATABASE}\""
#psql -c "GRANT ALL ON SCHEMA public to \"${PGDATABASE}\""
#psql -c "GRANT ALL ON ALL TABLES IN SCHEMA public TO \"${PGDATABASE}\""

rm -f /tmp/output

psql -c "\
SELECT format(\
  'ALTER TABLE %I.%I.%I OWNER TO %I;',\
  table_catalog,\
  table_schema,\
  table_name,\
  '${PGDATABASE}')\
FROM information_schema.tables \
WHERE table_schema='public'" | grep ALTER > /tmp/output

psql -c "\
SELECT format(\
  'ALTER SEQUENCE %I.%I.%I OWNER TO %I;',\
  sequence_catalog,\
  sequence_schema,\
  sequence_name,\
  '${PGDATABASE}')\
FROM information_schema.sequences \
WHERE sequence_schema='public'" | grep ALTER >> /tmp/output

psql -c "$(cat /tmp/output)"