[postgis-devel] utils/new_postgis_restore.pl

[Renzo Kottmann]

Paul,
> If you have a chance to try this out, I'd like to know if you find it  
> an improvement. The operating principles are the same as the old  
> approach: read the manifest of a -Fc dump and exclude things that are  
> part of postgis. The implementation varies, in that I have created my  
> exclusion list "manually" by reading the contents of databases from  
> versions 1.0 to 1.4. It makes the script much much simpler, and  
> hopefully less scary for folks to maintain.

Today, I tried out your script for a
migration from PG 8.4 and Postgis 1.4 to PG 9.0 and Postgis 1.5.
Basically, I figured out several things which I want to discuss:

1. The perl script seems to be fairly complicated
2. related to 1: I could not use it in my case, where I had not only to
perform a hard upgrade but also some other manipulations to the dump
list file (named manifest in your script). The way your script is
written I could only do a hard upgrade or my manipulations, but I wanted
both. And understanding that the upgrade is basically excluding all
postgis objects from the manifest I came to the solution to use grep
with a file containing the exclusion list.

Therefore, I copied your exclusion list (named signatures in the
script)  and changed it to *basic* regular expressions where an entry
per line looks like this:

AGGREGATE \(\w\+\) accum(geometry)

Basically, I put \w+ (escaped for use as basic regular expression) as a
placeholder for the schema name. Another example shows the escapes for
array types:

FUNCTION \(\w\+\) st_geom_accum(geometry\[\], geometry)

The following shell commands do the whole job then:

pg_restore -l olddb.dump > olddb.list
grep -i -v -f postgis_grep_exclusion_patterns olddb.list >
olddb-without-postgis-objects.list
pg_restore -L olddb-without-postgis-objects.list
pg_restore  -d newdb-with-new-postgis-pre-installed -L
olddb-without-postgis-objects.list olddb.dump

Especially now, I am able to also pipe some other manipulations in-between.

During the work on the grep based solution I come across two issues:

1. I had several more postgis related objects in my dump than your
exclusion list covers:

FUNCTION \(\w\+\) box3d_extent_in(cstring)
FUNCTION public box3d_extent_out(box3d_extent)
FUNCTION public _st_asgml(integer, geometry, integer, integer)
FUNCTION public st_asgml(geometry, integer, integer)
FUNCTION public st_asgml
FUNCTION public st_geohash
FUNCTION public combine_bbox(box3d_extent, geometry)
FUNCTION public st_combine_bbox(box3d_extent, geometry)
FUNCTION public st_geometry
FUNCTION public st_box3d_extent
FUNCTION public st_box2d
CAST \(\w\+\) CAST (public.box3d

I do not know why. It could be that I inherited this database from
postgis < 1.0. Would this be an explanation?

2. Even more severe your exclusion list also excludes some postgis
specific operators like e.g.
OPERATOR        &&
or in the grep version
OPERATOR \(\w\+\) &&

Unfortunately, this also deletes operators from the ltree contrib which
I have in my dump. The problem is that in the manifest file the
operators of ltree and postgis are only distinguishable by oid.
Therefore, I included oids of the postgis operator in the pattern:

6836195 OPERATOR \(\w\+\) &&

This is of course unfortunately dump specific. I do not know if it is a
bug or feature of the pg_restore manifest file, but if it contained the
left- and right argument type of each operator, they would be easily
uniquely identifiable without the need to know the oid.

With the fixes to the issues I managed to succesfully perform a hard
upgrade together with some other manipulations I had to do and now I am
a happy postgis 1.5 user looking forward to use 2.0 as soon as possible :)

ciao,

renzo