O$ PostgreSQL large objects and OSX

Andy Hilton andyh at totallybrilliant.com
Wed Jun 24 12:18:18 EDT 2015 

On the subject of backups and incremental - I typically have 2 different scripts for creating postgres backups - one that backs up the lot (for some of my clients that can be 100Gb and up…..) and one that backs up without the blob tables, which brings it down to mere hundreds of MB……(or way less for the little clients !) - so I can run the small script every night - and then just do the large one over a weekend say…..(depending upon the circumstances)

Although this can risk *potentially* losing any blobs stored/created during the week - if time and space are an issue - this always leaves you 99% covered without the vast storage requirement of doing a full backup nightly and saving a bunch of those copies…..(and if I wanted I could script it to add the ‘new’ blob entries only since the weekend - which is what I probably should do !!)

Depending upon your table structure - and in my case - a restore can function absolutely fine without the blob tables - it simply doesn’t have the blobs available, as I created my blob tables to simply be the binary and a foreign key to its related parent item - so no dependencies on the blobs, and it can work happily with or without them…..

Then  - in the worst case - the blob restore can come on its own from the previous weekend full backup and just add itself to the ‘current’ restore…..like I said 99% covered ! Postgres is great about doing that sort of stuff (individual table backup and restore)

As with all these things it’s all a matter of what suits the business case the best…..but fundamentally I too just stuff the binaries into the db which makes life so much easier to deal with different sources getting data out - no issues of network paths, or missing files etc - it’s all neatly in one place only…..

So it’s not strictly true that if you don’t do the whole thing you’ve lost the transactional integrity - that is down to your database design as to what *is* required for integrity, and what can be optional…..

Just my 2 cents

Andy


> On Jun 24, 2015, at 8:11 AM, Doug Easterbrook <doug at artsman.com> wrote:
> 
> hi Reg:
> 
> I see what you are saying —   incremental backups.
> 
> but as you say, this doesn’t apply for database backups because if you don’t do the whole thing, you’ve lost the transactional integrity of the backup and you may as well throw the backup away.
> 
> even for offsite backups — you need the whole db directory as it would be painful to try to reconstruct when blobs where changed if you didn’t back them up.     plus, saving in the background off site is all background processes.  In a data centre world, thats usually decent bandwidth.
> 
> 
> Doug Easterbrook
> Arts Management Systems Ltd.
> mailto:doug at artsman.com
> http://www.artsman.com
> Phone (403) 536-1205    Fax (403) 536-1210
> 
>> On Jun 24, 2015, at 5:57 AM, Reg Paling <Reg.Paling at Lokanet.com> wrote:
>> 
>> Hi Doug & all,
>> 
>> An advantage of storing the files in the filesystem is when using incremental backups, the files don't need to be backed up because they haven't changed.  The database will always need to be included wholus bolus even if only one row is changed, however it is smaller because it doesn't contain the files.  So overall it requires much less space for each incremental backup.
>> 
>> However, these days, networks are fast, backup media are big and the backups don't take long to run even for many gigabytes, so in many cases that advantage is not as important as it used to be.
>> 
>> Cheers,
>> Reg
>> 
>> On 24/06/2015 2:59 pm, Doug Easterbrook wrote:
>>> hi all:
>>> 
>>> I’ll probably get shot on this one …   but LOB is just a large object, binary or character.
>>> 
>>> so why try to access the database file structure and do a hybrid   ‘here’s a file and I’ll only save the pointer in the database??’
>>> 
>>> it just seems so much more reasonable to do an update to a table using standard sql like
>>> 
>>> update table set blob=[@blobvalue]
>>> 
>>> that way, if you delete the row, the blob goes
>>> 
>>> and if you back up the database, the blob gets backed up.
>>> and, its platform independent.
>>> and its fully ACID.
>>> and its easy to restore a database.
>>> 
>>> 
>>> 
>>> 
>>> it just doesn’t make sense to me to try this save the document via the file system and update the database with a value …   I know.. people say, there are advantages.    but it really escapes me on the most basest sense why to do it.
>>> 
>>> 
>>> I hope to be enlightened.
>>> 
>>> 
>>> 
>>> Doug Easterbrook
>>> Arts Management Systems Ltd.
>>> mailto:doug at artsman.com
>>> http://www.artsman.com
>>> Phone (403) 536-1205    Fax (403) 536-1210
>>> 
>>>> On Jun 23, 2015, at 6:06 PM, Andrew Clow <andrew at freedomsoftware.co.nz> wrote:
>>>> 
>>>> Thanks Bas,
>>>> I thought of the workaround but was hoping to avoid it, and I’m not sure if I’ll be able to get the import working with a workaround.
>>>> 
>>>> I’ll check if DLA can supply an updated DAM (assuming one exists) otherwise start working on the workaround unless there are some other $listers with suggestions.
>>>> 
>>>> Regards,
>>>> Andrew.
>>>> 
>>>>> On 24/06/2015, at 11:37 am, Bastiaan Olij <bastiaan at basenlily.me> wrote:
>>>>> 
>>>>> Hi Andrew,
>>>>> 
>>>>> I had similar issues with creating the debug log you can enable for the
>>>>> Postgres DAM. I think the Postgres side of life is converting the Mac
>>>>> path to a Linux path but the OS is still expecting a Mac path.
>>>>> I think the issue with the debug log was fixed in a later version of the
>>>>> DAM and may apply to this as well but I don't know if this is thus
>>>>> available in the latest 5 release or only 6 or if it is still a problem
>>>>> for these particular functions.
>>>>> 
>>>>> You might want to check with TL if they have a newer DAM available for
>>>>> you that fixes this issue.
>>>>> 
>>>>> Only workaround I can suggest is putting the file in the root and after
>>>>> that moving it to the desired location, not neat and not all users may
>>>>> have rights to do so.
>>>>> 
>>>>> Cheers,
>>>>> 
>>>>> Bas
>>>>> 
>>>>> On 24/06/2015 9:17 am, Andrew Clow wrote:
>>>>>> Hi,
>>>>>> 
>>>>>> Using Omnis Studio 5.2.3 with Postgres DAM on both Windows & OSX.
>>>>>> 
>>>>>> I've been experimenting with $lobimport & $lobexport and got it working smoothly on Windows, but I'm having an issue with the implementation on OSX and hoping someone out there is using them successfully.
>>>>>> 
>>>>>> $lobimport(lcFilePath) returns 0  (nothing is imported/created and no error is logged by the Postgres Server)
>>>>>> $lobexport(lcFilePath,$cinst.fs_oid) returns kTrue, but the file is generated in the root directory and not the selected path.
>>>>>> 
>>>>>> E.g. If lcFilePath is "Macintosh HD:Andrew:Desktop:Testfile.txt" Omnis creates the file as "Macintosh HD/Andrew/Desktop/Testfile.txt" in the root directory.
>>>>>> 
>>>>>> I suspect that this path issue is also preventing the $lobimport method from working.
>>>>>> I've tried replacing all the path separators prior to import/export but that results in the same failure.
>>>>>> 
>>>>>> Can anyone think of what I might be missing regarding filepath on OSX?
>>>>>> 
>>>>>> All my other OSX file generation with $putfilename works, although most of it is via BrainyData's PDF external or $writecharacter.
>>>>>> 
>>>>>> For reference the code that's working on Windows is:
>>>>>> :: Import method in table class.
>>>>>> Do FileOps.$getfilename(lcFilePath,'Select File to import...','*.*',lDirectory)
>>>>>> If not(isclear(lcFilePath))
>>>>>> Do $cinst.$sessionobject().$transactionmode.$assign(kSessionTranManual)
>>>>>> Do $cinst.$sessionobject().$begin()
>>>>>> Do $cinst.$sessionobject().$lobimport(lcFilePath) Returns $cinst.fs_oid
>>>>>> If $cinst.fs_oid
>>>>>> Do $cinst.$save() Returns bResultState
>>>>>> If bResultState
>>>>>> Do $cinst.$sessionobject().$commit()
>>>>>> Else
>>>>>> Do $cinst.$sessionobject().$rollback()
>>>>>> End If
>>>>>> Else
>>>>>> Do $cinst.$sessionobject().$rollback()
>>>>>> End If
>>>>>> Do $cinst.$sessionobject().$transactionmode.$assign(kSessionTranAutomatic)
>>>>>> End If
>>>>>> Quit method bResultState
>>>>>> 
>>>>>> :: Export method in table class.
>>>>>> Do FileOps.$putfilename(lcFilePath,'Download file to...','*.*',lDirectory)
>>>>>> If not(isclear(lcFilePath))
>>>>>> Do $cinst.$sessionobject().$transactionmode.$assign(kSessionTranManual)
>>>>>> Do $cinst.$sessionobject().$begin()
>>>>>> Do $cinst.$sessionobject().$lobexport(lcFilePath,$cinst.fs_oid) Returns bResultState
>>>>>> Do $cinst.$sessionobject().$rollback()
>>>>>> Do $cinst.$sessionobject().$transactionmode.$assign(kSessionTranAutomatic)
>>>>>> End If
>>>>>> Quit method bResultState
>>>>>> 
>>>>>> Regards,
>>>>>> Andrew Clow
>>>>>> 
>>>>>> _____________________________________________________________
>>>>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>>>>> 
>>>>>> 
>>>>> 
>>>>> 
>>>>> _____________________________________________________________
>>>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>>>> 
>>>> _____________________________________________________________
>>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>> _____________________________________________________________
>>> Manage your list subscriptions at http://lists.omnis-dev.com
>> 
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com

More information about the omnisdev-en mailing list