Django + PostgreSQL backup and restore on an EC2 instance

Question

I have a simple monolithic architecture:

A Django project hosted on an EC2 instance, talks to a PostgreSQL DB running on the same instance. I chose this architecture considering the traffic and cost. So, don't bash me on this one. :)

For disaster recovery, I regularly dump my DB (a full dump pg_dump -U postgres fishercoder_db > fishercoder_dump.sql).

At restoring, I cannot get Django and the restoring DB to talk nicely with each other:

If I launch Django and run ./manage.py migrate first, and then restore the DB from the dump, it fails because Django has already created a bunch of internal tables after running ./manage.py migrate which have exactly the same name of in my dump;
If I restore the DB from the dump first, then my Django app cannot stand up because of insufficientprivilege to run ./manage.py migrate, details asked here.

My question is:

Is my DR strategy reasonable? Any other more optimal ways?
How can I get this approach to work: restore my site on a new EC2 instance with DB restored from a .sql dump.

FlipperPA · Accepted Answer · 2020-06-20 01:49:29Z

4

I wrote my own set of utils for exactly this, as I could never fully remember the command line options necessary: https://github.com/FlipperPA/django-pg-copy

Have you tried pg_restore with these options?

pg_restore -c --if-exists -h localhost -U [your_django_user] -d [your_django_db]  fishercoder_dump.sql

You may also want to consider some additional parameters for your dump:

pg_dump -Fc -c -x ...

-Fc: custom format -c: (or --clean): dumps a clean version, with DROPs of objects. -x: skip dumping access privileges

Good luck!

answered Jun 20, 2020 at 1:49

FlipperPA

14.4k5 gold badges46 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fisher Coder Over a year ago

Amazing! Thanks for your incredible work! I was able to dump this way and pg_restore as well, but I cannot run pg_restore using my fishercoderuser, instead, I had to restore using postgres user, but after that, when I use fishercoderuser to connect to my DB via a sql client, I cannot query my tables, it says ERROR: permission denied for relation my_table

FlipperPA Over a year ago

After you restore using using postgres, you should be able to change the owner on the restored database. Logged in as postgres, ALTER DATABASE fishercoder_db OWNER TO fishercoderuser; Give that a whirl! :)

Collectives™ on Stack Overflow

Django + PostgreSQL backup and restore on an EC2 instance

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related