How to restore Django Postgres on dokku (using dokku-postgres plugin backup)

restore-django-postgres-dokku.md

First download the backup from S3. Should be in the form:

postgres-appname-2018-09-14-07-00-04.tgz

Then, delete your local db (or rename it).

Re-create (empty) db (guide).

Then extract, and cd into backup folder. There, the pg dump called export should reside. Execute:

pg_restore --dbname=your_db_name --no-tablespaces --host=localhost --port=5432 --username=your_pg_admin_username --verbose export

Should work except for these errors, which we do not care:

pg_restore: [archiver (db)] Error while INITIALIZING:
pg_restore: [archiver (db)] could not execute query: ERROR:  unrecognized configuration parameter "idle_in_transaction_session_timeout"
    Command was: SET idle_in_transaction_session_timeout = 0;

pg_restore: [archiver (db)] could not execute query: ERROR:  unrecognized configuration parameter "row_security"

So ignore them.

Then, you need to explicit grant ownership of all objects in the database, because your local user did not create them:

From: https://stackoverflow.com/questions/12233046/django-permission-denied-when-trying-to-access-database-after-restore-migratio

psql your_db_name -c "GRANT ALL ON ALL TABLES IN SCHEMA public to your_db_user;"
psql your_db_name -c "GRANT ALL ON ALL SEQUENCES IN SCHEMA public to your_db_user;"
psql your_db_name -c "GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to your_db_user;"

Finally, run this bash script:

From: https://stackoverflow.com/questions/33145349/must-be-owner-of-relation-django-site-when-manage-py-migrate

export user="your_db_user"
export db="your_db_name"

psql -c "alter database $db owner to $user" $db;

psql -c "alter schema public owner to $user" $db;

tables=`psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" $db`

for tbl in $tables; do
    psql -c "alter table \"$tbl\" owner to $user" $db;
done;

seqs=`psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" $db`

for seq in $seqs; do
    psql -c "alter table \"$seq\" owner to $user" $db ;
done;

views=`psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" $db`

for view in $views; do
    psql -c "alter table \"$view\" owner to $user" $db ;
done;