[NBLUG/talk] [SoCoSA/discuss] PostgreSQL Replication?

[Kevan Benson]

Quoting re-ordered to make it easier to follow... Comments below.

Rob Clark wrote:
> On 12/13/07, Kevan Benson <kbenson at a-1networks.com> wrote:
>> Rob Clark wrote:
>>> On Dec 12, 2007 3:04 PM, Augie Schwer <augie.schwer at gmail.com> wrote:
>>>> How are people doing PostgreSQL replication?
>>>>
>>>> Looking around on the web it seems that there is no built in way to do
>>>> PostgreSQL replication and that there are several third party tools,
>>>> so my question is: what way is the best way?
>>>>
>>>> The point of this would be to do live backups and have a stand by
>>>> server in case I needed it.
>>>>
>>>> Any suggestions?
 >>
>>> The preferred method is to off-load the replication to an appliance
>>> (storage) that allows for snapshots and delta replication.  That way if
>> you
>>> corrupt data at your primary site you will have the ability to recover to
>> a
>>> known good point in time at your DR site and be up and running in a few
>>> minutes rather than hours/days.  This method also relieves your pSQL
>> server
>>> from any of the replication burden.
>>>
>> Hmm.
>>
>> LVM+Snapshots+rsync from snapshot = poor man's NAS with snapshots?
>>
> You'd want to LVM to an appliance w/ storage and cordinate snapshots
> w/ a pSQL agent.  The appliance would replicate to another appliance
> at the DR site where you could SAN boot if desired.

The description was really poor, I was just trying to relate it to the 
earlier post with the least amount of explanation possible (stupid, 
huh?).  Not so much a poor man's NAS as an equivalent system.  I thought 
NAS when you said storage device, although that's not exactly a requirement.

For LVM, I was referring to linux LVM in this case, which provides 
snapshot support for volumes.

I wouldn't actually implement said system (LVM+rsync) myself if I could 
avoid it.  If live backups weren't of importance, I would go with a DRBD 
fail-over system.  It's a little hokey the way MySQL does it, but it's 
robust.  You could even get live backups if you were willing to break 
your DRBD consistency for short periods, but that's playing with fire.

-- 

-Kevan Benson
-A-1 Networks