Automate backing up Standard Notes database

[zcyph]

Hello,

After figuring out my issue with deleting users that I posted about on Reddit, it was suggested that I start a thread over here about my other question which was how to go about backing up the Standard Notes database. I hope that's ok!

As I expand my self hosting journey, I'm getting into the habit of using small Python scripts + cronjobs to automate backing up things. For Bitwarden, I use the SQLite command to backup the database "properly" rather than just copying the file, for Nextcloud I use the Snap, so I stop Nextcloud, use the built in export function to export only the database, then start it up again. Cron tells the script to run nightly. I'm looking to do something like this for Standard Notes now.

As I've just set up Standard Notes (took me quite a while to figure it out tbh), I want to make sure I've got this right. The docker-compose I've strung together that handles MySQL + Standard Notes sync-server + its web client shows a line under MySQL section - "dbdata:/var/lib/mysql" which appears to store its data at /var/lib/docker/volumes/standard-notes-server_dbdata/ on the host machine.

So if I back up /var/lib/docker/volumes/standard-notes-server_dbdata/, should I expect to then be able to restore my notes by simply re-copying over that folder on a fresh install? I suppose I should just go ahead and test it out rather than asking, but i figured it can't hurt to get some input from more knowledgeable people (maybe it's a terrible idea or there's just a way better way to go about it). Now that I'm explaining this idea to other people it's starting to feel like it's probably not the way to do it lol.

Thanks in advance for any pointers!

[moughxyz]

I believe that that DB would be persistent at that same location, but @karolsojko would know best here.

[karolsojko]

As I've just set up Standard Notes (took me quite a while to figure it out tbh)

We are sorry for the inconvenience. To give some more context we are in the midst of switching to a new syncing server and the documentation is pending. So hopefully we'll have that in place and it will be a much smoother process to setup.

So if I back up /var/lib/docker/volumes/standard-notes-server_dbdata/, should I expect to then be able to restore my notes by simply re-copying over that folder on a fresh install?

If you are using docker-compose we recommend using the one we provide. As you can see there are many more services that are required in the setup than just Syncing Server + MySQL + Web Client: https://github.com/standardnotes/syncing-server/blob/develop/docker-compose.yml

As for the backup, we mount the /data folder unto the MySQL container in here: https://github.com/standardnotes/syncing-server/blob/develop/docker-compose.yml#L63

So if you backup your /data folder from the project's directory then you should be able to recreate your database.

[zcyph]

We are sorry for the inconvenience

Not at all, while not a developer myself I have an inkling as to the amount of work involved, so I'm very thankful. I'm a huge fan of projects that find a way to balance monetization while staying true to the spirit of free & open source (almost sounds like an oxymoron).

I did try following the official steps but sadly could never get them working. After comparing the setup i managed to cobble together again I see that what you have mounted as ./data (/var/lib/mysql) is the same thing as what I had set to dbdata in mine.

So I remounted mine at ./data and copied over the folder I had at /var/lib/docker/volumes/standard-notes-server_dbdata/_data there to see if that works and all seems to be good. For automating the backup I just have the script shut down the containers first, then do the copy and start the containers up again.

Thanks for taking the time to respond. For anyone that's curious, what worked for me was this docker compose file. Note that I renamed the .env files as I just wanted them both in the same place, just personal preference:

version: '3'

services:
  mysql:
    image: mysql:latest
    restart: always
    security_opt:
      - "seccomp:unconfined"
    container_name: Standard-Notes-MySQL
    environment:
      MYSQL_ROOT_PASSWORD: changeme
      MYSQL_DATABASE: standard_notes_db
      MYSQL_USER: std_notes_user
      MYSQL_PASSWORD: changeme
    ports:
      - "3306:3306"
    volumes:
      - ./data:/var/lib/mysql
  standard-notes-server:
    image: standardnotes/syncing-server:stable
    restart: always
    container_name: Standard-Notes-Server
    env_file: server.env
    depends_on:
      - mysql
    ports:
      - 3000:3000
  standard-notes-web:
    image: standardnotes/web:stable
    restart: always
    container_name: Standard-Notes-Web
    env_file: web.env
    ports:
      - 3001:3001

And the dirty Python script I use for the backup that uses webdavclient to upload it to Nextcloud:

#! /usr/bin/python
import webdav.client, os, time

# configure & initialize webdav client
webdavconfig = {'webdav_hostname': "https://cloud.yourdomain.com/remote.php/dav/files/YourUser/", 'webdav_login': "YourUser", 'webdav_password': "changeme", 'webdav_root': "/"}
wc = webdav.client.Client(webdavconfig)

# set datestamp and file paths
datestamp = time.strftime("%Y-%m-%d")
sourcefiles = '/home/YourUser/Docker/standard-notes-server/data'
targetfiles = f'Backup/standard-notes-backup/{datestamp}/'

# stop Standard Notes containers
os.chdir('/home/YourUser/Docker/standard-notes-server')
print("Stopping containers...")
os.system('docker-compose stop')
print("Containers stopped.")

# make sure the destination folders exist
if not wc.check("Backup/standard-notes-backup"):
    print("Creating Nextcloud folder: /Backup/standard-notes-backup")
    wc.mkdir("Backup/standard-notes-backup")
if not wc.check(f"Backup/standard-notes-backup/{datestamp}"):
    print(f"Creating Nextcloud folder: /Backup/standard-notes-backup/{datestamp}")
    wc.mkdir(targetfiles)

# upload the data to Nextcloud
print("Uploading Standard Notes backup to Nextcloud...")
wc.upload_sync(remote_path=targetfiles, local_path=sourcefiles)
print("Upload complete.")

# start Standard Notes containers
print("Starting containers...")
os.system('docker-compose start')
print("Containers started.")

Thanks again and I hope this thread might be helpful for anyone else passing by.

[karolsojko]

Thanks for sharing this :) will close the issue as it is resolved. Please feel free to reopen if needed.