Обсуждение: Re: WAL file accumulation on log shipping primary node
Dear all,
We have setup log shipping replication between primary node and 2 secondary nodes with Postgresql version 8.4.8. From the data replication points of view, everything is fine. However, under heavy data modification load - we have an extremely busy system, we saw WAL files keep accumulating on the primary node - new WAL files are copied to secondary node and restored properly, but the primary node keeps holding a large number of old WAL files. To the end, the WAL files fill up the database partition. Previously, we tested in a lighter load environment, we did not have similar problem.
Please help to diagnosis the problem, and give me recommendations on how to fix this problem.
Thank you very much.
Dong Han
Dong, How are you copying the wal logs over to the secondary ? Since this is loosely coupled I am wondering how the master would be effected by load. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Tue, Dec 13, 2011 at 12:42 AM, Dong Han <dhan@bluecatnetworks.com> wrote: > Dear all, > > > > We have setup log shipping replication between primary node and 2 secondary > nodes with Postgresql version 8.4.8. From the data replication points of > view, everything is fine. However, under heavy data modification load - we > have an extremely busy system, we saw WAL files keep accumulating on the > primary node - new WAL files are copied to secondary node and restored > properly, but the primary node keeps holding a large number of old WAL > files. To the end, the WAL files fill up the database partition. Previously, > we tested in a lighter load environment, we did not have similar problem. > > > > Please help to diagnosis the problem, and give me recommendations on how to > fix this problem. > > > > Thank you very much. > > > > Dong Han > > > >
Dave, The WAL files were copied using rsync. It seems there is no problem to copy the file over to secondary nodes. It is the primarynode somehow keeps more and more old WAL files in the pg_xlog directory. Regards, Dong ________________________________________ From: davecramer@gmail.com [davecramer@gmail.com] on behalf of Dave Cramer [pg@fastcrypt.com] Sent: Monday, December 12, 2011 8:15 PM To: Dong Han Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] WAL file accumulation on log shipping primary node Dong, How are you copying the wal logs over to the secondary ? Since this is loosely coupled I am wondering how the master would be effected by load. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Tue, Dec 13, 2011 at 12:42 AM, Dong Han <dhan@bluecatnetworks.com> wrote: > Dear all, > > > > We have setup log shipping replication between primary node and 2 secondary > nodes with Postgresql version 8.4.8. From the data replication points of > view, everything is fine. However, under heavy data modification load - we > have an extremely busy system, we saw WAL files keep accumulating on the > primary node - new WAL files are copied to secondary node and restored > properly, but the primary node keeps holding a large number of old WAL > files. To the end, the WAL files fill up the database partition. Previously, > we tested in a lighter load environment, we did not have similar problem. > > > > Please help to diagnosis the problem, and give me recommendations on how to > fix this problem. > > > > Thank you very much. > > > > Dong Han > > > >
Dong, Once the wal_copy command completes the primary is free to handle the wal logs as it sees fit. Is it possible that rsync is getting behind ? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Mon, Dec 12, 2011 at 11:28 PM, Dong Han <dhan@bluecatnetworks.com> wrote: > Dave, > > The WAL files were copied using rsync. It seems there is no problem to copy the file over to secondary nodes. It is theprimary node somehow keeps more and more old WAL files in the pg_xlog directory. > > Regards, > > Dong > ________________________________________ > From: davecramer@gmail.com [davecramer@gmail.com] on behalf of Dave Cramer [pg@fastcrypt.com] > Sent: Monday, December 12, 2011 8:15 PM > To: Dong Han > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] WAL file accumulation on log shipping primary node > > Dong, > > How are you copying the wal logs over to the secondary ? Since this is > loosely coupled I am wondering how the master would be effected by > load. > > Dave Cramer > > dave.cramer(at)credativ(dot)ca > http://www.credativ.ca > > > > On Tue, Dec 13, 2011 at 12:42 AM, Dong Han <dhan@bluecatnetworks.com> wrote: >> Dear all, >> >> >> >> We have setup log shipping replication between primary node and 2 secondary >> nodes with Postgresql version 8.4.8. From the data replication points of >> view, everything is fine. However, under heavy data modification load - we >> have an extremely busy system, we saw WAL files keep accumulating on the >> primary node - new WAL files are copied to secondary node and restored >> properly, but the primary node keeps holding a large number of old WAL >> files. To the end, the WAL files fill up the database partition. Previously, >> we tested in a lighter load environment, we did not have similar problem. >> >> >> >> Please help to diagnosis the problem, and give me recommendations on how to >> fix this problem. >> >> >> >> Thank you very much. >> >> >> >> Dong Han >> >> >> >>