Re: WAL archive cleanup om master - Mailing list pgsql-general

On 01/06/2014 08:45 AM, Martijn van Oosterhout wrote:
> On Mon, Jan 06, 2014 at 08:17:37AM -0800, Adrian Klaver wrote:
>> On 01/06/2014 07:35 AM, Martijn van Oosterhout wrote:
>>> On Mon, Jan 06, 2014 at 07:16:25AM -0800, Adrian Klaver wrote:
>>>> On 01/06/2014 03:18 AM, Martijn van Oosterhout wrote:
>>>>> Hoi,
>>>>>
>>>>>
>>>
>>> I'm not sure what you mean, isn't this the recommended way of doing
>>> things? The configuration comes from here:
>>>
>>> http://wiki.postgresql.org/wiki/Hot_Standby
>>>
>>> The master saves the archives to a directory, rsync copies them to
>>> the slave, where there restore_command can find them.
>>
>> Well this is the above is where is could be redundant. In your
>> original post it would seem the archive_command and restore_command
>> are pointing at the same directory. I realize they are just
>> placeholder names, so is that the case?  If so I am not sure what
>> the rsync accomplishes. If not why not just make it so?
>
> Well, they're the same directory on different machines. The directory
> is actually /var/lib/postgresql/9.2/xlogs on both, but that's not
> really relevent.
>
> There's a cronjob on the master that says:
>
> rsync -avz /var/lib/postgresql/9.2/xlogs/* slave:/var/lib/postgresql/9.2/xlogs/
>
> The question is: what is it that prevents the WAL files in
> /var/lib/postgresql/9.2/xlogs from filling the disk on the master?
>
>> The minimal cover your bases setup is usually given as:
>>
>>
>> Primary(Machine1) --> Archive --> Archive Directory(Machine2)
>>     |                 |
>>     |  Streaming        Restore     |
>>     ->    Standby(Machine3) < -
>>
>> Excuse the ASCII art.
>
> The ascii art is fine, but you have the same problem: on the Machine1
> the WAL files are stored prior to copying to Machine2.  How do you know
> when you can delete files safely on Machine1?


WAL files are recycled by the server.

http://www.postgresql.org/docs/9.3/interactive/continuous-archiving.html

24.3.1. Setting Up WAL Archiving
" When not using WAL archiving, the system normally creates just a few
segment files and then "recycles" them by renaming no-longer-needed
segment files to higher segment numbers. It's assumed that segment files
whose contents precede the checkpoint-before-last are no longer of
interest and can be recycled.

When archiving WAL data, we need to capture the contents of each segment
file once it is filled, and save that data somewhere before the segment
file is recycled for reuse. "

For a more detailed explanation of those settings that affect this see:

http://www.postgresql.org/docs/9.3/interactive/wal-configuration.html

in particular

http://www.postgresql.org/docs/9.3/interactive/runtime-config-replication.html#GUC-WAL-KEEP-SEGMENTS


>
> Anyway, I found this page on the wiki:
>
> http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
>
> which says that:
>
> archive_command = 'rsync -a %p 192.168.0.2:/var/lib/pgsql/data/archive/%f'
>
> is acceptable and also avoids the problem. I'll just test what happens
> with network failure (hopefully it doesn't kill the master).
>
> Have a nice day,
>


--
Adrian Klaver
adrian.klaver@gmail.com