Re: better architecture? - Mailing list pgsql-general

On 11/20/14, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 11/20/2014 04:57 PM, zach cruise wrote:
>> On 11/20/14, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>> 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.
>
> First of all, the below is really in need of whiteboard/paper diagram to
> keep track of the moving parts. That being said here it goes:
>
>>
>> master, slave and archive can be 3 separate VMs on 1 host, with their
>> clones on 2nd and 3rd hosts.
>
> I can see the above being a potential nightmare. I am not sure how you
> ensure that the cloning process results in clones that exactly mirror
> the state of the originals at a particular point in time. Failing in
> that would seem to me to lead to no end of issues in the replication
> process.
>
>>
>> a follow-up question on WAL recycling: ("When WAL archiving is being
>> done, the log segments must be archived before being recycled or
>> removed" from
>> http://www.postgresql.org/docs/9.3/static/wal-configuration.html)
>>
>> say streaming is off-
>> * if both master and archive are down, slave is still up and running.
>> yes?
>
> Yes.
>
>> * if master writes data when archive is down, it will copy over to
>> slave when archive is back up. yes?
>
> If streaming is off and you are doing archiving then it will copy over
> to the archive.
>
>> * but if WAL is recycled before archive is back up, it will not copy
>> over to slave. yes?
>
> The issue here as pointed out previously is that the WALs will stack up
> on the master because it will not be able to archive them. So then you
> run into a potential of of space issue on the master. From here:
>
> http://www.postgresql.org/docs/9.3/interactive/continuous-archiving.html
>
> "It is important that the archive command return zero exit status if and
> only if it succeeds. Upon getting a zero result, PostgreSQL will assume
> that the file has been successfully archived, and will remove or recycle
> it. However, a nonzero status tells PostgreSQL that the file was not
> archived; it will try again periodically until it succeeds."
>
> ...
>
> "While designing your archiving setup, consider what will happen if the
> archive command fails repeatedly because some aspect requires operator
> intervention or the archive runs out of space. For example, this could
> occur if you write to tape without an autochanger; when the tape fills,
> nothing further can be archived until the tape is swapped. You should
> ensure that any error condition or request to a human operator is
> reported appropriately so that the situation can be resolved reasonably
> quickly. The pg_xlog/ directory will continue to fill with WAL segment
> files until the situation is resolved. (If the file system containing
> pg_xlog/ fills up, PostgreSQL will do a PANIC shutdown. No committed
> transactions will be lost, but the database will remain offline until
> you free some space.)"
>
>
>> see my concern with a separate archive is if archive is down and
>> master gets stuck retrying to push the same segment again and again,
>> there may be a problem in recovery when archive is back up. no?
>
> See above.

also streaming is working (checked with wireshark) and WAL is being
written to by master, but if i comment out streaming in pg_hba.conf,
WAL is not copying over to slave?!

how can i verify that WAL archiving is working for slave?