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

[Leonardo Mateo]

On 7/2/07, Greg Williamson <Gregory.Williamson at digitalglobe.com> wrote:
> Leonardo Mateo wrote:
> > On 7/2/07, Brent Wood <pcreso at pcreso.com> wrote:
> >>
> >> --- Jessica Richard <rjessil at yahoo.com> wrote:
> >>
> >> > I need to load a postgis enabled postgres database from on machine to
> >> > another, I got a long list of errors. Some of them are pointing to the
> >> > postgis path on the first machine, while on the second machine, the
> >> postgis
> >> > was installed in different location.  Thanks.
> >>
> >> I've done this in a single step, using the psql client on one host to
> >> access
> >> the Postgres server on the other, along the lines of:
> >>
> >> pgdump --create db1 | psql -h <host2>
> >>
> >> Does Postgis work correctly, and are both systems running the same
> >> (or at least
> >> a compatible) version?
> >
> > 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 ...
Totally worth, it was my mistake, I forgot to mention that. Thanx for
the correction.


-- 
Leonardo Mateo.
There's no place like ~