开发者

pg_dump not using $libdir on Linux for postgis

I'm using pg_dump to backup a PostgreSQL (9.0.3) + PostGIS (1.5.2) database that contains several GB of pre-calculated static geographic data. The PostGIS functions are included in the output file.

On Linux the plain-text .SQL output looks like:

CREATE FUNCTION geography_out(geography) RETURNS cstring
    LANGUAGE c IMMUTABLE STRICT
    AS '/opt/PostgreSQL/9.0/lib/postgis-1.5', 'geography_out';

On Windows t开发者_JAVA技巧he same .SQL file has:

CREATE FUNCTION geography_out(geography) RETURNS cstring
    LANGUAGE c IMMUTABLE STRICT
    AS '$libdir/postgis-1.5', 'geography_out';

In both instances, running pg_config shows the correct value for LIBDIR

  • Linux: LIBDIR = /opt/PostgreSQL/9.0/lib
  • Windows: LIBDIR = C:/PROGRA~2/POSTGR~1/9.0/lib

It's not guaranteed that ever Linux mirror I'll be restoring to has Postgres installed in the exact same location. Is it possible to force the pg_dump on Linux to use $libdir?

Update:

In both cases 9.0.3-1 PostgreSQL installers were downloaded from EnterpriseDB. PostGIS was installed using the StackBuilder tool that came with the install.

The spatially-enabled database was created following the standard PostGIS instructions:

createdb mydb
createlang plpgsql mydb
psql -d mydb -f postgis.sql
psql -d mydb -f spatial_ref_sys.sql

Interestingly... On Windows the file postgis.sql (C:\Program Files (x86)\PostgreSQL\9.0\share\contrib\postgis-1.5\postgis.sql):

CREATE OR REPLACE FUNCTION geography_out(geography)
    RETURNS cstring
    AS '$libdir/postgis-1.5','geography_out'
    LANGUAGE 'C' IMMUTABLE STRICT; 

Whereas on Linux (/opt/PostgreSQL/9.0/share/postgresql/contrib):

CREATE OR REPLACE FUNCTION geography_out(geography)
    RETURNS cstring
    AS '/opt/PostgreSQL/9.0/lib/postgis-1.5','geography_out'
    LANGUAGE 'C' IMMUTABLE STRICT;

So, the PostGIS SQL files that create the PostGIS functions use $libdir on Windows, but the full path on Linux. It's not an issue with pg_dump, but rather sounds like an installer issue.


pg_dump doesn't change these values. It just dumps them from the database as they are. If you created the functions with absolute paths they will be dumped with absolute paths. This makes sense since libdir may change over time and you don't want PostgreSQL to assume you want the locations to change just because you created them in what happened to be libdir at the time.

The problem here has to be somewhere else. I would expect that Stackbuilder is doing this so that it can ensure that the versions that it is packaged with are loaded. This reduces portability of course.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜