Обсуждение: pg_xlog - files are guaranteed to be sequentialy named?
Hi pgsql-list-members, I currently write a small script that deletes outdated xlog-files from my backup-location. Because I do not want to rely on creation-date, I found it usable to use the result of ln | sort -g -r Thus the newest WAL xlog-file is on top and I can delete all not needed files at the bottom of the list. My question: Is it for ALL cases guaranteed, that the naming of the WAL-files in $PGDATA/pg_xlog always produces a "higher" number for a newer file? What happens if the 24hexdigits reach upper bound? Thank your for your replies on that issue of postgresql inner working model. Regards Johannes
Johannes Konert wrote: > Hi pgsql-list-members, > I currently write a small script that deletes outdated xlog-files from > my backup-location. > Because I do not want to rely on creation-date, I found it usable to use > the result of > ln | sort -g -r > Thus the newest WAL xlog-file is on top and I can delete all not needed > files at the bottom of the list. Warning, this is NOT SAFE to do. You should NEVER delete "outdated" xlog files, unless you appreciate RANDOM CORRUPTION of your data. Not sure how those caps sneaked in there, sorry about that. Have a nice day, -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Johannes Konert wrote: > >> Hi pgsql-list-members, >> I currently write a small script that deletes outdated xlog-files from >> my backup-location. >> Because I do not want to rely on creation-date, I found it usable to use >> the result of >> ln | sort -g -r >> Thus the newest WAL xlog-file is on top and I can delete all not needed >> files at the bottom of the list. >> > > Warning, this is NOT SAFE to do. You should NEVER delete "outdated" > xlog files, unless you appreciate RANDOM CORRUPTION of your data. > I think he's talking about deleting pg_xlog files that are being used for PITR from the backup machine after they've been applied. But I'm not sure that's really what he meant or not.
On Wed, 13 Jun 2007, Alvaro Herrera wrote: > Johannes Konert wrote: >> I currently write a small script that deletes outdated xlog-files from >> my backup-location. > > Warning, this is NOT SAFE to do. You should NEVER delete "outdated" > xlog files, unless you appreciate RANDOM CORRUPTION of your data. He's talking about wiping out the ones on the backup server, so I think Johannes means erasing the old archived logs on the secondary here. That can screw up your backup if you do it wrong, but it's not an all-caps worthy mistake. On Wed, 13 Jun 2007, Johannes Konert wrote: > Because I do not want to rely on creation-date, No, you want to rely on creation date, because then this problem goes away. The idea you should be working toward is that you identify when your last base backup was started after it's copied to the secondary, and then you can safely delete any archived logs file on the secondary from before that time. Instead of doing "ls | sort -g -r" you should be doing something like looping over the files in a bash shell script and using [ -ot <first xlog in base backup> ] to determine which files to delete. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Greg Smith wrote: > He's talking about wiping out the ones on the backup server, so I > think Johannes means erasing the old archived logs on the secondary > here. That can screw up your backup if you do it wrong, but it's not > an all-caps worthy mistake. yes, that's what I am talking about related to http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html. Sorry, if that did not came out clearly enough. > > On Wed, 13 Jun 2007, Johannes Konert wrote: >> Because I do not want to rely on creation-date, > > No, you want to rely on creation date, because then this problem goes > away. Truely right...if I can gurantee, that the file-dates of my archived WAL-files do have proper timestamps. If the timestamps once are messed up and all have the same timestamp (due to a Windows-copy or something else foolish), then the delete-script might delete the wrong files... > The idea you should be working toward is that you identify when your > last base backup was started after it's copied to the secondary, and > then you can safely delete any archived logs file on the secondary > from before that time. Instead of doing "ls | sort -g -r" you should > be doing something like looping over the files in a bash shell script > and using > [ -ot <first xlog in base backup> ] to determine which files to delete. right; but as I said, then I rely on file-dates. But during the day I came out with an solution: I store the WAL-files with the time-stamp of archiving in their file-name. Thus I can order and delete them safely. Your hint was the one, that helped me to find that solution - so thanks for that, Greg.....and the others. Regards, Johannes
Johannes Konert wrote: > But during the day I came out with an solution: I store the WAL-files > with the time-stamp of archiving in their file-name. Thus I can order > and delete them safely. > Your hint was the one, that helped me to find that solution - so > thanks for that, Greg.....and the others. That solution has still a problem: It workes fine in case that the WAL-naming restarts with 000000000000000000000001, because the attached timestamp in name would still make it possible to identify the file as being a newer one as FFFFFFFFFFFFFFFFFFFFFFFF, but there is still the problem with shifts in time itself. If someone corrects the servers computer-time/date to a date before current time (e.g. set the clock two hours back), then the newer WAL files will have an older timestamp and will be deleted by accident. Thus now I increase the number of characters of the filename to infinite and the last 24 characters are the WAL file name. Thus the archived filenames ~always~ increase in naming and all backup files before the last base backup can be safely identified not relying on computer timestamps or with the risk of a restart in naming by postgresql. I hope this solutions only border is the 255 character restriction of file-name length....but if that one will be reached in future times I am sure longer names are possible :) Regards Johannes
On Wed, 13 Jun 2007, Johannes Konert wrote: > If someone corrects the servers computer-time/date to a date before current > time (e.g. set the clock two hours back), then the newer WAL files will have > an older timestamp and will be deleted by accident. This should never happen; no one should ever touch the clock by hand on a production system. The primary and backup server should both be syncronized via NTP. If you're thinking about clock changes for daylight savings time, those shouldn't have any effect on timestamps, which should be stored in UTC. If you're on Windows, I recommend reading http://searchwinit.techtarget.com/tip/0,289483,sid1_gci1241193,00.html and http://www.wilsonmar.com/1clocks.htm if you're not familiar with how UTC/NTP insulate you from this issue. On many types of systems that process time-sensitive data, an administrator adjusting the clock manually is considered a dangerous event that is specificly scheduled so issues like you're concerned about don't happen--and someone who tinkers with the clock without following that procedure would be in serious trouble. You're working hard to worry about problems that should be eliminated by the overall design of your system. If you can't trust your system clocks and that files are being copied with their attributes intact, you should consider thinking about how to resolve those problems rather than working around them. It's not just PostgreSQL that will suffer from weird, unpredictable behavior in a broken environment like that. Giving a Windows example, if you're running in a Windows Domain configuration, if the client time drifts too far from the server you can get "The system cannot log you on due to the following error: There is a time difference between the Client and Server." when trying to login. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Hello Johannes, Johannes Konert schrieb: > Thus the newest WAL xlog-file is on top and I can delete all not needed > files at the bottom of the list. You're using pg_controldata to figure out which file's serial is older than the latest redo checkpoint. In case of restart of the slave server PgSQL needs all files that were archived beginning with the one right after the latest redo checkpoint or it will fail to sync to its master. > What happens if the 24hexdigits reach upper bound? Did you calculate you question? I assume no. 24 Hex digits means 24^16 unique file names. Assuming your server saves a WAL file each second (you should review your config it it does) it takes (24^16)/(60*60*24*365)=3.84214066×10^14 years to reach the upper bound. (Plase forgive me ignoring leap years ;)) I assume that there will be a system change before that date so counting will start over again. ;) Greetings, Frank Wittig
Вложения
Frank Wittig schrieb: > 24 Hex digits means 24^16 unique file names. Assuming your server saves > a WAL file each second (you should review your config it it does) it > takes (24^16)/(60*60*24*365)=3.84214066×10^14 years to reach the upper > bound. How embarrassing - I messed up the calculation. It has to be 16^24. But pg does forge filenames other that that. It uses 2 hex digits to count segments. After 256 segments counting starts over and the serial is increased by one. The first 8 positions are the time line which I will ignore for my new calculation. So there is an eight hex digits serial for each time line which takes 256 segments. So there are 16^8*256 unique file names. If I assume one WAL file a second this would reach upper bound (for a single time line) after slightly more than 136 years. Please correct me if my assumptions are wrong. But I would say one can rely on serial file names to increase steadily. The attached restore.pl uses this assumption to delete all files which are older than the last redo checkpoint. Greetings, Frank Wittig
Вложения
Greg Smith wrote: > On Wed, 13 Jun 2007, Johannes Konert wrote: > >> If someone corrects the servers computer-time/date to a date before >> current time (e.g. set the clock two hours back), then the newer WAL >> files will have an older timestamp and will be deleted by accident. > > This should never happen; no one should ever touch the clock by hand > on a production system. The primary and backup server should both be > syncronized via NTP. If you're thinking about clock changes for > daylight savings time, those shouldn't have any effect on timestamps, > which should be stored in UTC. If you're on Windows, Its not Windows; it will be Debian Linux. I completely agree with you that of course our servers synchronize themselve via NTP with global time, but we already had the case that - for some reasons - NTP did not work and times drift away from each other. If you have to manage some servers you might not recognize that a NTP daemon does not work anymore or that a new firewall prohibits these TCP packages now....and time goes by, because everything seem to work just fine. Then one nice day you realize, that one, two or many of your servers just have their own time and you need to bring them back to synchronized time while they are online. If you made your applications be aware of such effects and use system-nanotime or global counters where possible, then even these time-corrections can be handled. But I agree with you: of course normally this will never happen...but it happened once. > > You're working hard to worry about problems that should be eliminated > by the overall design of your system. If you can't trust your system > clocks and that files are being copied with their attributes intact, > you should consider thinking about how to resolve those problems > rather than working around them. yes, but still there is a remaining risk in my opinion. > It's not just PostgreSQL that will suffer from weird, unpredictable > behavior in a broken environment like that. Giving a Windows example, > if you're running in a Windows Domain configuration, if the client > time drifts too far from the server you can get "The system cannot log > you on due to the following error: There is a time difference between > the Client and Server." when trying to login. If we add a new server to the cluster, the application will check times as it is in oyur Windows-example, but if it is allready in and working, then it cannot simply shutdown in case of time-diffs. Greg, thanks for your sophisticated hints. But the thread is going a little off-topic now, I guess :) The issue with the time-dependency of WAL archiving and deletion issolved for me by using a global infinite counter to rely on by now. I am sure next questions will come before long and I look forward to read any hints then, if you and others have time to read them. Regards Johannes
Frank Wittig wrote: >> 24 Hex digits means 24^16 unique file names. Assuming your server saves >> a WAL file each second (you should review your config it it does) it >> takes (24^16)/(60*60*24*365)=3.84214066×10^14 years to reach the upper >> bound. >> > > (..) It has to be 16^24. > But pg does forge filenames other that that. It uses 2 hex digits to > count segments. After 256 segments counting starts over and the serial > is increased by one. The first 8 positions are the time line which I > will ignore for my new calculation. > > So there is an eight hex digits serial for each time line which takes > 256 segments. So there are 16^8*256 unique file names. If I assume one > WAL file a second this would reach upper bound (for a single time line) > after slightly more than 136 years. > > Please correct me if my assumptions are wrong. But I would say one can > rely on serial file names to increase steadily. > Thanks for that answer. That was exactly what I could not immediatelly find mentioned in the documentation. If it is guaranteed - and I understood your comments this way - that the naming follows a sequential order, then I agree with you, that this is enough for a long time. I was not sure wether or not the naming follows this rule. Of course I calculated the number of possible filenames before, but as I said, I was not sure, that Postgresql follows a guaranteed naming convention of always increasing WAL filenames. Anyway, this is now for sure and I will rely on that now. Regards Johannes
On Wed, 2007-06-13 at 21:39 +0200, Frank Wittig wrote: > But I would say one can > rely on serial file names to increase steadily. The whole of the PostgreSQL recovery system relies upon that, so yes we can use that externally from the database also. There's a patch to 8.3 for the restore_command to be passed a %r parameter so you don't need to grovel in the control file. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Johannes Konert schrieb: > ....and time goes by, because everything seem to work just fine. One should never rely on something seeming to be any kind of anything. There are ways to _know_ things are right. Like using nagios to check every vital sign state of your systems. That should include checking system time against official time servers. But I also agree that if there is a possibility that times differ (and there of course is) the question is not if it can happen but when. Everything that can go wrong will go wrong - whatever you do to prevent things. Thats Finagle's law and its not proofed wrong yet. So your solution has to consider that. Therefore I disagree Greg in relying only on the system base. There have to be mechanisms which use reliable information that behave in only _one_ well known way (such as monotone increasing serial numbers) or the system will break. Regards, Frank Wittig
Вложения
On Thu, 14 Jun 2007, Frank Wittig wrote: > But I also agree that if there is a possibility that times differ (and > there of course is) the question is not if it can happen but when. Sure, but it doesn't matter one bit if the times between the primary and secondary servers differ. If the timestamps on the primary are being preserved when copied over, the secondary can operate on them with no reference whatsoever to its local time. Future transaction timestamps will be all screwed up when you switch to the secondary if its clock is wrong, but it doesn't impact the operation of the PITR mechanism or its cleanup. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD