EPCmHScZ

1
21. Create the persistent data volume
3=========================================================================================================
4
5docker volume create pgdata
6
72. Create postgres instance
8=========================================================================================================
9
10Run this script inside your local portainer "stack" area
11
12---
13
14version: "3"
15
16services:
17
18  db:
19    image: postgres
20    environment:
21      - POSTGRES_USER=postgres
22      - POSTGRES_PASSWORD=admin
23      - POSTGRES_DB=postgres
24    ports:
25      - "5433:5432"
26    volumes:
27      - pgdata:/var/lib/postgresql/data
28
29volumes: 
30  pgdata:
31
32
333. Run this script inside the new instance
34==============================================================================================
35
36-- This script was generated by a beta version of the ERD tool in pgAdmin 4.
37-- Please log an issue at https://redmine.postgresql.org/projects/pgadmin4/issues/new if you find any bugs, including reproduction steps.
38BEGIN;
39
40
41CREATE TABLE IF NOT EXISTS public.shelf
42(
43    "Id" bigint,
44    "Name" "char" NOT NULL,
45    PRIMARY KEY ("Id")
46);
47
48CREATE TABLE IF NOT EXISTS public."Book"
49(
50    "Id" bigint,
51    "ShelfId" bigint,
52    "GroupId" bigint,
53    "CreatorId" bigint,
54    "IdempotenceKey" character varying,
55    "SumTo" bigint,
56    "SumFrom" bigint,
57    "Type" character varying,
58    "Owner" character varying,
59    "Version" bigint,
60    PRIMARY KEY ("Id")
61);
62
63CREATE TABLE IF NOT EXISTS public."JournalEntry"
64(
65    "Id" bigint,
66    "ShelfId" bigint,
67    "Type" aclitem NOT NULL,
68    "IndempotenceKey" character varying NOT NULL,
69    "Metadata" json,
70    "RequestBody" json,
71    "CommitTime" timestamp without time zone NOT NULL,
72    "CursorShard" bigint
73);
74
75CREATE TABLE IF NOT EXISTS public."BookEntry"
76(
77    "Id" bigint,
78    "ShelfId" bigint,
79    "JournalEntryId" bigint,
80    "BookId" bigint,
81    "BookVersion" bigint,
82    "BookEntryId" character varying,
83    "ToAmount" bigint,
84    "FromAmount" bigint,
85    "Type" character varying,
86    "SumTo" bigint,
87    "SumFrom" bigint,
88    "Metadata" json,
89    PRIMARY KEY ("Id")
90);
91
92ALTER TABLE public.shelf
93    ADD FOREIGN KEY ("Id")
94    REFERENCES public."Book" ("ShelfId")
95    NOT VALID;
96
97
98ALTER TABLE public.shelf
99    ADD FOREIGN KEY ("Id")
100    REFERENCES public."JournalEntry" ("ShelfId")
101    NOT VALID;
102
103
104ALTER TABLE public.shelf
105    ADD FOREIGN KEY ("Id")
106    REFERENCES public."BookEntry" ("ShelfId")
107    NOT VALID;
108
109
110ALTER TABLE public."Book"
111    ADD FOREIGN KEY ("Id")
112    REFERENCES public."BookEntry" ("BookId")
113    NOT VALID;
114
115END;