Raster broken

[maeneak]

12-3.0 seems to have broken rasters due to changes in 3.0. Is there any chance of including it in the default install until another solution is decided #(187)?

[ImreSamu]

rasters due to changes in 3.0. Is there any chance of including it in the default install

workaround:

please check https://hub.docker.com/_/postgres "Initialization scripts" part to understand the initialization logic.

• you can "hack" and overwrite the /docker-entrypoint-initdb.d/10_postgis.sh start script

  • replacing postgis_tiger_geocoder --> postgis_raster
  • replacing fuzzystrmatch --> hstore

• or you can extend this image with new custom Initialization scripts ( *.sql, *.sql.gz, or *.sh )

  • /docker-entrypoint-initdb.d/70_create_extension.sql
  • /docker-entrypoint-initdb.d/71_create_extension_unaccent.sql

I have created 2 example .. ( simple ; hacker )

simple example ( recommended ) :

# create simple extension script
echo "CREATE EXTENSION IF NOT EXISTS postgis_raster;" > 80_create_extension_postgis_raster.sql

# Start Server
docker run --name simpleraster \
    -e POSTGRES_PASSWORD=mysecretpassword \
    -v $(pwd)/80_create_extension_postgis_raster.sql:/docker-entrypoint-initdb.d/80_create_extension_postgis_raster.sql \
    -d postgis/postgis:12-3.0

sleep 13
docker run --rm -it --link simpleraster:postgres \
     postgis/postgis:12-3.0 \
     sh -c 'PGPASSWORD=mysecretpassword exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -c "\dx "'


....
# expected result:

                                            List of installed extensions
          Name          | Version |   Schema   |                             Description                             
------------------------+---------+------------+---------------------------------------------------------------------
 fuzzystrmatch          | 1.1     | public     | determine similarities and distance between strings
 plpgsql                | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis                | 3.0.1   | public     | PostGIS geometry, geography, and raster spatial types and functions
 postgis_raster         | 3.0.1   | public     | PostGIS raster types and functions
 postgis_tiger_geocoder | 3.0.1   | tiger      | PostGIS tiger geocoder and reverse geocoder
 postgis_topology       | 3.0.1   | topology   | PostGIS topology spatial types and functions
(6 rows)

hacker example:

# create new modified  /docker-entrypoint-initdb.d/10_postgis.sh
rm -f initdb-postgis.sh
wget https://raw.githubusercontent.com/postgis/docker-postgis/master/12-3.0/initdb-postgis.sh
cat initdb-postgis.sh \
    | sed 's/postgis_tiger_geocoder/postgis_raster/g' \
    | sed 's/fuzzystrmatch/hstore/g' \
    > 10_postgis.sh

# create ./70_create_extension.sql
cat > ./70_create_extension.sql <<EOL
    ----  create multiple extensions ---------
    CREATE EXTENSION IF NOT EXISTS bloom;
    CREATE EXTENSION IF NOT EXISTS btree_gin;
    CREATE EXTENSION IF NOT EXISTS btree_gist;
    CREATE EXTENSION IF NOT EXISTS cube;
    CREATE EXTENSION IF NOT EXISTS intarray;
    CREATE EXTENSION IF NOT EXISTS pg_trgm;
    CREATE EXTENSION IF NOT EXISTS pgcrypto;
    ------------------------------------------
EOL

# create 71_create_extension_unaccent.sql
echo "CREATE EXTENSION IF NOT EXISTS unaccent;" > 71_create_extension_unaccent.sql

# Start Server
docker run --name mypostgis \
    -e POSTGRES_PASSWORD=mysecretpassword \
    -v $(pwd)/10_postgis.sh:/docker-entrypoint-initdb.d/10_postgis.sh \
    -v $(pwd)/70_create_extension.sql:/docker-entrypoint-initdb.d/70_create_extension.sql \
    -v $(pwd)/71_create_extension_unaccent.sql:/docker-entrypoint-initdb.d/71_create_extension_unaccent.sql \
    -d postgis/postgis:12-3.0

sleep 5
# check server logs;
docker logs mypostgis

#  Test server
docker run --rm -it --link mypostgis:postgres \
     postgis/postgis:12-3.0 \
     sh -c 'PGPASSWORD=mysecretpassword exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -c "\dx "'

log :

--2020-05-03 16:35:57--  https://raw.githubusercontent.com/postgis/docker-postgis/master/12-3.0/initdb-postgis.sh
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 151.101.112.133
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|151.101.112.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 617 [text/plain]
Saving to: ‘initdb-postgis.sh’

initdb-postgis.sh                                  100%[================================================================================================================>]     617  --.-KB/s    in 0s      

2020-05-03 16:35:58 (26.9 MB/s) - ‘initdb-postgis.sh’ saved [617/617]

e323e1543277c1b5b38cd0d493ac6ce6d1a463e83f20fe7dca864b84287c043f
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
waiting for server to start....2020-05-03 14:35:59.754 UTC [46] LOG:  starting PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-05-03 14:35:59.755 UTC [46] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-05-03 14:35:59.771 UTC [47] LOG:  database system was shut down at 2020-05-03 14:35:59 UTC
2020-05-03 14:35:59.775 UTC [46] LOG:  database system is ready to accept connections
 done
server started

/usr/local/bin/docker-entrypoint.sh: sourcing /docker-entrypoint-initdb.d/10_postgis.sh
CREATE DATABASE
Loading PostGIS extensions into template_postgis
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
Loading PostGIS extensions into postgres
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/70_create_extension.sql
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION


/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/71_create_extension_unaccent.sql
CREATE EXTENSION


2020-05-03 14:36:03.117 UTC [46] LOG:  received fast shutdown request
waiting for server to shut down....2020-05-03 14:36:03.119 UTC [46] LOG:  aborting any active transactions
2020-05-03 14:36:03.120 UTC [46] LOG:  background worker "logical replication launcher" (PID 53) exited with exit code 1
2020-05-03 14:36:03.120 UTC [48] LOG:  shutting down
2020-05-03 14:36:03.195 UTC [46] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2020-05-03 14:36:03.234 UTC [1] LOG:  starting PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-05-03 14:36:03.234 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-05-03 14:36:03.234 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2020-05-03 14:36:03.236 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-05-03 14:36:03.251 UTC [101] LOG:  database system was shut down at 2020-05-03 14:36:03 UTC
2020-05-03 14:36:03.256 UTC [1] LOG:  database system is ready to accept connections
                                         List of installed extensions
       Name       | Version |   Schema   |                             Description                             
------------------+---------+------------+---------------------------------------------------------------------
 bloom            | 1.0     | public     | bloom access method - signature file based index
 btree_gin        | 1.3     | public     | support for indexing common datatypes in GIN
 btree_gist       | 1.5     | public     | support for indexing common datatypes in GiST
 cube             | 1.4     | public     | data type for multidimensional cubes
 hstore           | 1.6     | public     | data type for storing sets of (key, value) pairs
 intarray         | 1.2     | public     | functions, operators, and index support for 1-D arrays of integers
 pg_trgm          | 1.4     | public     | text similarity measurement and index searching based on trigrams
 pgcrypto         | 1.3     | public     | cryptographic functions
 plpgsql          | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis          | 3.0.1   | public     | PostGIS geometry, geography, and raster spatial types and functions
 postgis_raster   | 3.0.1   | public     | PostGIS raster types and functions
 postgis_topology | 3.0.1   | topology   | PostGIS topology spatial types and functions
 unaccent         | 1.1     | public     | text search dictionary that removes accents
(13 rows)