[postgis-users] Postgis upgrade

[Mark Cave-Ayland]

Ivan Mincik wrote:

> Also for me, it is more comfortable to do dump/restore upgrade. I have
> also some experience with problematic "hard" upgrades.
> Mark, which pg_dump/pg_restore options do You for dumping tables. Can
> You explain little bit "You way" ?
> How do You extract commands which create old version postgis fuctions ?
> Do You extract tables with "-t" options, so the resulting SQL is
> containing only  CREATE TABLE and INSERT commands?
> 
> Ivan

Sure. Depending upon the amount of data/dump format I normally either:

1) pg_dump -t individual tables/sequences into individual files

or

2) Use pg_restore -L/-l options to remove all PostGIS functions from a 
compressed format dump catalogue, then restore the remaining data into a 
new database.

On my new machine I then create a brand new PostGIS database and then 
simply restore any backup files into it. This guarantees that the SQL 
definitions and the PostGIS shared library are always in sync.


HTH,

Mark.

-- 
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063