Re: Stuck trying to backup large database - best practice?

On 01/12/2015 02:16 PM, Antony Gelberg wrote:

On Mon, Jan 12, 2015 at 7:08 PM, Adrian Klaver
<adrian.klaver@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.klaver@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.klaver@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."

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.

​

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.