1

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:

  1. 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;

  2. 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:

  1. Is my DR strategy reasonable? Any other more optimal ways?

  2. How can I get this approach to work: restore my site on a new EC2 instance with DB restored from a .sql dump.

Fisher Coder
  • 3,278
  • 12
  • 49
  • 84

1 Answers1

3

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!

FlipperPA
  • 13,607
  • 4
  • 39
  • 71
  • 1
    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` – Fisher Coder Jun 20 '20 at 17:29
  • 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! :) – FlipperPA Jun 22 '20 at 00:43