Quick postgres note for database transfers

psql -h postgres -U postgres -c "create database db123" ; pg_dump -U jay -d $db -F t | pg_restore -h postgres -d $db -U postgres -C

This is a file-less way to migrate postgres databases over a network.

The whole script looks like this:

pg_dumpall -g -U postgres > globals.sql

# Now get the individual databases
psql -AtU postgres -c "SELECT datname FROM pg_database WHERE NOT datistemplate" > databases

for db in `cat databases`; do
  psql -h postgres -U postgres -c "createdatabase $db"
  pg_dump -Upostgres --format=t $db | psql -h postgres -U postgres $db
done

psql -h postgres -U postgres postgres -f globals.sql

Modified from https://www.commandprompt.com/blog/a_better_backup_with_postgresql_using_pg_dump/