kartoza/docker-postgis

Dockerfile for postgis

docker-postgis's Issues

Struggling to use data volume

"You need to ensure the postgres_data directory has sufficient permissions for the docker process to read / write it."

$ mkdir .postgres_data
$ chmod -R 777 .postgres_data/
$ fig up
postgres_1 | Creating Postgres data at /var/lib/postgresql/9.3/main
postgres_1 | mkdir: cannot create directory '/var/lib/postgresql/9.3/main': Permission denied
postgres_1 | ls: cannot access /var/lib/postgresql/9.3/main: No such file or directory
postgres_1 | Initializing Postgres Database at /var/lib/postgresql/9.3/main
postgres_1 | The files belonging to this database system will be owned by user "postgres".
postgres_1 | This user must also own the server process.

The ./postgres_data/9.3 dir gets created but it fails as above

How do I do this?

I can't connect to my postgis-docker (server) with my desktop qgis.

Hi,
I have modified the file pg_hba.conf thanks to persistent data, but I can not reload postgres for it to take my new settings.
have you any idea?
edit: I start a new container?

PostgreSQL 9.5?

Any plans to upgrade to PostgreSQL 9.5?

Can't run docker build with ubuntu 14.04

I'm trying to run docker build, but it failed each time :
The following packages have unmet dependencies: postgresql-9.3-postgis-2.1 : Depends: libgdal1h (>= 1.9.0) but it is not going to be installed

I tried many hacks, but without success keeping ubuntu 14.04. It works using ubuntu:latest, can we switch ?

CC : @timlinux

run-postgis-docker.sh: Permission denied error

Hi,
I tried using run-postgis-docker.sh and failed. I think it's because there is no HOST_DATA_DIR in script (removed here )

Here is the output of setup script:

chmod: missing operand after ‘a+w’
Try 'chmod --help' for more information.
Error response from daemon: No such container: postgis
2014/12/07 08:59:59 Error: failed to kill one or more containers
Error response from daemon: No such container: postgis
2014/12/07 08:59:59 Error: failed to remove one or more containers
Running\n
docker run --name=postgis --hostname=postgis --restart=always -e USERNAME=****** -e PASS=******* -d -t -v /srv/postgres_data/:/var/lib/postgresql kartoza/postgis /start-postgis.sh
923ee100d29df026afa7b3e19b37ea533a3145132c00279369aca659e761ac77
923ee100d29d        kartoza/postgis:latest   "/start-postgis.sh"   Less than a second ago   Up Less than a second   5432/tcp            postgis
Connect using:
psql -l -p 5432 -h  -U ******
and password

Alternatively link to this container from another to access it
e.g. docker run -link postgis:pg .....etc
Will make the connection details to the postgis server available
in your app container as  (for the ip address)
and  (for the port number).

Here are my logs:

2014-12-07 13:57:04 UTC FATAL:  could not read permissions of directory "/var/lib/postgresql/9.3/main": Permission denied
Initializing Postgres Database at /var/lib/postgresql/9.3/main
No directory, logging in with HOME=/
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 "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".

Data page checksums are disabled.

initdb: could not access directory "/var/lib/postgresql/9.3/main": Permission denied
No directory, logging in with HOME=/
2014-12-07 13:57:04 UTC FATAL:  could not open file "/var/lib/postgresql/9.3/main/PG_VERSION": Permission denied
No directory, logging in with HOME=/
2014-12-07 13:57:04 UTC FATAL:  could not read permissions of directory "/var/lib/postgresql/9.3/main": Permission denied

docker-compose error

I have this configration in docker-compose.yml :

version: '2'

services:
  postgis:
    image: kartoza/postgis
    restart: always
    env_file:
      - ./postgis.env
    ports:
      - "5432:5432"
    volumes:
      - "./pgdata:/var/lib/postgresql/data"

and in make file :

up:
	# bring up the services
	docker-compose up -d

sync: up
	# set up the database tablea
	docker-compose exec cartoview python manage.py makemigrations app_manager
	docker-compose exec cartoview python manage.py migrate people
	docker-compose exec cartoview python manage.py migrate

when i run :
make sync
this error appear :

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 354, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 346, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/base.py", line 394, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/base.py", line 445, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/commands/migrate.py", line 93, in handle
    executor = MigrationExecutor(connection, self.migration_progress_callback)
  File "/usr/local/lib/python2.7/site-packages/django/db/migrations/executor.py", line 19, in __init__
    self.loader = MigrationLoader(self.connection)
  File "/usr/local/lib/python2.7/site-packages/django/db/migrations/loader.py", line 47, in __init__
    self.build_graph()
  File "/usr/local/lib/python2.7/site-packages/django/db/migrations/loader.py", line 191, in build_graph
    self.applied_migrations = recorder.applied_migrations()
  File "/usr/local/lib/python2.7/site-packages/django/db/migrations/recorder.py", line 59, in applied_migrations
    self.ensure_schema()
  File "/usr/local/lib/python2.7/site-packages/django/db/migrations/recorder.py", line 49, in ensure_schema
    if self.Migration._meta.db_table in self.connection.introspection.table_names(self.connection.cursor()):
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 162, in cursor
    cursor = self.make_debug_cursor(self._cursor())
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 135, in _cursor
    self.ensure_connection()
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 130, in ensure_connection
    self.connect()
  File "/usr/local/lib/python2.7/site-packages/django/db/utils.py", line 98, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 130, in ensure_connection
    self.connect()
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 119, in connect
    self.connection = self.get_new_connection(conn_params)
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 176, in get_new_connection
    connection = Database.connect(**conn_params)
  File "/usr/local/lib/python2.7/site-packages/psycopg2/__init__.py", line 164, in connect
    conn = _connect(dsn, connection_factory=connection_factory, async=async)
django.db.utils.OperationalError: FATAL:  no pg_hba.conf entry for host "172.18.0.4", user "docker", database "cartoview", SSL on
FATAL:  no pg_hba.conf entry for host "172.18.0.4", user "docker", database "cartoview", SSL off

Makefile:6: recipe for target 'sync' failed

Ive got an issue when i run python manage.py runserver 0.0.0.0:8000 i get this long error. Someone please help

performance on queries.

I am surprised by the queries performances between a postgresql / postgis database installed on the host or on a container.
Query ex: rotation crop in 42000ha * 5 years)
host:
Total execution time of the application: 10153 ms.
129165 rows retrieved.
container:
Total execution time of the application: 117034 ms.
129165 rows retrieved.

Why such a significant drop in speed perform queries?

Cant access postgis from another container running SQLAlchemy.

Massively appreciated if someone can help out here:

I have a container running Flask, no issues:

api:
    restart: always
    build:
      context: .
      dockerfile: ./build/api/Dockerfile
    ports:
      - "5000:5000"
    depends_on:
      - postgis
    command: bash -c "python3 /project/manage.py runserver --host=0.0.0.0"

I am running postgis like so:

postgis:
    image: kartoza/postgis:9.6-2.4
    ports:
      - 5432:5432
    volumes:
      - './build/db:/var/lib/postgresql'
    environment:
      - POSTGRES_DB=***
      - POSTGRES_USER=***
      - POSTGRES_PASS=***
      - ALLOW_IP_RANGE=0.0.0.0/0
    restart: unless-stopped
    command: sh -c "echo \"host all all 0.0.0.0/0 md5\" >> /etc/postgresql/9.4/main/pg_hba.conf && /start-postgis.sh"

Accessing postgis from my host works fine:

$ psql -h 0.0.0.0 -U *** -p 5432 -l

However, when I try to connect via my api container, I get the following error:

    Is the server running on host "127.0.0.1" and accepting
    TCP/IP connections on port 5432?

I connect from within my api container like so:

postgres://***:***@postgis:5432/***

Is there any reason why postgis is not available from other containers, but does work when accessing from the host?

Thanks very much.

Update: the postgis container is reachable from the api container.

docker-compose exec api bash -c "ping postgis"

can't access with psql when using docker-compose

I am able to launch and access psql database with persistent local storage with

sudo docker run --name=postgiscontainer -d -e POSTGRES_USER=knn -e POSTGRES_PASS=knn -e POSTGRES_DBNAME=knn -e ALLOW_IP_RANGE=0.0.0.0/0 -p 54322:5432 -v $(pwd)/postgres_data:/var/lib/postgresql --restart=always kartoza/postgis:9.6-2.4

I can connect to the database from the host
PGPASSWORD=knn psql -U knn -h localhost -p 54322 knn

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

knn=#

So far so good. But since I want this a part of a more complex system, I would like to launch the container with docker-compose. Here is my docker-compose.yml

version: "3"
services:
  postgis:
    image: kartoza/postgis:9.6-2.4
    ports:
      - "54322:5432"
    volumes:
      - ./postgres_data:/var/lib/postgresql
    environment:
      - POSTGRES_USER=knn
      - POSTGRES_PASSWD=knn
      - POSTGRES_DBNAME=knn
      - ALLOW_IP_RANGE=0.0.0.0/0
volumes:
  db-data:

I launch the container as microservice
sudo docker-compose -f docker-compose.example.yml up -d Starting postgis_postgis_1 ... Starting postgis_postgis_1 ... done

Now I want to connect again to the database from the host
PGPASSWORD=knn psql -U knn -h localhost -p 54322 knn

But this time I cant login to the database

psql: FATAL:  password authentication failed for user "knn"
FATAL:  password authentication failed for user "knn

To verify if the parameters I passed are in the container's environment, I enter inside the container and check the environment

sudo docker exec -i -t 25b78fcb49eb /bin/bash
# env

and obtain

POSTGRES_PASSWD=knn
HOSTNAME=25b78fcb49eb
PWD=/
HOME=/root
POSTGRES_DBNAME=knn
DEBIAN_FRONTEND=noninteractive
TERM=xterm
POSTGRES_USER=knn
SHLVL=1
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
ALLOW_IP_RANGE=0.0.0.0/0
_=/usr/bin/env

I see the login credentials are correctly passed into the container's environment but are not considered by postgresql. I am stuck and at loss. Where am I doing wrong?

thanks for your help

docker-compose usage with persistent data, database exited with code 1

Hi Tim, thanks for the great job.

I tried to use the Postgis database container together with Qgis 2.14 container. Both separately worked fine.
I bundled all in a docker-compose.yml file, using persistent postgres data on local drive.
Here is my docker-compose.yml file:

version: '2'
services:
  qgis:
    image: "kartoza/qgis-desktop:2.14"
    environment:
      - DISPLAY=unix$DISPLAY
    volumes:
      - ${HOME}:/home/${USER}
      - /tmp/.X11-unix:/tmp/.X11-unix
    links :
      - db:db

  db:
    image: "kartoza/postgis:9.4-2.1"
    ports:
      - 25432:5432
    volumes:
      - /Docker/data/postgres_postgis/:/var/lib/postgresql
      - /Docker/data/postgis/pg_hba.conf:/etc/postgresql/9.4/main/pg_hba.conf

By doing this way, I do not need database container IP address to call it, I simply use db.
(/Docker is a drive dedicated to docker images)

When stopping both containers, the postmaster.pid is never reset. It makes the database container refusing to start next time even if I delete the file. The postmaster.pid file is contained in the persistent data directory, in 'main' folder.

The error message:

db_1    | 2016-04-26 16:15:17 UTC [63-1] FATAL:  pre-existing shared memory block (key 5432001, ID 32768) is still in use
db_1    | 2016-04-26 16:15:17 UTC [63-2] HINT:  If you're sure there are no old server processes still running, remove the shared memory block or just delete the file "postmaster.pid".
214_db_1 exited with code 1

The second persistent data is the pg_hba.conf file, a copy of the original, with 1 more line to enable qgis container IP range. (mine is 172.21.0.0/16) This file is normally adjusted at startup with startup.sh where IP 172.17.0.0/16 is hard coded and apended to the pg_hda.conf file. Do you think it is possible to read server IP and reuse it? I tried ifconfig but did'nt work within container.

directory sharing

Hello, Thank you for maintaining this postgres-docker repository.

I am sharing directories as recommended by your Readme... but when I start the container I get these errors below. I used to get other errors, but then I put this in: sudo chmod -R 0777 .

I am using a mac. How do you recommend I go about setting appropriate pemissions.

Thank you...

2015-12-03 14:52:25 UTC [21-1] FATAL:  could not create lock file "postmaster.pid": Permission denied
some-postgis_1 | 2015-12-03 14:52:25 UTC [26-1] FATAL:  data directory "/var/lib/postgresql/9.4/main" has wrong ownership
some-postgis_1 | 2015-12-03 14:52:25 UTC [26-2] HINT:  The server must be started by the user that owns the data directory.

The following packages have unmet dependencies:

anyone can help fix this?

/start-postgis.sh: line 74: /etc/postgresql/10.0/main/pg_hba.conf: No such file or directory

I've downloaded newest version (created one hour ago) of 10.0-2.4 from dockerhub, but during running container start-postgis.sh is still trying to update pg_hba.conf in /etc/postgresql/10.0/main/ instead of /etc/postgresql/10/main/

 echo "host    all             all             $ALLOW_IP_RANGE              md5" >> /etc/postgresql/10.0/main/pg_hba.conf`

so when I try to run my application using docker-compose I can not connect to database - I can connect to database only using host localhost.

My docker-compose:

version: '3.3'

services:
  db:
    image: kartoza/postgis:10.0-2.4
    ports:
      - "5432:5432"
    environment:
      - ALLOW_IP_RANGE=0.0.0.0/0
  app:
    build: .
    ports:
      - "4326:4326"
    links:
      - "db"
    environment:
       SPRING_PROFILES_ACTIVE: docker
    command: ["/wait-for-it.sh", "-h", "db", "-p", "5432", "-t", "30", "-s", "--", "java", "-jar", "/app.jar"]

Docker networks support

I am trying to use "docker-postgis" image within a Docker network.

The default Docker network is 172.17.0.0/16. Although, if a Docker network is used, the IPs change range to 172.18.0.0/16 for the first network, 172.19.0.0/16 for the second, etc.

Would it be possible to add support for Docker networking, not for the default network only - perhaps change 172.17.0.0/16 to 172.0.0.0/24?

Ref: Docker - Work with network commands (https://docs.docker.com/engine/userguide/networking/work-with-networks/)

Errors on restart

Hi There, I am getting some errors when starting or restarting the image. I am wondering if those could indicate a issue in the termination process and if this could lead to a future corruption, in case I deploy this image in production...

So when I first start the latest image (currently 9.5.6) with...
docker run -ti kartoza/postgis

I get...

...
CREATE EXTENSION
Loading legacy sql
/usr/share/postgresql/9.5/contrib/postgis-2.2/legacy_minimal.sql: No such file or directory
/usr/share/postgresql/9.5/contrib/postgis-2.2/legacy_gist.sql: No such file or directory
...
Postgres initialisation process completed .... restarting in foreground
2017-03-26 20:59:01.168 UTC [153] LOG:  database system was interrupted; last known up at 2017-03-26 20:59:00 UTC
2017-03-26 20:59:15.668 UTC [153] LOG:  database system was not properly shut down; automatic recovery in progress
2017-03-26 20:59:15.687 UTC [153] LOG:  redo starts at 0/1F1BA90
2017-03-26 20:59:15.687 UTC [153] LOG:  invalid record length at 0/1F1BAB8
2017-03-26 20:59:15.687 UTC [153] LOG:  redo done at 0/1F1BA90

I also get those errors every time it restarts. Any ideas?

Cannot connect to the container

psql -h $(boot2docker ip) -p 25432 -d gis -U docker

That should work, but doesn't.

  psql: FATAL:  no pg_hba.conf entry for host "192.168.59.3", user "docker", database "gis", SSL on
  FATAL:  no pg_hba.conf entry for host "192.168.59.3", user "docker", database "gis", SSL off

I noticed that the database only accept connections from Docker subnet. What does this mean for boot2docker users?

How to run SQL after startup?

When loading "mainstream" postgres containers via the docker-maven-plugin, we can put SQL files in /docker-entrypoint-initdb.d and they will run after startup. How can we achieve that with your postgis container? How can we create tables and insert data after startup?

Mounting volumes

While mounting a volume from my file system, I encountered this permission issue:

Initializing Postgres Database at /var/lib/postgresql/9.3/main
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

My fig file looks like this

volumes:
  - ~/postgres_data:/var/lib/postgresql

EDIT: I noticed this is addressed in the documentation, but I'm not sure what "sufficient permissions" means. Would be great to know.

Issue connecting to database from docker host and other containers on macOS

Currently seeing an error when trying to connect to the Postgres container either from host machine or other docker containers on macOS High Sierra, Docker Version 17.12.0-ce-mac49 (21995).

Error I get is-

docker@gis FATAL: no pg_hba.conf entry for host "172.19.0.1", user "docker", database "gis", SSL off

I assumed this was due to it defaulting (as the docs specify) to the docker private subnet. So I placed the below into my docker-compose file

postgres:
      image: kartoza/postgis
      environment:
        - POSTGRES_DB=${DB_NAME}
        - POSTGRES_USER=${DB_USER}
        - POSTGRES_PASSWORD=${DB_PASS}
        - ALLOW_IP_RANGE=${ALLOW_IP_RANGE}
      ports:
        - "5432:5432"

With the follow .env file -

DB_NAME=gis
DB_USER=docker
DB_PASS=docker
DB_PORT=5432
ALLOW_IP_RANGE='0.0.0.0/0'

(Also tried with ALLOW_IP_RANGE=0.0.0.0/0, as well as hardcoding rather than env variables)

Still, no luck and I couldn't connect. I decided to try out manually adding 0.0.0.0/0 to pg_hba.conf by running the following (note that 65e35bba325d is the container ID of the postgres container) -

docker exec -it 65e35bba325d bash
root@65e35bba325d:~# sed -i '$ a\host all all 0.0.0.0/0 md5' /etc/postgresql/10/main/pg_hba.conf
root@65e35bba325d:~# /etc/init.d/postgresql reload

This lets me successfully connect to the database from both my other containers and my host.

Not entirely sure if I am doing something wrong here in applying the allowed IP range, but I couldn't get it applying via docker compose. Any help would be great.

Thanks!

Chaning pg_hba.conf

Guys can you explain me how can I change the pg_hba.conf of container while I'm building it...
I have tried to add in Dockerfile
RUN echo -e "host all all 127.0.0.1/32 trust\n host all all ::1/128 trust\n local all all trust" > /etc/postgresql/10/main/pg_hba.conf

but nothing happened... Can you help ?
Thanks !

Confusing instructions and unable to connect

So, I read the README.md, and there's this line

sudo docker run --name "postgis" -p 25432:5432 -d -t kartoza/postgis

And then below there's another subsection called "Convenience run script"

./run-postgis-docker.sh (some parameters)
and then run
psql -h localhost -U docker -p 25432 -l

I open this script in an editor, and there's no section -p 25432:5432, so you should connect to 5432 instead. This has to be fixed.

I decided to stick with the scripts, just to keep things saved in files. I did

./run-postgis-docker.sh -v tst -n postgis1 -u culebron -p culebron

It should have created a role culebron with password culebron. I try to connect:

psql -l -p 5432 -h localhost -U culebron

And get this error:

Password for user culebron: 
psql: FATAL:  password authentication failed for user "culebron"
FATAL:  password authentication failed for user "culebron"

Did not touch anything. Inside the container, it works fine.

What am I doing wrong?

Database is dropped and recreated on every container restart?

It is not an issue, but more of a question. Every time i restart docker, the database seems to be dropped and created. Is it expected?

Package ?

• Does this docker image contain :
• ogr_fdw
• pgrouting

Thank you.

readme.md spelling mistake

close to the bottom of the readme: s/sufficinet/sufficient/

Branch 9.6-2.4 recently lost some access lines from pg_hba.conf

setup-pg_hba.sh:

# moved from setup.sh. Delete if unnecessary
# Restrict subnet to docker private network
#echo "host    all             all             172.0.0.0/8               md5" >> $ROOT_CONF/pg_hba.conf
# And allow access from DockerToolbox / Boottodocker on OSX or any other host in this range
#echo "host    all             all             192.168.0.0/16               md5" >> $ROOT_CONF/pg_hba.conf

But there is no setup.sh and this lines are not added in other script. As a result:

FATAL:  no pg_hba.conf entry for host "172.19.0.6", user "postgres", database "postgres", SSL on

could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied

If I just run sudo docker run -p 65432:5432 -t kartoza/postgis:latest, the whole container fails with the the messages below.

But if I build the image with sudo docker build -t kartoza/postgis git://github.com/kartoza/docker-postgis everything is alright.

Could you please look into this?

Cheers,
ubergesundheit

PostgreSQL stand-alone backend 9.3.4
backend> 2014-10-18 08:34:30 UTC ERROR:  role "docker" already exists
2014-10-18 08:34:30 UTC STATEMENT:  CREATE USER docker WITH SUPERUSER ENCRYPTED PASSWORD 'docker';

backend> 2014-10-18 08:34:30 UTC FATAL:  could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
Postgis is missing, installing now
Creating template postgis
createdb: could not connect to database template1: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
Enabling template_postgis as a template
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
Loading postgis.sql
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
Loading spatial_ref_sys.sql
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
Loading legacy sql
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
Granting on geometry columns
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
Granting on geography columns
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
Granting on spatial ref sys
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
createdb: could not connect to database template1: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

ALLOWED_HOSTS is not recognized in latest (10.0-2.4)

When ALLOWED_HOSTS is specified (0.0.0.0/0 in my case), the container starts with a line:
/start-postgis.sh: line 74: /etc/postgresql/10.0/main/pg_hba.conf: No such file or directory

and then
2018-03-12 10:40:13.095 UTC [144] docker@gis FATAL: no pg_hba.conf entry for host "172.20.0.4", user "docker", database "gis", SSL on

This does not happen in 9.6-2.4

Using 10.0-2.4 tag does not install Postgres 10

Thank you for publishing and maintaining this repository, I've been using it for awhile. Today I just pulled and ran kartoza/postgis:10.0-2.4 and found that it's actually installing Postgres 9.6.5.

How to reproduce:

alex@vultr:~$ docker run --rm -d kartoza/postgis:10.0-2.4
Unable to find image 'kartoza/postgis:10.0-2.4' locally
10.0-2.4: Pulling from kartoza/postgis
28f860a8714a: Pull complete
f80f6c14adaf: Pull complete
653ac9c59513: Pull complete
4f80ffd21575: Pull complete
c639a4a6d2aa: Pull complete
80ddf8ff0466: Pull complete
e8b099b8d3ac: Pull complete
27d17f797cc4: Pull complete
eef0bb59d991: Pull complete
a7ac0cd2c7e5: Pull complete
Digest: sha256:81e0c49ca5ebbc67ea36bfd748a845369ca0c6d2e5391de7243888b8c6ccdf85
Status: Downloaded newer image for kartoza/postgis:10.0-2.4
c75c9fbab87e8964dadb6f349abcec7f0ff29bdfcbcbeae527a72abf6026dbd1
alex@vultr:~$ docker logs c75

PostgreSQL stand-alone backend 9.6.5
backend> backend> Sun Oct 29 20:29:01 UTC 2017 - waiting for postgres (localhost-only)...
2017-10-29 20:29:01.029 UTC [24] LOG:  could not bind IPv6 socket: Cannot assign requested address
2017-10-29 20:29:01.029 UTC [24] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2017-10-29 20:29:01.029 UTC [24] WARNING:  could not create listen socket for "::1"
2017-10-29 20:29:01.042 UTC [28] LOG:  database system was shut down at 2017-10-29 20:29:00 UTC
2017-10-29 20:29:01.044 UTC [28] LOG:  MultiXact member wraparound protections are now enabled
2017-10-29 20:29:01.047 UTC [32] LOG:  autovacuum launcher started
2017-10-29 20:29:01.047 UTC [24] LOG:  database system is ready to accept connections
postgres ready
2017-10-29 20:29:02.027 UTC [36] [unknown]@[unknown] LOG:  incomplete startup packet
Postgis is missing, installing now
Creating template postgis
Enabling template_postgis as a template
UPDATE 1
Loading postgis extension
CREATE EXTENSION
Enabling hstore in the template
CREATE EXTENSION
Enabling topology in the template
CREATE EXTENSION
                                 List of databases
       Name       |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
------------------+----------+----------+---------+---------+-----------------------
 gis              | docker   | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres         | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0        | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
                  |          |          |         |         | postgres=CTc/postgres
 template1        | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
                  |          |          |         |         | postgres=CTc/postgres
 template_postgis | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
(5 rows)

2017-10-29 20:29:04.252 UTC [24] LOG:  received smart shutdown request
2017-10-29 20:29:04.253 UTC [32] LOG:  autovacuum launcher shutting down
2017-10-29 20:29:04.257 UTC [29] LOG:  shutting down
2017-10-29 20:29:04.266 UTC [24] LOG:  database system is shut down
Postgres initialisation process completed .... restarting in foreground
2017-10-29 20:29:05.302 UTC [132] LOG:  database system was shut down at 2017-10-29 20:29:04 UTC
2017-10-29 20:29:05.304 UTC [132] LOG:  MultiXact member wraparound protections are now enabled
2017-10-29 20:29:05.307 UTC [129] LOG:  database system is ready to accept connections
2017-10-29 20:29:05.308 UTC [136] LOG:  autovacuum launcher started

I'm also seeing 9.6.5 when doing SELECT version(); in psql.

Default locale SQL-ASCII?

I'm getting a SQL-ASCII charset when I run a container.

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

But in this tutorial, the default locale seems to be UTF8, which is what I need. Is there a way to change the default locale when running the container for the first time?

Default user is not as per readme

"If these are not specified then the postgresql user is set to 'docker' with password 'docker'."

this is failing somehow... if I look in the /tmp/PGPASSWORD.txt file that was created by the startup script I see:

postgresql user: postgres
postgresql password: docker

hmm... and in the logs when running my image:

postgres_1 | 2014-12-05 04:15:56 UTC ERROR:  role "postgres" already exists
postgres_1 | 2014-12-05 04:15:56 UTC STATEMENT:  CREATE USER postgres WITH SUPERUSER ENCRYPTED PASSWORD 'docker';

which leaves me where I am, i.e. intended docker user was not created and postgres user has an unknown password

WIll data persist if docker is shut down?

I wanted to know if databases will persist when docker shutsdown?

add configuration tweaks

There are several tweaks to optimise PostgreSQL performance for spatial, mostly in postgresql.conf.

Include these as defaults in this setup

• increase shared_buffers from default to 500MB (in postgresql.conf)
  • you also need to add these lines to the end of /etc/sysctl.conf.
    kernel.shmmax=543252480
kernel.shmall=2097152
  • shmall units are pages and shmmax units are bytes(?) equivalent to the desired shared_buffer size - in this case 500MB
  • to see page size so you can calculate number of pages for shmall:

python
>>> import resource
>>> resource.getpagesize()
4096

• change work_mem from default to 16MB
• change maintenance_work_mem from default to 128MB
• change wal_buffers from default to 1MB
• change checkpoint_segments from 3 to 6
  • only if PostgreSQL <= 9.4
• change random_page_cost from default to 2
• set xmloption = 'document'
  • so QGIS QML docs can be stored in layer_styles

After start script

Hello, how do I run script/command after container is up and running? I am thinking something like this

#!/bin/bash

# Allowing incoming connections out of docker
echo "host all all 0.0.0.0/0 md5" >> /etc/postgresql/9.4/main/pg_hba.conf

# Run container command
/start-postgis.sh

# Create database with tables if not exist
psql -tc "SELECT 1 FROM pg_database WHERE datname = 'czechnu'" | grep -q 1 || psql postgres -f /var/pg_schema/db_czechnu.sql

Connecting to postgres services within dockercompose service

I am completely blocked and couldn't understand what i am doing wrong. This is not related to this repo but I feel like you people can help me.

I am trying to connect postgresdb service with nodejs web service using docker compose

My docker-compose.yml file

version: "3"                                                                                                                                                                                                                                                                                                                  
services:                                                                                                                                                                                                                                                                                                                     
  web:                                                                                                                                                                                                                                                                                                                        
      build: ./                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      ports:                                                                                                                                                                                                                                                                                                                  
       - "40000:3000"                                                                                                                                                                                                                                                                                                        
      depends_on:                                                                                                                                                                                                                                                                                                                  
        - postgres
  postgres:                                                                                                                                                                                                                                                                                                                   
      image: kartoza/postgis:9.6-2.4                                                                                                                                                                                                                                                                                          
      restart: always                                                                                                                                                                                                                                                                                                         
      volumes:                                                                                                                                                                                                                                                                                                                
        - postgresdata:/data/db                                                                                                                                                                                                                                                                                               
      environment:                                                                                                                                                                                                                                                                                                            
        - POSTGRES_PASS=password                                                                                                                                                                                                                                                                                              
        - POSTGRES_DBNAME=sticki                                                                                                                                                                                                                                                                                              
        - POSTGRES_USER=renga                                                                                                                                                                                                                                                                                                 
        - ALLOW_IP_RANGE=0.0.0.0/0                                                                                                                                                                                                                                                                                            
      ports:                                                                                                                                                                                                                                                                                                                  
        - "1000:5432"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
volumes:                                                                                                                                                                                                                                                                                                                      
  postgresdata:                                                                                                                                                                                                                                                                                                               

So when i do docker-compose up in my root directory both services are running and i can access web service using localhost:40000 and postgres service using postico on localhost:1000

But in Node Web service i have written code to access postgres using Sequelize as

const sequelize = new Sequelize('sticki', 'renga', 'password', {
  host: 'postgres',
  dialect: 'postgres',
});

But I get the following error

SequelizeConnectionRefusedError: connect ECONNREFUSED 172.18.0.2:1000

Why does postgres Connection is made to 172.18.0.2 instead of localhost(0.0.0.0)? What i am doing wrong?

error in command-line parsing in run-postgis-docker.sh

I think there is a typo in https://github.com/kartoza/docker-postgis/blob/develop/run-postgis-docker.sh#L43; should it not be d) ?

raster2pgsql support?

I'm trying to use raster2pgsql to import a few .adf files into PostGIS using this container. I tried using docker exec -it /bin/bash raster2pgsql ..., but that hasn't worked. Is there a way I can use raster2pgsql with this container?

Can't log into the database

I started a docker container: docker run --name "postgis" -p 5432:5432 -d -t kartoza/postgis:9.6-2.4

When I try to log in using bash it fails with user docker and password docker:
docker exec -it postgis bash
root@23bc932b6063:/# psql -U docker
psql: FATAL: Peer authentication failed for user "docker"

Cannot create a database for custom user

This is perhaps related to issue #8. If you specify a custom USERNAME with environmental variables, the database creation fails:

$ docker run --name "postgis" --env USERNAME=citi -p 25432:5432  -t kartoza/postgis
...
2015-01-06 21:14:31 UTC ERROR:  role "docker" does not exist
2015-01-06 21:14:31 UTC STATEMENT:  CREATE DATABASE gis OWNER docker TEMPLATE template_postgis;

createdb: database creation failed: ERROR:  role "docker" does not exist

Unable to connect to PostGIS container outside of localhost

Thanks for putting this together... I'm using GeoServer with my OSM data stored in PostGIS. I'd like to connect to my PostGIS server outside of my localhost. This involves changing the pg_hba.conf file to allow all connections, within the running docker container. How am I able to edit this file in a running container? I tried changing the pg_hba file by running the following command, which resulted in an error.

sudo docker exec df6cf7519348 echo "host all all 0.0.0.0/0 md5" >> /etc/postgresql/9.4/main/pg_hba.conf
-bash: /etc/postgresql/9.4/main/pg_hba.conf: No such file or directory

This command provides the output of the conf file
sudo docker exec df6cf7519348 cat /etc/postgresql/9.4/main/pg_hba.conf

Is this the correct approach, as I am new to PostGIS.

Thanks,

Erik

Data directory has group or world access

When starting up the container I see the following the logs:

FATAL: data directory "/var/lib/postgresql/9.5/main" has group or world access

followed by

DETAIL: Permissions should be u=rwx (0700).

Any ideas on how to remedy this?

How to understand container restart?

I tried to use this container and imported my DB dump into it. However, I encountered strange issues on container restart (copied from stderr):

2015-02-04 10:11:22 UTC LOG:  record with zero length at 0/8303BF0
2015-02-04 10:11:23 UTC ERROR:  role "dbuser" already exists

Also, it seems that container starts PostgreSQL at least twice, based on stdlog & stderr. In stdlog, I can see 2 listings of tables, the first one mentions default DB (gis), the second one my custom DB created in the container.

I try to support my Drupal installation with this container and it seems, that these issues make Drupal forget its DB configuration, which must be created over & over again.

I', using latest version of docker-postgis.

license

The licensing terms of this project are not clear. Could you upload a license file to the repository?

Include gif in readme

It would be nice to include this GIF in teh readme:

The GIF shows replication working in QGIS

Support for shp2pgsql and pgsql2shp?

Hi,

Are there any plans or interest to add support for the shp2pgsql and phsql2shp utilities?

Being able to quickly and easily import/export data using these is useful for those of us who are required to dabble in the world of shapefiles occasionally.

I was able to get something working by mounting a volume and installing postgis (I assume the utilities are not part of the postgresql-9.4-postgis-2.1 specified in the Dockerfile) so there is a workaround. I suspect there is a cleaner way to do this though.

# The workaround

# Start the container and mount /gisdata volume using -v
docker run --name "postgis" -v /gisdata:/gisdata  -p 25432:5432 -d -t kartoza/postgis

# Attach to running container
docker exec -i -t postgis /bin/bash

# Install postgis
apt-get install postgis

# Install vim, edit pg_hba.conf, and reload postgres
apt-get install vim-nox
vi /etc/postgresql/9.5/main/pg_hba.conf 
# local  all      all          peer
local  all      all          md5
service postgresql reload # FYI - restart will kill the container

# Export data to /gisdata
pgsql2shp -u docker -P docker -f /gisdata/test.shp database table

Thanks,

Matt

Using osm2pgsql to upload in postgis fails

I have a pair of geoserver/postgis working, but when I try to add a OSM map:

geoserv-docker$ osm2pgsql  -U docker -H 172.17.0.1 -P 5432 -W docker -d toscana  toscana.osm
osm2pgsql SVN version 0.82.0 (64bit id space)

Password:
Using projection SRS 900913 (Spherical Mercator)
Setting up table: planet_osm_point
NOTICE:  table "planet_osm_point" does not exist, skipping
NOTICE:  table "planet_osm_point_tmp" does not exist, skipping
SELECT AddGeometryColumn('planet_osm_point', 'way', 900913, 'POINT', 2 );
 failed: ERROR:  function addgeometrycolumn(unknown, unknown, integer, unknown, integer) does not exist
LINE 1: SELECT AddGeometryColumn('planet_osm_point', 'way', 900913, ...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Error occurred, cleaning up

I suspect that osm2pgsql (v 0.82.0) and the postgis in the server do not match. Approximately the same happens imposm. Could you please help me?

Thank you

How to load rasters into database?

I would like to load raster datasets into the postgis database. Usually this is done with the shipped raster2pgsql script, but I can't seem to find that on the container.
Any ideas?

Server never fully starts

When I try to start up this container, the server seems stuck --> it continuously logs:

waiting for postgres (localhost-only)...

Presumably, this is based on these lines of code in start-postgis.sh:

# wait for postgres to come up
until `nc -z 127.0.0.1 5432`; do
    echo "$(date) - waiting for postgres (localhost-only)..."
    sleep 1
done

Any idea what might be happening here? Or even how to produce some more useful log information?

Recent commits to branch 9.5-2.2 appear to break buildq

I pulled down the three or four most recent commits to the 9.5-2.2 branch and then stopped, rebuilt, and restarted my container. Another key here is that I had a folder on the host listed as a volume for persistent storage.

The build and restart went fine, but when I try to access any tables with geometry I get the following error:
ERROR: could not access file "$libdir/postgis-2.3": No such file or directory

I'm not positive, but I think it might have something to do with the packages that get installed in the latest version. See here: 9987840#diff-3254677a7917c6c01f55212f86c57fbfL26

When I examined the install there was a /usr/lib/postgresql/10/lib/postgis-2.4.so file, despite the package name listing the 2.2 version. So it might be something weird with the postgis-related Debian packages.

For my case, I just reverted back to the old commit and it is working fine. Just wanted to let you know.

PostGis extension not available in postgres server

Our container with postgres is not built with the postGIS extension enabled.