Re: How to do incremental / differential backup every hour in Postgres 9.1?

On Thu, Jul 25, 2013 at 3:24 PM, Neil McGuigan <neilmcguigan(at)gmail(dot)com> wrote:
> Trying to do an hourly hot incremental backup of a single postgres server
> (windows).

Can you explain what "incremental backup" means to you? I find that
there is a surprising variety of opinions about what these terms mean.
To me, the accumulated wal archive *is* the incremental backup.

> archive_mode=on
> archive_command='copy "%p" "c:\\postgres\\archive\\%f"'
...
>
> Questions:
>
> 1. what command(s) do I run to do a new incremental backup (pg_basebackup
> does a new base backup which I don't want right now)? do I just run select
> pg_start_backup('label'); select pg_stop_backup(); on a schedule?

You shouldn't need to do anything (including pg_start_backup), your
archive_command should already be doing it, inherently. I guess you
need to backup c:\postgres\archive\, if it is not already being
backed up.

> 2. What does the label in pg_basebackup and pg_start_backup() do exactly?

It names your backup. The name is entirely for your convenience.

>
> 3. WAL Files don't seem to be removed from pg_xlog. What should I do about
> that? It seems to keep 5 or so WALs in pg_xlog. Is that to be expected?

If they weren't removed (or recycled), then there would eventually be
far more than 5, so they probably are getting removed as appropriate.
You must have checkpoint_segments set pretty low in for there to be
only 5.

>
> 4. Do I need to backup the .backup files in the archive folder or just the
> 16,384KB WAL files?

The .backup files are not strictly necessary, they are for your
convenience. But they are small and sometimes useful, so why not keep
them? There should be one of them for each base backup you have made.

> 5. should I use the --xlog parameter and if so do I need to change
> wal_keep_segments from 0?

Since you have archive_mode=on, you should not need -xlog unless you
are trying to do something special. I think it just leads to
confusion to use it when it is not necessary.

Cheers,

Jeff