Re: Master slave replication

Поиск
Список
Период
Сортировка
От Fabio Pardi
Тема Re: Master slave replication
Дата
Msg-id 8a596bf1-ef04-36e8-6e19-56eb5b9632a0@portavita.eu
обсуждение исходный текст
Ответ на Re: Master slave replication  (arvind chikne <arvind.chikne@gmail.com>)
Список pgsql-admin
Hi Arvind,

I got fooled by your statement 'my px_xlog and log_archive gets full'...

What probably is happening, is that first your archive folder gets full, then your pg_xlog starts to fill up until full. Then db stops operations.

That is normal, because if the archive folder gets full, then WAL files are retained and start to pile up on your pg_xlog folder until it becomes full too or the archiving is possible again.

Is good practice to give some intelligence to the archive command and take actions if the archive is (getting) full.

Together with a cronjob which archives or deletes WAL files present in the archive older than your retention period.


regards,

fabio pardi


On 19/09/18 12:22, arvind chikne wrote:
Hi Fabio, 

Thanks for your comment but my px_xlog, log_archive and data_dir all three are in the different-different partition.
pls see the attached snapshot 
image.png

On Wed, Sep 19, 2018 at 3:44 PM Fabio Pardi <f.pardi@portavita.eu> wrote:
Hi Arvind,

From what you describe, looks like you are sharing WAL and archive under the same partition. It is not good idea.

A lot depends from how much disk space you have available and how you setup the disks, but from what i see, max_wal_size in your configuration is quite big. I would set it to something smaller, and make sure that archive, data and pg_xlog are on different partitions or better, disks

https://www.postgresql.org/docs/9.5/static/runtime-config-wal.html#GUC-MAX-WAL-SIZE

regards,

fabio pardi



On 19/09/18 11:54, arvind chikne wrote:
Hi All, 

I have successfully configured the master slave replication through WAL, but after some time my px_xlog and log_archive gets full is there any option to set the limit for the same 


Error
cp: error writing '/var/lib/postgresql/9.5/main/log_archive/00000001000002E0000000D5': No space left on device
2018-09-19 15:19:19 IST [15642-34394] LOG:  archive command failed with exit code 1
2018-09-19 15:19:19 IST [15642-34395] DETAIL:  The failed archive command was: cp pg_xlog/00000001000002E0000000D5 /var/lib/postgresql/9.5/main/log_archive/00000001000002E0000000D5
cp: error writing '/var/lib/postgresql/9.5/main/log_archive/00000001000002E0000000D5': No space left on device
2018-09-19 15:19:20 IST [15642-34396] LOG:  archive command failed with exit code 1
2018-09-19 15:19:20 IST [15642-34397] DETAIL:  The failed archive command was: cp pg_xlog/00000001000002E0000000D5 /var/lib/postgresql/9.5/main/log_archive/00000001000002E0000000D5
2018-09-19 15:19:20 IST [15642-34398] WARNING:  archiving transaction log file "00000001000002E0000000D5" failed too many times, will try again later
cp: error writing '/var/lib/postgresql/9.5/main/log_archive/00000001000002E0000000D5': No space left on device
2018-09-19 15:19:24 IST [15642-34399] LOG:  archive command failed with exit code 1
2018-09-19 15:19:24 IST [15642-34400] DETAIL:  The failed archive command was: cp pg_xlog/00000001000002E0000000D5 /var/lib/postgresql/9.5/main/log_archive/00000001000002E0000000D5

pls see my postgres.conf
max_wal_size = 100GB
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/9.5/main/log_archive/%f'
max_wal_senders = 5  
wal_keep_segments = 32 
hot_standby = on

My recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=XXX.XXX.XXX.XXX port=5432 user=rep password=REP'

trigger_file = '/tmp/postgresql.trigger.5432'
restore_command = 'cp /var/lib/postgresql/9.5/main/log_archive/%f "%p"'
archive_cleanup_command = 'pg_archivecleanup /var/lib/postgresql/9.5/main/log_archive/ %r'


--
Thanks & Regards
Arvind rao chikne
9818979986
Think Green!!! Please consider your environmental responsibility before printing this e-mail




--
Thanks & Regards
Arvind rao chikne
9818979986
Think Green!!! Please consider your environmental responsibility before printing this e-mail


Вложения

В списке pgsql-admin по дате отправления:

Предыдущее
От: Adam Jensen
Дата:
Сообщение: Re: postgis Installation
Следующее
От: Shreeyansh Dba
Дата:
Сообщение: Re: postgis Installation