[postgis-users] PostGIS/Mapserver Mapfile Question

[Stephen Woodbridge]

Don,

Well I did not migrate the whole shebang. I started playing with postGIS 
v0.9 on a freeBSD server, then I loaded the v1.0 on the Windows XP 
laptop and I moved some selected tables using backup/restore (the 
postgis_restore.pl) so this is probably the root of my problem.

In general, it seems like there should be a procedure the allows me to 
copy a table from one server to another even if it has geometry columns 
in it. I guess I need to look at the postgis_restore.pl and backup 
scripts and see what they are doing.

There also seems to be a freeze on the freeBSD ports as non of the newer 
software releases have made it into the ports directory, so I am face 
with another problem of how to back port the database I now have built 
and working in postGIS v1.0 on XP back to the freeBSD postGIS v0.9. This 
will be easier in that I would drop the outdated database there and want 
to migrate the whole thing I have built on XP back to the freeBSD server.

-Steve

Don Drake wrote:
> I just completed an upgrade of Postgres7.4/PostGIS0.9 -->
> Postgres8.0.3/PostGIS1.0.1 this week using a backup and the
> postgis_restore.pl script with no real problems.
> 
> The geometry_columns table (and spatial_ref..) tables were backed up and
> restored just fine for me. 
> 
> Did you backup/restore a single schema maybe?
> 
> -Don
> 
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Stephen
> Woodbridge
> Sent: Tuesday, June 28, 2005 1:03 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] PostGIS/Mapserver Mapfile Question
> 
> Mark,
> 
> Awesome, that worked great.
> 
> So, what is the "correct" procedure if you want to move a table with 
> geometry from database to another. Obviously, backup/restore does not do 
> the "whole" thing.
> 
> -Steve
> 
> Mark Cave-Ayland wrote:
> 
>>>-----Original Message-----
>>>From: postgis-users-bounces at postgis.refractions.net 
>>>[mailto:postgis-users-bounces at postgis.refractions.net] On 
>>>Behalf Of Stephen Woodbridge
>>>Sent: 28 June 2005 18:22
>>>To: PostGIS Users Discussion
>>>Subject: Re: [postgis-users] PostGIS/Mapserver Mapfile Question
>>>
>>>
>>>OK, it looks like there are no entries in the geometry_columns table. 
>>>The zcta table is the only one that currently has a geometry column. 
>>>When I did the backup/restore, I did not save the 
>>>geometry_columns table 
>>>it there and easy way to regenerate this?
>>>
>>>-Steve
>>
>>
>>
>>Hi Steve,
>>
>>Sorry if it wasn't clear; the SQL given before should do that job for you.
>>The corrected version is:
>>
>>BEGIN;
>>CREATE TABLE old_geometry_columns WITH OIDS AS SELECT * FROM
>>geometry_columns;
>>DELETE FROM geometry_columns;
>>SELECT probe_geometry_columns();
>>COMMIT;
>>
>>
>>You can probably delete the old_geometry_columns table once you're done,
>>especially as in your case it was empty to begin with :)
>>
>>
>>Kind regards,
>>
>>Mark.
>>
>>------------------------
>>WebBased Ltd
>>17 Research Way
>>Tamar Science Park
>>Plymouth
>>PL6 8BT 
>>
>>T: +44 (0)1752 797131
>>F: +44 (0)1752 791023
>>W: http://www.webbased.co.uk