Thread: Deleting WAL archives and pg_xlog when there is not a shared drive
Hello everyone,
I am trying to implement a high-availability cluster using only two nodes, without any shared disk storage.
In my implementation, the primary database has continuous archiving set up to a directory residing on the second node, where the standby database is. Streaming replication is also established between the two. When failover occurs, the standby is promoted to primary, and will start its continuous archiving but now on a directory on the other (former primary) node.
Call the primary node N1 and the standby N2. When N1 fails and N2 is promoted, can I safely delete the archive logs stored on N2 (which were archived by N1 when it was primary?)? Also, when N1 is started but now it must become a standby, I run pg_start_backup() on N2, sync the data directories (except pg_xlog) then pg_stop_backup() on N2. Can I safely delete everything under pg_xlog in N1 BEFORE starting it since anyways they won't be used (what will be used instead is the archive directory on N1 which is being populated by N2)?
Please let me know if my explanation wasn't clear. Thanks a lot!
- Yamen Laliberté
I am trying to implement a high-availability cluster using only two nodes, without any shared disk storage.
In my implementation, the primary database has continuous archiving set up to a directory residing on the second node, where the standby database is. Streaming replication is also established between the two. When failover occurs, the standby is promoted to primary, and will start its continuous archiving but now on a directory on the other (former primary) node.
Call the primary node N1 and the standby N2. When N1 fails and N2 is promoted, can I safely delete the archive logs stored on N2 (which were archived by N1 when it was primary?)? Also, when N1 is started but now it must become a standby, I run pg_start_backup() on N2, sync the data directories (except pg_xlog) then pg_stop_backup() on N2. Can I safely delete everything under pg_xlog in N1 BEFORE starting it since anyways they won't be used (what will be used instead is the archive directory on N1 which is being populated by N2)?
Please let me know if my explanation wasn't clear. Thanks a lot!
- Yamen Laliberté
On Tue, Dec 11, 2012 at 9:37 AM, Eng. AlSamman <iyamen@live.com> wrote: > I am trying to implement a high-availability cluster using only two nodes, > without any shared disk storage. > > In my implementation, the primary database has continuous archiving set up > to a directory residing on the second node, where the standby database is. > Streaming replication is also established between the two. When failover > occurs, the standby is promoted to primary, and will start its continuous > archiving but now on a directory on the other (former primary) node. > > Call the primary node N1 and the standby N2. When N1 fails and N2 is > promoted, can I safely delete the archive logs stored on N2 (which were > archived by N1 when it was primary?)? You can. > Also, when N1 is started but now it > must become a standby, I run pg_start_backup() on N2, sync the data > directories (except pg_xlog) then pg_stop_backup() on N2. You must not start N1 before you have done a base backup (pg_start_backup()/pg_stop_backup()). > Can I safely > delete everything under pg_xlog in N1 BEFORE starting it since anyways they > won't be used (what will be used instead is the archive directory on N1 > which is being populated by N2)? You can delete everything under N1 data directory before making a base backup. -- Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray.ru@gmail.com
On Tue, Dec 11, 2012 at 9:37 AM, Eng. AlSamman <iyamen@live.com> wrote: > Hello everyone, > > I am trying to implement a high-availability cluster using only two nodes, > without any shared disk storage. > > In my implementation, the primary database has continuous archiving set up > to a directory residing on the second node, where the standby database is. > Streaming replication is also established between the two. When failover > occurs, the standby is promoted to primary, and will start its continuous > archiving but now on a directory on the other (former primary) node. > > Call the primary node N1 and the standby N2. When N1 fails and N2 is > promoted, can I safely delete the archive logs stored on N2 (which were > archived by N1 when it was primary?)? That depends on your backup policy. Do you try to maintain some kind of PITR availability window, or are you solely concerned with hardware issues and not human error? In any case, in the middle of an unusual event seems like a poor time to go around cleaning up only tangentially related things. Cheers, Jeff
Thank you for your input.
> From: gray.ru@gmail.com
> Date: Tue, 11 Dec 2012 14:06:32 -0800
> Subject: Re: [GENERAL] Deleting WAL archives and pg_xlog when there is not a shared drive
> To: iyamen@live.com
> CC: pgsql-general@postgresql.org
>
> On Tue, Dec 11, 2012 at 9:37 AM, Eng. AlSamman <iyamen@live.com> wrote:
> > I am trying to implement a high-availability cluster using only two nodes,
> > without any shared disk storage.
> >
> > In my implementation, the primary database has continuous archiving set up
> > to a directory residing on the second node, where the standby database is.
> > Streaming replication is also established between the two. When failover
> > occurs, the standby is promoted to primary, and will start its continuous
> > archiving but now on a directory on the other (former primary) node.
> >
> > Call the primary node N1 and the standby N2. When N1 fails and N2 is
> > promoted, can I safely delete the archive logs stored on N2 (which were
> > archived by N1 when it was primary?)?
>
> You can.
>
> > Also, when N1 is started but now it
> > must become a standby, I run pg_start_backup() on N2, sync the data
> > directories (except pg_xlog) then pg_stop_backup() on N2.
>
> You must not start N1 before you have done a base backup
> (pg_start_backup()/pg_stop_backup()).
>
> > Can I safely
> > delete everything under pg_xlog in N1 BEFORE starting it since anyways they
> > won't be used (what will be used instead is the archive directory on N1
> > which is being populated by N2)?
>
> You can delete everything under N1 data directory before making a base backup.
>
> --
> Database and Software Architect
> http://www.linkedin.com/in/grayhemp
>
> Phones:
> USA +1 415 867 9984
> Russia, Moscow +7 901 903 0499
> Russia, Krasnodar +7 988 888 1979
>
> Skype: gray-hemp
> Jabber: gray.ru@gmail.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> Date: Tue, 11 Dec 2012 14:06:32 -0800
> Subject: Re: [GENERAL] Deleting WAL archives and pg_xlog when there is not a shared drive
> To: iyamen@live.com
> CC: pgsql-general@postgresql.org
>
> On Tue, Dec 11, 2012 at 9:37 AM, Eng. AlSamman <iyamen@live.com> wrote:
> > I am trying to implement a high-availability cluster using only two nodes,
> > without any shared disk storage.
> >
> > In my implementation, the primary database has continuous archiving set up
> > to a directory residing on the second node, where the standby database is.
> > Streaming replication is also established between the two. When failover
> > occurs, the standby is promoted to primary, and will start its continuous
> > archiving but now on a directory on the other (former primary) node.
> >
> > Call the primary node N1 and the standby N2. When N1 fails and N2 is
> > promoted, can I safely delete the archive logs stored on N2 (which were
> > archived by N1 when it was primary?)?
>
> You can.
>
> > Also, when N1 is started but now it
> > must become a standby, I run pg_start_backup() on N2, sync the data
> > directories (except pg_xlog) then pg_stop_backup() on N2.
>
> You must not start N1 before you have done a base backup
> (pg_start_backup()/pg_stop_backup()).
>
> > Can I safely
> > delete everything under pg_xlog in N1 BEFORE starting it since anyways they
> > won't be used (what will be used instead is the archive directory on N1
> > which is being populated by N2)?
>
> You can delete everything under N1 data directory before making a base backup.
>
> --
> Database and Software Architect
> http://www.linkedin.com/in/grayhemp
>
> Phones:
> USA +1 415 867 9984
> Russia, Moscow +7 901 903 0499
> Russia, Krasnodar +7 988 888 1979
>
> Skype: gray-hemp
> Jabber: gray.ru@gmail.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Thanks for the input Jeff. The purpose is primarily automating failover (high-availability). There is no intention of using PITR.
We decided to actually keep the WAL logs in the archive directory (until they start to pose a storage problem) and delete what's under pg_xlog (in the situation I already mentioned in my original message).
We decided to actually keep the WAL logs in the archive directory (until they start to pose a storage problem) and delete what's under pg_xlog (in the situation I already mentioned in my original message).
> Date: Wed, 12 Dec 2012 08:24:21 -0800
> Subject: Re: [GENERAL] Deleting WAL archives and pg_xlog when there is not a shared drive
> From: jeff.janes@gmail.com
> To: iyamen@live.com
> CC: pgsql-general@postgresql.org
>
> On Tue, Dec 11, 2012 at 9:37 AM, Eng. AlSamman <iyamen@live.com> wrote:
> > Hello everyone,
> >
> > I am trying to implement a high-availability cluster using only two nodes,
> > without any shared disk storage.
> >
> > In my implementation, the primary database has continuous archiving set up
> > to a directory residing on the second node, where the standby database is.
> > Streaming replication is also established between the two. When failover
> > occurs, the standby is promoted to primary, and will start its continuous
> > archiving but now on a directory on the other (former primary) node.
> >
> > Call the primary node N1 and the standby N2. When N1 fails and N2 is
> > promoted, can I safely delete the archive logs stored on N2 (which were
> > archived by N1 when it was primary?)?
>
> That depends on your backup policy. Do you try to maintain some kind
> of PITR availability window, or are you solely concerned with hardware
> issues and not human error?
>
> In any case, in the middle of an unusual event seems like a poor time
> to go around cleaning up only tangentially related things.
>
> Cheers,
>
> Jeff
> Subject: Re: [GENERAL] Deleting WAL archives and pg_xlog when there is not a shared drive
> From: jeff.janes@gmail.com
> To: iyamen@live.com
> CC: pgsql-general@postgresql.org
>
> On Tue, Dec 11, 2012 at 9:37 AM, Eng. AlSamman <iyamen@live.com> wrote:
> > Hello everyone,
> >
> > I am trying to implement a high-availability cluster using only two nodes,
> > without any shared disk storage.
> >
> > In my implementation, the primary database has continuous archiving set up
> > to a directory residing on the second node, where the standby database is.
> > Streaming replication is also established between the two. When failover
> > occurs, the standby is promoted to primary, and will start its continuous
> > archiving but now on a directory on the other (former primary) node.
> >
> > Call the primary node N1 and the standby N2. When N1 fails and N2 is
> > promoted, can I safely delete the archive logs stored on N2 (which were
> > archived by N1 when it was primary?)?
>
> That depends on your backup policy. Do you try to maintain some kind
> of PITR availability window, or are you solely concerned with hardware
> issues and not human error?
>
> In any case, in the middle of an unusual event seems like a poor time
> to go around cleaning up only tangentially related things.
>
> Cheers,
>
> Jeff