Managing overview on amazon

Getting a database shell

1. overview-manage ssh production database to log in to the database instance
2. sudo -u postgres psql overview

Creating a .psql database backup

This works even while the database is being modified. The backed-up database will have the exact same state as the database had when the backup started.

If you're backing up a database from a remote Postgres server:

1. Find the host, username, database and password of the database to back up.
2. pg_dump -v --no-owner -Fc -f backup.psql --host HOST --user USERNAME DATABASE and enter the password.

But if you're logged into the database instance, it's easier (as long as you're in a directory with the right permissions):

1. sudo -u postgres pg_dump -v --no-owner -Fc -f backup.psql

(The --no-owner is necessary when backing up from Heroku, because the Heroku username, which is gibberish, won't exist on our Amazon AWS server and so the restore will fail.)

Restoring a .psql database backup

1. Run DROP SCHEMA public CASCADE; CREATE SCHEMA public; in a database shell.
2. Run sudo -u postgres pg_restore -v -f BACKUP-FILENAME DATABASE