To: pgsql-admin@postgresql.org
From: Christian Ullrich <chris@chrullrich.net>
Subject: Re: Standalone Hot Backups
Date: Wed, 21 Aug 2013 20:53:20 +0200
Lines: 55
Message-ID: <52150CA0.4070607@chrullrich.net>
References: <1506B870-FE7D-4A13-B7BF-700F33BE9FC6@gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows NT 6.2; WOW64;
 rv:17.0) Gecko/20130801 Thunderbird/17.0.8
In-Reply-To: <1506B870-FE7D-4A13-B7BF-700F33BE9FC6@gmail.com>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

* Sergey Arlashin wrote:

> In section 'Making a Base Backup Using the Low Level API' is said that
> once one has WAL archiving set up it is ok to omit pg_xlog folder from
> the backup dump:
>
>> "You can, however, omit from the backup dump the files within the
>> cluster's pg_xlog/ subdirectory. This slight adjustment is worthwhile

> But in section related to making standalone hot backups there is no
> information about it.
>
> There is only the following set of commands:
[...]
> I only can see that the backup folder is being archived with pg_xlog.
>
> So, the question is - is it ok to omit pg_xlog folder from backup dump
> while making standalone hot backups or not ?

I'm not sure why the example does not exclude pg_xlog; IMHO it should. 
Perhaps the author of the example was using the setup mentioned in 
section 24.3.3, where pg_xlog is a symbolic link elsewhere? Restoring a 
backup created using that procedure will never need WAL files that are 
not in the (transient) archive directory.

If your PostgreSQL version includes pg_basebackup, you should use it 
rather than the low-level API. If you include the -X option, you will 
get a backup that you can use as "standalone" as well as for 
point-in-time recovery, and if you use "plain" mode as well as -X, you 
can even use the backup directory as PGDATA directly: pg_basebackup will 
put the required WAL files into pg_xlog in the backup, so there is no 
need even for a recovery.conf file. Starting the database with that 
directory (and no recovery.conf) will only perform crash recovery. If 
you leave the -X option out, you can only do PITR, for which you need a 
WAL archive.


It is interesting to follow the example's logic and see why it works:

In the example, more WAL is archived than strictly necessary to restore 
the backup: Some WAL files will appear twice in the backup, once in 
pg_xlog, once in the "archive" directory. The first tar command will 
copy them (in an unusable intermediate state) from pg_xlog. 
pg_stop_backup() will archive all files used during the backup, and the 
second tar command will then append the "archive" to the backup. When 
restoring from the backup, you must have a recovery.conf file with a 
restore_command that will retrieve files from the (restored) archive.

The important point is that PostgreSQL will prefer files from the 
archive to those in pg_xlog. Therefore, it will use the finished 
versions and ignore the files copied from pg_xlog.

-- 
Christian


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin