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.
精彩评论