Re: pg_xlog on a hot_stanby slave - Mailing list pgsql-general

From Xavier 12
Subject Re: pg_xlog on a hot_stanby slave
Date
Msg-id 55811FC5.60806@gmail.com
Whole thread Raw
In response to Re: pg_xlog on a hot_stanby slave  (Guillaume Lelarge <guillaume@lelarge.info>)
List pgsql-general


On 16/06/2015 22:28, Guillaume Lelarge wrote:
2015-06-16 15:55 GMT+02:00 Xavier 12 <maniatux@gmail.com>:
I don't think so. There is no archive_command and the master doesn't
ship its wal here.
But how can I check that ?


What's the complete path to the directory on the salve that contains 951 files? what does PostgreSQL say on its log files?

in /var/lib/postgresql/9.1/main/pg_xlog/

1059 files today.
Too much to copy/paste here.
Here are the first ones :

-rw------- 1 postgres postgres 16777216 Jun  9 08:40 000000040000040B0000007E
-rw------- 1 postgres postgres 16777216 Jun  9 08:41 000000040000040B0000007F
-rw------- 1 postgres postgres 16777216 Jun  9 08:42 000000040000040B00000080
-rw------- 1 postgres postgres 16777216 Jun  9 08:44 000000040000040B00000081

There are no .done or .ready and archive_status is empty.

Nothing critical in the logs :

Jun 17 08:55:11 psql02 postgres[4231]: [2-1] 2015-06-17 08:55:11 CEST LOG:  paquet de d?marrage incomplet
Jun 17 08:55:41 psql02 postgres[4322]: [2-1] 2015-06-17 08:55:41 CEST LOG:  paquet de d?marrage incomplet
Jun 17 08:56:11 psql02 postgres[4356]: [2-1] 2015-06-17 08:56:11 CEST LOG:  paquet de d?marrage incomplet
Jun 17 08:56:41 psql02postgres[4460]: [2-1] 2015-06-17 08:56:41 CEST LOG:  paquet de d?marrage incomplet
Jun 17 08:56:55 psql02postgres[4514]: [2-1] 2015-06-17 08:56:55 CEST ERREUR:  restauration en cours
Jun 17 08:56:55 psql02postgres[4514]: [2-2] 2015-06-17 08:56:55 CEST ASTUCE :  les fonctions de contr?le des journaux de transactions ne peuvent pas
Jun 17 08:56:55 psql02postgres[4514]: [2-3] #011?tre ex?cut?es lors de la restauration.
Jun 17 08:56:55 psql02postgres[4514]: [2-4] 2015-06-17 08:56:55 CEST INSTRUCTION :  select pg_current_xlog_location()

pg_current_xlog_location() is for a zabbix check, "ERREUR" is because that server is readyonly.

Xavier C.



2015-06-16 12:41 GMT+02:00 Guillaume Lelarge <guillaume@lelarge.info>:
> Le 16 juin 2015 10:57 AM, "Xavier 12" <maniatux@gmail.com> a écrit :
>>
>> 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 ?
>>
>
> Depends on what you're talking about. If they are archived wal,
> pg_archive_cleanup is what you're looking for.



--

pgsql-general by date:

Previous
From: Albe Laurenz
Date:
Subject: Re: pg_dump 8.4.9 failing after upgrade to openssl-1.0.1e-30.el6_6.11.x86_64 on redhat linux
Next
From: Xavier 12
Date:
Subject: Re: pg_xlog on a hot_stanby slave