Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Question Unanswered: Migrate postgres database with postgis

    Hi, I have a many databases on Postgres-8.3 on Debian 5.0, but now we need to use Debin 6.0, but in Debian 6.0 haven't pack for Postgres-8.3, so we'll need to use Postgres-8.4, but I we are having the follow problem:

    Together with Postgres we use Postgis, the Postgis use some C library, and thw pach for that are fix like "/usr/lib/postgres/8.3/lib" and when I try to restore a dump, I get this erro:

    Some body knows how can I migrate it without problems ?

    Command was: CREATE FUNCTION ashexewkb(geometry, text) RETURNS text
    AS '/usr/lib/postgresql/8.3/lib/liblwgeom', 'LWGEOM_asHEXEWKB'
    ...
    pg_restore: [archiver (db)] could not execute query: ERROR: function public.ashexewkb(geometry, text) does not exist
    Command was: ALTER FUNCTION public.ashexewkb(geometry, text) OWNER TO postgres;
    pg_restore: [archiver (db)] Error from TOC entry 65; 1255 36105 FUNCTION askml(geometry, integer) postgres
    pg_restore: [archiver (db)] could not execute query: ERROR: SQL function cannot accept shell type geometry
    Command was: CREATE FUNCTION askml(geometry, integer) RETURNS text
    AS $_$SELECT _ST_AsKML(2, transform($1,4326), $2)$_$
    LANGUAGE ...
    pg_restore: [archiver (db)] could not execute query: ERROR: function public.askml(geometry, integer) does not exist
    Command was: ALTER FUNCTION public.askml(geometry, integer) OWNER TO postgres;
    pg_restore: [archiver (db)] Error from TOC entry 66; 1255 36106 FUNCTION askml(geometry) postgres
    pg_restore: [archiver (db)] could not execute query: ERROR: SQL function cannot accept shell type geometry
    Command was: CREATE FUNCTION askml(geometry) RETURNS text
    AS $_$SELECT _ST_AsKML(2, transform($1,4326), 15)$_$
    LANGUAGE sql IMMUT...
    pg_restore: [archiver (db)] could not execute query: ERROR: function public.askml(geometry) does not exist

    Regards,

    Targino Silveira

  2. #2
    Join Date
    Mar 2011
    Posts
    27
    Two things you can try:
    1. Setup a sym link from 8.3 to 8.4 directory for the libraries
    2. Convert the dump file into a plain text file and replace all 8.3 references with the new 8.4 directory names.

  3. #3
    Join Date
    May 2008
    Posts
    277
    I'm assuming you have your database and the postgis extension all loaded in the same schema? If so, you will want to follow the hard upgrade instructions to restore your database, which requires you to run your database dump file through an upgrade script.

    In the future, I think the best way to manage this is to load the postgis extension (and any other database-wide extensions) into the public schema and then put your own database in its own schema. Then when it comes times to upgrade:
    1. dump your custom schema(s)
    2. upgrade the PostgreSQL server
    3. create a new database and apply the postgis extension
    4. restore your schema(s) as normal

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •