1. Create the persistent data volume
=========================================================================================================

docker volume create pgdata

2. Create postgres instance
=========================================================================================================

Run this script inside your local portainer "stack" area

---

version: "3"

services:

  db:
    image: postgres
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=admin
      - POSTGRES_DB=postgres
    ports:
      - "5433:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes: 
  pgdata:


3. Run this script inside the new instance
==============================================================================================

-- This script was generated by a beta version of the ERD tool in pgAdmin 4.
-- Please log an issue at https://redmine.postgresql.org/projects/pgadmin4/issues/new if you find any bugs, including reproduction steps.
BEGIN;


CREATE TABLE IF NOT EXISTS public.shelf
(
    "Id" bigint,
    "Name" "char" NOT NULL,
    PRIMARY KEY ("Id")
);

CREATE TABLE IF NOT EXISTS public."Book"
(
    "Id" bigint,
    "ShelfId" bigint,
    "GroupId" bigint,
    "CreatorId" bigint,
    "IdempotenceKey" character varying,
    "SumTo" bigint,
    "SumFrom" bigint,
    "Type" character varying,
    "Owner" character varying,
    "Version" bigint,
    PRIMARY KEY ("Id")
);

CREATE TABLE IF NOT EXISTS public."JournalEntry"
(
    "Id" bigint,
    "ShelfId" bigint,
    "Type" aclitem NOT NULL,
    "IndempotenceKey" character varying NOT NULL,
    "Metadata" json,
    "RequestBody" json,
    "CommitTime" timestamp without time zone NOT NULL,
    "CursorShard" bigint
);

CREATE TABLE IF NOT EXISTS public."BookEntry"
(
    "Id" bigint,
    "ShelfId" bigint,
    "JournalEntryId" bigint,
    "BookId" bigint,
    "BookVersion" bigint,
    "BookEntryId" character varying,
    "ToAmount" bigint,
    "FromAmount" bigint,
    "Type" character varying,
    "SumTo" bigint,
    "SumFrom" bigint,
    "Metadata" json,
    PRIMARY KEY ("Id")
);

ALTER TABLE public.shelf
    ADD FOREIGN KEY ("Id")
    REFERENCES public."Book" ("ShelfId")
    NOT VALID;


ALTER TABLE public.shelf
    ADD FOREIGN KEY ("Id")
    REFERENCES public."JournalEntry" ("ShelfId")
    NOT VALID;


ALTER TABLE public.shelf
    ADD FOREIGN KEY ("Id")
    REFERENCES public."BookEntry" ("ShelfId")
    NOT VALID;


ALTER TABLE public."Book"
    ADD FOREIGN KEY ("Id")
    REFERENCES public."BookEntry" ("BookId")
    NOT VALID;

END;