Re: pg_xlog on a hot_stanby slave

On Wed, 17 Jun 2015 15:24 Xavier 12 <maniatux@gmail.com> wrote:

On 17/06/2015 03:17, Sameer Kumar wrote:

On Tue, 16 Jun 2015 16:55 Xavier 12 <maniatux@gmail.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@nas.lan:/data/pgbarman/psql01/incoming/%f'
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.

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)

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

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?

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.

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general