Re: Log-based repliaction?

For your immediate needs I'd recommend slony.
It's a quite reliable replication solution that works quite nicely.
It will sync the replica in nearly real-time and you won't have any
transactional problems either.

Uwe


On Friday 20 October 2006 21:22, Tobias Brox wrote:
> I would eventually like to:
>
>  1) find a way to (stress) test a mockup database server.
>
>  2) fix a near-realtime replication of our production database server
>     for pulling out statistics and reports.  Minor bugs would be
>     permitable in such a setup.  We don't need a full-fledged data
>     warehouse solution just yet.
>
>  3) set up a failover solution *)
>
> Now, why not just get the production database to log all the queries,
> and feed them into a replication database?  I guess this solution
> could be used for archieving any of the three things above.  This idea
> is very trivial, but I never found any pointers while googling, so I
> assume there are some problems with this approach?
>
> Here is some things I can think of at the moment:
>
>  - logging all queries at the production database will slow it down
>    considerably (haven't done much testing here)
>
>  - transactional model can easily be broken (postgres can log
>    transaction starts, commits and rollbacks, and the transactions are
>    also supposed to be serializable ... so I don't see the issue?)
>
>  - disregarded due to the resource consumption on the replica server.
>
>  - some of the transactions aren't really serializable, or relies on
>    the exact timestamp for the operation. **)
>
>  - unlike the wal, the log file doesn't get synced for every
>    transaction, and this will cause delays and potentially data loss.
>
>  ...anything else?
>
> The simplest thing would be to have one connection open towards the
> replica for every connection made towards the production database, run
> every query in order, and hope the best - should work good for problem
> 1 and problem 2 above.
>
> Still, maybe better (for 2) to filter out only queries altering the
> table and transactions ending with a commit - and do the transactions
> one by one, ordered by commit time.  Would save quite some CPU cycles
> on the replica database compared to the suggestion above.
>
> I was administring a mysql database quite some years ago ... well, it
> was a simple hobby project and we didn't even need transactional
> operations.  However, we did need point-in-time-recovery.  The mysql
> way of fixinge this was to write a separate log containing only the
> queries involving writing to the database.  This log was really nice
> to work with, and it could easily be used for replication as well.
>
>
>
> *) boss has decided that a failover solution is important and should
> be prioritied in 2007 ... even to the point where he's willing to of
> throw money at it.  If anyone have experiences with failover-solutions
> built over a SAN, and can do consultancy services for us, please send
> me a private email ;-)
>
> **) We have had problems with transactions doing "if not exists in
> database then insert".  Two such transactions can be run synchronously
> and result in duplicates.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
    UC

--
Open Source Solutions 4U, LLC    1618 Kelly St
Phone:  +1 707 568 3056        Santa Rosa, CA 95401
Cell:   +1 650 302 2405        United States
Fax:    +1 707 568 6416