38

I have a django 1.4 app with a populated postgres 9.1 database in development server locally. After successful deployment, I wanted to move the data from local to online database, so I used:

pg_dump -f dump.sql -Ox database

and then restored on the server with:

psql -1 -f dump.sql database

Now trying to login online to the website admin throws a "permission denied for relation django_session" exception. I've tried to dump the data with/without -Ox switch and all its combinations but without success. I am also dropping the database and recreating it from scratch on the server with the correct owner as set in settings.py.

If I run a normal syndb without a restore then everything works well.

Am I missing something here?

Rabih Kodeih
  • 9,361
  • 11
  • 47
  • 55

3 Answers3

107

It turns out that you should grant explicit ownership of all objects in the database to the owner after restore. The owner is not a superuser. It's not enough to only set the owner at database creation time. The final solution for migration goes like this:

on the client:

pg_dump -f dump.sql -Ox database

on the server:

su postgres    
dropdb database
createdb database -O user
psql database -f dump.sql

and then to set the privileges:

psql database -c "GRANT ALL ON ALL TABLES IN SCHEMA public to user;"
psql database -c "GRANT ALL ON ALL SEQUENCES IN SCHEMA public to user;"
psql database -c "GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to user;"

Note that we could've run the sql command in psql console but this form is easily embeddable in scripts and such.

Rabih Kodeih
  • 9,361
  • 11
  • 47
  • 55
2

Try to do this from postgres user:

sudo su - postgres
pg_dump -f dump.sql -Ox database

Or just pass -U flag:

pg_dump -f dump.sql -Ox database -U postgres
Daniil Ryzhkov
  • 7,416
  • 2
  • 41
  • 58
  • I forgot to mention, my client os is windows 7 and there is no postgres su. I just open a DOS prompt and dump. – Rabih Kodeih Sep 02 '12 at 02:52
  • Nothing changed, still having the same problem. FWIW, I am using a special user on the server (which does not exist on the client) and this is reflected in setting.py, when I recreate the database on the server (prior to the restore), I use something like: createdb database -O special_user and then I restore. – Rabih Kodeih Sep 02 '12 at 03:00
0

Here's how I fixed mine. I saved myself a ton of a headache by simply changing the user to match the current logged in user of the destination server where the import will happen.

In my case, the imported db had a user of x (x was also the username for the machine it was running on), and the destination machine had a username of y, and a postgres user of y too.

Therefore, I simply changed the Database User and Password in my Django settings to match the destination machine's y user details.

Then did this:

$ sudo -u postgres psql psql > GRANT ALL PRIVILEGES DATABASE ON mydb TO y;

Sipping some kool-aid now!

KhoPhi
  • 9,660
  • 17
  • 77
  • 128