1

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.

APPROACH 1:

I switch user to postgres via 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

APPROACH 2:

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

1

You should create the uauvuro0s8b9v4 user prior to restore the database or the pg_restore will raise such errors (as this user's owns a lot of relations, in your case).

In psql try:

CREATE USER uauvuro0s8b9v4 WITH PASSWORD '12334444';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.