On 10/1/2004 1:33 PM, Ryszard Lach wrote:

> On Fri, Oct 01, 2004 at 10:59:21AM -0400, Andrew Sullivan wrote:
>> On Fri, Oct 01, 2004 at 04:07:51PM +0200, Ryszard Lach wrote:
>> > 
>> > 1. disable (at application level) master db modifications for a moment
>> > 2. start replication
>> > 3. rsync application files
>> > 4. stop replication
>> > 5. enable modifications of master db
>> 
>> Why do this?  If you have the replication happening, when the mods
>> happen on the master, they'll be propogated.  Or are the mods
>> _schema_ mods?  In any case, I don't see what you're getting by
>> stopping slony.
> 
> It seems I did not explain what I meant.
> 
> My goal is to ensure that on slave host I receive a coherent copy of
> application and database from some timepoint.

To ensure that, you'd have to stop updating your main database and 
files. Then take a dump and file backup and restore those onto your 
second server. Your external files do not follow any transactional 
semantics, so without stopping your update activity you can't expect to 
get any consistent snapshot.

If you want to get a consistent copy of your data concurrent to update 
activity, you must store all of it inside the database so that the MVCC 
capabilities allow you to extract such consistent snapshot.

> 
> Let's say, that in database are stored fragments of some kind of text
> files used to generate html. They contain links to other files, which
> are binary (.doc,.xls, multimedia files etc.) files uploaded by users
> editing application content. The may be too big to store them in
> database, besides slony doesn't support blobs. 

Typical .doc or .xls files are somewhere up to 10 MB. That's no problem 
for bytea columns and if you have larger things, you can store chunks 
yourself.

> 
> Anyway: If I leave the replication running and someone modifies the
> content of the database adding to some (stored in that database) file
> link to a binary file, and then uploads the binary file to server - I
> will not know, that I have to copy that binary file to slave host. 

And if your binary copy mechanism copies an upload that is in progress, 
you have just copied junk. Think ACID!

> 
> So, if I block modifications of database, then replicate it to slave
> host and then rsync binary files between the hosts - I'll receive on
> slave host a copy without broken links and obsolete files, right? But,
> to do so, I need to know when started replication finishes the work,
> thus prohibiting propagation of subsequent changes (i.e. those made
> after rsync) to slave db.

You have to block read access to your slave host during the entire 
operation because it is not applied in one atomic transaction either. So 
half way through someone can see an inconsistent state.

This kind of problems have been discussed to death with countless MySQL 
fans in the past. Forget it, if you want ACID, store your data in the 
database.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #