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

[Greg 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.)