Re: pg_basebackup and checkpoints

On Wed, Feb 5, 2014 at 9:15 AM, Alan Nilsson <anilsson(at)apple(dot)com> wrote:

> I tried (and finally succeeded) to do a base backup tonight using
> pg_basebackup. I was backing up a server that had no activity on it. The
> backup connected but no data was transferring, both ends sitting idle. It
> finally occurred to me to try a manual checkpoint and it then the backup
> took off and completed.
>
> According to 24.3 in the docs, pg_start_backup() performs a checkpoint.
> So I was surpassed that using the pg_baseback utility I had to manual kick
> a checkpoint before the transfer would start.
>
> Is this correct behavior?
>

The checkpoint that's started by both pg_basebackup and pg_start_backup()
is by default a "spread checkpoint", which means we intentionally try to
make it take longer, up to several minutes (depending on the value of
checkpoint_completion_target). This is normally a good idea on a busy
system, but can lead to these "interesting" results on those that are
almost idle.

Running a manual CHECKPOINT always runs it in "fast" mode, which is
checkpoint as quickly as possible, ignoring the setting of the completion
target.

My guess is you just didn't wait long enough (typically
checkpoint_completion_target * checkpoint_timeout or somewhere in that
region) - if you had, it would've started the backup for you.

If you know your system is going to be mostly idle, you can instruct
pg_basebackup to issue a fast checkpoint instead of the default one. Just
add "-c fast" to the commandline options. There should never be a need to
run a manual checkpoint.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/