Hey Everyone<div><br></div><div>Thanks for the replies, and sorry for the late response (I set my filter up wrong so I wasn't seeing the new messages right away). When I saw Charles message I realized I could just drop any geometry type columns from from the relevant tables and I would be able to dump. Basically what Paragon said, but with a bit more labour. So I backup'ed the original data directory, dropped the columns, and was able to dump. Had to fiddle around to a bit to get the restore to work, but I did. </div>
<div><br></div><div>Thanks for your help. I do appreciate it.</div><div><br></div><div>Chris<br><br><div class="gmail_quote">On Tue, Aug 16, 2011 at 10:15 PM, Paragon Corporation <span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<u></u>
<u></u>
<div lang="EN-US" link="blue" vlink="blue">
<div>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy">Here is a thought. Move all the
tables without geometry columns to a new schema and then backup that new
schema.<u></u><u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy"><u></u> <u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy">So this script for example will generate
logic to move all the tables in public that do not contain geometry_columns.<u></u><u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy"><u></u> <u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy">CREATE SCHEMA nongeomdata;<u></u><u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy">SELECT 'ALTER TABLE ' || table_name || '
SET SCHEMA nongeomdata;'<u></u><u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy"> FROM
information_schema.tables<u></u><u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy">
WHERE table_schema = 'public' AND table_name NOT IN('geometry_columns',
'spatial_ref_sys')<u></u><u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy">
AND table_name NOT IN(SELECT table_name FROM information_schema.columns<u></u><u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy">
WHERE udt_name = 'geometry' AND table_schema = 'public');<u></u><u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy"><u></u> <u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy">Then simple matter of backing up the new
schema<u></u><u></u></span></font></p>
<pre><code><font size="2" face="Courier New"><span style="font-size:10.0pt">pg_dump –-schema=nongeomdata -F c -b -v -f "/somepath/somedb.backup" somedb</span></font></code><u></u><u></u></pre>
<p class="MsoNormal" style="margin-left:.25in"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy"><u></u> <u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy"><u></u> <u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy">You would do the reverse the set schema
back to public or whatever when you restore if you want.<u></u><u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy"><u></u> <u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy">Hope that helps,<u></u><u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy">Leo and <u></u><u></u>Regina<u></u><u></u><u></u><u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy"><a href="http://www.postgis.us/" target="_blank">http://www.postgis.us</a><u></u><u></u></span></font></p>
<p class="MsoNormal"><font size="2" color="navy" face="Arial"><span style="font-size:10.0pt;font-family:Arial;color:navy"><u></u> <u></u></span></font></p>
<div style="border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt">
<div>
<div class="MsoNormal" align="center" style="text-align:center"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">
<hr size="2" width="100%" align="center">
</span></font></div>
<p class="MsoNormal"><b><font size="2" face="Tahoma"><span style="font-size:10.0pt;font-family:Tahoma;font-weight:bold">From:</span></font></b><font size="2" face="Tahoma"><span style="font-size:10.0pt;font-family:Tahoma">
<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>
[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>] <b><span style="font-weight:bold">On Behalf Of </span></b>Chris Gat<br>
<b><span style="font-weight:bold">Sent:</span></b> Tuesday, August 16, 2011
6:29 PM<br>
<b><span style="font-weight:bold">To:</span></b>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<b><span style="font-weight:bold">Subject:</span></b> [postgis-users] Help
dumping data from an old database</span></font><u></u><u></u></p>
</div><div><div></div><div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt"><u></u> <u></u></span></font></p>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">Hello,<u></u><u></u></span></font></p>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt"><u></u> <u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">I've recently been tasked with reviving an older database that hasn't
been touch in a while. Both postgres and postgis were used to create this
database. My ultimate goal at this point is to be able to dump the database
from the linux server where it currently resides, to a mac workstation. <u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt"><u></u> <u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">Here is the short version of what I've done so far and the problem I'm
having:<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">build/install postgresql 8.2.3<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">at this point, I can access the database (via psql, SELECT), but if I
try to use pg_dump (pg_dump MYDB > MYDB.sql), I get the error:<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt"><u></u> <u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">ERROR: could not access file "$libdir/<a href="http://liblwgeom.1.2.so" target="_blank">liblwgeom.1.2.so</a>": No such file or
directory<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">STATEMENT: COPY public.boundaries (gid, fnode_, tnode_, lpoly_,
rpoly_, length, world_, world_id, bnd_type, bnd_status, bnd_study, area, len,
the_geom) TO stdout; <u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt"> <u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">Obviously, after some investigation, I realize I need postgis. So I,<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">build/install proj4 4.7<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">build/install geos 3.1.1<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">when I try to build postgis-1.2.1, I get the build error (after running
make):<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt"><u></u> <u></u></span></font></p>
</div>
<div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c: In function `postgis_geos_version':<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c:84: warning: implicit declaration of function
`VARATT_SIZEP'<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c:84: error: invalid lvalue in assignment<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c: In function `relate_full':<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c:2073: error: invalid lvalue in assignment<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c: In function `GEOS2LWGEOM':<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c:2430: warning: assignment discards qualifiers from
pointer target type<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c:2439: warning: assignment discards qualifiers from
pointer target type<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c:2449: warning: assignment discards qualifiers from
pointer target type<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c:2450: warning: assignment discards qualifiers from
pointer target type<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c:2454: warning: assignment discards qualifiers from
pointer target type<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c:2455: warning: assignment discards qualifiers from
pointer target type<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c:2476: warning: assignment discards qualifiers from
pointer target type<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c: In function `polygonize_garray':<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c:2815: warning: passing arg 1 of `GEOSPolygonize' from
incompatible pointer type<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c: In function `LWGEOM_buildarea':<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c:2965: warning: passing arg 1 of `GEOSPolygonize' from
incompatible pointer type<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">lwgeom_geos_c.c:3008: warning: assignment discards qualifiers from
pointer target type<u></u><u></u></span></font></p>
</div>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt"><u></u> <u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">I can build/install postgis-1.3 and postgis-1.4, but these versions
don't create the proper liblwgeom.so file.<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt"><u></u> <u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">Any ideas as to why this is occurring would help greatly. I should also
mention that the most important information in this database has nothing to do
with postgis, therefore, if there is a way to ignore the tables associated with
postgis, and thus avoid the liblwgeom requirement, that would suffice.<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt"><u></u> <u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">Some additional information:<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">-when configuring postgres, proj4, geos, I used --prefix=/mydirectory/<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">-when configuring postgis, I used --prefix=/mydirectory/
--with-psql=/pathToPgconfig --with-geos=/pathToGeoConfig --with-proj=/mydirectory/<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt"><u></u> <u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">Also, I use GNU make 3.8<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt"><u></u> <u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">Thanks for you help, I appreciate it.<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt"><u></u> <u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt">Chris<u></u><u></u></span></font></p>
</div>
<div>
<p class="MsoNormal"><font size="3" face="Times New Roman"><span style="font-size:12.0pt"><u></u> <u></u></span></font></p>
</div>
</div></div></div>
</div>
</div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>