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?
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With