Re: better architecture?

On 11/20/14, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
> On 11/20/2014 11:02 AM, zach cruise wrote:
>> On 11/20/14, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>> On 11/20/2014 08:00 AM, zach cruise wrote:
>>>> combining replies for the list:
>>>>
>
>>> Well it would depend on your setup and the load on the master. Assuming
>>> streaming replication. Simple explanation:
>> yes streaming replication.
>>>
>>> 1) If the master is down and slave is up then the slave will stall at
>>> whatever the last WAL was sent. When the master comes back up it will
>>> catch up as new WALs are generated.
>>>
>>> 2) If the slave is down and the master is up, the master will keep on
>>> creating WALs. The issue is that WALs are recycled over time, so given a
>>> significant load on the master and extended downtime for the slave it is
>>> possible that when the slave comes back up a WAL it needs is no longer
>>> available and it will start throwing errors. One way to tune this is
>>> modify wal_keep_segments (integer):
>>>
>>> http://www.postgresql.org/docs/9.3/interactive/runtime-config-replication.html
>> while the WAL archive directory has to be shared with both master and
>> slave, should the WAL archive directory be independent of them ie
>> should it not go down with either of them? if it has to go down with
>> one, it seems it'd best for the WAL archive directory to go down with
>> slave?
>
> So I am to understand that you have WAL archiving set up also?
yes, slave gets updates from stream and WAL. if either fails, the
other will update.

> Again a simplified version:
>
> The ideal situation is you have a third machine that has the WAL
> archives. The issue is that if the master cannot archive a WAL it will
> keep it around until it can. So depending on load and outage you can end
> with disk space issues on the master should it not be able clear the WALs.
>
> 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?

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.