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

[Paragon Corporation]

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 <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

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110817/3ba274c0/attachment.html>