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.
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
If you really don't want or need PostGIS extensions, you could use: postgres/postgres:14-alpine
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.
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.
docker-compose up -d
- Note the -d flag which detaches the action from the terminal and lets you have the command prompt back.
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!
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.
Drop the container command line:
Shutdown the running container from the host command line:
- Note this needs you to be in the same folder as when the 'up' command was issued.
- 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!