Dockerizing Phoenix Development (3) PostgreSQL + PostGIS
Tags: docker, elixir, phoenix, postgres
Published: 2021-05-30 18:47:49

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](https://hub.docker.com) and look around for a reasonable PostgreSQL image that includes [PostGIS](https://hub.docker.com/_/postgis/postgis) extensions.  One choice that is current (June 2022): [postgis/postgis:14-3.2-alpine](https://hub.docker.com/_/postgis/postgis). 

If you really don't want or need PostGIS extensions, you could use: [postgres/postgres:14-alpine](https://hub.docker.com/_/postgres/postgres)

## Figuring out settings for database image

[https://registry.hub.docker.com/_/postgres/](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](https://www.beekeeperstudio.io/)._ 

## Run the container

```SHELL
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:**
```SHELL
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:**
```SQL
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:**
```SQL
CREATE SCHEMA postgis ;
ALTER DATABASE myp_dev SET search_path = postgis,public ;
\q
```
Re enter database for changed **search_path** to take effect:
```SQL
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](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!