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

From: Amit Langote <amitlangote09(at)gmail(dot)com>
To: Neil McGuigan <neilmcguigan(at)gmail(dot)com>
Cc: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Re: How to do incremental / differential backup every hour in Postgres 9.1?
Date: 2013-07-26 07:26:15
Message-ID: CA+HiwqHvLskDRLaEcNKTnOPmc+8QW64Cxw+o_58O9Fb0JbKSQQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Fri, Jul 26, 2013 at 7:24 AM, Neil McGuigan <neilmcguigan(at)gmail(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message amulsul 2013-07-26 08:32:24 Re: How to do incremental / differential backup every hour in Postgres 9.1?
Previous Message Joe Van Dyk 2013-07-26 07:16:31 Re: casting tsrange to tstzrange doesn't seem to work?