ryanhelms1

Postgres SQL Accounting

Oct 5th, 2021
9,671
0
Never
11
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. 1. Create the persistent data volume
  3. =========================================================================================================
  4.  
  5. docker volume create pgdata
  6.  
  7. 2. Create postgres instance
  8. =========================================================================================================
  9.  
  10. Run this script inside your local portainer "stack" area
  11.  
  12. ---
  13.  
  14. version: "3"
  15.  
  16. services:
  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.  
  29. volumes:
  30.   pgdata:
  31.  
  32.  
  33. 3. 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.
  38. BEGIN;
  39.  
  40.  
  41. CREATE TABLE IF NOT EXISTS public.shelf
  42. (
  43.     "Id" bigint,
  44.     "Name" "char" NOT NULL,
  45.     PRIMARY KEY ("Id")
  46. );
  47.  
  48. CREATE 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.  
  63. CREATE 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.  
  75. CREATE 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.  
  92. ALTER TABLE public.shelf
  93.     ADD FOREIGN KEY ("Id")
  94.     REFERENCES public."Book" ("ShelfId")
  95.     NOT VALID;
  96.  
  97.  
  98. ALTER TABLE public.shelf
  99.     ADD FOREIGN KEY ("Id")
  100.     REFERENCES public."JournalEntry" ("ShelfId")
  101.     NOT VALID;
  102.  
  103.  
  104. ALTER TABLE public.shelf
  105.     ADD FOREIGN KEY ("Id")
  106.     REFERENCES public."BookEntry" ("ShelfId")
  107.     NOT VALID;
  108.  
  109.  
  110. ALTER TABLE public."Book"
  111.     ADD FOREIGN KEY ("Id")
  112.     REFERENCES public."BookEntry" ("BookId")
  113.     NOT VALID;
  114.  
  115. END;
Advertisement
Comments
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
Add Comment
Please, Sign In to add comment