[postgis-users] Help dumping data from an old database

[Chris Gat]

Hey Everyone

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.

Thanks for your help. I do appreciate it.

Chris

On Tue, Aug 16, 2011 at 10:15 PM, Paragon Corporation <lr at pcorp.us> wrote:

> ** **
>
> Here is a thought.  Move all the tables without geometry columns to a new
> schema and then backup that new schema.****
>
> ** **
>
> So this script for example will generate logic to move all the tables in
> public that do not contain geometry_columns.****
>
> ** **
>
> CREATE SCHEMA nongeomdata;****
>
> SELECT 'ALTER TABLE ' || table_name || ' SET SCHEMA nongeomdata;'****
>
>     FROM information_schema.tables****
>
>         WHERE table_schema = 'public' AND table_name NOT
> IN('geometry_columns', 'spatial_ref_sys')****
>
>             AND table_name NOT IN(SELECT table_name FROM
> information_schema.columns****
>
>                                     WHERE udt_name = 'geometry' AND
> table_schema = 'public');****
>
> ** **
>
> Then simple matter of backing up the new schema****
>
> pg_dump –-schema=nongeomdata -F c -b -v -f "/somepath/somedb.backup" somedb****
>
> ** **
>
> ** **
>
> You would do the reverse the set schema back to public or whatever when you
> restore if you want.****
>
> ** **
>
> Hope that helps,****
>
> Leo and ****Regina********
>
> http://www.postgis.us****
>
> ** **
>   ------------------------------
>
> *From:* postgis-users-bounces at postgis.refractions.net [mailto:
> postgis-users-bounces at postgis.refractions.net] *On Behalf Of *Chris Gat
> *Sent:* Tuesday, August 16, 2011 6:29 PM
> *To:* postgis-users at postgis.refractions.net
> *Subject:* [postgis-users] Help dumping data from an old database****
>
> ** **
>
> Hello,****
>
> ** **
>
> 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. **
> **
>
> ** **
>
> Here is the short version of what I've done so far and the problem I'm
> having:****
>
> build/install postgresql 8.2.3****
>
> 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:****
>
> ** **
>
> ERROR:  could not access file "$libdir/liblwgeom.1.2.so": No such file or
> directory****
>
> 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; ****
>
>  ****
>
> Obviously, after some investigation, I realize I need postgis. So I,****
>
> build/install proj4 4.7****
>
> build/install geos 3.1.1****
>
> when I try to build postgis-1.2.1, I get the build error (after running
> make):****
>
> ** **
>
> lwgeom_geos_c.c: In function `postgis_geos_version':****
>
> lwgeom_geos_c.c:84: warning: implicit declaration of function
> `VARATT_SIZEP'****
>
> lwgeom_geos_c.c:84: error: invalid lvalue in assignment****
>
> lwgeom_geos_c.c: In function `relate_full':****
>
> lwgeom_geos_c.c:2073: error: invalid lvalue in assignment****
>
> lwgeom_geos_c.c: In function `GEOS2LWGEOM':****
>
> lwgeom_geos_c.c:2430: warning: assignment discards qualifiers from pointer
> target type****
>
> lwgeom_geos_c.c:2439: warning: assignment discards qualifiers from pointer
> target type****
>
> lwgeom_geos_c.c:2449: warning: assignment discards qualifiers from pointer
> target type****
>
> lwgeom_geos_c.c:2450: warning: assignment discards qualifiers from pointer
> target type****
>
> lwgeom_geos_c.c:2454: warning: assignment discards qualifiers from pointer
> target type****
>
> lwgeom_geos_c.c:2455: warning: assignment discards qualifiers from pointer
> target type****
>
> lwgeom_geos_c.c:2476: warning: assignment discards qualifiers from pointer
> target type****
>
> lwgeom_geos_c.c: In function `polygonize_garray':****
>
> lwgeom_geos_c.c:2815: warning: passing arg 1 of `GEOSPolygonize' from
> incompatible pointer type****
>
> lwgeom_geos_c.c: In function `LWGEOM_buildarea':****
>
> lwgeom_geos_c.c:2965: warning: passing arg 1 of `GEOSPolygonize' from
> incompatible pointer type****
>
> lwgeom_geos_c.c:3008: warning: assignment discards qualifiers from pointer
> target type****
>
> ** **
>
> I can build/install postgis-1.3 and postgis-1.4, but these versions don't
> create the proper liblwgeom.so file.****
>
> ** **
>
> 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.****
>
> ** **
>
> Some additional information:****
>
> -when configuring postgres, proj4, geos, I used --prefix=/mydirectory/****
>
> -when configuring postgis, I used --prefix=/mydirectory/
> --with-psql=/pathToPgconfig --with-geos=/pathToGeoConfig
> --with-proj=/mydirectory/****
>
> ** **
>
> Also, I use GNU make 3.8****
>
> ** **
>
> Thanks for you help, I appreciate it.****
>
> ** **
>
> Chris****
>
> ** **
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110817/4356f528/attachment.html>