Re: [postgis-users] Help dumping data from an old database
maybe you could first reduce the problem create a view of all fields of your table except the geom columns try to pg_dump it, this don't need postgis so could go create a second view of the geom columns specifying AsText(geom) and a field reference copy this view to a text file with \g option join together all ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Help dumping data from an old database
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 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-boun...@postgis.refractions.net [mailto:
> postgis-users-boun...@postgis.refractions.net] *On Behalf Of *Chris Gat
> *Sent:* Tuesday, August 16, 2011 6:29 PM
> *To:* postgis-users@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
&
Re: [postgis-users] Help dumping data from an old database
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-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Chris
Gat
Sent: Tuesday, August 16, 2011 6:29 PM
To: postgis-users@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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Help dumping data from an old database
Chris, This is probably a bad suggestion, but iff you are desparate and iff you can build a more recent postgis, then to dump it out you may find that creating a simlink from a more recent liblwgeom.so with the name of the missing one might work. the COPY command won't actually take anything other than the SQL, so shouldn't affect the importing. cheers Ben On 17/08/2011, at 6:28 AM, Chris Gat wrote: > 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@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Help dumping data from an old database
Chris, just use the postgres copy command to copy out the data you need, and then copy into your new DB then. It can take an arbitrary sql expression so if needed do a query that grabs the columns you care about and ignores the location ones. hth charles On Aug 16, 2011, at 6:28 PM, Chris Gat wrote: > 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@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[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@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
