Re: [RFC] What should we do for reliable WAL archiving?

On Sun, Mar 16, 2014 at 7:53 PM, MauMau <maumau307(at)gmail(dot)com> wrote:
> From: "Amit Kapila" <amit(dot)kapila16(at)gmail(dot)com>
>
>> How about using pg_receivexlog for archiving purpose?
>
>
> pg_receivexlog is good in that it does fsync(). But it seems difficult to
> use correctly, and I'm not sure if I can catch all WAL segments without any
> loss. pg_receivexlog must be started with postmaster and monitored with
> some measures. This won't be very easy at least on Windows.
>
> The pg_receivexlog reference page suggests another difficulty:
>
> Notes
> When using pg_receivexlog instead of archive_command, the server will
> continue to recycle transaction log files even if the backups are not
> properly archived, since there is no command that fails. This can be worked
> around by having an archive_command that fails when the file has not been
> properly archived yet, for example:
> archive_command = 'sleep 5 && test -f /mnt/server/archivedir/%f'
>
> This suggestion is not correct, because it only checks the existence of the
> file. What if the file size is less than 16MB? How can we check if the
> file is completely archived?

The most probable reasons for un-successful archiving could be:
1. Disk space got full - pg_receivexlog makes sure while open/create new
segment file that the size of new file should be 16MB (open_walfile()). So due
to this reason there should not be a problem to above command.

2. Permission got denied - I think this will lead to failure of above archive
command mentioned by you.

3. n/w connection broken - This will also lead to failure of above command, but
here I think there is a possibility that it might have checked the existence of
in-complete wal file on archive location and consider it archived, but I think
wal_keep_segments can avoid this problem.

Also if you are on 9.4, then may be --slot parameter can help you.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com