On Tue, Jan 13, 2015 at 7:04 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 01/12/2015 02:16 PM, Antony Gelberg wrote: > >> On Mon, Jan 12, 2015 at 7:08 PM, Adrian Klaver >> <adrian.kla...@aklaver.com> wrote: >> >>> >>> On 01/12/2015 08:40 AM, Antony Gelberg wrote: >>> >>>> >>>> On Mon, Jan 12, 2015 at 6:23 PM, Adrian Klaver >>>> <adrian.kla...@aklaver.com> wrote: >>>> >>>>> >>>>> On 01/12/2015 08:10 AM, Antony Gelberg wrote: >>>>> >>>>>> >>>>>> On Mon, Jan 12, 2015 at 5:31 PM, Adrian Klaver >>>>>> <adrian.kla...@aklaver.com> wrote: >>>>>> >>>>> pg_basebackup has additional features which in your case are creating >>>>> issues. pg_dump on the other hand is pretty much a straight forward >>>>> data >>>>> dump and if you use -Fc you get compression. >>>>> >>>> >>>> >>>> So I should clarify - we want to be able to get back to the same point >>>> as we would once the WAL was applied. If we were to use pg_dump, >>>> would we lose out in any way? >>>> >>> >>> >>> pg_dump does not save WALs, so it would not work for that purpose. >>> >>> Appreciate insight as to how >>> >>>> >>>> pg_basebackup is scuppering things. >>>> >>> >>> >>> From original post it is not entirely clear whether you are using the >>> -X or -x options. The command you show does not have them, but you mention >>> -Xs. In any case it seems wal_keep_segments will need to be bumped up to >>> keep WAL segments around that are being recycled during the backup process. >>> How much will depend on a determination of fast Postgres is using/recycling >>> log segments? Looking at the turnover in the pg_xlog directory would be a >>> start. >>> >> >> The original script used -xs, but that didn't make sense, so we used >> -Xs in the end, but then we cancelled the backup as we assumed that we >> wouldn't have enough space for it uncompressed. Did we miss >> something? >> > > Not sure missed as much as not fully understand:) When you use either -x > or -X you are telling pg_basebackup that you care that the WAL files in the > backup directory are update to the point the backup completed. If you use > -Xf which the same as -x then you are saying wait till the rest of the > backup is finished then collect and copy over all the relevant WAL files. > This is where wal_keep_segments comes into play. It needs to be set high > enough that relevant WAL files in place at the beginning of the backup are > still there when the backup completes in order to have a complete set. If > you use -Xs, then a parallel process is started to copy over the WAL files > while the other data files are being copied over. Though as the docs say: > > http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html > > "As long as the client can keep up with transaction log received, using > this mode requires no extra transaction logs to be saved on the master." > Wouldn't streaming the WAL files (in a parallel process, which is what wal-stream option in pg_basebackup does) during the backup resolve the original issue he faced with WAL being removed ? So it is possible for the client to fall behind and have a WAL file be > recycled before it can be transferred. If you are experiencing this then > again wal_keep_segments is way of forcing Postgres to keep WAL files > around. The basic concept is that by default WAL files are recycled when > they fall out of scope on the primary and so you have to 'catch' them > before they do or force them to hang around. > > Compression is a separate operation and applies only in the tar format > case and should not be affected by the -x(X) options. > But I guess if you take backup on a 2nd machine, the WAL or data-files are not compressed before sending them over network. > > If it where me I would start looking at another 'machine' to offload the > backup to. Otherwise you will be looking at increasingly convoluted methods > of getting two bodies to occupy one space. +1 I agree... Best Regards, *Sameer Kumar | Database Consultant* *ASHNIK PTE. LTD.* 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533 M: *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com *[image: icons]* [image: Email patch] <http://www.ashnik.com/> This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
