Database backup, upgrade, and restore

We recommend backing up your database regularly, and especially prior to upgrading Speckle. Backing up a database is often a requirement of a database upgrade, migrating your database between servers, or recovering from an incident.

TL;DR

Speckle's preferred method of backing up and restoring data in a Postgres database is with pgAdmin (opens new window). This guide assumes you are running Speckle via Docker-Compose, perhaps a manual setup or a DigitalOcean 1-click setup.

Using pgAdmin

Prerequisites

For DigitalOcean 1-click deployments

1. Log in to DigitalOcean and find your Droplet which is running Speckle
2. Connect to your DigitalOcean Droplet, full instructions can be found in DigitalOcean's documentation (opens new window).

For Docker-Compose ('manual setup') deployments

1. Docker should be installed and running (opens new window).
2. We expect you to have followed the instructions for manual installation

Ensure Postgres and pgAdmin are running

1. Determine if pgAdmin is already installed and running. From your terminal window, request the list of running processes from Docker. Full details of this command can be found in Docker's documentation (opens new window).

   docker ps --filter name='pgadmin'
   

2. If pgAdmin is already running, you should see something like the following output. If so, you can move on to the next section.

   CONTAINER ID   IMAGE            COMMAND            CREATED      STATUS          PORTS                              NAMES
   04fb5cfc43e9   dpage/pgadmin4   "/entrypoint.sh"   8 days ago   Up 53 minutes   443/tcp, 127.0.0.1:16543->80/tcp   speckle-server-pgadmin-1
   

3. Save the following Docker-Compose configuration in a file named docker-compose-pgadmin.yml

   version: '3'
   services:
     pgadmin:
       image: dpage/pgadmin4
       restart: always
       environment:
         PGADMIN_DEFAULT_EMAIL: 'admin@localhost.com'
         PGADMIN_DEFAULT_PASSWORD: 'admin'
       volumes:
         - pgadmin-data:/var/lib/pgadmin
       ports:
         - '127.0.0.1:16543:80'
   
   volumes:
     pgadmin-data:
   

4. Start the pgAdmin using Docker-Compose:

   docker-compose --file docker-compose-pgadmin.yml up --detach
   

5. Verify that pgAdmin is now running:

   docker ps --filter name='pgadmin'
   

Connecting to pgAdmin

Connecting to pgAdmin running in Docker on your local machine

1. Find the port that pgAdmin is being served on using Docker's ps command (opens new window).

   docker ps --filter name='pgadmin'
   

2. The above command should produce something like the below. In this example, the port we are interested in is the port exposed on our localhost (127.0.0.1), this port is 5432.

   CONTAINER ID   IMAGE            COMMAND            CREATED       STATUS       PORTS                              NAMES
   465897401a96   dpage/pgadmin4   "/entrypoint.sh"   2 hours ago   Up 2 hours   443/tcp, 127.0.0.1:16543->80/tcp   speckle-server-pgadmin-1
   

3. Open the pgAdmin dashboard (opens new window) in your browser. If you have changed the configuration of pgAdmin, you can find how to access it by running docker ps --filter name='pgadmin' and making a note of the host and port it is being served on.

Connecting to pgAdmin running in a DigitalOcean 1-click Droplet

1. From your local machine, create an SSH tunnel to your DigitalOcean droplet. Replace DROPLET_USERNAME with the username for your droplet, and DROPLET_IP_ADDRESS with the IP address of your Droplet; more information on how to find these can be found in DigitalOcean's documentation (opens new window).

   ssh -L 16543:localhost:16543 DROPLET_USERNAME@DROPLET_IP_ADDRESS
   

For those who are interested, more information about SSH tunnels can be found in DigitalOcean's Documentation (opens new window)

Connecting pgAdmin to Postgres

1. The user name and password can be found next to PGADMIN_DEFAULT_EMAIL and PGADMIN_DEFAULT_PASSWORD in your configuration file for pgAdmin.

   • The default values we provided in our docker-compose-pgadmin.yml file are admin@localhost.com and the password admin.

   

2. Click add new server.

   

3. In the dialog box in the General tab, enter the name docker-compose.

4. In the dialog box in the Connection tab:

   • for the Host name/addresses enter the name of the Postgres container, in our example it is speckle-server-postgres-1
     • the name of the Postgres container can be found with docker ps --filter name='postgres'
   • for port, use 5432 (or the value discovered when running docker ps --filter name='postgres', see instructions above).
   • for database, username, and password use the values in your configuration assigned to the respective POSTGRES_DB, POSTGRES_USER, and POSTGRES_PASSWORD keys. If installed with Speckle's manual installation or DigitalOcean 1-click, by default the value of these are all speckle.

5. (optional) For some databases, but not the default database provided by Speckle, you may also have to provide a Certificate.

   1. Copy this certificate into the pgadmin container with docker cp ./local/path/to/my/certificate.crt speckle-server-pgadmin-1:/var/lib/pgadmin/storage/admin_localhost.com/ (assuming the default pgadmin user is admin@localhost.com and speckle-server-pgadmin-1 is the name of the pgAdmin container.)
   2. In the dialog box in the SSL tab, select the certificate within the Root Certificate input.

   

6. Click save.

Backing up Postgres

1. You can now select the database from the browser window (typically found on the left hand side). Expand docker-compose and databases to select your database. The default database name for Speckle is speckle.

2. Once you have found the database in pgAdmin, right-click the database name and select backup.

   

3. Provide a filename in the general tab.

   

4. In the options tab, select the options Include CREATE DATABASE statement and Include DROP DATABASE statement.

5. Then start the backup process. This will generate a backup that is stored in the container's volume.

6. Once the backup is prepared, we now need to save the file to your local machine.

   • If a Click to open file location button is displayed (this may depend on the version of pgAdmin running), click on it and download the backup to your machine.

   • If a button is not displayed, you can copy the file from the container to your machine by user docker. Replace speckle-server-pgadmin-1 with the name of your pgAdmin container and BACKUP_FILE_NAME with the file name you provided in the pgAdmin dialog.

     docker cp speckle-server-pgadmin-1:'/var/lib/pgadmin/storage/admin_localhost.com/BACKUP_FILE_NAME' .
     

   • If connected via SSH to a DigitalOcean or other virtual or remote machine, you will have to undertake further steps to move the file from the host machine to either your local machine or storage location of your choice. DigitalOcean suggest using scp (opens new window).

7. We recommend testing the integrity of the backup at this time. One way of doing this is to install speckle-server on another machine (or modify the name and ports in the file and install a second locally), then upload the backup per the below instructions using pgAdmin. Use pgAdmin or Speckle to check that the information is present.

8. Store the backup in a safe and secure location. You may wish to copy the backup to multiple secure locations to provide redundancy.

Backing up a Docker volume (optional)

As well as creating a Postgres backup, we also recommend a full Docker volume backup. The Docker volume backup can only be used with the current version of Postgres, so is more limited than the Postgres backup created via pgAdmin (the latter allows us to move data between different versions of Postgres, which is useful when upgrading). Generating an additional backup from the Docker volume provides us with additional confidence that we can recover to a previous good working state.

1. Find the name of the Postgres database volume.

   docker volume ls --filter name='postgres'
   

2. Follow the instructions on backing up a Docker Volume in Docker's Documentation (opens new window)

3. Store the backup in a safe and secure location. You may wish to copy the backup to multiple secure locations to provide redundancy.

If you are not upgrading the Postgres database, you can stop at this step. The following instructions relate only to the event of a major version upgrade of Postgres.

Preparing Postgres for an upgrade

1. Determine the name of the Docker container that is running your Postgres database, and the version of Postgres.

   docker ps --filter name='postgres'
   

2. If successful, the above will print out something similar to the below. In our example the name is speckle-server-postgres-1 and the image version 13-alpine.

   CONTAINER ID   IMAGE                COMMAND                  CREATED       STATUS       PORTS                      NAMES
   510e024a4700   postgres:13-alpine   "docker-entrypoint.s…"   2 hours ago   Up 2 hours   127.0.0.1:5432->5432/tcp   speckle-server-postgres-1
   

3. ⚠️ CAUTION This step will cause Speckle, and any other application relying on the database, to stop working until the database is restored. You may wish to notify users of your Speckle server about the disruption to Speckle before proceeding with this and the following steps. Stop your Postgres database with the following command, replacing speckle-server-postgres-1 with the name of your container.

   docker stop speckle-server-postgres-1
   

4. Remove the container from docker, this detaches it from its volume and allows the volume to be deleted. Replace speckle-server-postgres-1 with the name of your container.

   docker rm speckle-server-postgres-1
   

5. Find the name of the Postgres database volume.

   docker volume ls --filter name='postgres'
   

6. During upgrades we recommend undertaking a Docker volume backup. This allows us to restore to the previous version if the upgrade was unsuccessful. See the instructions for undertaking a Docker volume backup in the section above.

7. ⚠️ CAUTION This step is destructive and cannot be undone, please ensure that the backup is correct and saved securely before proceeding - see instructions above. Delete the volume storing your Postgres database, replacing speckle-server_postgres-data with the name of your Postgres volume.

   docker volume rm speckle-server_postgres-data
   

Upgrading Postgres

1. If you installed Speckle via a DigitalOcean 1-click setup, please SSH into the Droplet (instructions in section above). If you installed Docker using a manual setup, please log into the machine in which you installed it.

2. Using git, clone the Speckle Server repository. More details instructions are available in GitHub's documentation.

   git clone https://github.com/specklesystems/speckle-server.git
   

3. Navigate into the directory of the cloned repository.

   cd speckle-server
   

4. Check out the version of Speckle Server you wish to deploy. For example, using git on the command line to checkout a tagged version - in this case Speckle Server version 2.9.0 (please change to the latest released version (opens new window), or the version you wish to deploy):

   git checkout tags/2.9.0 -b main
   

5. Deploy the docker-compose file with the updated Postgres version:

   docker-compose -f ./docker-compose-deps.yml up --detach
   

6. Verify everything is up and running by viewing the status of all containers:

   docker ps
   

Restoring your data to Postgres from pgAdmin backup

1. If connected via SSH to a DigitalOcean or other virtual or remote machine, you will have to undertake steps to move the file your local machine or storage location to the host machine (Droplet). DigitalOcean suggest using scp (opens new window).

2. Copy the file from the host machine to the pgAdmin container by user Docker. Replace speckle-server-pgadmin-1 with the name of your pgAdmin container and BACKUP_FILE_NAME with the file name you provided in the pgAdmin dialog.

   docker cp BACKUP_FILE_NAME speckle-server-pgadmin-1:'/var/lib/pgadmin/storage/admin_localhost.com/'
   

3. Connect to pgAdmin using the instructions above.

4. Connect pgAdmin to the server using the instructions above.

5. Right-click databases from the browser window, and click create. The name of the database should be speckle.

6. Right-click the created database and click restore, selecting your previously stored backup. You will need to upload the backup you previously downloaded back into pgAdmin first.

   

7. If you encounter errors at this point, it may be because pgAdmin considers the database to be a maintenance database. To workaround this issue please try the following:

   1. Create another (maybe empty) database.
   2. Disconnect from the Speckle database.
   3. Edit server properties for other database.
   4. On the connection tab set the other database as Maintenance database.
   5. Reconnect to the Speckle database.

8. Deploy Speckle server, if not already deployed, and verify the data is correct.

Congratulations! - your data has been backed up, your database has been upgraded, and your data restored. We recommend undertaking the backup steps regularly, even if you are not upgrading your database.