SQLite: Backup, Restore and scale out proposal

[gedw99]

Clear and concise description of the problem

The only durable state for woodpecker is the Database.

Sqlite is great for its ease of deployment but not so create if you need some HA aspects such as backup, restore and scale out to multi servers.

Suggested solution

https://github.com/benbjohnson/litestream is a golang lib that continuously backsup sqlite to s3, and can restore from it.

It can be imported into woodpecker OR it can run alongside it.

There is no changes to the way the data layer works in woodpecker.

We could add to the WoodPecker CLI to allow making calls to Litestream to do restores.

Also scale out with litestream is being worked on and also being used by some. So you could then have one READ /WRITE Woodpecker with embedded Litestream and many READONLY Woodpeckers with litestream. It would mean that multi HA would be an automatic thing for Woodpecker.

If you interested i can show examples etc.

Alternative

No response

Additional context

No response

Validations

• Read the Contributing Guidelines.
• Read the docs.
• Check that there isn't already an issue that request the same feature to avoid creating a duplicate.

[6543]

@gedw99 for your case I think mysql or postgress is more apropriate
https://woodpecker-ci.org/docs/administration/database

[gedw99]

@6543 did you look into litestream ?

i have used stolen with postresql and it's very complicated to scale out. There is alot to getting a DB working in day 2 ops.

[mscherer]

If it can be run as a external process to woodpecker, what is needed to change to woodpecker, besides adding documentation ?

[gedw99]

SQLite soppprt I guess ?

your not using Ent ?

[6543]

We already do have sqlite support

Whats ent?

[6543]

For litestream i think we wont integrate it directly

[qwerty287]

I think we can close this?