How to connect GeoNode Docker to External Database

geonode-docker-connect-to-external-db.md

Connecting GeoNode Docker to an External Database

1. Make sure docker components are down

>_ docker-compose down

2. Disable GeoNode docker database and related commands

• @docker-compose.yml comment out the db section

  # # PostGIS database.
  # db:
  #   # use geonode official postgis 11 image
  #   image: geonode/postgis:latest
  #   container_name: db4${COMPOSE_PROJECT_NAME}
  #   env_file:
  #     - ./scripts/docker/env/${DOCKER_ENV}/db.env
  #   volumes:
  #     - dbdata:/var/lib/postgresql/data
  #     - dbbackups:/pg_backups
  #   restart: on-failure
  #   # uncomment to enable remote connections to postgres
  #   #ports:
  #   #  - "5432:5432"

• @docker-compose.yml comment out the related volumes section

  # dbdata:
  #   name: ${COMPOSE_PROJECT_NAME}-dbdata
  # dbbackups:
  #   name: ${COMPOSE_PROJECT_NAME}-dbbackups

• @entrypoint.sh comment out the related "waitfordbs task done" section

  • Note 1: It is expected to have the external database up running before running, So no need to wait for the databases
  • Note 2: Please check how the script for wait for databases could be improved at the end on the file

  # /usr/local/bin/invoke waitfordbs > /usr/src/geonode/invoke.log 2>&1
  # echo "waitfordbs task done"

• Rebuild django docker service

  >_ docker-compose build

3. As for the external PostgreSQL database it is expected to have the following parameters:

• database user with password for connection
• database name for geonode django tables
• database name for geonode data tables
• exposed database IP address
• exposed database port
• Docker containers network IP address

4. Prepare The External PostgreSQL Database:

Skip this part if it is already prepared

• create database user <username> with <your_strong_password>

      >_ sudo -u postgres createuser -P <username>

• Create databases for geonode django tables with name geonode

      >_ sudo -u postgres createdb -O <username> geonode

• Create databases for geonode django tables with name geonode_data

      >_ sudo -u postgres createdb -O <username> geonode_data

• Use PostGIS extension for the created databases

  Replace <username> with your username

  >_ sudo -u postgres psql -d geonode -c 'CREATE EXTENSION postgis;'
  >_ sudo -u postgres psql -d geonode -c 'GRANT ALL ON geometry_columns TO PUBLIC;'
  >_ sudo -u postgres psql -d geonode -c 'GRANT ALL ON spatial_ref_sys TO PUBLIC;'
  >_ sudo -u postgres psql -d geonode -c 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO <username>;'
  
  >_ sudo -u postgres psql -d geonode_data -c 'CREATE EXTENSION postgis;'
  >_ sudo -u postgres psql -d geonode_data -c 'GRANT ALL ON geometry_columns TO PUBLIC;'
  >_ sudo -u postgres psql -d geonode_data -c 'GRANT ALL ON spatial_ref_sys TO PUBLIC;'
  >_ sudo -u postgres psql -d geonode_data -c 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO <username>;'

• Scenario 1: PostgreSQL service with docker service on the same host machine

  • Get the geonode docker network IP address:

    >_ docker network inspect geonode_default | grep Subnet
    # similar output:
    #       "Subnet": "172.21.0.0/16",

• Scenario 2: PostgreSQL is running on separate host machine:

  • Get the host machine IP address:

  >_ ifconfig

• Now, Having the IP address, Add two rules for both databases at /etc/postgres/<postgres_version_number>/pg_hba.conf

  # Syntax 
  # host DATABASE        USER         ADDRESS             METHOD
  # Ex
  # host geonode_data    geonode_user 172.21.0.0/16      md5 
  host   geonode         <username>   <ip_address>/16     md5
  host   geonode_data    <username>   <ip_address>/16     md5        
  

• Restart postgresql database service

  >_ sudo service postgresql restart

• Allow GeoNode docker to connect to the external database:

  • At geonode project dir, edit .env file
  • Replace with your username, password, ip_address, port
  • Replace database names if changed than geonode & geonode_data

      POSTGRES_USER=<username>
      POSTGRES_PASSWORD=<password>
      GEONODE_DATABASE=geonode
      GEONODE_DATABASE_PASSWORD=geonode
      GEONODE_GEODATABASE=geonode_data
      GEONODE_GEODATABASE_PASSWORD=geonode_data
      DATABASE_URL=postgis://<username>:<password>@<ip_address>:<port>/geonode
      GEODATABASE_URL=postgis://<username>:<password>@<ip_address>:<port>/geonode_data
  

  • Start the docker containers

  Expected to see the migrations running again

      >_ docker-compose up -d

  • Done!

Improvement suggestion for wait-for-databases-script

• The following .env configuration will allow the user to have an external database without disabling wait-for-databases check part
• @.env file, it is suggested to be like this

  POSTGRES_USER=postgres
  POSTGRES_PASSWORD=postgres
  GEONODE_DATABASE=geonode
  GEONODE_DATABASE_PASSWORD=geonode
  GEONODE_GEODATABASE=geonode_data
  GEONODE_GEODATABASE_PASSWORD=geonode_data
  GEONODE_DATABASE_SCHEMA=public
  GEONODE_GEODATABASE_SCHEMA=public
  
  GEONODE_DATABASE_HOST=db
  GEONODE_DATABASE_PORT=5432
  DATABASE_URL=postgis://$POSTGRES_USER:$POSTGRES_PASSWORD@$GEONODE_DATABASE_HOST:$GEONODE_DATABASE_PORT/$GEONODE_DATABASE
  
  GEONODE_GEODATABASE_HOST=db
  GEONODE_GEODATABASE_PORT=5432
  GEODATABASE_URL=postgis://$POSTGRES_USER:$POSTGRES_PASSWORD@$GEONODE_GEODATABASE_HOST:$GEONODE_GEODATABASE_PORT/$GEONODE_GEODATABASE
  
  GEONODE_DB_CONN_MAX_AGE=0GEONODE_GEODATABASE_HOST
  GEONODE_DB_CONN_TOUT=5
  DEFAULT_BACKEND_DATASTORE=datastore
  BROKER_URL=amqp://guest:guest@rabbitmq:5672/
  ASYNC_SIGNALS=True
  

• @wait-for-databases.sh file it could be like this

  #!/bin/bash
  
  set -e
  
  shift
  
  until PGPASSWORD=${GEONODE_DATABASE_PASSWORD} psql -h "$GEONODE_DATABASE_HOST" -p "$GEONODE_DATABASE_PORT" -U ${GEONODE_DATABASE} -d ${GEONODE_DATABASE} -P "pager=off" -c '\l'; do
  >&2 echo "${GEONODE_DATABASE} is unavailable - sleeping"
  sleep 1
  done
  
  until PGPASSWORD=${GEONODE_GEODATABASE_PASSWORD} psql -h "$GEONODE_GEODATABASE_HOST" -p "$port" -U ${GEONODE_GEODATABASE_PORT} -d ${GEONODE_GEODATABASE} -P "pager=off" -c '\l'; do
  >&2 echo "${GEONODE_GEODATABASE} is unavailable - sleeping"
  sleep 1
  done
  
  >&2 echo "GeoNode databases are up - executing command"