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

[Leonardo Mateo]

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.


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