Over a million developers have joined DZone.

Dumping and Restoring a PostGIS Database

DZone's Guide to

Dumping and Restoring a PostGIS Database

· Big Data Zone
Free Resource

Learn how you can maximize big data in the cloud with Apache Hadoop. Download this eBook now. Brought to you in partnership with Hortonworks.

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


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.


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


  • 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= --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.

Hortonworks DataFlow is an integrated platform that makes data ingestion fast, easy, and secure. Download the white paper now.  Brought to you in partnership with Hortonworks


Published at DZone with permission of David Winterbottom, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}