[Feature] Support Postgres as DB backend

[its-josh4]

Stash stores all data in a SQLite database. Although SQLite is great and is the best option for 90% or more of Stash users, supporting external DB servers such as Postgres would be great.

Postgres:

• Is more reliable
• Has better tooling for operations (incl. backups and restore)
• Can be hosted on a separate node (better for K8s users)
• Doesn't fail spectacularly if the data is on a NFS/SMB drive like SQLite
• Can offer better performance.

At a quick glance, it appears that supporting Postgres shouldn't be too complex in stash. We should be able to add that as an option (alternative to SQLite).

Only thing that is an open question is what to do with the built-in backup feature of stash, since it's currently implemented so it does a VACUUM into a separate SQLite database. This is obviously not an option. My suggestion would be to disable built-in backups when using a DB server, since backups should be performed in the server.

[trollboy]

I'd throw bounty cash at this if it also included mysql, I get that postgres is more popular right now.
IRT Backups, why not a giant JSON dump? JSON would be SQL agnostic.

[MrX292]

@trollboy you mean like the Export task?

[trollboy]

@MrX292 yeah. Exporting/backing up to JSON would be RDMS agnostic.

[NodudeWasTaken]

After investigating the main hindrance seems to be the custom functions registered to sqlite. https://github.com/stashapp/stash/blob/7086109d7879b64529578e3e16dcafbb8b57e842/pkg/sqlite/driver.go#L27-L39 To do this in postgres you would need to register a binary to the service with said functions, which could be possible but would require some careful considerations about how to do it.

[Traxey]

Couldn't this be handled by using an abstraction layer?