Question: How to restore SQLite to a different server with already existing SQLite

[khaled-badenjki]

If I'm on a server A with a rails app (running with Kamal 2) and using Litestream. Then i decided to move my app to a new server B

The data is already synced to my S3, when I moved to server B i tried to restore the database, but i got this error error="cannot restore, output path already exists: /rails/storage/production.sqlite3"

I deleted the existing sqlite files and then I restored. It didn't throw errors, but I wasn't able to access the data. I felt that manually deleting the sqlite files is not a good action since there are already shm and wal files.

What should be the better way to handle my scenario?

[fractaledmind]

How are you running restore? And can you provide the full logs that lead up to that error?

[khaled-badenjki]

I'm running restore like this: bin/rails litestream:restore -- --database=storage/production.sqlite3

The logs are only the command above, and this error message: level=ERROR msg="failed to run" error="cannot restore, output path already exists: /rails/storage/production.sqlite3"

[oandalib]

I also ran into something similar. I got it to work like so:

1. Delete production.sqlite3/production.sqlite3-shm/production.sqlite3-wal.
2. IMPORTANT: do not try to access any of the resource pages (e.g. posts) or anything else that relies on ActiveRecord/SQLite. If you do this Rails could regenerate the production.sqlite3, and thus you end up getting the output path already exists error.
3. Run the restore command.
4. Restart the Docker container.
5. Data should be restored.

[fractaledmind]

Indeed. And rails db:drop can be used to delete the 3 SQLite files

[fractaledmind]

@oandalib would you want to open a PR to add a section to the README detailing when one might want to restore to a new server, why the restore command can't be run until after db:drop, and the importance of restarting the Docker container

[oandalib]

I think we need to better define the entire restoration process, not strictly just for a new server. See #52.

[richjdsmith]

For what it's worth, I'd love to be able to have the option of seeing/restoring from my prod server to my dev, and vice versa.

Something along the lines of the following in litestream.yml:

# For more details, see: https://litestream.io/reference/config/
dbs:
  # Development database
  - path: ./db/development/data.sqlite3
    replicas:
      - type: s3
        bucket: fundsite-db-backup
        endpoint: https://XXXXX.r2.cloudflarestorage.com
        access-key-id: $LITESTREAM_ACCESS_KEY_ID
        secret-access-key: $LITESTREAM_SECRET_ACCESS_KEY
        path: litestreamBackups/development/

  # Production database
  - path: /home/deploy/fundsite/shared/production/data.sqlite3
    replicas:
      - type: s3
        bucket: fundsite-db-backup
        endpoint: https://XXXXX.r2.cloudflarestorage.com
        access-key-id: $LITESTREAM_ACCESS_KEY_ID
        secret-access-key: $LITESTREAM_SECRET_ACCESS_KEY
        path: litestreamBackups/production/

So that when I visit localhost:3000/admin/litestream, I am able to see my prod backup, and restore it to my dev computer and vice versa. You could swap dev with a different server etc.