Bring the production postgres db back to your dev machine (using some Django niceties)

In a previous post I gave you some ignorant and possibly harmful information (that could be vaguely useful in some cases). Let's look at a better way you can bring the production database home to your dev machine. Let's say that you have a file resulting from a simple pg_dump. The database was created by a postgres superuser, let's call this user, superfoo. The user actually 'owning' the database will be an unprivileged user, call this user unpriv.

You have a basic postgres set-up with a superuser or two on your home machine. Let's connect to the server as one of these superusers and create a new superuser.

$ createuser superfoo

Let's give this user a password. Open the psql terminal with a command like psql template1, then run:

ALTER USER superfoo WITH PASSWORD 'supersecret';

Give this user permission to access everything with this password by editing your pg_hba.conf. Here is a relevant slice of mine:

local   all         postgres                          ident sameuser
local   all         myuser                            ident sameuser
local   all         superfoo                             md5
local   geopinax    unpriv                               md5

Now you can create the database, connecting with the new superfoo user by password:

$ createdb -O unpriv -T template_postgis -U superfoo -W geopinax

Provided that your settings are ready to connect to the db, geopinax, with the user, unpriv, you should be able to run:

$ ./manage.py dbshell < out.sql

Where out.sql is the output of pg_dump from the production machine's database that was created with the same owner (-O), template (-T), connector (-U) and name (geopinax) (-W forces password).