Re: Master slave replication - Mailing list 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

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 

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