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.

@trollboy you mean like the Export task?

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

After investigating the main hindrance seems to be the custom functions registered to sqlite.

It might also require some deep dives into specific sqlite and postgres macros like current date etc.

EDIT2:
So after some investigation we need:

• Alternative mitigations (AUTOINCREMENT/integer, datetime => timestamp, etc. needs replacing)
• Create a Postgresql binary that registers the missing commands (not too hard, but i dont know that the project wants to maintain that).
• Specify database type and connection string in stash config.

EDIT3:
So i added a bunch of switches for db type, i think the strategy currently is to create premigrations for creating tables such that i can select what type of index it is pr db type ("integer not null primary key autoincrement", vs "serial not null primary key")

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

Doesn't fail spectacularly if the data is on a NFS/SMB drive like SQLite

Just happened to me on my Unraid server. Wishing I'd stored in the dbms I'm already running on the same server :(

I would also throw a small bounty, like maybe 100+ usd, for support for using either MySQL or Postgres as the backend. I wouldn't need those settings exposed in the settings UI, hand-editing config.yml is fine, or automating the setup of the binary service mentioned above, basic instructions (community-sourced maybe?) for how that might be done and user has to set it up themselves.

I know it would require abstracting parts of the system that currently aren't abstracted like the regex, phash_distance, etc functions mentioned above, so it wouldn't be trivial... but the performance gains for very large databases would be awesome..

I've never done bounties before so I'm not sure how that works, but I give small amounts to all kinds of things.. $60/mo patreon budget, monthly donations to Wikipedia, Internet Archive...

I have create some preliminary work on a branch here:
https://github.com/NodudeWasTaken/stash/tree/postgres-support-2
Its MOSTLY FUNCTIONAL, DONT USE IT.

TODOS:

• Some backup specific functions arent implemented in postgres.
  Given we only have a network connection we cant run pg_dump.
  We can fetch everything and dump it into an sqlite file (like anonymize does), but i fear this may cause issues.
  I could be heavy handed and demand some backup file exists like pg_backup_{date}.gz and give the command to generate it, since this is an advanced feature anyway.
• The missing functions are created, but the operator necessitates sql changes
  Im not that great at C, but here's the initial version of the plugin:
  https://github.com/NodudeWasTaken/stash_pge
  I added the collation using a builtin postgresql function, i need to find if this is sufficient or i need another c function:
  CREATE COLLATION IF NOT EXISTS NATURAL_CI (provider = icu, locale = 'en@colNumeric=yes');
• I was lazy with the migrations, so we have one big one and it bugs out on first boot saying "found 2 migrations, expected 68" or something.
  I should probably just lower the number for postgres, like its always 68 behind, but increments with it.

There are probably more bugs which i haven't found.
Any help would be appreciated 😊

@NodudeWasTaken I would be very happy to help. Let me know how we can sync! (Discord?)