Draft blog article on restoring a PostGIS database.

postgis-restore.rst

Problem

You have a Django site using GeoDjango with Postgres and PostGIS and want to take a backup copy of your production database and restore it on a different server with a different owner.

Solution

Suppose your production database is called "myproject_prod" and you want to replace your existing stage database "myproject_stage"

Dump your production database, ignoring access control and object ownership:

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

Copy the SQL file across to the appriate server and create the new database:

$ sudo -u postgres psql
postgres=# drop database myproject_stage;
postgres=# create database myproject_stage template template_postgis owner myproject_role_stage;

and load the dump in as the stage user:

$ sudo -u postgres psql -h 127.0.0.1 --username=mmyproject_role_stage myproject_stage < dump.sql

Since the initial dump contains all the definitions of PostGIS types, this load will raise a load of warnings which should be ignored.

Related:

• http://postgis.17.x6.nabble.com/Backup-and-Restore-of-a-PostGis-database-td3565498.html
• http://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql