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

Hi Neil,

Il 26/07/2013 00:24, Neil McGuigan ha scritto:
> Trying to do an hourly hot incremental backup of a single postgres
> server (windows).
>
> I have the following setup in postgresql.conf:
>
> max_wal_senders=2
> wal_level=archive
> archive_mode=on
> archive_command='copy "%p" "c:\\postgres\\archive\\%f"'
> I did a base backup with pg_basebackup -U postgres -D ..\archive -Ft
> -l 2013-07-07 -x
>
> Which made a big base.tar file in the archive folder and added some
> long file name files, which I assume are the WALs.

Yes, they are WAL files. WAL filename follows a definite format, made by
24 digits.

>
> pg_start_backup('label') and pg_stop_backup() seem to create the WAL
> files in xp_log, and then copy them to the archive folder.
>
> 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?

Yes, you have to schedule on Windows Scheduler pg_start_backup() and
pg_stop_backup() every time is needed, without doing a base backup.
pg_start_backup() function performs on-line backup and pg_stop_backup()
finishes the performing, meaning that they take care of WAL and backup
copy from the data directory, not of backup creation. So, try to
schedule also a single pg_basebackup to have an updated base backup.

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

The label in pg_start_backup() can be any arbitrary user-defined label.
A good practice is to usethe name under which the backup dump file will
be stored.The label is not used later by any other PostgreSQL command.

>
> 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?

Which PostgreSQL version are you using? WALs should be removed
automatically after the archive_command starting from 8.2. Anyway, do a
check in pg_xlog/archive_status/ if it contains a matching
XXXXXXXX.backup.done file.

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

The .backup file is very small, and contains some information about the
backup. I'm not sure that it is strictly necessary to move it in the
archive folder, anyway it is so small (<1kB) so it could be lightly copied.

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

You're already using the -x option, this is the reason you're including
WAL files in the backup when you launch pg_basebackup. Notice that WALs
are collected at the end of the backup, so you need to set
wal_keep_segments parameter high enough that the log is not removed
before the end of the backup.

Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it