Re: pg_xlog on a hot_stanby slave

On 18/06/2015 04:00, Sameer Kumar wrote:
>
>
> On Wed, 17 Jun 2015 15:24 Xavier 12 <maniatux(at)gmail(dot)com
> <mailto:maniatux(at)gmail(dot)com>> wrote:
>
> On 17/06/2015 03:17, Sameer Kumar wrote:
>
>
> On Tue, 16 Jun 2015 16:55 Xavier 12 <maniatux(at)gmail(dot)com
> <mailto:maniatux(at)gmail(dot)com>> 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
> <mailto:barman(at)nas(dot)lan:/data/pgbarman/psql01/incoming/%25f>'
> max_wal_senders = 5
> wal_keep_segments = 64
>
> What's this parameter's value on Slave?
>
> Hm... You have a point.
> That autovacuum parameter seems to be useless on a slave.
> I'll try to remove it and check pg_xlog.
>
> That was not my point. I was actually asking about wal_keep_segment.
> Nevermind I found that I had misses the info (found it below. Please
> see my response).
> Besides I try to keep my master and standby config as same as
> possible(so my advise ia to not switchoff autovacuum). The parameters
> which are imeffective on slave anyways won't have an effect. Same goes
> for parameters on master.
> This helps me when I swap roles or do a failover. I have less
> parameters to be worried about.
>

Okay

> Can you check the pg_log for log files. They may have se info? I am
> sorry if you have already provided that info (after I finish I will
> try to look at your previous emails on this thread)
>

Nothing...
/var/log/postgresql/postgresql-2015-06-17_111131.log is empty (except
old messages at the begining related to a configuration issue - which is
now solved - after rebuilding the cluster yesterday).
/var/log/syslog has nothing but these :

Jun 18 09:10:11 Bdd02 postgres[28400]: [2-1] 2015-06-18 09:10:11 CEST
LOG: paquet de d?marrage incomplet
Jun 18 09:10:41 Bdd02 postgres[28523]: [2-1] 2015-06-18 09:10:41 CEST
LOG: paquet de d?marrage incomplet
Jun 18 09:11:11 Bdd02 postgres[28557]: [2-1] 2015-06-18 09:11:11 CEST
LOG: paquet de d?marrage incomplet
Jun 18 09:11:41 Bdd02 postgres[28652]: [2-1] 2015-06-18 09:11:41 CEST
LOG: paquet de d?marrage incomplet
Jun 18 09:12:11 Bdd02 postgres[28752]: [2-1] 2015-06-18 09:12:11 CEST
LOG: paquet de d?marrage incomplet
Jun 18 09:12:41 Bdd02 postgres[28862]: [2-1] 2015-06-18 09:12:41 CEST
LOG: paquet de d?marrage incomplet
Jun 18 09:13:11 Bdd02 postgres[28891]: [2-1] 2015-06-18 09:13:11 CEST
LOG: paquet de d?marrage incomplet
Jun 18 09:13:40 Bdd02 postgres[28987]: [2-1] 2015-06-18 09:13:40 CEST
LOG: paquet de d?marrage incomplet

These messages are related to Zabbix (psql port check).

> Also can you share the vacuum cost parameters in your environment?
>

I don't understand that part... is this in postgresql.conf ?

>
> autovacuum = on
>
> Slave postgresql.conf :
>
> [...]
> wal_level = minimal
> wal_keep_segments = 32
>
> Sorry I missed this somehow earlier. Any reason why you think you need
> to retain 32 wal files on slave?
>

No but I get the feeling that the parameter is ignored by my slave...
should I try another value ?

>
> 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'
>
> Also consider setting hot_standby_feesback to on.
>

I will check that parameter in the documentation,

Thanks

> How can I reduce the number of WAL files on the hot_stanby
> slave ?
>
> Thanks
>
> Regards.
>
> Xavier C.
>
> --
> Sent via pgsql-general mailing list
> (pgsql-general(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>