[postgis-users] Moving PostGIS tables between different PGSQL+PostGIS versions

[Leonardo Mateo]

>
> I'm on LInux, so any pointers will be much appreciated :)
>
Ok, Great!
Now we have two options to do this.
1) If the tables are not too many, you can create the tables by hand,
turn the database into a PostGIS one on the new server, export only
the data from the old server and import it on the new server. I'll
assume this is not your case because this is the easy case.

First of all, you should know where liblwgeom.so is located in both
servers, the older and the newer, a simple:
[root at localhost]# locate liblwgeom.so
Will help you. If you're moving from one distro to another this may
vary a lot, but if you're moving from one debian / debian-based to
another this will vary just a little.
Let's say you will be moving from a non-debian to a debian-based
distro, you'll see something like this:
/****************************************/
[root at old-server]# locate lliblwgeom.so
/usr/lib/liblwgeom.so
/usr/lib/liblwgeom.so.1.1
/usr/lib/liblwgeom.so.1
/*****************************************/

/*****************************************/
[root at new-server]# locate lliblwgeom.so
/usr/lib/postgresql/8.2/lib/liblwgeom.so
/usr/lib/postgresql/8.2/lib/liblwgeom.so.1
/usr/lib/postgresql/8.2/lib/liblwgeom.so.1.2
/*****************************************/

Now, assume the dump file is in the old server
/*****************************************/
[root at old-server]# sed -i -e
's/\/usr\/lib\/liblwgeom/\/usr\/lib\/postgresql\/8.2\/lib\/liblwgeom/g'
dumpfile.sql

This will replace all entries of
/usr/lib/liblwgeom with /usr/lib/postgresql/8.2/lib/liblwgeom in-place
on dumpfile.sql (that's why -i modifier on sed command)
/*****************************************/

Now we have to repeat this process to replace the version of the shared object.
/*****************************************/
[root at old-server]# sed -i -e 's/liblwgeom.so.1.1/liblwgeom.so.1.2/g'
dumpfile.sql
/*****************************************/

Now, if everything went fine, you're ready to do:
/*****************************************/
[root at old-server]# psql -h new-server -U postgres -d new-database -f
dumpfile.sql >migration.log
/*****************************************/

After this last command you can see the errors sent to the screen and
the successful commands sent to migration.log file.
I cannot find a way to redirect the error output to a file.
If no critical errors are shown, you should be able to enjoy your
brand new PostGIS database.


Hope it helps.
Cheers.
-- 
Leonardo Mateo.
There's no place like ~