Backup and Restore Guide for Coder Installations – Coder

Coder provides no formal Backup or Restore Guide or Method. These are 'standard' PostgreSQL instructions, compiled in a format that should make it easier to build your own backups.

It applies to both local deployments and Kubernetes-based environments.

A managed PostgreSQL database, with daily backups, is recommended! Coder recommends scheduling regular database backups, especially before upgrading Coder to a new release.

A few articles about PostgreSQL in Coder Docs you should be familiar with before continuing:

Are you an advanced user?

You may consider utilizing snapshots and remote filesystems as demonstrated in this example script, developed by Coder. While this is not an officially supported solution, users are encouraged to experiment with it at their discretion. Please note that users undertake all associated risks, including potential data deletion. We welcome feedback on your experience to explore possible improvements. You can find the script here: https://coder.zendesk.com/guide-media/01JVCNKGPRKKR3EHSRF5T45RHV

Prerequisites

Access to a running Coder installation
Shell access to the host or Kubernetes cluster
PostgreSQL client tools (psql, pg_dump)
Access to environment secrets or configuration values
Coder Version 2.X, this article should work well with 2.18+

Backup Process

1. Locate Database Credentials

For Local/Single-Host Installations:

Retrieve the database URL and password:

sudo -u coder coder server postgres-builtin-url
cat ~coder/.config/coderv2/postgres/password

Example connection:

# this will find the details you can add to Env variables
sudo -u coder coder server postgres-builtin-url
psql "postgres://coder@localhost:36717/coder?sslmode=disable&password=$PASSWORD"

For Kubernetes Installations:

Extract the database URL from the secret:

POSTGRES_URL=$(kubectl -n coder get secret coder-db-url -o json | jq -r .data.url | base64 -d)
echo "$POSTGRES_URL"

Example format:

postgres://coder:$PASSWORD@$PGHOST:$PGPORT/coder?sslmode=disable&search_path=myschema

2. Simplify Authentication with .pgpass

Create and configure a .pgpass file:

# This works on Kubernetes, see below for 'local instance' methods to pull the variables
POSTGRES_URL=$(kubectl -n coder get secret coder-db-url -o json | jq -r .data.url | base64 -d)
export PGPORT=$(echo $POSTGRES_URL | sed -E 's/postgres:\/\/[^:]+:[^@]+@[^:]+:([0-9]+)\/.+/\1/')
export PGPASSWORD=$(echo $POSTGRES_URL | sed -E 's/postgres:\/\/[^:]+:([^@]+)@.+/\1/')
# Create .pgpass to automate login
echo "127.0.0.1:$PGPORT:coder:coder:$PGPASSWORD" >> ~/.pgpass
chmod 0600 ~/.pgpass
export PGPASSFILE=~/.pgpass

3. Create a Backup

Standard backup command:

pg_dump -bcC -f coder-backup-$(date +%s).db -h 127.0.0.1 -p $PGPORT -U coder -d coder

Compressed backup:

pg_dump -bcC -h 127.0.0.1 -p $PGPORT -U coder -d coder | gzip > coder-backup-$(date +%s).db.gz

For Kubernetes, you can use port forwarding to access the database:

kubectl port-forward -n coder svc/coder-db-postgresql 5432:5432
# Then run pg_dump with localhost and optional port 5432 (default)
# Grab the Password since we don't have it yet:
# echo "Use the password if needed: $PGPASSWORD" ; echo
pg_dump -bcC -h 127.0.0.1 -U coder -p $PGPORT | gzip > coder-backup-$(date +%s).db.gz

Restore Process

1. Pause the Coder Application

For Kubernetes:

kubectl scale deployment -n coder coder --replicas=0

For local/VPC:

sudo systemctl stop coder

2. Restore the Database

# Including options to drop DB because of nuances with open DB, and then restoring if the DB is not present
psql -d postgres
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'coder' AND pid <> pg_backend_pid();
# Danger! Line commented out because it is inherently dangerous
### drop database coder;
#
# Backup file with DROP will fail if there is no DB to drop, so we don't run that step:
sed -i 's/DROP DATABASE/# DROP DATABASE/' backup-file.db

# Otherwise if there are no obstacles, this is the only required action that needs to succeed:
# Line commented out because it is inherently dangerous
### psql -d postgres -h 127.0.0.1 -p $PGPORT -U coder < ./backup-file.db

Note: Backups made with the `-bcC` option will drop and recreate the database. Please make sure the target database can be safely replaced.

3. Restart the Application

For Kubernetes:

kubectl scale deployment -n coder coder --replicas=1

For local/VPC:

sudo systemctl start coder

Validation Steps

After restoring the database:

1. Verify you can log in to Coder
2. Check that dashboards and workspace lists are displayed correctly
3. Confirm workspace functionality
4. Review system logs/journal/k8s events/pod(s) for any errors

Important Notes

- PostgreSQL role/user accounts are not included in backups
- Preserve the `search_path` parameter from the connection string and check you've restored all the schemas.
- The `search_path` may indicate a SCHEMA that may need to be recreated - https://coder.com/docs/tutorials/external-database#custom-schema

- Always store backups securely
- Test your backup and restore process regularly
- The backup file by default is a text file; you can check the presence of known data
- You can compress the backup file to reduce the size

Troubleshooting

If your PostgreSQL server only listens on localhost, check the process:

ps -ef | grep postgres

Look for the `-p` flag which indicates the port, e.g., `-p 36717` or `-p 5432`.

For connection issues, verify the PostgreSQL server is accepting connections from your client's IP address by checking the pg_hba.conf file.

Should your configuration use an external PostgreSQL server, your pg_hba.conf may restrict the access to the database in ways that are not covered here.

Need a little time saving and optimisation?

These commands can help you extract helpful PG* variables and save some typing in conjunction with the .pgpass which will also avoid printing sensitive entries on your console or in log files.

# Get the Postgres URL - Kubernetes/Rancher/OpenShift
POSTGRES_URL=$(kubectl -n coder get secret coder-db-url -o json | jq -r .data.url | base64 -d)

# Extract components using sed
export PGUSER=$(echo $POSTGRES_URL | sed -E 's/postgres:\/\/([^:]+):.+/\1/')
export PGPASSWORD=$(echo $POSTGRES_URL | sed -E 's/postgres:\/\/[^:]+:([^@]+)@.+/\1/')
export PGHOST=$(echo $POSTGRES_URL | sed -E 's/postgres:\/\/[^@]+@([^:]+):.+/\1/')
export PGPORT=$(echo $POSTGRES_URL | sed -E 's/postgres:\/\/[^:]+:[^@]+@[^:]+:([0-9]+)\/.+/\1/')
export PGDATABASE=$(echo $POSTGRES_URL | sed -E 's/postgres:\/\/[^\/]+\/([^\?]+).+/\1/')
export PGOPTIONS=$(echo $POSTGRES_URL | grep -o 'search_path=[^&]*' | sed 's/search_path=//')

For the second format (built-in URL):

# Get the Postgres URL
POSTGRES_URL=$(sudo -u coder coder server postgres-builtin-url | grep -o 'postgres://.*')
# Alternatively cat ~coder/.config/coderv2/postgres/password
# Parse the POSTGRES_URL with simple parameter extraction
if [[ $POSTGRES_URL == postgres://*:*@* ]]; then
 # Expected format with username:password@host
 export PGUSER=$(echo $POSTGRES_URL | sed -E 's/postgres:\/\/([^:]+):.+/\1/')
 export PGPASSWORD=$(echo $POSTGRES_URL | sed -E 's/postgres:\/\/[^:]+:([^@]+)@.+/\1/')
else
 # Format with just username@host
 export PGUSER=$(echo $POSTGRES_URL | sed -E 's/postgres:\/\/([^@]+)@.+/\1/')
 # Password might be in a query parameter
 export PGPASSWORD=$(echo $POSTGRES_URL | grep -o 'password=[^&]*' | sed 's/password=//')
fi
# Is your Password somewhere else?
# export PGPASSWORD=$( kubectl -n coder get secret coder-db-postgresql -o json | jq -r '.data."postgres-password"' | base64 --decode )

export PGHOST=$(echo $POSTGRES_URL | sed -E 's/postgres:\/\/[^@]+@([^:]+):.+/\1/')
export PGPORT=$(echo $POSTGRES_URL | sed -E 's/postgres:\/\/[^:]+(@[^:]+:|:[^@]+@[^:]+:)([0-9]+)\/.+/\2/')
# If there is no :PORT in the string:
if [[ ! $PGPORT =~ ^[0-9]+$ ]]; then
export PGPORT=5432
fi
export PGDATABASE=$(echo $POSTGRES_URL | sed -E 's/postgres:\/\/[^\/]+\/([^\?]+).+/\1/')
export PGOPTIONS=$(echo $POSTGRES_URL | grep -o 'search_path=[^&]*' | sed 's/search_path=//')

Follow this with the .pgpass creation:

echo "$PGHOST:$PGPORT:$PGDATABASE:$PGUSER:$PGPASSWORD" >> ~/.pgpass
chmod 0600 ~/.pgpass
export PGPASSFILE=~/.pgpass

These scripts will extract the relevant parts from either URL format and set them as environment variables.

Caveats and warnings

This is not provided as a supported service or supported by Coder, but a 'best effort' solution to the question 'How do I backup Coder?'

Any loss of data or damage to your systems is your responsibility.

By using backup and 'delete' of resources is not guaranteed to shut down all resources, resource groups or 'cloud' objects that could have been started or running.

By making a backup, especially from a running Coder instances, is a 'snapshot in time'. This snapshot may contain running Workspaces and other entities that may have changed once your database is restored and can cause artifacts or problems with your deployment.

The 'ultimate' or 'best' backup is an 'offline backup', where the desired state of Workspaces and Coder is 'Stopped'.

By leaving Workspaces and/or Coder, the database itself and any other pods in 'Running' during the backup will make the restoration potentially more difficult and you will likely need to clean up some data, pods or handle issues post restoration.

Related to

coder-disaster-recovery-plan.md
10 KB Download

Are you an advanced user?

Prerequisites

Backup Process

1. Locate Database Credentials

For Local/Single-Host Installations:

Example connection:

For Kubernetes Installations:

Example format:

2. Simplify Authentication with .pgpass

3. Create a Backup

Restore Process

1. Pause the Coder Application

For Kubernetes:

For local/VPC:

2. Restore the Database

3. Restart the Application

For Kubernetes:

For local/VPC:

Validation Steps

After restoring the database:

Important Notes

Troubleshooting

Need a little time saving and optimisation?

Caveats and warnings

Related articles