search

invinst / invisible-flow

9 stars 2 forks source link

Setting up local databases #8

Open adesca opened 5 years ago

adesca commented 5 years ago

AS Sam I SHOULD be able to run a local docker instance with Postgres and postgis THAT uses the existing invisible institute schema

AS Sam WHEN I USE the local docker instance I SHOULD SEE data dumped from invisible institutes database

Refer here for details: Docker: https://hub.docker.com/r/mdillon/postgis/

Postgres with Docker tutorial: https://webcache.googleusercontent.com/search?q=cache:zAx4MWZ9GHEJ:https://hackernoon.com/dont-install-postgres-docker-pull-postgres-bee20e200198+&cd=1&hl=en&ct=clnk&gl=us

Invisible institute schema: https://paper.dropbox.com/doc/CPDP-Schema-Documentation-jiOJTlxkx2L99s9ycJqj3

Schema cleaned of extraneous database functions: https://docs.google.com/spreadsheets/d/1f83CcUdcyt-1kRnzkm_v0_rSJ0w3hfgd5TRBSlayFvQ/edit?usp=sharing

dtoakley commented 5 years ago

This is just a proof of concept for now, but here's a branch that's working for me locally: https://github.com/invinst/invisible-flow/pull/24

You need to have the /data/postgis_init.sql file in order for it to run. This is based on the data dump (https://www.dropbox.com/s/riixbrze6apmcrn/cpdp-apr-5-2019.sql?dl=0), however with the following changes to it:

  1. Remove SELECT pg_catalog.set_config('search_path', '', false);
  2. Add in:
--
-- Create Postgis Extension
--

CREATE EXTENSION postgis;

--
-- Create required roles
--

CREATE USER numeracy;
CREATE USER notebook;
CREATE USER civis;
  1. Remove:
-- Name: data_attachmentfile data_attachmentfile_last_updated_by_id_061dfbf8_fk_auth_user_id; Type: FK CONSTRAINT; Schema: public; Owner: cpdb
--

ALTER TABLE ONLY public.data_attachmentfile
    ADD CONSTRAINT data_attachmentfile_last_updated_by_id_061dfbf8_fk_auth_user_id FOREIGN KEY (last_updated_by_id) REFERENCES public.auth_user(id) DEFERRABLE INITIALLY DEFERRED;