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 is being run on an Azure VM with Linux on it. There are around 40 tables and the total size doesn't exceed 2GB. I've tried two approaches; both have failed. Can an expert point out what might be the problem here? Note that the postgres data dump is called
latest.dump and resides at
/home/myuser/ in my Linux VM.
I switch user to
sudo su postgres and then go into
psql. There I run
CREATE DATABASE mydatabase;. Next I quit
psql, and run the following command from user postgres:
pg_restore latest.dump -d mydatabase -U postgres. The process is run, but in the end I get:
WARNING: errors ignored on restore: 75
Virtually all the errors I got were of the 'role does not exist' type:
pg_restore: [archiver (db)] Error from TOC entry 241; 1259 44416 TABLE links_grouptraffic uauvuro0s8b9v4 pg_restore: [archiver (db)] could not execute query: ERROR: role "uauvuro0s8b9v4" does not exist Command was: ALTER TABLE public.links_grouptraffic OWNER TO uauvuro0s8b9v4;
Note that 'uauvuro0s8b9v4' is the user on heroku; I haven't created such a user on Azure. When I run my Django app at example.cloudapp.net, and I see a permission denied error. Full body is something like:
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
This time, I again create a fresh database via
CREATE DATABASE mydatabase; in
psql. Then I come out from psql, and run
python manage.py syncdb (notice I didn't do that in APPROACH 1). A bunch of tables that are created as a result. I select
yes to 'would I like to create a superuser'. I give the necessary details and it's created for me. Next, I quickly run
python manage.py migrate djcelery and
python manage.py migrate user_sessions to migrate two external packages. Thus my table structure is complete.
I then proceed to run
pg_restore latest.dump -d damadam -U postgres again. This time the command ends with
WARNING: errors ignored on restore: 333.
If I go to example.cloudapp.net to test my app, I get no error, but no data was restored either (whatsoever). Following is a sampling of errors which are seen while
pg_restore is running:
1) Relation already exists:
pg_restore: [archiver (db)] Error from TOC entry 242; 1259 44432 SEQUENCE links_groupinvite_id_seq uauvuro0s8b9v4 pg_restore: [archiver (db)] could not execute query: ERROR: relation "links_groupinvite_id_seq" already exists Command was: CREATE SEQUENCE links_groupinvite_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
2) Foreign key constraint violated:
pg_restore: [archiver (db)] Error from TOC entry 2572; 0 44416 TABLE DATA links_grouptraffic uauvuro0s8b9v4 pg_restore: [archiver (db)] COPY failed for table "links_grouptraffic": ERROR: insert or update on table "links_grouptraffic" violates foreign key constraint "links_grouptraffic_visitor_id_fkey"
3) Relation already exists:
pg_restore: [archiver (db)] Error from TOC entry 2273; 1259 16773 INDEX links_link_submitter_id uauvuro0s8b9v4 pg_restore: [archiver (db)] could not execute query: ERROR: relation "links_link_submitter_id" already exists Command was: CREATE INDEX links_link_submitter_id ON links_link USING btree (submitter_id);
4) Constraint for relation already exists:
pg_restore: [archiver (db)] Error from TOC entry 2372; 2606 16881 FK CONSTRAINT links_userprofile_user_id_fkey uauvuro0s8b9v4 pg_restore: [archiver (db)] could not execute query: ERROR: constraint "links_userprofile_user_id_fkey" for relation "links_userprofile" already exists Command was: ALTER TABLE ONLY links_userprofile ADD CONSTRAINT links_userprofile_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth_u...
Can an expert point out what I'm doing wrong, and what's the right thing to do here?
Note: please ask for more information in case you need it