Обсуждение: How to do incremental / differential backup every hour in Postgres 9.1?

On Fri, Jul 26, 2013 at 7:24 AM, Neil McGuigan <neilmcguigan@gmail.com> wrote:
> 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.
>
> 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?
>

pg_start_backup('label') and pg_stop_backup() do not perform actual
copy (full or incremental) of your data. They merely mark the start
and end of a backup operation. Moreover, pg_start_backup() performs
actions (like checkpoint) necessary to prepare a snapshot of your data
(which you would eventually copy/backup) that you can consistently
recover from later.

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

The label is used to name a file included in the backup directory that
contains information required to recover from a backup.

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

You don't need to manually move any of the files in pg_xlog/,
PostgreSQL automatically gets rid of or recycles the files.

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

Your archiving setup (that is archive_command) should take care of all
that needs to be copied to the archive location.

Have you read about pg_rman? Read about it at the following link:
https://code.google.com/p/pg-rman/wiki/readme


--
Amit Langote

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

you have already use  --xlog   as -x in your above command of  pg_basebackup
.

yes it better to change wal_keep_segments, if you want start a postmaster
directly in the extracted directory without the need to consult the log
archive, thus making this a completely standalone backup.


Regards,
Amul Sul



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-to-do-incremental-differential-backup-every-hour-in-Postgres-9-1-tp5765208p5765249.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

On Thu, Jul 25, 2013 at 3:24 PM, Neil McGuigan <neilmcguigan@gmail.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

Jeff Janes <jeff.janes@gmail.com> wrote:
> Neil McGuigan <neilmcguigan@gmail.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.

Yeah, if you are archiving WAL and set archive_timeout = 1h, you
have all the recovery options that an hourly incremental backup
gives you, and then some.  Basically, incremental backup as
described by most people I've heard would only have one advantage
over this -- the incremental backups would essentially be a
*summary* of the WAL for each hour, taking less space (at the cost
of not being able to pick your recovery point at transaction
granularity).

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company