Database administration

Current production database on Heroku

The credentials are
Database: d1hlu2cr9ta2p3
User: ud6dfgef6bi48k
Password can be found on the database credentials page on Heroku

New production database on AWS

The credentials are
Database: cobudget_prod
User: cobudget
Password is currently only known by Michael and Juliana

Moving a database to a new database instance

  • Use pg_dump to dump the exsting database into a file
  • Edit the user name to fit the new database to be used
  • use psql to load the data into the new database

Example: Dumping the heroku database and loading it on AWS

First dump the Heroku database
pg_dump -h -U ud6dfgef6bi48k -d d1hlu2cr9ta2p3 >db_dump
Edit the user name from "ud6dfgef6bi48k" to "cobudget"
sed -e 's/Owner: ud6dfgef6bi48k/Owner: cobudget/' -e 's/OWNER TO ud6dfgef6bi48k/OWNER TO cobudget/' db_dump >db_loadable
Create the new database
createdb -h -U cobudget cobudget_prod
Load the new database with the previously dumped data
psql -h -U cobudget -d cobudget_prod <db_loadable
The load should be executed with no errors.
To look around in the database, use psql
psql -h -U cobudget -d cobudget_prod
In case something goes wrong, you can always delete the database
dropdb -h -U cobudget cobudget_prod
and restart with creating the new database.