add timescale migration & clean up postgres stuff (coder#607)

ericpaulsen · Katie Horne · web-flow · commit e17e2d834ef5 · 2021-09-20T08:25:18.000-04:00
* add timescale migration &amp; clean up postgres stuff

* colin's feedback

* Edit text

* Clarify user

* Update manifest

* fix code blocks

Co-authored-by: Katie Horne &lt;katie@coder.com&gt;
diff --git a/guides/admin/timescale-migration.md b/guides/admin/timescale-migration.md
@@ -0,0 +1,60 @@
+---
+title: Database migration
+description: Learn how to migrate data from Coder's built-in database.
+---
+
+By default, Coder deploys a built-in database in the installation's Kubernetes
+namespace. We recommend using this database _only_ for evaluation purposes.
+
+At the end of your evaluation period, you may need to migrate the data from the
+built-in database to an out-of-cluster PostgreSQL database for production use.
+This article will walk you through the process of doing so.
+
+> You must be a cluster admin for your Kubernetes cluster.
+
+## Migration Steps
+
+1. Access the database pod and dump the database into a file:
+
+   ```console
+   kubectl exec -it statefulset/timescale -n coder -- pg_dump -U coder -d coder > backup.sql
+   ```
+
+1. **Optional**: If your database is large, you can truncate Coder's telemetry,
+   metrics, and audit log data to reduce the file size:
+
+   ```sql
+   TRUNCATE metric_events;
+   TRUNCATE environment_stats;
+   TRUNCATE audit_logs;
+   ```
+
+1. Access your PostgreSQL instance and create user `coder` and database `coder`
+
+1. Import the data you exported in the first step into your external database:
+
+   ```sql
+   psql -U coder < backup.sql
+   ```
+
+1. Connect your Coder instance to the database:
+
+   ```console
+   helm upgrade --reuse-values -n coder coder coder/coder \
+       --set postgres.default.enable=false \
+       --set postgres.host=<HOST_ADDRESS> \
+       --set postgres.port=<PORT_NUMBER> \
+       --set postgres.user=<DATABASE_USER> \
+       --set postgres.database=<DATABASE_NAME> \
+       --set postgres.passwordSecret=<secret-name> \
+       --set postgres.sslMode=require
+   ```
+
+1. **Optional**: If you'd like to delete the Timescale persistent volume, run:
+
+   ```console
+   kubectl delete pvc timescale-data-timescale-0 -n coder
+   ```
+
+At this point, you should be able to log in to your Coder deployment
+successfully.
diff --git a/guides/deployments/postgres.md b/guides/deployments/postgres.md
@@ -16,6 +16,8 @@ streamline maintenance operations, such as backups and upgrades. The database
 state is _not_ backed up and will be lost when deleting the Kubernetes namespace
 or cluster.
 
+## Configuration steps
+
 > For optimal performance, it is important to ensure that the round-trip latency
 > between the Coder control plane services and the database is low. We recommend
 > ensuring that the database is within the same data center as the control
diff --git a/manifest.json b/manifest.json
@@ -300,6 +300,9 @@
             {
               "path": "./guides/admin/resources.md"
             },
+            {
+              "path": "./guides/admin/timescale-migration.md"
+            },
             {
               "path": "./guides/admin/helm-charts.md"
             },
diff --git a/setup/installation.md b/setup/installation.md
@@ -84,7 +84,8 @@ kubectl config set-context --current --namespace=coder
 
    ```yaml
    postgres:
-     useDefault: false
+     default:
+      enable: false
      host: HOST_ADDRESS
      port: PORT_NUMBER
      user: YOUR_USER_NAME

Original file line number	Diff line number	Diff line change
`@@ -300,6 +300,9 @@`
`300`	`300`	`{`
`301`	`301`	`"path": "./guides/admin/resources.md"`
`302`	`302`	`},`
	`303`	`+ {`
	`304`	`+ "path": "./guides/admin/timescale-migration.md"`
	`305`	`+ },`
`303`	`306`	`{`
`304`	`307`	`"path": "./guides/admin/helm-charts.md"`
`305`	`308`	`},`