omnisdev-en Digest, Vol 16, Issue 13
Chuck Martin
clmartin at theombudsman.com
Sun Nov 25 11:25:19 EST 2018
I apologize for not changing the subject to: Re: Saving Documents in
PG vs File system. It seems that gmail does not allow changing the
subject when you reply.
I've followed the discussion on storage of documents in the file
system vs. database. I've done both with my app, but somewhat
differently. I originally stored documents in the server's file system
(protected from access by users). When a user needed a document, the
server copied it from the protected directory to an accessible
directory, and the user fetched by FTP. But my users began to need
remote access, which meant risking FTP traffic over the internet. The
hassle of trying to secure FTP and communicate over multiple ports
securely led me to begin storing the documents in the database. This
allowed having only one port to secure.
It does lead to a huge database, well over 500 GB now. But documents
need to be backed up whether in or out of the database. I guess if you
keep them in the file system, you can use rsync to just copy the
changed files, though. You could probably write SQL to select just
changed documents for an export, though. But I just go for the simple
solution. I do love Doug E.'s plan to couple replication with frequent
dumps of small tables and less frequent dumps of large ones.
One caveat about storing documents in the database, though. pg_dump,
for reasons I only half understand, cannot handle records that exceed
500mb. While it shouldn't happen, in my app it sometimes does.
Chuck Martin
Avondale Software
On Thu, Nov 15, 2018 at 12:00 PM
<omnisdev-en-request at lists.omnis-dev.com> wrote:
>
> Message: 1
> Date: Wed, 14 Nov 2018 12:37:33 -0500
> From: Andrew Stolarz <stolarz at gmail.com>
> To: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
> Subject: Re: Saving Documents in PG vs File system
> Message-ID:
> <CAPrUNcTMsbyxx_VdampN2hH=kO18bn4qLs06ehbLsjw=nz0kUQ at mail.gmail.com>
> Content-Type: text/plain; charset="UTF-8"
>
> Mike,
>
> In our use case, other 3rd party applications need to access the files we
> save from our Omnis app, so we don't deal with any restrictions etc.
>
...
> Message: 3
> Date: Wed, 14 Nov 2018 13:51:24 -0500
> From: Steve Finger <steve at srptech.com>
> To: OmnisDev - English <omnisdev-en at lists.omnis-dev.com>
> Subject: Re: Saving Documents in PG vs File system
> Message-ID: <426E094D-44D5-4F49-B06F-45B242708DA5 at srptech.com>
> Content-Type: text/plain; charset=utf-8
>
> Hi Mike,
>
> We store all our documents in PG. We have all types of document types: Spreadsheets, PDF?s, Email, Word Documents etc.
>
> The business has pretty much gone paperless so when orders come in or documentation on the raw materials we buy come in, it gets scanned and the paper gets tossed. The Omnis app makes sure the naming of the record in the database is correct for multiple versions of the same document.
>
> I agree the problem is the size of the database. Not sure how to get around it but it sure is nice not having to worry about someone deleting, moving or renaming a file and we know how to retrieve the latest version of a file for the user and keep old versions for historical purposes since revision naming of the document is not under the users control.
>
> Steve Finger
>
...
>
> Message: 4
> Date: Wed, 14 Nov 2018 11:25:51 -0800
> From: Doug Easterbrook <doug at artsman.com>
> To: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
> Subject: Re: Saving Documents in PG vs File system
> Message-ID: <C470517D-0DD0-4568-BA1F-AE20E0E54E65 at artsman.com>
> Content-Type: text/plain; charset=utf-8
>
> hi Mats.
>
> we store stuff in the database ? using postgres. Some argue good idea, some argue not a good idea. I?ll leave that argument out of this.
>
> how to manage large BACKUPS ? which is the question that I?m hearing, if one chooses to backup PDF?s in the database for simplicity.
>
> if you use pg_dump for your backup, do a pg_dump ?help
>
> one of the flags is -T where you can ?exclude? certain files.
>
> so if you want a bunch of daily backups without your large document file, you can do something like.
>
> pg_dump -F c -T documentTable database > destFile
>
>
> and then once a day or once a week go after the entire database.
>
>
> or, you can specifically backup just the documents database using -t
>
> pg_dump -F c -t documentTable database > destFile
>
>
>
> if you have replication set up (we like to do that), and have a live copy of the database, now its into risk management where you can
>
> 1) assume that replication is running and you have two complete copies of the database at all times
> 2) dump all but the large file on a daily basis for off-machine safety
> 3) dump the large file weekly and take a risk that you won?t lose both replication and the main database in one day (or week).
>
>
>
> even if you store the large files out side of the database, you are faced with backing them up. you can do incremental if you want using some file system backup or time machine like tool ? but sooner or later you?ll get to a spot where getting them back for a test database or what have you necessitates a full dump or a weird restore process.
>
> so I like to be clean and use one common appraoch
> - dump everything once in a while (if thats needed)
> - dump the small stuff all the time
> - use replication
>
> and its all postgres and easily importable/usable by the database & application.
>
> just my thoughts
>
>
>
>
> Doug Easterbrook
> Arts Management Systems Ltd.
> mailto:doug at artsman.com
> http://www.artsman.com
> Phone (403) 650-1978
...
> Message: 7
> Date: Thu, 15 Nov 2018 06:46:47 +1100
> From: Reg Paling <reg.paling at lokanet.com>
> To: Michael Matthews via omnisdev-en <omnisdev-en at lists.omnis-dev.com>
> Subject: Re: Saving Documents in PG vs File system
> Message-ID: <7908826c-ec7e-d1b0-df08-4078da11df2f at lokanet.com>
> Content-Type: text/plain; charset=utf-8; format=flowed
>
> Hi Mike,
>
> My application stores documents in the file system.? On windows it works
> mostly without a problem but on macOS problems pop up regularly across
> the user base: permissions needing to be repaired, folder structures
> getting corrupted and so on.? I could not believe it during the first
> years after I took over the app, but the only conclusion based on my
> experience is that macOS is flakey.? Newer versions may be slightly less
> so but nonetheless all versions are flakey.? Some servers appear to be
> more vulnerable than others.? Attempts by IT support to rebuild and
> repair the disks don't work, but then the client buys a new server and
> the problems (sometimes) largely go away.
>
> So I have been thinking of going in the opposite direction!? I like your
> idea of the temp and permanent databases.? I haven't done anything with
> S3 but my experience of other services from AWS is that they are indeed
> a beautiful thing as Andrew says.
>
> Regards,
> Reg
...
> Message: 8
> Date: Wed, 14 Nov 2018 15:04:23 -0500
> From: "Michael Mantkowski" <michaelj at clientrax.com>
> To: "'OmnisDev List - English'" <omnisdev-en at lists.omnis-dev.com>
> Subject: RE: Saving Documents in PG vs File system
> Message-ID: <021e01d47c55$3e6c5b20$bb451160$@clientrax.com>
> Content-Type: text/plain; charset="UTF-8"
>
> We store all our Pictures and Documents in the file system.
>
> We have folders called "Attachments", "Pictures" and "Documents". Files of the correct type are named and placed in these folders by Omnis as required.
>
> We then store the file name in the data record. The application knows where the folders are based on the location of the data (Or the Data Setup File in the case of PostGres Data) and simply retrieves the files from there.
>
> We do not have any type of security on the folders themselves to prevent someone from looking at them directly and I have to admit to the folders going missing once or twice over the last 30 years because of a customer not knowing what they were doing and decided to move things around. But for 99.98% of the time we have never had issues with this system.
>
> We have had some issues with the MacOS version as Reg said with permissions going wrong and having a hell of a time fixing it. But we have had that with the general Omnis Instillation as well. (Omnis can't open the icon files because the system sees them as read only and things like that.)
>
> The backup process of course needs to include these folders as well as the data.
>
> *********************************************************************
> Michael Mantkowski
> ClienTrax Software
> 1-614-875-2245
> *********************************************************************
...
> Message: 12
> Date: Thu, 15 Nov 2018 10:26:57 +1100
> From: Bastiaan Olij <Bastiaan.Olij at instinctsystems.com.au>
> To: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
> Subject: Re: Saving Documents in PG vs File system
> Message-ID: <343606704-10394 at mail.instinctsystems.com.au>
> Content-Type: text/plain; charset="utf-8"
>
> Hey Mike,
>
>
> The approach we took is going down the WebDAV route. We wrote our own WebDAV server that queries our database for the meta data but then saves the actual files on disk. This ensures that users can't suddenly start moving files around and when things change in our database the WebDAV server magically gets updated.?
>
>
> Now WebDAV is a bit of a conundrum, you can mount WebDAV servers as drives to give users direct access through the OS but support for this is dwindling. Apple Finder gets completely overloaded when you have large repositories (not really a WebDAV issue, also happens on normal file servers) and Microsoft was once a WebDAV stronghold but now has some weird 250 character maximum path length issue and limits the file size of WebDAV stored files to 500Mb due to security issues they had with publicly available WebDAV servers.
> The WebDAV framework we've build our solution on (http://sabre.io) has a web interface that does a good job for access outside of our application so we rarely use the mount option. Yes it's PHP based but it's incredibly easy to build your own backend solution into this. It's really a joy to work with.
>
>
> I'm simplifying but in broad strokes we expose a directory structure to the user that is totally virtual. So the user will see a list of folders, one for each client, go into a clients folder and we see a list of folders for the clients jobs, and inside of that folder sees the files they have uploaded for that job. That folder structure does not exist, we are creating it from the actual clients and jobs in our database. There is a table joined to our jobs table that contains the meta data for the files such as the name, location on disk, etc.
>
>
> Say you do mount the WebDAV as a drive in your OS you will see this as a network drive with all the folders and stuff. When you copy a file in what happens on the background is that this becomes and HTTP upload of the file, our web server than moves that file into a nice location we manage and creates a new entry in our database so we know where the file is stored. The folder structure on the actual server can be anything.
>
> >From within Omnis we use HTTP directly to communicate with the WebDAV server. We had to write our own XComp because in Studio 4 days Omnis didn't have the support but I think in Studio 8 the new HTTP workers have all the needed enhancements as they are similar to the improvements needed for REST.?
>
> Kindest Regards,
>
>
> Bastiaan Olij
> Head of development - Instinct Systems: The JobBag People
> Ground Floor, 48 Chandos Street
> St Leonards NSW 2065
> Australia
>
>
> Phone +61 2 8115 8000
> Mobile +61 4 321 44833
> bastiaan.olij at instinctsystems.com.au
> http://www.jobbag.com
>
More information about the omnisdev-en
mailing list