[postgis-users] POSTGIS_ENABLE_OUTDB_RASTERS=1: PostGIS won't start (RHEL 6.5) SOLVED

Phil Hurvitz phurvitz at uw.edu
Wed Feb 25 13:21:10 PST 2015


Yup, I'd considered that and re-loaded with absolute path and still 
choked. But adding the tile size as well was the key.

Now I get, for example:

select rid, st_value(rast, 1, 1, 1) from slope where rid = 44;
  rid |     st_value
-----+-------------------
   44 | 0.374891400337219
(1 row)

Big thanks to Bborie for taking the time to help!

So in summary for others who may run into this problem,

1. In the installation instructions 
<http://postgis.net/docs/postgis_installation.html#install_short_version> it 
is not entirely clear where one would set the environment variables:

"As of PostGIS 2.1.3, out-of-db rasters and all raster drivers are 
disabled by default. In order to re-enable these, you need to set the 
following environment variables: POSTGIS_GDAL_ENABLED_DRIVERS and 
POSTGIS_ENABLE_OUTDB_RASTERS in the server environment."

The variables need to be set in the environment in which postgresql starts.

So if starting PostGIS from the command line, include the env vars 
before starting postmaster e.g.,

POSTGIS_ENABLE_OUTDB_RASTERS=1 POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL \
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &

or in a shell script alter the 'start' and 'restart' commands, e.g.,

su - $PGUSER -c "POSTGIS_ENABLE_OUTDB_RASTERS=1 \
POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL $DAEMON \
-D '$PGDATA' &" >>$PGLOG 2>&1

2. Make sure and load rasters using absolute pathnames AND specify a 
tile size, e.g.,

raster2pgsql -I -C -e -Y -F -d -R -s 2926 -t 1000x1000 \
/home/user/slope_ps.tif gis.slope | psql osm_test

-P.

**************************************************************
Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
Urban Form Lab  | 1107 NE 45th Street, Suite 535  | Box 354802
University of Washington, Seattle, Washington  98195-4802, USA
phurvitz at u.washington.edu | http://gis.washington.edu/phurvitz
"What is essential is invisible to the eye." -de Saint-Exupéry
**************************************************************

On 2/25/2015 12:56, Bborie Park wrote:
> Going back a few messages, it looks like you're specifying these out-db
> rasters with relative paths. Use absolute paths.
>
> Instead of:
>
> raster2pgsql -I -C -e -Y -F -d -R -s 2926  ./slope/slope_ps.tif
> gis.slope | psql osm_test
>
> Use:
>
> raster2pgsql -I -C -e -Y -F -d -R -s 2926
>   /ABSOLUTE/PATH/TO/slope/slope_ps.tif gis.slope | psql osm_test
>
> PostgreSQL cannot access paths that were relative to the current working
> directory when calling raster2pgsql.
>
> Also, specify a tile size.
>
> -bborie
>
>
>
> On Wed, Feb 25, 2015 at 12:51 PM, Phil Hurvitz <phurvitz at uw.edu
> <mailto:phurvitz at uw.edu>> wrote:
>
>     Thank you, Bborie. This seems to be a partial solution (at least now
>     PostGIS isn't complaining about the out-db raster). But now I get a
>     different problem, which is that attempting to access the out-db
>     raster makes the connection choke:
>
>     select st_summarystats(rast) from slope;
>     The connection to the server was lost. Attempting reset: Failed.
>     !>
>
>     -P.
>
>     ******************************__******************************__**
>     Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
>     Urban Form Lab  | 1107 NE 45th Street, Suite 535  | Box 354802
>     University of Washington, Seattle, Washington  98195-4802, USA
>     phurvitz at u.washington.edu <mailto:phurvitz at u.washington.edu> |
>     http://gis.washington.edu/__phurvitz
>     <http://gis.washington.edu/phurvitz>
>     "What is essential is invisible to the eye." -de Saint-Exupéry
>     ******************************__******************************__**
>
>     On 2/25/2015 12:41, Bborie Park wrote:
>
>         Ah. I use a script similar to this.
>
>         In the following block...
>
>         start)
>               ...
>               su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
>               ...
>
>         You'll want to modify the su line to be like:
>
>               su - $PGUSER -c "POSTGIS_ENABLE_OUTDB_RASTERS=__1
>         POSTGIS_GDAL_ENABLED_DRIVERS=____ENABLE_ALL $DAEMON -D '$PGDATA' &"
>           >>$PGLOG 2>&1
>
>         See if that works...
>
>         -bborie
>
>
>         On Wed, Feb 25, 2015 at 12:29 PM, Phil Hurvitz <phurvitz at uw.edu
>         <mailto:phurvitz at uw.edu>
>         <mailto:phurvitz at uw.edu <mailto:phurvitz at uw.edu>>> wrote:
>
>              Certainly!
>
>              ! /bin/sh
>
>              # chkconfig: 2345 98 02
>              # description: PostgreSQL RDBMS
>
>              # This is an example of a start/stop script for SysV-style
>         init, such
>              # as is used on Linux systems.  You should edit some of the
>         variables
>              # and maybe the 'echo' commands.
>              #
>              # Place this file at /etc/init.d/postgresql (or
>              # /etc/rc.d/init.d/postgresql) and make symlinks to
>              #   /etc/rc.d/rc0.d/K02postgresql
>              #   /etc/rc.d/rc1.d/K02postgresql
>              #   /etc/rc.d/rc2.d/K02postgresql
>              #   /etc/rc.d/rc3.d/S98postgresql
>              #   /etc/rc.d/rc4.d/S98postgresql
>              #   /etc/rc.d/rc5.d/S98postgresql
>              # Or, if you have chkconfig, simply:
>              # chkconfig --add postgresql
>              #
>              # Proper init scripts on Linux systems normally require
>         setting lock
>              # and pid files under /var/run as well as reacting to network
>              # settings, so you should treat this with care.
>
>              # Original author:  Ryan Kirkpatrick <pgsql at rkirkpat.net
>         <mailto:pgsql at rkirkpat.net>
>              <mailto:pgsql at rkirkpat.net <mailto:pgsql at rkirkpat.net>>>
>
>              # contrib/start-scripts/linux
>
>              ## EDIT FROM HERE
>
>              # Installation prefix
>              prefix=/usr/local/pgsql
>
>              # Data directory
>              PGDATA="/usr/local/pgsql/data"
>              POSTGIS_GDAL_ENABLED_DRIVERS=____ENABLE_ALL
>
>              # Who to run the postmaster as, usually "postgres".  (NOT
>         "root")
>              PGUSER=postgres
>
>              # Where to keep a log file
>              PGLOG="$PGDATA/serverlog"
>
>              # It's often a good idea to protect the postmaster from
>         being killed
>              by the
>              # OOM killer (which will tend to preferentially kill the
>         postmaster
>              because
>              # of the way it accounts for shared memory).  Setting the
>              OOM_SCORE_ADJ value
>              # to -1000 will disable OOM kill altogether.  If you enable
>         this,
>              you probably
>              # want to compile PostgreSQL with
>         "-DLINUX_OOM_SCORE_ADJ=0", so that
>              # individual backends can still be killed by the OOM killer.
>              #OOM_SCORE_ADJ=-1000
>              # Older Linux kernels may not have
>         /proc/self/oom_score_adj, but instead
>              # /proc/self/oom_adj, which works similarly except the
>         disable value
>              is -17.
>              # For such a system, enable this and compile with
>         "-DLINUX_OOM_ADJ=0".
>              #OOM_ADJ=-17
>
>              POSTGIS_ENABLE_OUTDB_RASTERS=1
>              POSTGIS_GDAL_ENABLED_DRIVERS=____ENABLE_ALL
>
>              ## STOP EDITING HERE
>
>              # The path that is to be used for the script
>
>         PATH=/usr/local/sbin:/usr/____local/bin:/sbin:/bin:/usr/____sbin:/usr/bin
>
>              # What to use to start up the postmaster.  (If you want the
>         script
>              to wait
>              # until the server has started, you could use "pg_ctl start
>         -w" here.
>              # But without -w, pg_ctl adds no value.)
>              DAEMON="$prefix/bin/____postmaster"
>
>
>              # What to use to shut down the postmaster
>              PGCTL="$prefix/bin/pg_ctl"
>
>              set -e
>
>              # Only start if we can find the postmaster.
>              test -x $DAEMON ||
>              {
>                       echo "$DAEMON not found"
>                       if [ "$1" = "stop" ]
>                       then exit 0
>                       else exit 5
>                       fi
>              }
>
>
>              # Parse command line parameters.
>              case $1 in
>                 start)
>                       echo -n "Starting PostgreSQL: "
>                       test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" >
>              /proc/self/oom_score_adj
>                       test x"$OOM_ADJ" != x && echo "$OOM_ADJ" >
>         /proc/self/oom_adj
>                       su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
>                       echo "ok"
>                       ;;
>                 stop)
>                       echo -n "Stopping PostgreSQL: "
>                       su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast"
>                       echo "ok"
>                       ;;
>                 restart)
>                       echo -n "Restarting PostgreSQL: "
>                       su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m
>         fast -w"
>                       test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" >
>              /proc/self/oom_score_adj
>                       test x"$OOM_ADJ" != x && echo "$OOM_ADJ" >
>         /proc/self/oom_adj
>                       su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
>                       echo "ok"
>                       ;;
>                 reload)
>                       echo -n "Reload PostgreSQL: "
>                       su - $PGUSER -c "$PGCTL reload -D '$PGDATA' -s"
>                       echo "ok"
>                       ;;
>                 status)
>                       su - $PGUSER -c "$PGCTL status -D '$PGDATA'"
>                       ;;
>                 *)
>                       # Print help
>                       echo "Usage: $0
>         {start|stop|restart|reload|____status}" 1>&2
>                       exit 1
>                       ;;
>              esac
>
>              exit 0
>
>              -P.
>
>
>         ******************************____****************************__**__**
>              Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
>              Urban Form Lab  | 1107 NE 45th Street, Suite 535  | Box 354802
>              University of Washington, Seattle, Washington  98195-4802, USA
>         phurvitz at u.washington.edu <mailto:phurvitz at u.washington.edu>
>         <mailto:phurvitz at u.washington.__edu
>         <mailto:phurvitz at u.washington.edu>> |
>         http://gis.washington.edu/____phurvitz
>         <http://gis.washington.edu/__phurvitz>
>              <http://gis.washington.edu/__phurvitz
>         <http://gis.washington.edu/phurvitz>>
>              "What is essential is invisible to the eye." -de Saint-Exupéry
>
>         ******************************____****************************__**__**
>
>              On 2/25/2015 12:25, Bborie Park wrote:
>
>                  Can you post the shell script?
>
>
>
>                  On Wed, Feb 25, 2015 at 11:50 AM, Phil Hurvitz
>         <phurvitz at uw.edu <mailto:phurvitz at uw.edu>
>                  <mailto:phurvitz at uw.edu <mailto:phurvitz at uw.edu>>
>                  <mailto:phurvitz at uw.edu <mailto:phurvitz at uw.edu>
>         <mailto:phurvitz at uw.edu <mailto:phurvitz at uw.edu>>>> wrote:
>
>                       Thanks Bborie, I am starting with a shell script,
>         and have
>                  added the
>                       env vars to that script, but am still unable to
>         access the
>                  out-db
>                       rasters.
>
>                       -P.
>
>
>
>         ******************************______**************************__**__**__**
>                       Philip M. Hurvitz, PhD | Research Assistant
>         Professor | UW-CBE
>                       Urban Form Lab  | 1107 NE 45th Street, Suite 535
>         | Box 354802
>                       University of Washington, Seattle, Washington
>         98195-4802, USA
>         phurvitz at u.washington.edu <mailto:phurvitz at u.washington.edu>
>         <mailto:phurvitz at u.washington.__edu
>         <mailto:phurvitz at u.washington.edu>>
>                  <mailto:phurvitz at u.washington.
>         <mailto:phurvitz at u.washington.>____edu
>                  <mailto:phurvitz at u.washington.__edu
>         <mailto:phurvitz at u.washington.edu>>> |
>         http://gis.washington.edu/______phurvitz
>         <http://gis.washington.edu/____phurvitz>
>                  <http://gis.washington.edu/____phurvitz
>         <http://gis.washington.edu/__phurvitz>>
>                       <http://gis.washington.edu/____phurvitz
>         <http://gis.washington.edu/__phurvitz>
>                  <http://gis.washington.edu/__phurvitz
>         <http://gis.washington.edu/phurvitz>>>
>                       "What is essential is invisible to the eye." -de
>         Saint-Exupéry
>
>
>         ******************************______**************************__**__**__**
>
>                       On 2/25/2015 11:45, Bborie Park wrote:
>
>                           The env variables need to be within the
>         environment of
>                  the postgres
>                           process. How are you starting postgres?
>
>                           Shell script? Then you should be able to add the
>                  variables to
>                           that script.
>
>                           Direct invocation of posrgres on the command
>         line? You
>                  need to
>                           have the
>                           variables before the command
>
>                           VAR=1 postgres ...
>
>                           -bborie
>
>                           On Feb 25, 2015 11:40 AM, "Phil Hurvitz"
>                  <phurvitz at uw.edu <mailto:phurvitz at uw.edu>
>         <mailto:phurvitz at uw.edu <mailto:phurvitz at uw.edu>>
>                           <mailto:phurvitz at uw.edu
>         <mailto:phurvitz at uw.edu> <mailto:phurvitz at uw.edu
>         <mailto:phurvitz at uw.edu>>>
>                           <mailto:phurvitz at uw.edu
>         <mailto:phurvitz at uw.edu> <mailto:phurvitz at uw.edu
>         <mailto:phurvitz at uw.edu>>
>                  <mailto:phurvitz at uw.edu <mailto:phurvitz at uw.edu>
>         <mailto:phurvitz at uw.edu <mailto:phurvitz at uw.edu>>>>> wrote:
>
>                                Thanks Bborie, I built from the tarball
>         rather
>                  than using
>                           an rpm;
>                                does that still mean I should be adding the
>                  environment
>                           variables to
>
>                                /etc/sysconfig/pgsql/________postgresql
>
>                                So for overkill I added the env vars to
>         that file
>                  as well
>                           as to the
>                                init script, restarted PostgreSQL, and it
>         seems I
>                  still cannot
>                                access the out-db raster.
>
>                                I added a slope raster using:
>
>                                raster2pgsql -I -C -e -Y -F -d -R -s 2926
>                  ./slope/slope_ps.tif
>                                gis.slope | psql osm_test
>
>                                I can get metadata (sorry for the ugly
>         text wrapping):
>
>                                select rid, (foo.md <http://foo.md>
>         <http://foo.md>
>                  <http://foo.md> <http://foo.md>).* from
>                           (select rid,
>                                st_Metadata(rast)  as md from slope) as foo;
>                                  rid |    upperleftx    |    upperlefty    |
>                  width | height |
>                                scalex      |      scaley       | skewx |
>         skewy |
>                  srid |
>                           numbands
>
>
>
>         -----+------------------+-----________-------------+-------+--__--__--__--__+-----------------__-+--__----__----__---------+--__-----+__------__-+----__--+---__-------
>                                    1 | 835161.301005914 | 758483.868026069 |
>                  31935 |  34649 |
>                                32.8083333333333 | -32.8083333333333 |
>           0 |
>                    0 | 2926
>                           |        1
>
>                                But cannot access values:
>
>                                select st_summarystats(rast) from slope;
>                                ERROR:  rt_raster_load_offline_data:
>         Access to offline
>                           bands disabled
>                                CONTEXT:  SQL function "st_summarystats"
>         statement 1
>
>                                select st_value(rast, 1, 1, 1) from slope;
>                                ERROR:  rt_raster_load_offline_data:
>         Access to offline
>                           bands disabled
>
>                                -P.
>
>
>
>
>         ******************************________************************__**__**__**__**
>                                Philip M. Hurvitz, PhD | Research Assistant
>                  Professor | UW-CBE
>                                Urban Form Lab  | 1107 NE 45th Street,
>         Suite 535
>                  | Box 354802
>                                University of Washington, Seattle, Washington
>                  98195-4802, USA
>         phurvitz at u.washington.edu <mailto:phurvitz at u.washington.edu>
>         <mailto:phurvitz at u.washington.__edu
>         <mailto:phurvitz at u.washington.edu>>
>                  <mailto:phurvitz at u.washington.
>         <mailto:phurvitz at u.washington.>____edu
>                  <mailto:phurvitz at u.washington.__edu
>         <mailto:phurvitz at u.washington.edu>>>
>                           <mailto:phurvitz at u.washington
>         <mailto:phurvitz at u.washington>.
>                  <mailto:phurvitz at u.washington
>         <mailto:phurvitz at u.washington>.__>____edu
>                           <mailto:phurvitz at u.washington.
>         <mailto:phurvitz at u.washington.>____edu
>                  <mailto:phurvitz at u.washington.__edu
>         <mailto:phurvitz at u.washington.edu>>>> |
>         http://gis.washington.edu/________phurvitz
>         <http://gis.washington.edu/______phurvitz>
>                  <http://gis.washington.edu/______phurvitz
>         <http://gis.washington.edu/____phurvitz>>
>                           <http://gis.washington.edu/______phurvitz
>         <http://gis.washington.edu/____phurvitz>
>                  <http://gis.washington.edu/____phurvitz
>         <http://gis.washington.edu/__phurvitz>>>
>                                <http://gis.washington.edu/______phurvitz
>         <http://gis.washington.edu/____phurvitz>
>                  <http://gis.washington.edu/____phurvitz
>         <http://gis.washington.edu/__phurvitz>>
>                           <http://gis.washington.edu/____phurvitz
>         <http://gis.washington.edu/__phurvitz>
>                  <http://gis.washington.edu/__phurvitz
>         <http://gis.washington.edu/phurvitz>>>>
>                                "What is essential is invisible to the
>         eye." -de
>                  Saint-Exupéry
>
>
>
>         ******************************________************************__**__**__**__**
>
>
>                                    Bborie Park dustymugs at gmail.com
>         <mailto:dustymugs at gmail.com>
>                  <mailto:dustymugs at gmail.com <mailto:dustymugs at gmail.com>>
>                           <mailto:dustymugs at gmail.com
>         <mailto:dustymugs at gmail.com>
>                  <mailto:dustymugs at gmail.com
>         <mailto:dustymugs at gmail.com>>> <mailto:dustymugs at gmail.com
>         <mailto:dustymugs at gmail.com>
>                  <mailto:dustymugs at gmail.com <mailto:dustymugs at gmail.com>>
>                           <mailto:dustymugs at gmail.com
>         <mailto:dustymugs at gmail.com> <mailto:dustymugs at gmail.com
>         <mailto:dustymugs at gmail.com>>>>
>                                    Wed Feb 25 10:46:59 PST 2015
>
>                                 >
>
>                                    Philip,
>
>                                    POSTGIS_ENABLE_OUTDB_RASTERS=1 is an
>         environment
>                           variable not to
>                                    be in
>                                    postgresql.conf.
>
>                                    The same is true for
>                           POSTGIS_GDAL_ENABLED_DRIVERS=________ENABLE_ALL
>
>         https://wiki.postgresql.org/________wiki/PostgreSQL_on_RedHat_________Linux
>         <https://wiki.postgresql.org/______wiki/PostgreSQL_on_RedHat_______Linux>
>
>         <https://wiki.postgresql.org/______wiki/PostgreSQL_on_RedHat_______Linux
>         <https://wiki.postgresql.org/____wiki/PostgreSQL_on_RedHat_____Linux>>
>
>
>         <https://wiki.postgresql.org/______wiki/PostgreSQL_on_RedHat_______Linux
>         <https://wiki.postgresql.org/____wiki/PostgreSQL_on_RedHat_____Linux>
>
>         <https://wiki.postgresql.org/____wiki/PostgreSQL_on_RedHat_____Linux
>         <https://wiki.postgresql.org/__wiki/PostgreSQL_on_RedHat___Linux>>>
>
>
>
>         <https://wiki.postgresql.org/______wiki/PostgreSQL_on_RedHat_______Linux
>         <https://wiki.postgresql.org/____wiki/PostgreSQL_on_RedHat_____Linux>
>
>         <https://wiki.postgresql.org/____wiki/PostgreSQL_on_RedHat_____Linux
>         <https://wiki.postgresql.org/__wiki/PostgreSQL_on_RedHat___Linux>>
>
>
>         <https://wiki.postgresql.org/____wiki/PostgreSQL_on_RedHat_____Linux
>         <https://wiki.postgresql.org/__wiki/PostgreSQL_on_RedHat___Linux>
>
>         <https://wiki.postgresql.org/__wiki/PostgreSQL_on_RedHat___Linux
>         <https://wiki.postgresql.org/wiki/PostgreSQL_on_RedHat_Linux>>>>
>
>                                    Based upon the above, it looks like
>         you should add
>
>                                    POSTGIS_ENABLE_OUTDB_RASTERS=1
>
>         POSTGIS_GDAL_ENABLED_DRIVERS=________ENABLE_ALL
>
>                                    to
>         /etc/sysconfig/pgsql/________postgresql
>
>                                    The above assumes you're using the
>         packages
>                  provided by
>                           PostgreSQL.
>
>         http://www.postgresql.org/________download/linux/redhat/
>         <http://www.postgresql.org/______download/linux/redhat/>
>                  <http://www.postgresql.org/______download/linux/redhat/
>         <http://www.postgresql.org/____download/linux/redhat/>>
>
>           <http://www.postgresql.org/______download/linux/redhat/
>         <http://www.postgresql.org/____download/linux/redhat/>
>                  <http://www.postgresql.org/____download/linux/redhat/
>         <http://www.postgresql.org/__download/linux/redhat/>>>
>
>
>         <http://www.postgresql.org/______download/linux/redhat/
>         <http://www.postgresql.org/____download/linux/redhat/>
>                  <http://www.postgresql.org/____download/linux/redhat/
>         <http://www.postgresql.org/__download/linux/redhat/>>
>
>           <http://www.postgresql.org/____download/linux/redhat/
>         <http://www.postgresql.org/__download/linux/redhat/>
>                  <http://www.postgresql.org/__download/linux/redhat/
>         <http://www.postgresql.org/download/linux/redhat/>>>>
>
>                                    -bborie
>
>
>
>                                On 2/25/2015 10:10, Phil Hurvitz wrote:
>
>                                    Hi all, I am having trouble starting
>         PostGIS with
>                           out-db raster
>                                    support
>
>                                    In my
>                  /usr/local/pgsql/data/________postgresql.conf file I
>                           include the
>                                    line
>
>                                    POSTGIS_ENABLE_OUTDB_RASTERS=1
>
>                                    after which PostGIS won't start (service
>                  postgresql start).
>
>                                    Software is
>
>                                    postgis_full_version
>
>
>
>         ------------------------------________------------------------__--__--__--__-----------
>                                       POSTGIS="2.1.3 r12547"
>                  GEOS="3.4.2-CAPI-1.8.2 r3921"
>                                    PROJ="Rel. 4.7.1,
>                                    23 September 2009" GDAL="GDAL 1.11.2,
>         released
>                  2015/02/10"
>                                    LIBXML="2.7.6" TOPOLOGY RASTER
>
>
>                                    Also PostGIS won't start when I specify
>
>
>         POSTGIS_GDAL_ENABLED_DRIVERS=________ENABLE_ALL
>
>                                    Any help would be appreciated!
>
>
>
>


More information about the postgis-users mailing list