Re: pg_xlog on a hot_stanby slave

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(at)nas(dot)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.

--
Stéphane Schildknecht
Contact régional PostgreSQL pour l'Europe francophone
Loxodata - Conseil, expertise et formations
06.17.11.37.42