docs: Setup external PostgreSQL server

coder · mtojek · Nov 4, 2022 · Nov 4, 2022 · Nov 4, 2022 · Nov 4, 2022
commit f7b4088df8cd59238c797aceba0ab6e160cbebcb
diff --git a/docs/install/database.md b/docs/install/database.md
@@ -0,0 +1,88 @@
+## Recommendation
+
+For production deployments, we recommend using an external [PostgreSQL](https://www.postgresql.org/) database (version 13 or higher).
+
+## Basic configuration
+
+Before starting the Coder server, prepare the database server. Create a role and a database.
+Remember that the role must have access to the created database.
+
+With `psql`:
+
+```sql
+CREATE ROLE coder LOGIN SUPERUSER PASSWORD 'secret42';
+```
+
+With `psql -U coder`:
+
+```sql
+CREATE DATABASE coder;
+```
+
+Coder configuration is defined via [environment variables](../admin/configure.md).
+The database client requires the connection string provided via the `CODER_PG_CONNECTION_URL` variable.
+
+```sh
+export CODER_PG_CONNECTION_URL="postgres://coder@localhost/coder?password=secret42&sslmode=disable"
+```
+
+## Custom schema
+
+For installations with elevated security requirements, it's advised to use a separate [schema](https://www.postgresql.org/docs/current/ddl-schemas.html) instead of the public one.
+
+With `psql -U coder`:
+
+```sql
+CREATE SCHEMA myschema;
+```
+
+Once the schema is created, you can list all schemas with `\dn`:
+
+```
+     List of schemas
+     Name  |  Owner
+-----------+----------
+ myschema  | coder
+ public    | postgres
+(2 rows)
+```
+
+In this case the database client requires the modified connection string:
+
+```sh
+export CODER_PG_CONNECTION_URL="postgres://coder@localhost/coder?password=secret42&sslmode=disable&search_path=myschema"
+```
+
+The `search_path` parameter determines the order of schemas in which they are visited while looking for a specific table.
+The first schema named in the search path is called the current schema. By default `search_path` defines the following schemas:
+
+```sql
+SHOW search_path;
+
+search_path
+--------------
+ "$user", public
+```
+
+Using the `search_path` in the connection string corresponds to the following `psql` command:
+
+```sql
+SET search_path TO myschema;
+```
+
+## Troubleshooting
+
+### Coder server fails startup with "current_schema: converting NULL to string is unsupported"
+
+Please make sure that the schema selected in the connection string `...&search_path=myschema` exists
+and the role has granted permissions to access it. The schema should be present on this listing:
+
+```sh
+psql -U coder -c '\dn'
+```
+
+## Next steps
+
+- [Quickstart](../quickstart.md)
+- [Configuring Coder](../admin/configure.md)
+- [Templates](../templates.md)
diff --git a/docs/manifest.json b/docs/manifest.json
@@ -55,6 +55,11 @@
           "description": "Run Coder in offline / air-gapped environments",
           "path": "./install/offline.md"
         },
+        {
+          "title": "External database",
+          "description": "Use external PostgreSQL database",
+          "path": "./install/database.md"
+        },
         {
           "title": "Uninstall",
           "description": "Learn how to uninstall Coder",