On Sun, Jul 23, 2006 at 11:22:18AM -0700, Claire McLister wrote:
> We have a database with Postgis extensions installed. When we do a
> full dump of the database (using pg_dump -Ft), somehow the "geometry"
> type does not get dumped, so the restore fails.
What are the exact dump and restore commands you're running? Do
you get any errors? Have you verified that the geometry type
exists with a command like "\dT geometry" in psql or a query
like "SELECT * FROM pg_type WHERE typname = 'geometry'"?
> If I try to load the geometry type before restoring, then I have to
> createlang 'plpgsql' because postgis needs it. Then again the dump
> fails as it tries to createlang plpgsql again.
>
> Would appreciate any pointers. This is with Postgresql 7.4.8 and
> Postgis 1.1.2
What platform? I couldn't reproduce this problem with PostgreSQL
7.4.13 and PostGIS 1.1.4CVS on Solaris 9. Here's what I did (PGUSER
is "postgres"):
createdb test
createlang plpgsql test
psql -d test -q -f lwpostgis.sql
psql -d test -q -f spatial_ref_sys.sql
pg_dump -Ft -f test.dump test
dropdb test
createdb test
pg_restore -d test test.dump
The above commands successfully created, dropped, and restored a
test database with PostGIS. What happens if you run the same test?
What types do you see if you run "pg_restore -l" to display the
dump's table of contents? Here's what I get:
pg_restore -l test.dump | grep TYPE
5; 2652927 TYPE histogram2d postgres
6; 2652931 TYPE spheroid postgres
7; 2652935 TYPE geometry postgres
8; 2652953 TYPE box3d postgres
9; 2652963 TYPE chip postgres
10; 2652967 TYPE box2d postgres
11; 2653112 TYPE geometry_dump postgres
and
pg_restore test.dump | grep 'CREATE TYPE'
CREATE TYPE histogram2d (
CREATE TYPE spheroid (
CREATE TYPE geometry (
CREATE TYPE box3d (
CREATE TYPE chip (
CREATE TYPE box2d (
CREATE TYPE geometry_dump AS (
What output do you get?
--
Michael Fuhr