Thread: To be a korea mirror [www ,ftp] both.
Hello. I'm a HolyNet development team manager JunSeon Oh. We are mirroring postgresql page (www,ftp) during 2 years. but , our mirroring server does not listed in mirror page on www.postgresql.org.maybe our ip has been changed because moving another idc. we sending a email many times with changed ip, but can't receive no answer. WWW : http://postgresql.holywar.net FTP : ftp://ftp.holywar.net/pub/mirrors/pgsql Check the site , and reply to us please. Thanks. Regards, -- Software Development Team / Manager / JunSeon Oh Email : holywar@corp.holywar.net / Tel : 031-712-2250 My-PGP-Key: http://www.holywar.net/~hollywar/holywar_publickey.txt Fingerprint = A94D 9F04 9662 D84C 480D F05F B36A A1AD 8508 AA48 --
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
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
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? > >
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
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
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
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
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
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
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
On Mon, 2005-10-03 at 20:00 -0700, Jeff Frost wrote: > 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 If you trust the times your filesystem hands you. PostgreSQL doesn't use the file times it uses the sequential number naming, so for paranoid accuracy, this should too so perhaps take the t off. > > 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? Nothing until I finish it. :-) > 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. Sounds like a good setup. > 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? Yes, but more generally if I lost one node I would always be inclined to fully backup the remaining one just in case. > 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. That may be your exact case, it depends upon how you did recovery. Both systems think they are the same one, so you need to be careful. Look for the last log file of the went-down node. That file should exist twice, once as finally written by the went-down node and once as continued to be written to by the stayed-up node after switchover. It sounds like the wrong file was used to recover with and as a result stopped recovery on Sep 21. But if you're running with the system now, you'll need to do an intermediate rebuild and recover the data manually. (I recommend testing recovery procedures before you go live and then at least every 6 months, to ensure that they actually still work when you need 'em. Trying to do a recovery with any confidence is not easy at 4am on a Sunday morning under maximum stress, from experience.) > We restored from a nightly pg_dump which we have kept doing, but that means we > lost about 20 hrs of data. :-( But sounds recoverable. Best Regards, Simon Riggs
On Tue, 4 Oct 2005, Simon Riggs wrote: >> 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? > > Yes, but more generally if I lost one node I would always be inclined to > fully backup the remaining one just in case. I believe they were switched not because one went down, but to add more ram to the primary. They switched back just peachy at that time. >> 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. > > That may be your exact case, it depends upon how you did recovery. Both > systems think they are the same one, so you need to be careful. > > Look for the last log file of the went-down node. That file should exist > twice, once as finally written by the went-down node and once as > continued to be written to by the stayed-up node after switchover. It > sounds like the wrong file was used to recover with and as a result > stopped recovery on Sep 21. But if you're running with the system now, > you'll need to do an intermediate rebuild and recover the data manually. What would constitute an intermediate rebuild? Of course the system is up and live and having data added to it. How would one restore from multiple timelines? > (I recommend testing recovery procedures before you go live and then at > least every 6 months, to ensure that they actually still work when you > need 'em. Trying to do a recovery with any confidence is not easy at 4am > on a Sunday morning under maximum stress, from experience.) > I absolutely agree..unfortunately, it's only been 3 months since the last test. :-( -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
Jeff Frost wrote: > 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 > Look at this post I did last year: http://archives.postgresql.org/pgsql-admin/2005-06/msg00013.php in that messages there are two script that deliver remotelly the archive wall, and they store too last WAL in order to not loose the current WAL in case of crash. This was the function I used to find the WAL in use: function copy_last_wal{ FILE=$( ls -t1p $PGXLOG | grep -v / | head -1 ) echo "Last Wal> " $FILE cp ${PGXLOG}/${FILE} ${PARTIAL}/${FILE}.tmp mv ${PARTIAL}/${FILE}.tmp ${PARTIAL}/${FILE}.partial find ${PARTIAL} -name*.partial | grep -v ${FILE} | xargs -i rm -fr {}} At that time Tom Lane agreed to provide some functions to ask the engine the name of WAL currently in use, dunno if in the new 8.1 something was done in order to help this process. Regards Gaetano Mendola
On Tue, 2005-10-04 at 08:03 -0700, Jeff Frost wrote: > >> 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. > > > > That may be your exact case, it depends upon how you did recovery. Both > > systems think they are the same one, so you need to be careful. > > > > Look for the last log file of the went-down node. That file should exist > > twice, once as finally written by the went-down node and once as > > continued to be written to by the stayed-up node after switchover. It > > sounds like the wrong file was used to recover with and as a result > > stopped recovery on Sep 21. But if you're running with the system now, > > you'll need to do an intermediate rebuild and recover the data manually. > > What would constitute an intermediate rebuild? Of course the system is up and > live and having data added to it. How would one restore from multiple > timelines? This is only if you are back up and working on the went-down box: intermediate rebuild: I mean that you will have to restore the data from the period of switchover, manually extract the relevant data with SQL and then re-insert those changes yourself and resolve conflicts. There isn't a process to merge the two log streams. The same txnid will have been used on both servers to refer to separate transactions, so there can be no automated way of resolving the data. It has to be done using business domain knowledge rather than log data. All of that's no different from any other RDBMS, as I'm sure you know. Best Regards, Simon Riggs
On Tue, 4 Oct 2005, Simon Riggs wrote: >> What would constitute an intermediate rebuild? Of course the system is up and >> live and having data added to it. How would one restore from multiple >> timelines? > > This is only if you are back up and working on the went-down box: > > intermediate rebuild: I mean that you will have to restore the data from > the period of switchover, manually extract the relevant data with SQL > and then re-insert those changes yourself and resolve conflicts. I think we're likely out of luck here, but let me provide a few more details so you might give me a better idea. We are up on the went-down box with a pg_dump restore from the 3:30a.m. of the previous day. We have WAL files available from the box that was up during the failure of the primary. What I do not have is a base backup of the box which was up for part of the day while we brought up the went-down box. So, unless there is some method of replaying WAL files by hand one at a time, I think we are out of luck, no? > > There isn't a process to merge the two log streams. The same txnid will > have been used on both servers to refer to separate transactions, so > there can be no automated way of resolving the data. It has to be done > using business domain knowledge rather than log data. > > All of that's no different from any other RDBMS, as I'm sure you know. I do know, and in fact, I think PITR is great, I just wish we had thought about the corner case of the servers switching roles between the biweekly base backups. -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
On Tue, 4 Oct 2005, Gaetano Mendola wrote: Looks like we're doing just about the same thing, but you're using head and I'm using tail. However, it seems that your method does not require grepping out the special files, so that's awesome! BTW you do not have to use the -1 flag when you put ls to a pipe. -Jeff > FILE=$( ls -t1p $PGXLOG | grep -v / | head -1 ) -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
Looks like using that FILE= expression, I ended up with a bad file choice last
night immediately after the base backup:
Copying 0000000100000015000000F8.0088A490.backup to
/mnt/pgbackup/pitr/0000000100000015000000F8.0088A490.backup
Also, I noticed that rsync had a file disappear out from under it during the
base backup:
file has vanished: "/usr/local/pgsql/data/pg_subtrans/0743"
So, I have modified my FILE= expression to the following:
FILE=`ls -tp /pg_xlog/ | grep -v "backup\|/" | head -1`
Which gives me this file: 00000001000000160000002A
Given these in the pg_xlog dir.
00000001000000160000002A
00000001000000160000002B
00000001000000160000002C
00000001000000160000002D
00000001000000160000002E
00000001000000160000002F
000000010000001600000030
000000010000001600000031
Removing the -t takes yields the same result as long as I grep out the .backup
files.
On Tue, 4 Oct 2005, Gaetano Mendola wrote:
> This was the function I used to find the WAL in use:
>
> function copy_last_wal
> {
> FILE=$( ls -t1p $PGXLOG | grep -v / | head -1 )
>
> echo "Last Wal> " $FILE
>
> cp ${PGXLOG}/${FILE} ${PARTIAL}/${FILE}.tmp
> mv ${PARTIAL}/${FILE}.tmp ${PARTIAL}/${FILE}.partial
> find ${PARTIAL} -name *.partial | grep -v ${FILE} | xargs -i rm -fr {}
> }
>
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954