Re: better architecture?

On 11/20/2014 12:30 PM, zach cruise wrote:

>>
>> For more info see:
>>
>> http://www.postgresql.org/docs/9.3/interactive/continuous-archiving.html
> to be clear- i change my 2 VMs setup {"1. master (dev) - 2. slave
> (prod) setup"} to 3 VMs {"1. master (dev) - 2. slave (prod) setup - 3.
> archive (wal)"}.
>
> but what do i gain?

Extra protection against failure, maybe.

So:

---> WAL Archive ---
| |
| Streaming |
master --- --------------------> slave

If the direct link between the master and slave goes down, the slave can
still get WALs from the archive. If the archive machine goes down you
still have the direct link. If you take the slave down the master can
still push WALs to the archive. This assumes the 'machines' are actually
separated and connecting through different networks. You say you are
using VMs, but not where they are running. If they are all running on
the same machine running through the same network link then you really
do not have protection against network issues. The same if the host
machine goes down. This is one of those pen and paper times, when you
sketch out the arrangement and start doing what ifs.

>
> as it is, in the worst case, VMs can always be restored "fairly
> quickly" for our use.
>
>>>> For failover see:
>>>>
>>>> http://www.postgresql.org/docs/9.3/interactive/warm-standby-failover.html
>>>>
>>>> "PostgreSQL does not provide the system software required to identify a
>>>> failure on the primary and notify the standby database server. Many such
>>>> tools exist and are well integrated with the operating system facilities
>>>> required for successful failover, such as IP address migration."
>>>>
>>>> So if you are looking for auto-promote you will need to look at third
>>>> party tools or writing your own script.
>>> while i can always use "pg_ctl promote", any recommendations for windows?
>>
>> Not from me, I do not run Postgres on Windows so I will be of no help
>> there.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com