postgresql/postgis backup strategy to restore after geos/postgis recompile?

Question

Current situation:

• Ubuntu 8.04 server edition (live server)
• Postgresql 8.3.7 (from standard repositories)
• Postgis 1.3.3 (from standard repositories)
• GEOS 2.3.4 (from standard repositories)

Problem:

GEOS contains bugs which are fixed in the 3.0 release. I have encountered these and need to upgrade GEOS/Postgis to include the GEOS fixes.

Where i'm standing now:

On a test machine with nearly identical setup, i removed the postgis-packages and tried to recompile Geos 3.1.0 against Postgis 1.3.5 and Postgresql 8.3.7.

After fixing some linking and path problems this works.

My specific question:

What is the best way to migrate my databases (tables, functions, triggers, gist indexes, data...) from the 'based on older geos/postgis' version to the 'newer' one?

I'll try the 'soft-upgrade' way described in the documentation but do you think this will work?

What's the best way to make a full backup of this postgis-enabled database so i can completely restore it on the 'newer postgis version' i'm compiling?

Answer 1

I'll try the 'soft-upgrade' way described in the documentation but do you think this will work?

I don't know enough about GEOS to say for sure, but it sounds like a good thing to try. From the docs you linked to:

If a soft upgrade is not possible the script will abort and you will be warned about HARD UPGRADE being required, so do not hesitate to try a soft upgrade first.

Otherwise, I'd just follow their "hard upgrade" directions, which appear to be functionally equivalent to the usual pg_dump/pg_restore approach used to upgrade to a new major version of PostgreSQL. There's plenty more information in the Postgres documentation about how to do that; it's a very safe procedure and, as the official migration method, is extremely well supported.

One thing you may wish to consider is upgrading to the PostgreSQL 8.4 beta while you're doing all of this work. It's beta software, true, but that might be acceptable for your environment, and if it is suitable, then you get the new features of 8.4 plus the ability to do a soft upgrade to 8.4 final (as the on-disk formats are not expected to change after the start of beta).