Cannot flush Postgres DB in Django

Question

I am trying to load the local postgres database with json dumped data(from online db) by using the loaddata command. But it fails with "Integrity Error" as I already had some data in the db with the primary key. Then I tried to flush the database so as to take it to state just after syncdb. But it gives me the following message and fails.

You have requested a flush of the database.
This will IRREVERSIBLY DESTROY all data currently in the 'app' database,
and return each table to the state it was in after syncdb.
Are you sure you want to do this?

    Type 'yes' to continue, or 'no' to cancel: yes
Error: Database app couldn't be flushed. Possible reasons:
  * The database isn't running or isn't configured correctly.
  * At least one of the expected database tables doesn't exist.
  * The SQL was invalid.
Hint: Look at the output of 'django-admin.py sqlflush'. That's the SQL this command wasn't able to run.
The full error: cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "taskapp_taskrequest" references "taskapp_task".
HINT:  Truncate table "taskapp_taskrequest" at the same time, or use TRUNCATE ... CASCADE.

This is the corresponding output for sqlflush command

BEGIN;
TRUNCATE "auth_permission", "auth_group", "tagging_taggeditem", "auth_user_user_permissions", "taskapp_task_reviewers", "taskapp_task", "django_site", "profile_profile", "django_content_type", "profile_notification", "django_session", "auth_user_groups", "taskapp_pyntrequest", "profile_rolerequest", "tagging_tag", "taskapp_textbook_chapters", "registration_registrationprofile", "taskapp_textbook", "django_admin_log", "auth_group_permissions", "taskapp_task_selected_users", "taskapp_taskcomment", "south_migrationhistory", "taskapp_task_claimed_users", "taskapp_taskclaim", "taskapp_workreport", "auth_message", "taskapp_reportcomment", "auth_user";
SELECT setval(pg_get_serial_sequence('"registration_registrationprofile"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"tagging_tag"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"tagging_taggeditem"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"south_migrationhistory"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"auth_permission"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"auth_group"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"auth_user"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"auth_message"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"django_content_type"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"django_site"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"django_admin_log"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"profile_profile"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"profile_notification"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"profile_rolerequest"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"taskapp_task"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"taskapp_taskcomment"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"taskapp_taskclaim"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"taskapp_workreport"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"taskapp_reportcomment"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"taskapp_pyntrequest"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"taskapp_textbook"','id'), 1, false);
COMMIT;

I have a database with multiple models and many foreign key relations among them. From what I read over the internet, I understand that Truncate should used to drop tables which are dependent on. I can't quite figure the exact syntax for using it in the dbshell.

I also accessing the postgresql shell as the sudo user and tried to drop the database using

DROP DATABASE DBNAME

command. But the database persists.

Edit:

Thanks to stevejalim's comment I was able to drop the database and created a new one. I ran syncdb command again and created the db again. But trying to load the db using loaddata command throws the error

IntegrityError: duplicate key value violates unique constraint

Any help on this would be much appreciated.

Answer 1

The reason you could not drop the database is probably that you were connected to it. I have to guess because you are holding back on the actual error message. Connect to the default maintenance database postgres instead (as superuser or owner of the db):

psql postgres

And launch the command from there:

DROP DATABASE dbname;

Check out this related question od dba.SE for a robust way to drop a database that doesn't want to go.

---

Concerning the new error you get: you have a UNIQUE or PRIMARY index or constraint defined on one of your tables but try to insert duplicate values. In modern versions of PostgreSQL you get more information in the error message. Like:

ERROR:  duplicate key value violates unique constraint "tbl_pkey"
DETAIL:  Key (tbl_id)=(10) already exists.

Raises the question: which version of PostgreSQL do you use?
In any case: check your database log file for those details and fix the duplicate values in your source (or remove the UNIQUE constraint).