Re: pg_xlog on a hot_stanby slave

On 29/06/2015 11:38, Stéphane Schildknecht wrote:
> On 16/06/2015 10:55, Xavier 12 wrote:
>> Hi everyone,
>>
>> Questions about pg_xlogs again...
>> I have two Postgresql 9.1 servers in a master/slave stream replication
>> (hot_standby).
>>
>> Psql01 (master) is backuped with Barman and pg_xlogs is correctly
>> purged (archive_command is used).
>>
>> Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days
>> only, it keeps growing up until disk space is full). I have found
>> documentation and tutorials, mailing list, but I don't know what is
>> suitable for a Slave. Leads I've found :
>>
>> - checkpoints
>> - archive_command
>> - archive_cleanup
>>
>> Master postgresq.conf :
>>
>> [...]
>> wal_level = 'hot_standby'
>> archive_mode = on
>> archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f
>> barman@nas.lan:/data/pgbarman/psql01/incoming/%f'
>> max_wal_senders = 5
>> wal_keep_segments = 64
>> autovacuum = on
>>
>> Slave postgresql.conf :
>>
>> [...]
>> wal_level = minimal
>> wal_keep_segments = 32
>> hot_standby = on
>>
>> Slave recovery.conf :
>>
>> standby_mode = 'on'
>> primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres'
>> trigger_file = '/var/lib/postgresql/9.1/triggersql'
>> restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f "%p"'
>> archive_cleanup_command =
>> '/usr/lib/postgresql/9.1/bin/pg_archivecleanup
>> /var/lib/postgresql/9.1/wal_archive/ %r'
>>
>>
>>
>>
>>
>> How can I reduce the number of WAL files on the hot_stanby slave ?
>>
>> Thanks
>>
>> Regards.
>>
>> Xavier C.
>>
>>
>
> I wonder why you are doing "cp" in your recovery.conf on the slave.
> That is "quite" correct when the streaming can't get WAL from the master. But
> cp is probably not the right tool.
>
> You also cp from the master archive directory, and are cleaning on that
> directory as well.
>
> You don't clean up the standby xlog directory. And cp may copy incomplete WAL
> files.
>
> The streaming replication can take care of your xlog clean up, until you
> introduce WAL files by another mean (manual cp for instance).
>
> S.
>
cp because /var/lib/postgresq/9.1/wal_archive/ is a temporary directory.
I use it to build the replication (copy wal with rsync from the master).
The slave use it to start, then next wal are written ton
/var/lib/postgresql/9.1/main/pg_xlog.

Xavier C.