Updating SSH storage locations' paths in Arq backup when app doesn't let you

A month or two ago I started using Arq 7 to backup my Mac to my (also recently created) rsync.net account. It was all running smoothly until a couple of days ago when I started seeing error messages about missing files.

Error: /data1/home/xx1234/7ebc7498-b7a8-4e20-9f7b-9f50c092e67f/encryptedkeyset.dat not found

A quick check reveals that the file is still there:

❯ ssh xx1234@xx1234.rsync.net ls 7ebc7498-b7a8-4e20-9f7b-9f50c092e67f/encryptedkeyset.dat

Since all commands against rsync.net servers are usually without the absolute path, could it be that my home folder got moved (Arq stores the absolute path of the backup when the storage location is created)? Why, yes, it is:

❯ ssh xx1234@xx1234.rsync.net pwd

I tried editing the storage location, but it’s not possible to change the path. Removing the storage location and creating a new one fills me with uncomfortable dread of either losing all history or requiring me to monkey patch old backups into the new location. I’d rather fill taxes.

Thus, I looked how to hack something that works. As Arq is a Mac application, it definitely stores its settings in some file and reads them from there. These files are usually .plist files, so let’s see what is available:

❯ fd -0 arq /Library ~/Library| xargs -0 sudo rg -l -uuu xx1234
/Library/Logs/ArqAgent/com.haystacksoftware.ArqAgent 2021-04-03--08-32-54-565.log
/Library/Logs/ArqAgent/com.haystacksoftware.ArqAgent 2021-03-18--21-18-38-219.log
/Library/Logs/ArqAgent/com.haystacksoftware.ArqAgent 2021-03-14--12-17-04-770.log
/Library/Logs/ArqAgent/com.haystacksoftware.ArqAgent 2021-04-03--08-32-54-565.log
/Library/Logs/ArqAgent/com.haystacksoftware.ArqAgent 2021-03-14--12-17-04-770.log
/Library/Logs/ArqAgent/com.haystacksoftware.ArqAgent 2021-03-18--21-18-38-219.log
/Library/Application Support/ArqAgent/server.db
/Library/Application Support/ArqAgent/logs/backup/backup-00161578513952-728C8593-08D4-44BD-97D2-81B6AC34B1F6
/Library/Application Support/ArqAgent/logs/backup/backup-00161515081233-BE57BFBA-2E42-46FA-8B98-3155973F1084

The only file in the list is not a .plist file, but a .db:

❯ sudo file '/Library/Application Support/ArqAgent/server.db'
/Library/Application Support/ArqAgent/server.db: SQLite 3.x database, last written using SQLite version 3024000

sqlite is an amazing piece of technology and it might be possible that Arq uses it to store settings instead of using those pseudo programming languages YAML, JSON, and TOML. Let’s dig deeper:

❯ sudo sqlite3 '/Library/Application Support/ArqAgent/server.db'
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
sqlite> .tables
activities                 restore_jobs
backup_plans               storage_locations
backup_plans_history       storage_locations_history
imported_legacy_commits    users
index_schema               users_history

storage_locations sounds exactly right:

sqlite> .schema --indent storage_locations
CREATE TABLE storage_locations(
  created_time REAL NOT NULL,
  updated_time REAL NOT NULL,
  plan_type TEXT NOT NULL,
  provider_type TEXT NOT NULL,
  username TEXT,
  password TEXT,
  private_key_path TEXT,
  private_key_passphrase TEXT,
  hostname TEXT,
  port INTEGER,
  use_ssl INTEGER,
  path TEXT,
  region_name TEXT ,
  json TEXT
sqlite> select * from storage_locations;
1|0|1614804534.0|1614804559.0|Arq Cloud Storage|backup|arqpremium||||||||||{
  "active" : false,
  "useSSL" : false,
  "displayDescription" : "Arq Cloud Storage",
  "id" : 1,
  "providerType" : "arqpremium",
  "planType" : "backup",
  "hasPrivateKeyPassphrase" : false,
  "name" : "Arq Cloud Storage",
  "hasPassword" : false
  "useSSL" : true,
  "id" : 2,
  "hasPrivateKeyPassphrase" : false,
  "hasPassword" : false,
  "displayDescription" : "SFTP:/data1/home/xx1234",
  "active" : true,
  "hostname" : "xx1234.rsync.net",
  "path" : "/data1/home/xx1234",
  "providerType" : "sftp",
  "planType" : "backup",
  "username" : "xx1234",
  "privateKeyPath" : "/Users/knl/.ssh/id_rsync_net",
  "name" : "xx1234.rsync.net",
  "port" : 22

Definitely, absolutely, positively the right thing. Now, the last field is JSON, sadly. Meaning I could use the following lovely construct that utilizes sqlite’s JSON1 extension:

UPDATE storage_locations SET json =
  (SELECT json_set(json(storage_locations.json),'$.displayDescription', 'STFP:/data2/home/xx1234')
  FROM storage_locations
  WHERE id = 2)
WHERE id = 2;
UPDATE storage_locations SET json =
  (SELECT json_set(json(storage_locations.json),'$.path', '/data2/home/xx1234')
  FROM storage_locations
  WHERE id = 2)
WHERE id = 2;

Instead, I opted for a more brutish way, guaranteed to work with or without extensions – I simply copy/pasted the JSON data into /tmp/foo.json, replaced all occurrences of data1 with data2, et voila:

sqlite> update storage_locations set json=readfile('/tmp/foo.json') where id=2;

To finish it off, I had to restart all Arq processes to ensure they’ll get the memo:

❯ sudo pkill -f Arq

(Note: Before touching the sqlite file, I should’ve stopped all Arq processes, but who in this time and age has nerves to fiddle with launchctl?).