PostgreSQL Discussion - Replicating new sequences

Author

Replicating new sequences

Todd Eddy

2005-09-22, 8:24 pm

I know this gets asked all the time, but I'm having issues with
replication. I got Slony setup between two computers and that does
replication of transactions. But we have a table that because of how
it works new sequences are added on a somewhat regular basis, maybe a
couple times a day. Also replication needs to be "almost" instant
because we distribute the load so updates go to one database and
selects happen on another database. So having these sequences
replicate on a fairly quick basis is important.

I think what we need is a log based replication solution instead of
the trigger based solution of Slony or other replication systems out
there. Only one I've seen that does log based replications is
Mammoth. Which aside from the large price tag and closed source, it
also doesn't run on Debian, which is the current distribution we're
using on the database servers. Not a big problem at this point since
we're in a big transition so using something like suse. Are there
any other log based replication systems out there that would still
work? Thanks for any help.

--
Todd Eddy
Developer

The information contained in this email is CONFIDENTIAL AND
PRIVILEGED and is intended only for the individual to whom it is
addressed.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Jan Wieck

2005-09-26, 8:24 pm

On 9/22/2005 3:54 PM, Todd Eddy wrote:

> I know this gets asked all the time, but I'm having issues with
> replication. I got Slony setup between two computers and that does
> replication of transactions. But we have a table that because of how
> it works new sequences are added on a somewhat regular basis, maybe a
> couple times a day. Also replication needs to be "almost" instant
> because we distribute the load so updates go to one database and
> selects happen on another database. So having these sequences
> replicate on a fairly quick basis is important.
>
> I think what we need is a log based replication solution instead of
> the trigger based solution of Slony or other replication systems out

I think it makes little difference how the changes are recorded
(triggers or WAL log analysis). The thing that matters is that both are
asynchronous replication methods which allow the master to run ahead.

What you are asking for is a synchronous replication system.

I hope that your load balancing system is smart enough to at least
guarantee that "SELECT ... FOR UPDATE" queries are executed in the same
"master transaction" as their corresponding "UPDATE" queries. If that is
the case, you shouldn't really have much of a problem. If not, I am not
even sure a synchronous replication system under MVCC can guarantee
consistency for you.

Jan

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links