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;
Comments
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎