On 11/20/14, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote: > On 11/20/2014 04:57 PM, zach cruise wrote: >> On 11/20/14, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
