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

[Gregory Williamson]

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

Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070702/f459f9e6/attachment.html>