Dockerizing Phoenix Development (3) PostgreSQL + PostGIS

This part is intended to pull together a running PostgreSQL DBMS, with the PostGIS extensions on hand, as a container co-ordinated by docker-compose.

Approach

This involves these parts:

• Selecting an image from Docker Hub,
• Figuring out settings for this database image,
• Assembling a 'docker-compose.yml' file
• Run the container via 'docker-compose'
  • Establish a database instance & grant permissions
  • Install PostGIS into a separate SQL schema

Selecting an image from Docker Hub

Visit https://hub.docker.com and look around for a reasonable PostgreSQL image that includes PostGIS extensions. One choice that is current (June 2022): postgis/postgis:14-3.2-alpine.

If you really don't want or need PostGIS extensions, you could use: postgres/postgres:14-alpine

Figuring out settings for database image

https://registry.hub.docker.com/_/postgres/ provides good documentation, in the ‘Overview’ tab, and allows you to look at the Dockerfile that generated the postgis image.

Assembling a 'docker-compose.yml' file

This version will be extended in a later part of this series to include the Phoenix web server container.

version: "3"

volumes:
  postgis14_data:

services:

  db:
    image: postgis/postgis:14-3.2-alpine
    ports:
      - 5432:5432
    environment:
#     - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
#     - POSTGRES_DB=postgres
    volumes:
      - postgis14_data:/var/lib/postgresql/data/

• Note: the port 5432 being exposed allows access from tools on the host machine such as, say, BeeKeeper Studio.

Run the container

docker-compose  up  -d

• Note the -d flag which detaches the action from the terminal and lets you have the command prompt back.

Establish a fresh database instance & grant permissions

Connect to container as 'root' user:

docker  exec  -it myp_dev_db_1  /bin/ash

• You should now have a shell prompt within the container.

• Note the use of 'ash' command shell rather than the 'bash' command shell.

Within the container go into 'psql' as 'postgres':

psql   -h localhost   -d postgres   -U postgres

• You should now have a SQL command prompt.

Create a fresh user and database instance for development:

CREATE USER myp_user WITH ENCRYPTED PASSWORD 'Drowssap1' ;
CREATE DATABASE myp_dev ;
GRANT ALL PRIVILEGES ON DATABASE myp_dev TO postgres ;
\q

• Note: On some GUI tools: if you try to paste these statements in together, they will be handled as a transaction which will then fail/ rollback from part way. So probably best to do these statements one at a time!

Enable PostGIS into a separate SQL schema

Connect into 'myp_dev' database as 'postgres':

psql   -h localhost   -d myp_dev   -U postgres

• You should have a SQL command program at this point.

Establish the 'postgis' extension into it's own SQL schema:

CREATE SCHEMA postgis ;
ALTER DATABASE myp_dev SET search_path = postgis,public ;
\q

Re enter database for changed search_path to take effect:

CREATE EXTENSION postgis;
ALTER DATABASE myp_dev SET search_path = public,postgis ;
\q

• Note that 'postgis' items are installed into the first schema in the 'search_path'.
• There are further PostGIS related extensions - see https://www.postgis.net for more details.

Close things

Drop the container command line:

exit

Shutdown the running container from the host command line:

docker-compose  down

• Note this needs you to be in the same folder as when the 'up' command was issued.

Remarks

• This setup is for development only; testing and deployment will each be different.
• Some would develop while working as the 'postgres' user (with postgres administrator rights), and it can be convenient. Although everything here is within a sandbox (aka container): developing as 'root' user or database administrator is avoided as being bad practice.
• Good practice here includes ensuring that the 'postgis' tables, indexes, functions, etc are created into their own SQL schema rather than being mixed in with the rest of your application tables.
• Docker-compose does some automated naming of containers, networks and volumes that seems really intuitive and useful!