Skip site navigation (1) Skip section navigation (2)

Re: Using postgres.log file for replication

From: Ioana Danes <ioanasoftware(at)yahoo(dot)ca>
To: pgsql-general(at)postgresql(dot)org, Chris Browne <cbbrowne(at)acm(dot)org>
Subject: Re: Using postgres.log file for replication
Date: 2008-11-28 16:19:20
Message-ID: 685653.3004.qm@web45107.mail.sp1.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-general
Thanks for the reply Chris,

None of these are a problem for me because we are not using them at all...  

Thanks again,
Ioana 



--- On Thu, 11/27/08, Chris Browne <cbbrowne(at)acm(dot)org> wrote:

> From: Chris Browne <cbbrowne(at)acm(dot)org>
> Subject: Re: [GENERAL] Using postgres.log file for replication
> To: pgsql-general(at)postgresql(dot)org
> Received: Thursday, November 27, 2008, 6:10 PM
> ioanasoftware(at)yahoo(dot)ca (Ioana Danes) writes:
> > I've been wondering if anybody tried to use the
> postgresql csv log
> > file to replicate sql statements.  I've been
> looking into it in the
> > past days and after a brief testing it doesn't
> look bad at all...
> 
> It's *plausible*, but you have to ensure that you
> *never* use any
> operations that could break the assumption that it is OK to
> apply the
> queries in "logged order."
> 
> 1.  As has been mentioned separately, references to NOW()
> will break;
> analagous problems will occur for any function that
> doesn't provide
> immutable results, including:
> 
>   - NOW(), obviously, and any time-based function
>   - currval('any_sequence')
>   - random()  :-)
> 
> Thus, any reasonable usage of DEFAULT values on tables will
> cause
> discrepancies.
> 
> 2.  Further, if there can be multiple requests acting on
> the database
> concurrently, this can make it nigh unto impossible to
> ensure that
> they are applied in a compatible order.
> 
> It makes my head hurt a bit to think about the kinds of
> cases where
> this breaks down, but I know it's not difficult for
> concurrency to
> make this break badly.
> 
> 3.  If you ever have queries that create data in
> nondeterministic
> ways, that will cause a discrepancy.
> 
> For instance:
>    insert into table_2 (a,b,c)
>     select a,b,c from table_1 limit 100;
> 
> will NOT be able to be replicated consistently unless there
> were only
> 100 tuples to be found.
> 
> That being said, the following alteration to that query
> *could* work
> out:
>    insert into table_2 (a,b,c)
>     select a,b,c from table_1 order by a,b,c limit 100;
> 
> (assuming that the result of the select is, itself, a
> relation, which
> implies that there are no repeated values...)
> 
> I'm afraid I don't have sufficiently draconian
> powers over *our*
> developers to ensure that they NEVER do anything that would
> violate
> the above set of requirements.  
> 
> Nor, frankly, would I want to.  I strongly *APPROVE* of
> them using
> NOW() and currval(), and we build applications to be able
> to support
> multiple concurrent users.
> 
> The only piece where I might *imagine* I'd want to be
> "Lord of
> Draconia" would be on #3, and, if offered
> "Draconian Powers," I'd
> rather apply those powers to more vital matters :-).
> -- 
> output = ("cbbrowne" "@"
> "cbbrowne.com")
> http://www3.sympatico.ca/cbbrowne/slony.html
> Signs  of a   Klingon  Programmer  #6: "Debugging?  
> Klingons  do  not
> debug. Our software does not coddle the weak."
> 
> -- 
> Sent via pgsql-general mailing list
> (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


      __________________________________________________________________
Instant Messaging, free SMS, sharing photos and more... Try the new Yahoo! Canada Messenger at http://ca.beta.messenger.yahoo.com/

In response to

pgsql-general by date

Next:From: Stephane BortzmeyerDate: 2008-11-28 16:30:20
Subject: Re: PostgreSQL 8.3.5 client_encoding WIN1251 trouble
Previous:From: William TemperleyDate: 2008-11-28 16:18:37
Subject: Re: Very large tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group