Tool to interface/synchronize between database (Postgresql) and filesystem (Linux/Ubuntu)

Question

I am using the filesystem for storing large files off the database and keeping only a record of the directory path on the database. So I am looking for a tool to use as an interface for keeping the database aligned with the content stored on the filesystem.

I am writing what I know/have thought of so far. Could I please have some ideas on if any such tools are already out there?

On the server side, I think I need:

Script to monitor directory for new files Cron jobs to run the monitoring script periodically Script to update database contents periodically On the client side (end user), I think I should:

Upload the file to a /tmp directory Run virus scan on updated file(s) Use a script to move checked file to actual location

Answer 1

I don't think such an off-the-shelf tool exist. However, you can build it pretty easily if you use a clever approach.

1. Store ALL the files as BLOBs.

No filesystem => no sync-with-filesystem problems. See here for caveats though: https://softwareengineering.stackexchange.com/questions/150669/is-it-a-bad-practice-to-store-large-files-10-mb-in-a-database

2. Store only the paths for ALL the files in the DB.

Advantage: gets rid of a subtle sync problems, like: if you have a small file in the database and on the disk, their content may be out of sync. OTOH if you store small file as a BLOB, you do not have unified view of filesystem in one place (be it either filesystem itself or DB).

You only need to sync filesystem changes one way: into the database. That should be pretty easy to write.

If the number of files is not too high, you could even have this near real-time: https://stackoverflow.com/questions/7566569/how-to-continuosly-monitor-the-directory-using-dnotify-inotify-command

Disadvantage: you need 2 operations for every file access, i.e. DB transaction and a filesystem access. Alleviation of this disadvantage: most likely most of the time reading a single file path in the DB is going to be pretty fast and cached at some level anyway.