I am trying to restore a postgres data dump for a Django app of mine. The data dump was extracted from Heroku, and pg_restore latest.dump -d mydb -U postgres was run on an Azure VM with Linux on it.
The user on Heroku is called uauv8b9v4. There's no similar user on Azure. So when I ran pg_restore on Azure, I got tons of role does not exist errors. E.g.:
pg_restore: [archiver (db)] Error from TOC entry 241; 1259 44416 TABLE links_grouptraffic uauv8b9v4
pg_restore: [archiver (db)] could not execute query: ERROR: role "uauv8b9v4" does not exist
Command was: ALTER TABLE public.links_grouptraffic OWNER TO uauv8b9v4;
Now accessing my Django app at example.cloudapp.net just shows the following error:
Exception Type: DatabaseError Exception Value:
permission denied for relation links_link
Exception Location: /home/myuser/.virtualenvs/myenv/local/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py in execute, line 54
Note that I got exactly the same permission denied error if I run pg_restore with -Ox; it doesn't help.
Next, I dropped the database, and ran CREATE USER uauv8b9v4 with password 'password';, CREATE DATABASE mydb; and GRANT ALL PRIVILEGES ON DATABASE mydb to uauv8b9v4;. I thought now if I ran pg_restore latest.dump -d MYDB -U uauv8b9v4, my problem will be solved.
Instead I got 3 errors:
1) Must be owner of extension plpgsql:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2584; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
2) Permission denied to create extension "pg_stat_statements":
pg_restore: [archiver (db)] Error from TOC entry 249; 3079 16622 EXTENSION pg_stat_statements
pg_restore: [archiver (db)] could not execute query: ERROR: permission denied to create extension "pg_stat_statements"
HINT: Must be superuser to create this extension.
Command was: CREATE EXTENSION IF NOT EXISTS pg_stat_statements WITH SCHEMA public;
3) Extension "pg_stat_statements" does not exist:
pg_restore: [archiver (db)] Error from TOC entry 2585; 0 0 COMMENT EXTENSION pg_stat_statements
pg_restore: [archiver (db)] could not execute query: ERROR: extension "pg_stat_statements" does not exist
Command was: COMMENT ON EXTENSION pg_stat_statements IS 'track execution statistics of all SQL statements executed';
Trying to access my website still gives me the original permission denied error.
What do I do to fix this situation? Secondly, I've also begun wondering whether I need to run syncdb in this process at some stage? I tried going through this, but it hasn't solved my problem. I feel I'm missing something fundamental.
