SEO

I've been playing around with recovery and am a bit confused as to
when this method gets invoked.
At the moment, I have two servers set up, both of them with postgres
installed. Lets call them server A and server B. Server A is running
postgres and processing transactions. Server B has postgres installed,
but the postmaster is not running. My archive_command is set to use
rsync copy the wal log from server A to server B. When i'm watching the
number of wal files on server A I notice sometimes there are 3 files
that have not been archived, sometimes there are 4, and sometimes there
are more.
So, my question is, when is archive_command invoked?

Thanks in advance!
Kris

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Kris,

It is invoked when the WAL file is filled. I believe the server preallocates
8 or so of them on startup.

On Wed, 28 Sep 2005, Kris Kiger wrote:

> set to use rsync copy the wal log from server A to server B. When i'm
> watching the number of wal files on server A I notice sometimes there are 3
> files that have not been archived, sometimes there are 4, and sometimes there
> are more. So, my question is, when is archive_command invoked?

--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Jeff: I see, that does make a lot of sense ;-). Thanks

However, that answer brings me to another question:
To begin with an example, lets say we start the server and are writing
to wal file X, however postgres has preallocated wal files up to X+7.
We assume that archive command will be invoked on wal files as they
become full. We don't care about wal files that are greater than X,
because they have either been recycled or preallocated, in either case
the data has been backed up from a previous archive command or does not
yet exist. So, we can assume that all relevant wal logs have been
archived, save the current one.

My question then is, how do we tell which wal log is X (the one
currently being written to)?


Jeff Frost wrote:

> Kris,
>
> It is invoked when the WAL file is filled. I believe the server
> preallocates 8 or so of them on startup.
>
> On Wed, 28 Sep 2005, Kris Kiger wrote:
>
>> set to use rsync copy the wal log from server A to server B. When
>> i'm watching the number of wal files on server A I notice sometimes
>> there are 3 files that have not been archived, sometimes there are 4,
>> and sometimes there are more. So, my question is, when is
>> archive_command invoked?
>
>


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Wed, 28 Sep 2005, Kris Kiger wrote:

> that all relevant wal logs have been archived, save the current one.
> My question then is, how do we tell which wal log is X (the one currently
> being written to)?

I do something like this in my scripting:

ls -rt /pg_xlog/ | grep -v "backup\|archive" | tail -1

(My pg_xlog is on a different disk set from the PGDATA dir.)

It appears to work fine for me. Don't forget to make a base backup before you
begin the WAL archiving, or you won't be able to restore anything.

--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Wed, 2005-09-28 at 12:08 -0500, Kris Kiger wrote:
> My question then is, how do we tell which wal log is X (the one
> currently being written to)?

You don't say why you need to know?

If you really care, you can look at the status files in the
archive_status directory underneath pg_xlog. This is where the archiver
checks to see for notifications of filled WAL files, then clears the
notification afterwards. Only filled WAL filenames are shown.

Don't touch the files or you might interfere with the archiver's
activities.

Best Regards, Simon Riggs



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Fri, 30 Sep 2005, Simon Riggs wrote:

> You don't say why you need to know?

Not sure why Kris needs to know, but I need to know for PITR and keeping the
latest WAL file saved by a script which runs every few minutes as my client
does not turn over WAL files very often (about 4/day).

> If you really care, you can look at the status files in the
> archive_status directory underneath pg_xlog. This is where the archiver
> checks to see for notifications of filled WAL files, then clears the
> notification afterwards. Only filled WAL filenames are shown.

I see lots of items like this:

0000000100000013000000A4.00AEE2F0.backup.done
0000000100000013000000DE.00B8A498.backup.done

I presume these correlate with the files in the pg_xlog directory that look
like so:

0000000100000013000000A4.00AEE2F0.backup
0000000100000013000000DE.00B8A498.backup

0000000100000013000000DF
0000000100000013000000E0
0000000100000013000000E1
0000000100000013000000E2
0000000100000013000000E3
0000000100000013000000E4
0000000100000013000000E5
0000000100000013000000E6

Given that list, does that mean that 0000000100000013000000DF is the in use
WAL file?

--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Fri, 2005-09-30 at 09:29 -0700, Jeff Frost wrote:
> On Fri, 30 Sep 2005, Simon Riggs wrote:
>
> > You don't say why you need to know?
>
> Not sure why Kris needs to know, but I need to know for PITR and keeping the
> latest WAL file saved by a script which runs every few minutes as my client
> does not turn over WAL files very often (about 4/day).
>
> > If you really care, you can look at the status files in the
> > archive_status directory underneath pg_xlog. This is where the archiver
> > checks to see for notifications of filled WAL files, then clears the
> > notification afterwards. Only filled WAL filenames are shown.
>
> I see lots of items like this:
>
> 0000000100000013000000A4.00AEE2F0.backup.done
> 0000000100000013000000DE.00B8A498.backup.done
>
> I presume these correlate with the files in the pg_xlog directory that look
> like so:
>
> 0000000100000013000000A4.00AEE2F0.backup
> 0000000100000013000000DE.00B8A498.backup
>
> 0000000100000013000000DF
> 0000000100000013000000E0
> 0000000100000013000000E1
> 0000000100000013000000E2
> 0000000100000013000000E3
> 0000000100000013000000E4
> 0000000100000013000000E5
> 0000000100000013000000E6
>
> Given that list, does that mean that 0000000100000013000000DF is the in use
> WAL file?
>

If I follow your example, yes. But that assumes there is only one
timeline's WAL files in your pg_xlog. It could get more complex in that
situation because you could be in any of the timelines.

But, if you know which timeline you're in, then yes, the file after the
latest file in archive_status is the current WAl file.

It might be interesting to submit your script to put into contrib?

Best Regards, Simon Riggs





---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Fri, 30 Sep 2005, Simon Riggs wrote:

> If I follow your example, yes. But that assumes there is only one
> timeline's WAL files in your pg_xlog. It could get more complex in that
> situation because you could be in any of the timelines.
>
> But, if you know which timeline you're in, then yes, the file after the
> latest file in archive_status is the current WAl file.
>
> It might be interesting to submit your script to put into contrib?

So, actually, my script is probably doing things incorrectly at the moment.
What effect would the different timelines create? In my client's case there
is only one timeline, but I'd love to generalize the script so we can put it
in contrib, but first I'd like to make sure it actually works properly. :-)

--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Sat, 2005-10-01 at 21:43 -0700, Jeff Frost wrote:
> On Fri, 30 Sep 2005, Simon Riggs wrote:
>
> > If I follow your example, yes. But that assumes there is only one
> > timeline's WAL files in your pg_xlog. It could get more complex in that
> > situation because you could be in any of the timelines.
> >
> > But, if you know which timeline you're in, then yes, the file after the
> > latest file in archive_status is the current WAl file.
> >
> > It might be interesting to submit your script to put into contrib?
>
> So, actually, my script is probably doing things incorrectly at the moment.
> What effect would the different timelines create? In my client's case there
> is only one timeline, but I'd love to generalize the script so we can put it
> in contrib, but first I'd like to make sure it actually works properly. :-)

The initial 000....0001 prefix of the WAL filename is the timeline
number.

If you recover the database from backup, specifying a target before the
end of WAL (with recovery_target_time or recovery_target_xid), then you
will generate a new timeline. If you then recover the database *again*
but this time specify a recovery_target_timeline less than the highest,
*but* this time *don't* specify a recovery_target_time or
recovery_target_xid then you'll end up with the current timeline being
less than the highest timeline number. (All of that seeming complexity
is absolutely necessary when you are trying to save your business data
from the skip. Thank Tom for having the insight to create it.)

It's possible that you would have files of the higher timeline numbers
still in the directory, that might confuse your program.

Probably the best idea is to backup the last WAL file for each timeline
seen. Keep track of that, so when the current file changes you'll know
which timeline you're on and stick to that from then on. Or more simply,
put some notes with your program saying "if you ever use a complex
recovery situation, make sure to clear all archive_status files for
higher timeline ids before using this program".

This would not be necessary had I completed my logswitch patch in time
for 8.1 freeze, but I regret that I was unable to do that.

Best Regards, Simon Riggs




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Sun, 2 Oct 2005, Simon Riggs wrote:

> Probably the best idea is to backup the last WAL file for each timeline
> seen. Keep track of that, so when the current file changes you'll know
> which timeline you're on and stick to that from then on. Or more simply,
> put some notes with your program saying "if you ever use a complex
> recovery situation, make sure to clear all archive_status files for
> higher timeline ids before using this program".

Tell me if you think this is the most reasonable way to determine the in use
WAL file:

ls -rt $PGDATA/pg_xlog/ | grep -v "backup\|archive\|RECOVERY" | tail -1

> This would not be necessary had I completed my logswitch patch in time
> for 8.1 freeze, but I regret that I was unable to do that.

What's the logswitch patch going to accomplish? Sounds interesting.

Also, I have an interesting and unrelated question...this past weekend, we had
a failure in which we had to restore from backup. I have created a script
which makes a base backup every two weeks and we keep the last two. Also, we
use PITR for replication, making a base backup between the primary and
secondary server every 8 hours, and running my rsynclastlog script once/minute
to keep as up to date as possible. Now here's the problem...the servers
switched roles on Sep 21. We switched them back a little while after that.
When I went to replay the WAL files using the Sep 15 base backup, it happily
played back the WAL files to Sep 21 and stopped. I presume that this means
whenever the servers switch roles I need to create a fresh base backup?

Is there any possible way to replay the other WAL files after the Sep 21
switchover/switchback? I'm going to guess this is similar to having another
timeline.

We restored from a nightly pg_dump which we have kept doing, but that means we
lost about 20 hrs of data. :-(

--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster