pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWAL segment management on a standby
6+ messages / 3 participants
[nested] [flat]
* WAL segment management on a standby
@ 2014-07-11 16:49 John Scalia <jayknowsunix@gmail.com>
2014-07-11 16:58 ` Re: WAL segment management on a standby Matheus de Oliveira <matioli.matheus@gmail.com>
2014-07-14 02:18 ` Re: WAL segment management on a standby Craig Ringer <craig@2ndquadrant.com>
0 siblings, 2 replies; 6+ messages in thread
From: John Scalia @ 2014-07-11 16:49 UTC (permalink / raw)
To: pgsql-admin
Again, thanks to all to have assisted me with getting the WAL segments to both my standby servers. Everything with that is now working quite well. I do have a related followup
question, however. On these standby's nothing is built in to manage those WAL segments in the archive directory. Thus, that directory can grow to the point where it fills up the
disk rather quickly. Any good strategies for dealing with the WAL segments that get put in there. Do I really need them after postgresql has copied them up into the pg_xlog
directory? If so, how far back should I keep them? Yes, I know about keeping everything between backups. So, if my directory looks like:
00000000300000000C000000A1
00000000300000000C000000A2
00000000300000000C000000A3
00000000300000000C000000A3.backup
00000000300000000C000000A4
Could I safely delete the *A1, *A2, and maybe even the *A3 files?
--
Jay
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL segment management on a standby
2014-07-11 16:49 WAL segment management on a standby John Scalia <jayknowsunix@gmail.com>
@ 2014-07-11 16:58 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2014-07-11 17:56 ` Re: WAL segment management on a standby John Scalia <jayknowsunix@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Matheus de Oliveira @ 2014-07-11 16:58 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
On Fri, Jul 11, 2014 at 1:49 PM, John Scalia <jayknowsunix@gmail.com> wrote:
> 00000000300000000C000000A1
> 00000000300000000C000000A2
> 00000000300000000C000000A3
> 00000000300000000C000000A3.backup
> 00000000300000000C000000A4
>
> Could I safely delete the *A1, *A2, and maybe even the *A3 files?
>
If you only care to storing files needed for backup made at
00000000300000000C000000A3, you can remove 00000000300000000C000000A1 and
00000000300000000C000000A2, but not the others.
Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL segment management on a standby
2014-07-11 16:49 WAL segment management on a standby John Scalia <jayknowsunix@gmail.com>
2014-07-11 16:58 ` Re: WAL segment management on a standby Matheus de Oliveira <matioli.matheus@gmail.com>
@ 2014-07-11 17:56 ` John Scalia <jayknowsunix@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: John Scalia @ 2014-07-11 17:56 UTC (permalink / raw)
To: Matheus de Oliveira <matioli.matheus@gmail.com>; pgsql-admin
Thanks Matheus,
What I guess I'm more than curious about is as this is one of two standbys and I could always rebuild it again from either of the other two machines, is what am I risking if delete
everything except maybe the last handful of WAL segments? When I start the database, I usually only see one or maybe two references to WAL segments being used to sync up. And being
that the standby streams anyway, I'm not all that concerned if another pg_basebackup has to run to rebuild the standby. I only want it to start cleanly in the event of a stoppage.
Which almost never happens anyway.
--
Jay
On 7/11/2014 12:58 PM, Matheus de Oliveira wrote:
>
> On Fri, Jul 11, 2014 at 1:49 PM, John Scalia <jayknowsunix@gmail.com <mailto:jayknowsunix@gmail.com>> wrote:
>
> 00000000300000000C000000A1
> 00000000300000000C000000A2
> 00000000300000000C000000A3
> 00000000300000000C000000A3.backup
> 00000000300000000C000000A4
>
> Could I safely delete the *A1, *A2, and maybe even the *A3 files?
>
>
> If you only care to storing files needed for backup made at 00000000300000000C000000A3, you can remove 00000000300000000C000000A1 and 00000000300000000C000000A2, but not the others.
>
> Regards,
> --
> Matheus de Oliveira
> Analista de Banco de Dados
> Dextra Sistemas - MPS.Br nível F!
> www.dextra.com.br/postgres <http://www.dextra.com.br/postgres/;
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL segment management on a standby
2014-07-11 16:49 WAL segment management on a standby John Scalia <jayknowsunix@gmail.com>
@ 2014-07-14 02:18 ` Craig Ringer <craig@2ndquadrant.com>
2014-07-14 13:57 ` Re: WAL segment management on a standby John Scalia <jayknowsunix@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Craig Ringer @ 2014-07-14 02:18 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; pgsql-admin
On 07/12/2014 12:49 AM, John Scalia wrote:
> Again, thanks to all to have assisted me with getting the WAL segments
> to both my standby servers. Everything with that is now working quite
> well. I do have a related followup question, however. On these standby's
> nothing is built in to manage those WAL segments in the archive
> directory. Thus, that directory can grow to the point where it fills up
> the disk rather quickly. Any good strategies for dealing with the WAL
> segments that get put in there. Do I really need them after postgresql
> has copied them up into the pg_xlog directory? If so, how far back
> should I keep them? Yes, I know about keeping everything between
> backups. So, if my directory looks like:
>
> 00000000300000000C000000A1
> 00000000300000000C000000A2
> 00000000300000000C000000A3
> 00000000300000000C000000A3.backup
> 00000000300000000C000000A4
>
> Could I safely delete the *A1, *A2, and maybe even the *A3 files?
Take a look at the pg_archivecleanup tool.
You might also want to look at PgBarman to automate this.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL segment management on a standby
2014-07-11 16:49 WAL segment management on a standby John Scalia <jayknowsunix@gmail.com>
2014-07-14 02:18 ` Re: WAL segment management on a standby Craig Ringer <craig@2ndquadrant.com>
@ 2014-07-14 13:57 ` John Scalia <jayknowsunix@gmail.com>
2014-07-26 12:14 ` Re: WAL segment management on a standby Craig Ringer <craig@2ndquadrant.com>
0 siblings, 1 reply; 6+ messages in thread
From: John Scalia @ 2014-07-14 13:57 UTC (permalink / raw)
To: Craig Ringer <craig@2ndquadrant.com>; pgsql-admin
Thanks Craig, I didn't know about pg_archivecleanup. (it's hard to figure out every utility when you're not given the time to thoroughly study the system.)
I altered the recovery.conf per the suggestion on the documentation page for it. It does seem a little aggresive, but I'm guessing that because all the transactions have been
recorded from the streaming walreceiver. If I might, I'd like to ask one final question on this matter. In looking through the standby after configuring this and restarting the
system, the log file shows a few warnings.
First, it whined about no history.0000004 file, which as the last WAL segment is 0000000030000000E00000004E, I'm guessing the standby thought the timeline might have been changed,
but it was not. Then, it complained about no history.00000003 file in the archive area. This file had already been copied up into pg_xlog and deleted from the archive. Finally, the
last complaint was that it couldn't find 0000000030000000E00000004F in the archive. It appears that under streaming a standby server will write concurrently with the primary any
WAL segment the primary is still generating. I say this as that missing WAL segment ("...4F") was already in the pg_xlog directory, but it hadn't yet made it into the archive area.
Eventually, 10 minutes later, 4F did appear in the archive. Is my interpretation here correct, that a standby will concurrently write a WAL segment in streaming replication mode?
I just want to be able to explain this thoroughly to my QA folks.
--
Jay
On 7/13/2014 10:18 PM, Craig Ringer wrote:
> On 07/12/2014 12:49 AM, John Scalia wrote:
>> Again, thanks to all to have assisted me with getting the WAL segments
>> to both my standby servers. Everything with that is now working quite
>> well. I do have a related followup question, however. On these standby's
>> nothing is built in to manage those WAL segments in the archive
>> directory. Thus, that directory can grow to the point where it fills up
>> the disk rather quickly. Any good strategies for dealing with the WAL
>> segments that get put in there. Do I really need them after postgresql
>> has copied them up into the pg_xlog directory? If so, how far back
>> should I keep them? Yes, I know about keeping everything between
>> backups. So, if my directory looks like:
>>
>> 00000000300000000C000000A1
>> 00000000300000000C000000A2
>> 00000000300000000C000000A3
>> 00000000300000000C000000A3.backup
>> 00000000300000000C000000A4
>>
>> Could I safely delete the *A1, *A2, and maybe even the *A3 files?
> Take a look at the pg_archivecleanup tool.
>
> You might also want to look at PgBarman to automate this.
>
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL segment management on a standby
2014-07-11 16:49 WAL segment management on a standby John Scalia <jayknowsunix@gmail.com>
2014-07-14 02:18 ` Re: WAL segment management on a standby Craig Ringer <craig@2ndquadrant.com>
2014-07-14 13:57 ` Re: WAL segment management on a standby John Scalia <jayknowsunix@gmail.com>
@ 2014-07-26 12:14 ` Craig Ringer <craig@2ndquadrant.com>
0 siblings, 0 replies; 6+ messages in thread
From: Craig Ringer @ 2014-07-26 12:14 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; pgsql-admin
On 07/14/2014 09:57 PM, John Scalia wrote:
>
> First, it whined about no history.0000004 file, which as the last WAL
> segment is 0000000030000000E00000004E, I'm guessing the standby thought
> the timeline might have been changed, but it was not. Then, it
> complained about no history.00000003 file in the archive area. This file
> had already been copied up into pg_xlog and deleted from the archive.
No immediate answer for you there.
> Finally, the last complaint was that it couldn't find
> 0000000030000000E00000004F in the archive. It appears that under
> streaming a standby server will write concurrently with the primary any
> WAL segment the primary is still generating. I say this as that missing
> WAL segment ("...4F") was already in the pg_xlog directory, but it
> hadn't yet made it into the archive area. Eventually, 10 minutes later,
> 4F did appear in the archive. Is my interpretation here correct, that a
> standby will concurrently write a WAL segment in streaming replication
> mode?
The xlog files get written to in pg_xlog until they're rotated. When
rotated, they get archived; when the archive command completes, they
become accessible to standby servers.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2014-07-26 12:14 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-07-11 16:49 WAL segment management on a standby John Scalia <jayknowsunix@gmail.com>
2014-07-11 16:58 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2014-07-11 17:56 ` John Scalia <jayknowsunix@gmail.com>
2014-07-14 02:18 ` Craig Ringer <craig@2ndquadrant.com>
2014-07-14 13:57 ` John Scalia <jayknowsunix@gmail.com>
2014-07-26 12:14 ` Craig Ringer <craig@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox