[postgis-users] Problems reloading data during hard upgrade

[Mark Cave-Ayland]

On Fri, 2008-01-04 at 11:44 +0200, Toomas Aas wrote:

> Thanks very much for your advice. As I still have the dumps made from old 
> version before upgrade, I could probably just drop the databases and 
> re-create them, but this would mean losing couple of hours worth of 
> 'production' data. I use the word production in quotes, since there is 
> actually not much production going on, so losing this data is no big deal. 
> However, I wonder whether I could also dump the databases from *new* 
> version, then re-create them and restore tables using pg_restore -L. It 
> seems to me that this should achieve the same goal?


Hi Toomas,

As long as your upgrade to 8.2.5 got as far as creating the geometry
type, you should be able to try what you suggest since the on-disk
format for geometries hasn't changed since 1.1.6. So I would suggest the
following:


- Install PostGIS 1.3.2
- Create a new database called "test", and load
lwpostgis.sql/spatial_ref_sys.sql into the new database
- Confirm this by connecting to "test" and doing "SELECT
postgis_full_version()"
- pg_dump the tables you need from your production database, and
pg_restore -L the tables/sequences you need into "test" (NOTE: don't
forget the geometry_columns table!)
- Swap over the databases


HTH,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk