-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathtool-postgresql.html
More file actions
328 lines (288 loc) · 30.7 KB
/
tool-postgresql.html
File metadata and controls
328 lines (288 loc) · 30.7 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
<!DOCTYPE HTML>
<html lang="en" class="light" dir="ltr">
<head>
<!-- Book generated using mdBook -->
<meta charset="UTF-8">
<title>PostgreSQL</title>
<!-- Custom HTML head -->
<meta name="description" content="">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="theme-color" content="#ffffff">
<link rel="shortcut icon" href="favicon.png">
<link rel="stylesheet" href="css/variables.css">
<link rel="stylesheet" href="css/general.css">
<link rel="stylesheet" href="css/chrome.css">
<link rel="stylesheet" href="css/print.css" media="print">
<!-- Fonts -->
<link rel="stylesheet" href="FontAwesome/css/font-awesome.css">
<link rel="stylesheet" href="fonts/fonts.css">
<!-- Highlight.js Stylesheets -->
<link rel="stylesheet" href="highlight.css">
<link rel="stylesheet" href="tomorrow-night.css">
<link rel="stylesheet" href="ayu-highlight.css">
<!-- Custom theme stylesheets -->
<script data-host="https://app.microanalytics.io" data-dnt="false" src="https://app.microanalytics.io/js/script.js" id="ZwSg9rf6GA" async defer></script>
</head>
<body class="sidebar-visible no-js">
<div id="body-container">
<!-- Provide site root to javascript -->
<script>
var path_to_root = "";
var default_theme = window.matchMedia("(prefers-color-scheme: dark)").matches ? "navy" : "light";
</script>
<!-- Work around some values being stored in localStorage wrapped in quotes -->
<script>
try {
var theme = localStorage.getItem('mdbook-theme');
var sidebar = localStorage.getItem('mdbook-sidebar');
if (theme.startsWith('"') && theme.endsWith('"')) {
localStorage.setItem('mdbook-theme', theme.slice(1, theme.length - 1));
}
if (sidebar.startsWith('"') && sidebar.endsWith('"')) {
localStorage.setItem('mdbook-sidebar', sidebar.slice(1, sidebar.length - 1));
}
} catch (e) { }
</script>
<!-- Set the theme before any content is loaded, prevents flash -->
<script>
var theme;
try { theme = localStorage.getItem('mdbook-theme'); } catch(e) { }
if (theme === null || theme === undefined) { theme = default_theme; }
var html = document.querySelector('html');
html.classList.remove('light')
html.classList.add(theme);
var body = document.querySelector('body');
body.classList.remove('no-js')
body.classList.add('js');
</script>
<input type="checkbox" id="sidebar-toggle-anchor" class="hidden">
<!-- Hide / unhide sidebar before it is displayed -->
<script>
var body = document.querySelector('body');
var sidebar = null;
var sidebar_toggle = document.getElementById("sidebar-toggle-anchor");
if (document.body.clientWidth >= 1080) {
try { sidebar = localStorage.getItem('mdbook-sidebar'); } catch(e) { }
sidebar = sidebar || 'visible';
} else {
sidebar = 'hidden';
}
sidebar_toggle.checked = sidebar === 'visible';
body.classList.remove('sidebar-visible');
body.classList.add("sidebar-" + sidebar);
</script>
<nav id="sidebar" class="sidebar" aria-label="Table of contents">
<div class="sidebar-scrollbox">
<ul class="chapter">
<li><a class="active" href="../">chuck-stack Home</a></li> <!--chuboe changed-->
</ul>
<ol class="chapter"><li class="chapter-item expanded "><a href="introduction.html"><strong aria-hidden="true">1.</strong> Introduction</a></li><li class="chapter-item expanded "><a href="picture-success.html"><strong aria-hidden="true">2.</strong> Picture of Success</a></li><li class="chapter-item expanded "><a href="success-factor.html"><strong aria-hidden="true">3.</strong> Success Factors</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="success-hr.html"><strong aria-hidden="true">3.1.</strong> Success with HR</a></li><li class="chapter-item expanded "><a href="success-ai.html"><strong aria-hidden="true">3.2.</strong> Success with AI</a></li><li class="chapter-item expanded "><a href="success-cli.html"><strong aria-hidden="true">3.3.</strong> Success with CLI</a></li><li class="chapter-item expanded "><a href="success-linux.html"><strong aria-hidden="true">3.4.</strong> Success with Linux</a></li><li class="chapter-item expanded "><a href="success-postgresql.html"><strong aria-hidden="true">3.5.</strong> Success with PostgreSQL</a></li><li class="chapter-item expanded "><a href="success-api.html"><strong aria-hidden="true">3.6.</strong> Success with API</a></li></ol></li><li class="chapter-item expanded "><a href="getting-started.html"><strong aria-hidden="true">4.</strong> Getting Started</a></li><li class="chapter-item expanded "><a href="stack-faq.html"><strong aria-hidden="true">5.</strong> Stack FAQ</a></li><li class="chapter-item expanded "><a href="stack-tools.html"><strong aria-hidden="true">6.</strong> Stack Tools</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="tool-incus.html"><strong aria-hidden="true">6.1.</strong> Incus</a></li><li class="chapter-item expanded "><a href="tool-linux.html"><strong aria-hidden="true">6.2.</strong> Linux</a></li><li class="chapter-item expanded "><a href="tool-postgresql.html" class="active"><strong aria-hidden="true">6.3.</strong> PostgreSQL</a></li><li class="chapter-item expanded "><a href="tool-postgrest.html"><strong aria-hidden="true">6.4.</strong> PostgREST</a></li><li class="chapter-item expanded "><a href="tool-aichat.html"><strong aria-hidden="true">6.5.</strong> AIChat</a></li><li class="chapter-item expanded "><a href="tool-nushell.html"><strong aria-hidden="true">6.6.</strong> Nushell</a></li><li class="chapter-item expanded "><a href="tool-zellij.html"><strong aria-hidden="true">6.7.</strong> Zellij</a></li><li class="chapter-item expanded "><a href="tool-git.html"><strong aria-hidden="true">6.8.</strong> Git</a></li><li class="chapter-item expanded "><a href="tool-obsidian.html"><strong aria-hidden="true">6.9.</strong> Obsidian</a></li><li class="chapter-item expanded "><a href="tool-netbird.html"><strong aria-hidden="true">6.10.</strong> Netbird</a></li><li class="chapter-item expanded "><a href="tool-idempiere.html"><strong aria-hidden="true">6.11.</strong> iDempiere</a></li><li class="chapter-item expanded "><a href="tool-htmx.html"><strong aria-hidden="true">6.12.</strong> htmx</a></li><li class="chapter-item expanded "><a href="tool-discourse.html"><strong aria-hidden="true">6.13.</strong> Discourse</a></li><li class="chapter-item expanded "><a href="tool-mdbook.html"><strong aria-hidden="true">6.14.</strong> mdBook</a></li><li class="chapter-item expanded "><a href="tool-others.html"><strong aria-hidden="true">6.15.</strong> Others</a></li></ol></li><li class="chapter-item expanded "><a href="stack-architecture.html"><strong aria-hidden="true">7.</strong> Stack Architecture</a></li><li class="chapter-item expanded "><a href="stack-application.html"><strong aria-hidden="true">8.</strong> Stack Application</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="application-deploy.html"><strong aria-hidden="true">8.1.</strong> Deployment</a></li><li class="chapter-item expanded "><a href="application-workflow.html"><strong aria-hidden="true">8.2.</strong> Workflow</a></li><li class="chapter-item expanded "><a href="application-attribute.html"><strong aria-hidden="true">8.3.</strong> Attribute Tagging</a></li></ol></li><li class="chapter-item expanded "><a href="best-practices.html"><strong aria-hidden="true">9.</strong> Stack Best Practices</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="best-practices-operation.html"><strong aria-hidden="true">9.1.</strong> Operation Best Practices</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="best-practices-operation-say.html"><strong aria-hidden="true">9.1.1.</strong> Say What We Do</a></li><li class="chapter-item expanded "><a href="best-practices-operation-do.html"><strong aria-hidden="true">9.1.2.</strong> Do What We Said</a></li><li class="chapter-item expanded "><a href="best-practices-operation-prove.html"><strong aria-hidden="true">9.1.3.</strong> Prove We Did It</a></li><li class="chapter-item expanded "><a href="best-practices-operation-feedback.html"><strong aria-hidden="true">9.1.4.</strong> Welcome Feedback</a></li><li class="chapter-item expanded "><a href="best-practices-operation-improve.html"><strong aria-hidden="true">9.1.5.</strong> Continuously Improve</a></li></ol></li><li class="chapter-item expanded "><a href="data-management-philosophy.html"><strong aria-hidden="true">9.2.</strong> Data Management Philosophy</a></li><li class="chapter-item expanded "><a href="postgres-conventions.html"><strong aria-hidden="true">9.3.</strong> Postgres Conventions</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="postgres-convention/schema.html"><strong aria-hidden="true">9.3.1.</strong> Private and API Schema</a></li><li class="chapter-item expanded "><a href="postgres-convention/abbreviation.html"><strong aria-hidden="true">9.3.2.</strong> Abbreviation</a></li><li class="chapter-item expanded "><a href="postgres-convention/table-convention.html"><strong aria-hidden="true">9.3.3.</strong> Table Convention</a></li><li class="chapter-item expanded "><a href="postgres-convention/column-convention.html"><strong aria-hidden="true">9.3.4.</strong> Column Convention</a></li><li class="chapter-item expanded "><a href="postgres-convention/table-record-convention.html"><strong aria-hidden="true">9.3.5.</strong> Table and Record</a></li><li class="chapter-item expanded "><a href="postgres-convention/uuid.html"><strong aria-hidden="true">9.3.6.</strong> UUID</a></li><li class="chapter-item expanded "><a href="postgres-convention/json-array-table-column.html"><strong aria-hidden="true">9.3.7.</strong> JSON vs Array vs Table</a></li><li class="chapter-item expanded "><a href="postgres-convention/enum-type-convention.html"><strong aria-hidden="true">9.3.8.</strong> enum and Type</a></li><li class="chapter-item expanded "><a href="postgres-convention/partition-convention.html"><strong aria-hidden="true">9.3.9.</strong> Partition</a></li><li class="chapter-item expanded "><a href="postgres-convention/function-convention.html"><strong aria-hidden="true">9.3.10.</strong> Function</a></li><li class="chapter-item expanded "><a href="postgres-convention/trigger-convention.html"><strong aria-hidden="true">9.3.11.</strong> Trigger</a></li><li class="chapter-item expanded "><a href="postgres-convention/comment.html"><strong aria-hidden="true">9.3.12.</strong> Comments</a></li><li class="chapter-item expanded "><a href="postgres-convention/sample-table-convention.html"><strong aria-hidden="true">9.3.13.</strong> Sample Table</a></li><li class="chapter-item expanded "><a href="postgres-convention/scalability-convention.html"><strong aria-hidden="true">9.3.14.</strong> Scalability</a></li><li class="chapter-item expanded "><a href="postgres-convention/nushell.html"><strong aria-hidden="true">9.3.15.</strong> Nushell psql</a></li><li class="chapter-item expanded "><a href="postgres-convention/to-be-resolved-convention.html"><strong aria-hidden="true">9.3.16.</strong> To Be Resolved</a></li></ol></li><li class="chapter-item expanded "><a href="postgres-services.html"><strong aria-hidden="true">9.4.</strong> Postgres Services</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="postgres-convention/async.html"><strong aria-hidden="true">9.4.1.</strong> Async</a></li><li class="chapter-item expanded "><a href="postgres-convention/attribute-tag.html"><strong aria-hidden="true">9.4.2.</strong> Attribute Tagging</a></li><li class="chapter-item expanded "><a href="postgres-convention/change-log.html"><strong aria-hidden="true">9.4.3.</strong> Change Log</a></li><li class="chapter-item expanded "><a href="postgres-convention/event.html"><strong aria-hidden="true">9.4.4.</strong> Event</a></li><li class="chapter-item expanded "><a href="postgres-convention/notify.html"><strong aria-hidden="true">9.4.5.</strong> Notify</a></li><li class="chapter-item expanded "><a href="postgres-convention/request.html"><strong aria-hidden="true">9.4.6.</strong> Request Workflow</a></li><li class="chapter-item expanded "><a href="postgres-convention/statistics-convention.html"><strong aria-hidden="true">9.4.7.</strong> Statistics</a></li><li class="chapter-item expanded "><a href="postgres-convention/system-configuration-convention.html"><strong aria-hidden="true">9.4.8.</strong> System Configuration</a></li></ol></li></ol></li><li class="chapter-item expanded "><a href="cli-tutor.html"><strong aria-hidden="true">10.</strong> Stack Tutor</a></li><li class="chapter-item expanded "><a href="stack-academy.html"><strong aria-hidden="true">11.</strong> Stack Academy</a></li><li class="chapter-item expanded "><a href="stack-local.html"><strong aria-hidden="true">12.</strong> Local Community Support</a></li><li class="chapter-item expanded "><a href="terminology.html"><strong aria-hidden="true">13.</strong> Terminology</a></li><li class="chapter-item expanded "><a href="roadmap.html"><strong aria-hidden="true">14.</strong> Roadmap</a></li><li class="chapter-item expanded "><a href="project-history.html"><strong aria-hidden="true">15.</strong> Project History</a></li><li class="chapter-item expanded "><a href="reference.html"><strong aria-hidden="true">16.</strong> References</a></li><li class="chapter-item expanded affix "><li class="spacer"></li><li class="chapter-item expanded "><a href="code-of-conduct.html"><strong aria-hidden="true">17.</strong> Code of Conduct</a></li><li class="chapter-item expanded "><a href="privacy-policy.html"><strong aria-hidden="true">18.</strong> Privacy Policy</a></li><li class="chapter-item expanded affix "><li class="spacer"></li><li class="chapter-item expanded "><a href="blog.html"><strong aria-hidden="true">19.</strong> Blog</a></li><li><ol class="section"><li class="chapter-item expanded "><a href="blog-postgresql-json-show-sales-order.html"><strong aria-hidden="true">19.1.</strong> How to Extract JSON from PostgreSQL</a></li><li class="chapter-item expanded "><a href="blog-incus-netbird-phoenixnap-isolated.html"><strong aria-hidden="true">19.2.</strong> Isolated Public-Facing Services in Incus</a></li><li class="chapter-item expanded "><a href="blog-local-co-op-funding-help.html"><strong aria-hidden="true">19.3.</strong> Local AI Funding - Time to Bring Back the Co-op</a></li><li class="chapter-item expanded "><a href="blog-llm-ai-production-deploy.html"><strong aria-hidden="true">19.4.</strong> Production Open Source Chat with Work Instructions</a></li><li class="chapter-item expanded "><a href="blog-chuck-stack-it-job-description-assessment.html"><strong aria-hidden="true">19.5.</strong> chuck-stack IT Job Description</a></li><li class="chapter-item expanded "><a href="blog-rsync-net.html"><strong aria-hidden="true">19.6.</strong> Immutable Off-site Backups are Imperative</a></li><li class="chapter-item expanded "><a href="blog-llm-ai-operations-automation.html"><strong aria-hidden="true">19.7.</strong> Operations AI LLM Automation in 2025</a></li><li class="chapter-item expanded "><a href="blog-incus-netbird-phoenixnap.html"><strong aria-hidden="true">19.8.</strong> Hybrid Cloud Strategy: Incus + Netbird + PhoenixNAP</a></li><li class="chapter-item expanded "><a href="blog-letter-ceo.html"><strong aria-hidden="true">19.9.</strong> Letter to the CEO</a></li><li class="chapter-item expanded "><a href="blog-cli-better.html"><strong aria-hidden="true">19.10.</strong> The CLI is better than...</a></li><li class="chapter-item expanded "><a href="blog-conversational-enterprise-computing.html"><strong aria-hidden="true">19.11.</strong> Why Conversational Enterprise Computing Matters</a></li><li class="chapter-item expanded "><a href="blog-work-instruction-sexy.html"><strong aria-hidden="true">19.12.</strong> Work Instructions Make your Organization Sexy</a></li><li class="chapter-item expanded "><a href="blog-live-markdown-world.html"><strong aria-hidden="true">19.13.</strong> We Live in a Markdown World</a></li></ol></li></ol>
</div>
<div id="sidebar-resize-handle" class="sidebar-resize-handle">
<div class="sidebar-resize-indicator"></div>
</div>
</nav>
<!-- Track and set sidebar scroll position -->
<script>
var sidebarScrollbox = document.querySelector('#sidebar .sidebar-scrollbox');
sidebarScrollbox.addEventListener('click', function(e) {
if (e.target.tagName === 'A') {
sessionStorage.setItem('sidebar-scroll', sidebarScrollbox.scrollTop);
}
}, { passive: true });
var sidebarScrollTop = sessionStorage.getItem('sidebar-scroll');
sessionStorage.removeItem('sidebar-scroll');
if (sidebarScrollTop) {
// preserve sidebar scroll position when navigating via links within sidebar
sidebarScrollbox.scrollTop = sidebarScrollTop;
} else {
// scroll sidebar to current active section when navigating via "next/previous chapter" buttons
var activeSection = document.querySelector('#sidebar .active');
if (activeSection) {
activeSection.scrollIntoView({ block: 'center' });
}
}
</script>
<div id="page-wrapper" class="page-wrapper">
<div class="page">
<!-- add things here that will go above the body navigation -->
<div id="menu-bar-hover-placeholder"></div>
<div id="menu-bar" class="menu-bar sticky">
<div class="left-buttons">
<label id="sidebar-toggle" class="icon-button" for="sidebar-toggle-anchor" title="Toggle Table of Contents" aria-label="Toggle Table of Contents" aria-controls="sidebar">
<i class="fa fa-bars"></i>
</label>
<button id="theme-toggle" class="icon-button" type="button" title="Change theme" aria-label="Change theme" aria-haspopup="true" aria-expanded="false" aria-controls="theme-list">
<i class="fa fa-paint-brush"></i>
</button>
<ul id="theme-list" class="theme-popup" aria-label="Themes" role="menu">
<li role="none"><button role="menuitem" class="theme" id="light">Light</button></li>
<li role="none"><button role="menuitem" class="theme" id="rust">Rust</button></li>
<li role="none"><button role="menuitem" class="theme" id="coal">Coal</button></li>
<li role="none"><button role="menuitem" class="theme" id="navy">Navy</button></li>
<li role="none"><button role="menuitem" class="theme" id="ayu">Ayu</button></li>
</ul>
<button id="search-toggle" class="icon-button" type="button" title="Search. (Shortkey: s)" aria-label="Toggle Searchbar" aria-expanded="false" aria-keyshortcuts="S" aria-controls="searchbar">
<i class="fa fa-search"></i>
</button>
</div>
<h1 class="menu-title"></h1>
<div class="right-buttons">
<a href="print.html" title="Print this book" aria-label="Print this book">
<i id="print-button" class="fa fa-print"></i>
</a>
</div>
</div>
<div id="search-wrapper" class="hidden">
<form id="searchbar-outer" class="searchbar-outer">
<input type="search" id="searchbar" name="searchbar" placeholder="Search this book ..." aria-controls="searchresults-outer" aria-describedby="searchresults-header">
</form>
<div id="searchresults-outer" class="searchresults-outer hidden">
<div id="searchresults-header" class="searchresults-header"></div>
<ul id="searchresults">
</ul>
</div>
</div>
<!-- Apply ARIA attributes after the sidebar and the sidebar toggle button are added to the DOM -->
<script>
document.getElementById('sidebar-toggle').setAttribute('aria-expanded', sidebar === 'visible');
document.getElementById('sidebar').setAttribute('aria-hidden', sidebar !== 'visible');
Array.from(document.querySelectorAll('#sidebar a')).forEach(function(link) {
link.setAttribute('tabIndex', sidebar === 'visible' ? 0 : -1);
});
</script>
<div id="content" class="content">
<main>
<h1 id="postgresql"><a class="header" href="#postgresql">PostgreSQL</a></h1>
<p><a href="https://www.postgresql.org/">PostgreSQL</a> is an open source database, and it is quite possibly the best general purpose tool for managing data in the world. We realize this is a bold statement; however, after almost 30 years in mid-market ERP (Enterprise Resource Planning), we know a thing about managing data. PostgreSQL is our preferred tool in its category for some very good reasons.</p>
<h2 id="toc"><a class="header" href="#toc">TOC</a></h2>
<ul>
<li><a href="#why-we-love-postgresql">Why We Love PostgreSQL</a></li>
<li><a href="#supabase">Supabase</a></li>
<li><a href="#postgresql-primer">PostgreSQL Primer</a></li>
<li><a href="#installation">Installation</a>
<ul>
<li><a href="#server-installation">Server Installation</a></li>
<li><a href="#local-installation">Local Installation</a></li>
<li><a href="#installation-notes">Installation Notes</a></li>
</ul>
</li>
</ul>
<h2 id="why-we-love-postgresql"><a class="header" href="#why-we-love-postgresql">Why We Love PostgreSQL</a></h2>
<p>The list of reasons we love PostgreSQL is long.</p>
<ul>
<li>It is commonly used and well documented.</li>
<li>It is open source.</li>
<li>It is easily accessible from anywhere in the world.</li>
<li>Its accessibility is secure and well vetted.</li>
<li>It has a vibrant online support community.</li>
<li>It makes the data we need to do our jobs immediately available.</li>
<li>It can be deployed in every corner of the world.</li>
<li>It can be deployed in hybrid cloud scenarios.</li>
<li>It is CLI first to help support AI and automation.</li>
<li>It offers a reasonably simple architecture given the nature of its purpose.</li>
<li>It scales massively for small to medium organizations.</li>
<li>It does its job (data) quite well.</li>
<li>And, most importantly it has great synergy with the rest of the chuck-stack.</li>
</ul>
<p>Here are some bullets that are unique to PostgreSQL that make it significantly better than its peers for the chuck-stack.</p>
<ul>
<li>It can hold almost any type of data: relational, GIS, vector, document, json, and more...</li>
<li>It is not enough to simply say it can hold this data - it is better than almost all of its peers who often specialize in these types of data.</li>
<li>Data replication is predictable.</li>
<li>Data replication is configurable (streaming vs logical).</li>
<li>Data backup strategies are numerous, robust and well documented.</li>
<li>There are PostgreSQL drivers, adapters, connectors for almost every major platform (OS and application).</li>
<li>It is fast!</li>
<li>It can read from many foreign data containers via FDW (Foreign Data Wrappers) - <a href="https://wiki.postgresql.org/wiki/Foreign_data_wrappers">see the list</a>.</li>
</ul>
<p>References;</p>
<ul>
<li><a href="https://www.youtube.com/watch?v=3JW732GrMdg">PostgreSQL for Everything</a></li>
</ul>
<h2 id="supabase"><a class="header" href="#supabase">Supabase</a></h2>
<p><a href="https://supabase.com/">Supabase.com</a>, a hosted PostgreSQL database and added-value services organization, deserves special acknowledgement here. They are in part why the chuck-stack exists. They helped us see and validate a better path forward.</p>
<p>They proposed that the database is one of the best places to add your role and business logic management. Doing so has the following benefits:</p>
<ul>
<li>PostgreSQL has an amazingly capable user/role archetypes, and it is quite possible one of the best in the world</li>
<li>If you maintain your roles in the database:
<ul>
<li>You only define role and data access logic once</li>
<li>If you can't see it in the database, you cannot see it anywhere (ERP, BI, web, ...)</li>
<li>If users cannot violate data access policies in the database, why not let everyone connect directly</li>
<li>AI knows SQL as well as any language</li>
<li>Give users a kind AI with knowledge of your system, and let them ask for what they need</li>
</ul>
</li>
<li>If you maintain your business logic in the database:
<ul>
<li>Any tool can execute the appropriate logic on behalf of the user</li>
<li>AI can start augmenting and automating tasks for you</li>
</ul>
</li>
</ul>
<p>All the sudden, you can start imagining a world where users can start to self-help with minimal involvement from IT. IT can now create systems that are simply bound by roles, and the database and logic (in the DB) helps ensure integrity.</p>
<p>You are left with a thinly veiled system that is easy to create, test, deploy, automate and improve. It is quite magical!</p>
<h2 id="postgresql-primer"><a class="header" href="#postgresql-primer">PostgreSQL Primer</a></h2>
<p>Content below this section is intended to provide reference material that gets you up and running with PostgreSQL as quickly as possible.</p>
<h2 id="installation"><a class="header" href="#installation">Installation</a></h2>
<p>There are two scenarios when you want to install PostgreSQL related to chuck-stack.</p>
<ol>
<li>Server - Production or UAT/Test environments running NixOS</li>
<li>Local - development environment running any Linux distribution where Nix is installed</li>
</ol>
<h3 id="server-installation"><a class="header" href="#server-installation">Server Installation</a></h3>
<p>The chuck-stack uses NixOS to manage server packages and configuration. Here are two Nix configuration files that work together to install and configure PostgreSQL for the chuck-stack.</p>
<ul>
<li><a href="https://github.com/chuckstack/chuck-stack-nixos/blob/main/nixos/postgresql.nix">postgresql.nix</a> - base PostgreSQL installation</li>
<li><a href="https://github.com/chuckstack/chuck-stack-nixos/blob/main/nixos/stk-core.nix">stk-core.nix</a> - extends the base PostgreSQL installation with the chuck-stack core application framework.</li>
</ul>
<h3 id="local-installation"><a class="header" href="#local-installation">Local Installation</a></h3>
<p>The chuck-stack uses the Nix package manager's <code>nix-shell</code> feature to deploy and configure a local base PostgreSQL installation. Here are two different examples of nix-shell installing and configuring PostgreSQL.</p>
<p>The chuck-stack todo test <a href="https://github.com/chuckstack/chuck-stack-core/blob/main/test/shell.nix">shell.nix</a> installs PostgreSQL and configures it with a specific database and runs database migrations. This file is used as part of the <a href="https://github.com/chuckstack/chuck-stack-core">chuck-stack-core</a> test suite.</p>
<h3 id="installation-notes"><a class="header" href="#installation-notes">Installation Notes</a></h3>
<p>Notes about installing and using PostgreSQL:</p>
<ul>
<li>It is sometimes desirable to install multiple 'clusters' of PostgreSQL on a single server. This is especially true when you are first learning the chuck-stack or you are hosting multiple small databases.</li>
<li>The above nix-shell local installation instructions show you how to create a PostgreSQL cluster in a specific location so that you can create multiple clusters if needed.</li>
<li>As a general rule, we will disable TCP access to PostgreSQL to maximize security. You should only enable TCP access when you have a good reason to do so.</li>
<li>Once you have created a database 'cluster', you can then create one or more 'databases' inside that cluster.</li>
<li>It is important to note the database needs of a small organization with just a few users are different than the needs of a medium organization with hundreds or thousands of users. We will start small and finish big.</li>
</ul>
<br>
<p>Copyright © 2024-<script>document.write(new Date().getFullYear())</script>, CHUBOE LLC. All rights reserved.</p> <!--chuboe changed-->
</main>
<nav class="nav-wrapper" aria-label="Page navigation">
<!-- Mobile navigation buttons -->
<a rel="prev" href="tool-linux.html" class="mobile-nav-chapters previous" title="Previous chapter" aria-label="Previous chapter" aria-keyshortcuts="Left">
<i class="fa fa-angle-left"></i>
</a>
<a rel="next prefetch" href="tool-postgrest.html" class="mobile-nav-chapters next" title="Next chapter" aria-label="Next chapter" aria-keyshortcuts="Right">
<i class="fa fa-angle-right"></i>
</a>
<div style="clear: both"></div>
</nav>
</div>
</div>
<nav class="nav-wide-wrapper" aria-label="Page navigation">
<a rel="prev" href="tool-linux.html" class="nav-chapters previous" title="Previous chapter" aria-label="Previous chapter" aria-keyshortcuts="Left">
<i class="fa fa-angle-left"></i>
</a>
<a rel="next prefetch" href="tool-postgrest.html" class="nav-chapters next" title="Next chapter" aria-label="Next chapter" aria-keyshortcuts="Right">
<i class="fa fa-angle-right"></i>
</a>
</nav>
</div>
<script>
window.playground_copyable = true;
</script>
<script src="elasticlunr.min.js"></script>
<script src="mark.min.js"></script>
<script src="searcher.js"></script>
<script src="clipboard.min.js"></script>
<script src="highlight.js"></script>
<script src="book.js"></script>
<!-- Custom JS scripts -->
</div>
</body>
</html>