Re: Master slave replication

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(dot)pardi(at)portavita(dot)eu <mailto:f(dot)pardi(at)portavita(dot)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 partitionsor 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
>