[postgis-users] How to load postgis enabled database from one machine to another?

[Brent Wood]

--- Greg Williamson <Gregory.Williamson at digitalglobe.com> wrote:

Sort of on this topic...

If you are regularly building PostGIS databases, creating a template db with
PostGIS installed simplifies things. Then use this template whenever you create
a new database to have all you need already done.

I keep meaning to write a script to create such a template, so any new install
just needs to have this run to set up a new system. Perhaps something like this
could go in postgis/contribs (if I ever get around to it :-)


Cheers,

  Brent Wood



<snip>

> > This will work if and only if the same postgis version is installed on
> > both machines and postgis is installed in the same location.
> > What I've done on cases like Jessica's is:
> > Case 1
> > 1) pg_dump database > database.source.sql (on source pc)
> > 2) locate liblwgeom.so and find out the path on source pc 
> > (/path/to/source/)
> > 3) locate liblwgeom.so and find out the path on target pc 
> > (/path/to/target/)
> > 4) sed -e 's/\/path\/to\/source\/\/path\/to\/target/g' > 
> > database.dest.sql
> > 5) Load the resultant sql file on the target pc
> >
> > This will dump source database and the sed command will replace the
> > path to liblwgeom.so of the source pc with the one in the target pc.
> >
> > Case 2 (my preferred after a few tries)
> > 1) pg_dump database > database.source.sql (on source pc)
> > 2) Edit database.source.sql and delete all code related to creation of
> > types and  types and everything about postgis (All is in the first
> > part of the file).
> > 3) create the target database
> > 4) createlang -U postgres -d targetdatabase plpgsql
> > 5) locate liblwgeom.sql and spatial_ref_sys.sql (this files are
> > distributed with postgis)
> > 6) run liblwgeom.sql and spatial_ref_sys.sql into the targetdatabase
> > 7) run database.source.sql into the target database
> >
> > The second case may look more complicated, but is cleaner and more
> > elegant to my point of view, because it creates a PostGIS enabled
> > database according to the PostGIS installation on that PC. Besides,
> > once you did it once, you can automate the createlang and run of 2
> > PostGIS sql files by scripting.
> > The second method will save you from a few headaches.
> >
> > Hope it helps.
> >
> >
> I've opted for the 2nd method for some time now ... one thing to be 
> careful of is that a dump will include the spatial_ref_sys table from 
> the old instance; if you have customized entries you'll need to make 
> sure they get ported to the new db; if not you should not copy this 
> table over. If you are moving from different enough versions of postGIS 
> the geometry_columns table layout may also change which necessitates 
> some hand crafting for the load.
> 
> My $0.02 worth ...
> 
> Greg Williamson
> Senior DBA
> GlobeXplorer LLC, a DigitalGlobe company