From: Lists <lists@on-track.ca>
To: ries van Twisk <typo3@rvt.dds.nl>
Cc: pgsql-performance@postgresql.org
Subject: Re: Best replication solution?
Date: Sun, 05 Apr 2009 14:57:17 -0700
Message-ID: <49D9293D.2020101@on-track.ca> (raw)
In-Reply-To: <480ED41A-B4AF-4A14-A10B-9F7181D7949D@rvt.dds.nl>
References: <49D8FA31.8010407@on-track.ca>
<480ED41A-B4AF-4A14-A10B-9F7181D7949D@rvt.dds.nl>
I have a high traffic database with high volumes of reads, and moderate
volumes of writes. Millions of queries a day.
Running the latest version of Postgresql 8.2.x (I want to upgrade to
8.3, but the dump/reload requires an unacceptable amount of downtime)
Server is a dual core xeon 3GB ram and 2 mirrors of 15k SAS drives (1
for most data, 1 for wal and a few tables and indexes)
In total all databases on the server are about 10G on disk (about 2GB in
pgdump format).
The IO on the disks is being maxed out and I don't have the budget to
add more disks at this time. The web server has a raid10 of sata drives
with some io bandwidth to spare so I would like to replicate all data
over, and send some read queries to that server -- in particular the
very IO intensive FTI based search queries.
ries van Twisk wrote:
> Dr Mr No Name,
>
> what replication solution is the best depends on your requirements.
> May be you can tell a bit more what your situation is?
> Since you didn't gave us to much information about your requirements
> it's hard to give you any advice.
>
> Ries
>
> On Apr 5, 2009, at 1:36 PM, Lists wrote:
>
>> I am looking to setup replication of my postgresql database,
>> primarily for performance reasons.
>>
>> The searching I've done shows a lot of different options, can anyone
>> give suggestions about which one(s) are best? I've read the archives,
>> but there seems to be more replication solutions since the last
>> thread on this subject and it seems to change frequently.
>>
>> I'd really like a solution that replicates DDL, but very few do so I
>> think I'm out of luck for that. I can live without it.
>> Multi-master support would be nice too, but also seems to cause too
>> many problems so it looks like I'll have to do without it too.
>>
>>
>> *Slony-I* - I've used this in the past, but it's a huge pain to work
>> with, caused serious performance issues under heavy load due to long
>> running transactions (may not be the case anymore, it's been a while
>> since I used it on a large database with many writes), and doesn't
>> seem very reliable (I've had replication break on me multiple times).
>>
>> *Mammoth Replicator* - This is open source now, is it any good? It
>> sounds like it's trigger based like Slony. Is it based on Slony, or
>> simply use a similar solution?
>>
>> *pgpool* - Won't work for us reliably for replication because we have
>> some triggers and stored procedures that write data.
>>
>> *PGCluster* - Sounds cool, but based on the mailing list traffic and
>> the last news post on the site being from 2005, development seems to
>> be near dead. Also, no releases seems to make it beyond the RC stage
>> -- for multi-master stability is particularly important for data
>> integrity.
>>
>> *PGReplicator - *Don't know anything special about it.
>> *
>> Bucardo* - Don't know anything special about it.
>>
>> *Postgres-R* - Don't know anything special about it.
>>
>> *SkyTools/Londiste* - Don't know anything special about it.
>
>
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@on-track.ca, typo3@rvt.dds.nl
Subject: Re: Best replication solution?
In-Reply-To: <49D9293D.2020101@on-track.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox