Big Data/BI Zone is brought to you in partnership with:

I work at Tangent Labs, a digital agency, writing applications in python and django. I spend most of my free time hacking. I run commandlinefu.com and am the author of the e-commerce framework django-oscar amongst other things. I used to be a mathematician; I have a PhD in Mathematics from the University of Nottingham and an associated interest in cryptic crosswords, chess and playing devil's advocate. David is a DZone MVB and is not an employee of DZone and has posted 25 posts at DZone. You can read more from them at their website. View Full User Profile

Dumping and Restoring a PostGIS Database

07.29.2013
| 1170 views |
  • submit to reddit

I wasted at least twenty minutes getting this to work. These are my notes.

Problem

You are using a PostGIS database and want to take a backup copy from production and restore it in a different environment. One complication is that each environment connects to its database with its own user.

This is a common scenario if you are using GeoDjango.

Solution

Suppose your production database is called "myproject_prod" which you connect to with user "myproject_prod_role" and you want to replace your existing stage database "myproject_stage" that you connect to with user "myproject_stage_role".

First, dump your production database:

$ pg_dump --no-acl --no-owner $DATABASE > dump.sql

where:

  • access control is ignored (--no-acl) as your production database may have additional users that you're not interested in;
  • ownership is ignored (--no-owner) as you will be restoring as a different user.

Now, copy the SQL file across to the appropriate server and create the destination database from a PostGIS template with the appropriate owner:

postgres=# DROP DATABASE myproject_stage;
postgres=# CREATE DATABASE myproject_stage TEMPLATE template_postgis OWNER myproject_role_stage;

and restore the database using the stage user:

$ psql --host=127.0.0.1 --username=myproject_role_stage myproject_stage < dump.sql

Be warned: this command will generated a lot of warnings (which can be ignored). This happens as pg_dump generates SQL relative to the 'template0' database (not 'template_postgis' which would be more helpful in this situation). Thus, the dumped SQL file contains the definitions of PostGIS types which have already been defined when we created the database from "template_postgis".

The host is specified in the restore operation so as to trigger the correct authentication rules from pg_hba.conf. I usually forget this and am confused about why I can't authenticate. You not need this is your authentication config is different.

Published at DZone with permission of David Winterbottom, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)