Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Kevin Grittner [EMAIL PROTECTED] writes: Attached is a version as good as I know how to get it. It works for us, so barring any problems as we use it, I'm done. I finally got around to looking at this. Neither err.h nor err() are portable (they're not in the Single Unix Spec, and they don't work here...). The intent seems moderately clear, but please replace those calls with something portable. Otherwise, I think the only thing standing in the way of committing this as a contrib module is that we'll need some user-facing documentation, preferably in the form of an SGML file. (We'd also need a Makefile of course, but that's pretty dang trivial. See pg_standby's files if you need a sample to work from.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Sun, Sep 9, 2007 at 4:16 AM, apoc9009 [EMAIL PROTECTED] wrote: No! Actually I'm wearing my tin hat right now and I Never say Anything about My Suspicions about 9/11 on Internet in fear of Echelon catching and filing me. --- Hannu hmm, a little bit Para? http://www.myvideo.de/watch/1776449 Ok, now your point of View its more clearly... This is a total aside Seriously... the kid in the vid has _issues_!!! Is it the OP himself? I'd love to see the subtitled edition of this, or if someone can translate it!!! Best regards, -- [EMAIL PROTECTED] EnterpriseDB http://www.enterprisedb.com [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com Mail sent from my BlackLaptop device
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
It seems there is already a project on pgfoundry but there are no files: http://pgfoundry.org/projects/clearxlogtail/ Should this be on pgfoundry or in the Postgres distribution. It seems it might be tied enough to the WAL format to be in the Postgres distribution. --- Kevin Grittner wrote: On Thu, Sep 6, 2007 at 7:31 PM, in message [EMAIL PROTECTED], Kevin Grittner [EMAIL PROTECTED] wrote: On Thu, Sep 6, 2007 at 7:03 PM, in message [EMAIL PROTECTED], Jeff Davis [EMAIL PROTECTED] wrote: I think ... there's still room for a simple tool that can zero out the meaningless data in a partially-used WAL segment before compression. It seems reasonable to me, so long as you keep archive_timeout at something reasonably high. If nothing else, people that already have a collection of archived WAL segments would then be able to compact them. That would be a *very* useful tool for us, particularly if it could work against our existing collection of old WAL files. Management here has decided that it would be such a useful tool for our organization that, if nobody else is working on it yet, it is something I should be working on this week. Obviously, I would much prefer to do it in a way which would be useful to the rest of the PostgreSQL community, so I'm looking for advice, direction, and suggestions before I get started. I was planning on a stand-alone executable which could be run against a list of files to update them in-place, or to handle as single file as a stream. The former would be useful for dealing with the accumulation of files we've already got, the latter would be used in our archive script, just ahead of gzip in the pipe. Any suggestions on an existing executable to use as a model for best practices are welcome, as are suggestions for the safest and most robust techniques for identifying the portion of the WAL file which should be set to zero. Finally, I assume that I should put this on pgfoundry? -Kevin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Thu, Sep 27, 2007 at 4:59 PM, in message [EMAIL PROTECTED], Kevin Grittner [EMAIL PROTECTED] wrote: By the way, I realize that the error messages are still lame. I'm going to do something about that. Attached is a version as good as I know how to get it. It works for us, so barring any problems as we use it, I'm done. I confirmed with management that this code can be contributed to the PostgreSQL community at large. It was written by myself as an employee of the Wisconsin Supreme Court, Consolidated Court Automation Programs. It is is distributed under the terms of the license of the University of California as currently referenced here: http://www.postgresql.org/docs/8.2/interactive/LEGALNOTICE.html The only other code I looked at to derive technique was also distributed under that license. I gratefully acknowledge the examples provided by the authors of the code I examined: Tom Lane and Kevin Fall; although any errors are my own. I hope that others may find this filter useful. -Kevin J. Grittner pg_clearxlogtail.c Description: Binary data ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Wed, 2007-09-26 at 16:31 -0500, Kevin Grittner wrote: The one downside I've found is that it adds 0.2 seconds of CPU time per WAL file archive during our heaviest update periods. It's in the archiver process, not a backend process that's running a query, and we're not generally CPU bound, so this is not a problem for us. OK, first time anybody's measured a significant cost to process creation during execution of the archive_command. Still fairly low though. Increasing the size of the WAL files would cure that. :-( -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Thu, Sep 27, 2007 at 6:56 AM, in message [EMAIL PROTECTED], Simon Riggs [EMAIL PROTECTED] wrote: On Wed, 2007-09-26 at 16:31 -0500, Kevin Grittner wrote: The one downside I've found is that it adds 0.2 seconds of CPU time per WAL file archive during our heaviest update periods. OK, first time anybody's measured a significant cost to process creation during execution of the archive_command. Still fairly low though. Since it's that unusual, I'll check it closely during more normal testing. The timings so far are against old WAL files on the box running 72 warm standby instances and actively running rsync against all of the sources. Perhaps the unusual load somehow distorted the measurement. I based this on running a gzip of various WAL files versus the filter piped to gzip; writing to a file in both cases, and using time to get the metrics. Any suggestions for different or better ways to measure the impact are welcome. -Kevin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Wed, Sep 26, 2007 at 7:29 PM, in message [EMAIL PROTECTED], Florian G. Pflug [EMAIL PROTECTED] wrote: Kevin Grittner wrote: I omitted the code I was originally considering to have it work against files in place rather than as a filter. It seemed much simpler this way, we didn't actually have a use case for the additional functionality, and it seemed safer as a filter. Thoughts? A special non-filter mode could save some IO and diskspace by not actually writing all those zeros, but instead just seek to SizeOfWal-1 after writing the last valid byte, and writing one more zero. Of course, if you're gonna compress the WAL anyway, there is no point... Right. And if you're not, why bother setting to zero? I couldn't invent a plausible scenario where we would want to do the update in place, and I'm afraid someone might be tempted to run it against live WAL files. So I decided it was best to let it lie unless someone else had a real- life situation where it was useful. Even then, I could write a bash script to do it using the filter a lot faster than I could modify the C code to safely deal with the files in-place, so I'm pretty skeptical. -Kevin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Thu, Sep 27, 2007 at 6:56 AM, in message [EMAIL PROTECTED], Simon Riggs [EMAIL PROTECTED] wrote: On Wed, 2007-09-26 at 16:31 -0500, Kevin Grittner wrote: The one downside I've found is that it adds 0.2 seconds of CPU time per WAL file archive during our heaviest update periods. It's in the archiver process, not a backend process that's running a query, and we're not generally CPU bound, so this is not a problem for us. OK, first time anybody's measured a significant cost to process creation during execution of the archive_command. Still fairly low though. Confirmed in further tests on a normal production environment. Starting from a set of OS cached, 16 MB WAL files representing several days of activity, the overall time to compress through gzip to disk went down when piped through this filter, but the time for a full file went up. Best case: gzip: real0m0.769s user0m0.759s sys 0m0.009s gz size: 4562441 pg_cleanxlogtail | gzip: real0m0.132s user0m0.119s sys 0m0.024s gz size: 16406 Worst case: gzip: real0m0.781s user0m0.770s sys 0m0.010s gz size: 4554307 pg_cleanxlogtail | gzip: real0m1.073s user0m1.018s sys 0m0.063s gz size: 4554307 Is it necessary to try to improve that worst case? By the way, I realize that the error messages are still lame. I'm going to do something about that. I particularly don't like this as a failure message: echo 777 `cat 0001000300EF` | pg_clearxlogtail /dev/null pg_clearxlogtail: Warning, unexpected magic number pg_clearxlogtail: stdin: Success Is the filter-only approach acceptable, after the discussion here? Is the magic number hanlding OK; if not, what would be? Any other issues that I should address? -Kevin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Mon, Sep 24, 2007 at 4:17 PM, in message [EMAIL PROTECTED], Kevin Grittner [EMAIL PROTECTED] wrote: On Thu, Sep 6, 2007 at 7:03 PM, in message [EMAIL PROTECTED], Jeff Davis [EMAIL PROTECTED] wrote: I think ... there's still room for a simple tool that can zero out the meaningless data in a partially-used WAL segment before compression. so I'm looking for advice, direction, and suggestions before I get started. Lacking any suggestions, I plowed ahead with something which satisfies our needs. First, rough, version attached. It'll save us buying another drawer of drives, so it was worth a few hours of research to figure out how to do it. If anyone spots any obvious defects please let me know. We'll be running about 50,000 WAL files through it today or tomorrow; if any problems turn up in that process I'll repost with a fix. Given the lack of response to my previous post, I'll assume it's not worth the effort to do more in terms of polishing it up; but if others are interested in using it, I'll make some time for that. Adding this to the pipe in our archive script not only saves disk space, but reduces the CPU time overall, since gzip usually has less work to do. When WAL files switch because they are full, the CPU time goes from about 0.8s to about 1.0s. -Kevin pg_clearxlogtail.c Description: Binary data ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Wed, 2007-09-26 at 11:05 -0500, Kevin Grittner wrote: Lacking any suggestions, I plowed ahead with something which satisfies our needs. First, rough, version attached. It'll save us buying another drawer of drives, so it was worth a few hours of research to figure out how to do it. If anyone spots any obvious defects please let me know. We'll be running about 50,000 WAL files through it today or tomorrow; if any problems turn up in that process I'll repost with a fix. Given the lack of response to my previous post, I'll assume it's not worth the effort to do more in terms of polishing it up; but if others are interested in using it, I'll make some time for that. Adding this to the pipe in our archive script not only saves disk space, but reduces the CPU time overall, since gzip usually has less work to do. When WAL files switch because they are full, the CPU time goes from about 0.8s to about 1.0s. It's nicely written and looks like it would perform well. The logic for zeroing the blocks makes me nervous. It doesn't locate the block from which to start, it treats all blocks equally, so might zero some blocks and not others. What you have should work, but I'd be inclined to put a test in there to check that doesn't happen: once we begin to zero pages, all of them should be zeroed to end of file. If we find one that shouldn't be zeroed, throw an error. We should also document that this is designed to help compress files that aren't full because we switched early because of archive_timeout. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Wed, Sep 26, 2007 at 3:14 PM, in message [EMAIL PROTECTED], Simon Riggs [EMAIL PROTECTED] wrote: It's nicely written Thanks. I spent some time looking at Tom Lane's pg_resetxlog and the source code for cat to model my code. I'm rather rusty on C, so I wanted to minimize the chance of doing anything outrageously stupid. Should I be including anything in the comments to give credit for that? (I'm never sure where the line is on that.) and looks like it would perform well. In my tests so far, it is faster to pipe through this and then gzip than to just gzip, except when the WAL file is full or nearly so. In tests with small counties (which rarely fill a file except at peak periods), I'm seeing archival WAL space reduced to 27% of the original. I expect that to climb to 35% to 40% when we do all counties, but that's just a guess. I've seen some clues that it will get a bit better in 8.3 because of HOT updates. (We force WAL files to be written hourly, by the way.) For us, this reduces overall CPU time used in archiving, reduces disk space needed for backups, reduces network traffic (including over a relatively slow WAN). The one downside I've found is that it adds 0.2 seconds of CPU time per WAL file archive during our heaviest update periods. It's in the archiver process, not a backend process that's running a query, and we're not generally CPU bound, so this is not a problem for us. The logic for zeroing the blocks makes me nervous. It doesn't locate the block from which to start, it treats all blocks equally, so might zero some blocks and not others. What you have should work, but I'd be inclined to put a test in there to check that doesn't happen: once we begin to zero pages, all of them should be zeroed to end of file. If we find one that shouldn't be zeroed, throw an error. Agreed. That is one of the reasons I referred to this as a first, rough version. I wanted to prove the technique in general before that refinement. Another reason is that it is rather light on error checking in general. While I was loath limit it to an exact match on the magic number, since it works unmodified on multiple versions, it seems dangerous not to enforce any limits there. I wasn't sure how best to approach that. Suggestions? I think I should also error if stdin has more data when I think I'm done. Agreed? I omitted the code I was originally considering to have it work against files in place rather than as a filter. It seemed much simpler this way, we didn't actually have a use case for the additional functionality, and it seemed safer as a filter. Thoughts? We should also document that this is designed to help compress files that aren't full because we switched early because of archive_timeout. Sure. Again, this is more at a proof of concept stage. It's enough to get us out of a tight spot on drive space, even as it stands, but I know that it needs polishing and documentation if it is to be accepted by the community. I just wasn't sure the interest was actually there. I'm still not sure whether this might be considered for inclusion in the base release or contrib, or whether I should open a pgfoundry project. Thanks for the feedback. -Kevin ---(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
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Wed, Sep 26, 2007 at 3:14 PM, in message [EMAIL PROTECTED], Simon Riggs [EMAIL PROTECTED] wrote: The logic for zeroing the blocks makes me nervous. It doesn't locate the block from which to start, it treats all blocks equally, so might zero some blocks and not others. What you have should work, but I'd be inclined to put a test in there to check that doesn't happen: once we begin to zero pages, all of them should be zeroed to end of file. If we find one that shouldn't be zeroed, throw an error. We should also document that this is designed to help compress files that aren't full because we switched early because of archive_timeout. Attached is a modified version to implement both of these. I also bailed out if there was surplus input. I tried an optimization of allocating a separate buffer for outputting the zeros, to avoid repeated memset calls. It didn't seem to make a very big difference; do you think it's worth cluttering the code with that? -Kevin pg_clearxlogtail.c Description: Binary data ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Kevin Grittner wrote: I omitted the code I was originally considering to have it work against files in place rather than as a filter. It seemed much simpler this way, we didn't actually have a use case for the additional functionality, and it seemed safer as a filter. Thoughts? A special non-filter mode could save some IO and diskspace by not actually writing all those zeros, but instead just seek to SizeOfWal-1 after writing the last valid byte, and writing one more zero. Of course, if you're gonna compress the WAL anyway, there is no point... greetings, Florian Pflug ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Thu, Sep 6, 2007 at 7:31 PM, in message [EMAIL PROTECTED], Kevin Grittner [EMAIL PROTECTED] wrote: On Thu, Sep 6, 2007 at 7:03 PM, in message [EMAIL PROTECTED], Jeff Davis [EMAIL PROTECTED] wrote: I think ... there's still room for a simple tool that can zero out the meaningless data in a partially-used WAL segment before compression. It seems reasonable to me, so long as you keep archive_timeout at something reasonably high. If nothing else, people that already have a collection of archived WAL segments would then be able to compact them. That would be a *very* useful tool for us, particularly if it could work against our existing collection of old WAL files. Management here has decided that it would be such a useful tool for our organization that, if nobody else is working on it yet, it is something I should be working on this week. Obviously, I would much prefer to do it in a way which would be useful to the rest of the PostgreSQL community, so I'm looking for advice, direction, and suggestions before I get started. I was planning on a stand-alone executable which could be run against a list of files to update them in-place, or to handle as single file as a stream. The former would be useful for dealing with the accumulation of files we've already got, the latter would be used in our archive script, just ahead of gzip in the pipe. Any suggestions on an existing executable to use as a model for best practices are welcome, as are suggestions for the safest and most robust techniques for identifying the portion of the WAL file which should be set to zero. Finally, I assume that I should put this on pgfoundry? -Kevin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Joshua D. Drake schrieb: This is not acceptable on our lists. Do not post in such a way again. Sincerely, Joshua D. Drake Hi Josh, Your're right, but this special Guy has just boring me a lot with Replication Things but my [Featurerequest] on the Topic was dedicated to Streaming Onlinebackup and this is not just the same as a simply Replication. Sometimes i think, its an Scandinavian Tradition, boring and talking without link to the Topic and giving primitive, useless advices like: look in google.com OR use: www.postgresql.org search Button tadt doesnt match with the Subject of Discussion. Apoc ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Ühel kenal päeval, L, 2007-09-08 kell 10:39, kirjutas apoc9009: Joshua D. Drake schrieb: This is not acceptable on our lists. Do not post in such a way again. Sincerely, Joshua D. Drake Hi Josh, Your're right, but this special Guy has just boring me a lot with Replication Things but my [Featurerequest] on the Topic was dedicated to Streaming Onlinebackup and this is not just the same as a simply Replication. Sometimes i think, its an Scandinavian Tradition, boring and talking without link to the Topic and giving primitive, useless advices like: look in google.com OR use: www.postgresql.org search Button tadt doesnt match with the Subject of Discussion. While his second advice: 2) read what other people write to you and try to understand that. may look like something primitive in general, I think it was to the point in this case. Try thinking of it some more, and you may eventually zen it, recognizing that A. you can easily roll your own Streaming Onlinebackup in any scripting language and with exactly the ftp directory structure using the info provided, and without needing any more skills than making queries to database and reading a portion of file from position N to position M. B. it probably won't take more than an hour to set up including testing. C. 'backup _is_ replication' is also true -- Hannu ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
C. 'backup _is_ replication' is also true -- Hannu It is useless to speak with a person like you about the diffrence between Backup and Replications.Both Things having diffrent Concepts and Approaches, but for you it is all the same. What should i say? Thadts the typically scandinavian Fishheadnature. A Fish is a Fish. (anyway if one is a Wale and next is a Shark). I guess, the next Thing you will say is: Nobody was on the Moon and the 9/11 Incident was the Work of George W. Bush Junior itself and Santa Claus will be the next President of the United States. Apoc ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Ühel kenal päeval, L, 2007-09-08 kell 21:15, kirjutas Apoc Sagdiyev: C. 'backup _is_ replication' is also true -- Hannu It is useless to speak with a person like you Oh, you think that _I_ am scandinavian ?? Never thought about that possibility ;P If speaking with me is useless to you, then don't. about the diffrence between Backup and Replications. Both Things having diffrent Concepts and Approaches, but for you it is all the same. by Capitalising these Nouns you really make them look like some Big Concepts that are Beyond Understanding Of Mere Mortals. What should i say? Thadts the typically scandinavian Fishheadnature. A Fish is a Fish. (anyway if one is a Wale and next is a Shark). The reason I have been so patient with you is that your question and writing style suggests that you are still quite young, probably no more that 17 to 19, and also I thought that you were looking for a solution to your problem, not just trying to look smart or pushing your latest great idea. meat What I was trying to tell you, is that in order to have a backup that can actually be used quickly in case of failure on master db, you need your backup to be in form of replica (that is one meaning of backup _is_ replication). If you had your backup as a week-old base backup + a set of WAL files, it can take days to bring the replacement machine up, as both unpacking the base backup and especially replaying the WAL files take time. /meat I guess, the next Thing you will say is: Nobody was on the Moon and the 9/11 Incident was the Work of George W. Bush Junior itself and Santa Claus will be the next President of the United States. No! Actually I'm wearing my tin hat right now and I Never say Anything about My Suspicions about 9/11 on Internet in fear of Echelon catching and filing me. --- Hannu ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
No! Actually I'm wearing my tin hat right now and I Never say Anything about My Suspicions about 9/11 on Internet in fear of Echelon catching and filing me. --- Hannu hmm, a little bit Para? http://www.myvideo.de/watch/1776449 Ok, now your point of View its more clearly... ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Hi, apoc9009 wrote: Thadt is Replication NOT Backup I've now read all of your messages in this thread, but I simply fail to understand why you are that much opposed to the term 'replication'. I think the only thing which comes any close to what you're looking for is replication (in particular eager multi-master replication). I'd recommend you familiarize yourself with the world of database replication. You already know the important chapter from our manual, learn that by heart. Then read [2] and [3]. :-) Regards Markus [1]: Postgres advocacy wiki: http://developer.postgresql.org/index.php/Replication%2C_Clustering%2C_and_Connection_Pooling [2]: Terms and Definitions of Database Replication http://www.postgres-r.org/documentation/terms ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
backup is not replication. but replicated database can be treated as good source of backup. please take following remarks: 1) in English you don't capitalize nouns 2) read what other people write to you and try to understand that. 3) this is open source, try to be more cooperative not just cry for a ready made solution 2007/9/7, apoc9009 [EMAIL PROTECTED]: Markus Schiltknecht schrieb: Hi, apoc9009 wrote: Thadt is Replication NOT Backup I've now read all of your messages in this thread, but I simply fail to understand why you are that much opposed to the term 'replication'. I think the only thing which comes any close to what you're looking for is replication (in particular eager multi-master replication). What is your Problem in understanding the Word Backup? Translation for you: A Backup is a File or Set of Files thadt contains the Data of your Business critical Informations. It should not be Archived on the same place, the same House or the same Room. A Replication Database has nothing to do with a Backup, it works only for Failover if the Primary Database has a Mailfunction. A good Backuptool is needed if you have Databases with sizes over 1 Terrabyte. The common Backup methods wont Work with Online Productiondatabases and without the Problem of Datalosses, this is only a Way for small and Mediumsize Databases, not for Hugh Databases. Keep in Mind: Backup is NOT Replication! Write it down 100 times and maybe you understand ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Filip Rembiałkowski ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
apoc9009 wrote: Write it down 100 times and maybe you understand If you are going to be rude nobody will bother to respond to you. Acknowledged experts have been very patient with you so far in this thread. You should be appreciative, not truculent. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Markus Schiltknecht wrote: [2]: Terms and Definitions of Database Replication http://www.postgres-r.org/documentation/terms Markus, the links in the left side menu are broken on the about and documentation page. They point to http://www.postgres-r.org/overview instead of http://www.postgres-r.org/documentation/overview, etc. Rainer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Markus Schiltknecht schrieb: Hi, apoc9009 wrote: Thadt is Replication NOT Backup I've now read all of your messages in this thread, but I simply fail to understand why you are that much opposed to the term 'replication'. I think the only thing which comes any close to what you're looking for is replication (in particular eager multi-master replication). What is your Problem in understanding the Word Backup? Translation for you: A Backup is a File or Set of Files thadt contains the Data of your Business critical Informations. It should not be Archived on the same place, the same House or the same Room. A Replication Database has nothing to do with a Backup, it works only for Failover if the Primary Database has a Mailfunction. A good Backuptool is needed if you have Databases with sizes over 1 Terrabyte. The common Backup methods wont Work with Online Productiondatabases and without the Problem of Datalosses, this is only a Way for small and Mediumsize Databases, not for Hugh Databases. Keep in Mind: Backup is NOT Replication! Write it down 100 times and maybe you understand ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Ühel kenal päeval, R, 2007-09-07 kell 12:03, kirjutas apoc9009: Andrew Sullivan schrieb: It seems that what you want is near-real-time online backups with _no cost_, which is not a feature that I think anyone will ever work on. A 100% Correct! I think anyone commit the Statement, thadt a Databases is a very imported Part of Software for a wide range of Peoples. It is absoluty normaly thadt thoose Peoples who using Databases having the Wish to have there Data save and up to Date until to the Last Record of a Customer (for Example: A Order form a Onlineshop). Very Large Databases with sizes more then over 1 Terrabyte cannot be backed up continously using Dumps or the common Backupmethods The Abstract need is: 1.An exact Copy (1:1) of the Productiondatabase should be mirrored at a diffrent Machine, connected via (LAN/WAN) (as Backup) 2.The Backup Operations should be done normaly while the Database is Online with very small Bandwich, I/O and CPU Trafficcosts. 3.Per Month or Week (or choosable) at a New Backup-Timeline/Version should be started (depend on the Basebackup) Probably you don't want your backup as a base backup + a bunch of logfiles. If you need to rally move to backup, then even a weeks worth of logfiles will take several hours or possibly days to apply. What you want is a standby machine, which applies all WAL files as they are copied over. 4.A New Base and its Streaming Backup Configuration should be Hot-Addable to an running Productiondatabase (without Shutdown or Lock) It currently is 5.A Remote Operationmanager Interface (GUI) should be there for Monitoring and Maintaining the Backups (maybe in PGAdmin) Huh ? I don't think this has anything to do with postgres-hackers list. Actually your other questions should also go to some users/newbies list, as this is a question of using existing features, and not something that needs to be added to backend. 6.If the Production and Mirror replicated Database is crashed (Hardware Failure or Provider Problems) the Recovery should done verry fast. Yep. That is what Hot-standby as described in postgres manual is meant to do. (An Idea for 5. / Clientside)... It would be a great Feature, if the PSQL-Client having a Local Deamon, thadt can Download the Backup Catalogues from the WAN-Backup FTP-Server continously down to the local Adminmachine. Then the Admin is able to burn the DB-Backups on DVD or write it on Tape on its local Machine at his Company (if the Backupreplicationserver fails two or the Internetprovider has Problems, the DB-Admin can apply its local Backups from DVD to the New DB on a New Machine anytime) All this keeping the WAL logs on CD's/tapes has nothing to do with fast recovery after failure. This is something that would be useful, but for entirely other purposes. Namely for a case, when you need to _GO_BACK_ to an earlier state. For example you discover that an careless operator or software bug has deleted important data last Wednesday and you need a way to get it back. In that case you take your last Sundays base backup and apply WAL up to few moments before the error happened. But it will not be fast. Hannu ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Ühel kenal päeval, R, 2007-09-07 kell 12:20, kirjutas apoc9009: Trevor Talbot schrieb: Backup 12/24/2008 Version 2 /pg/backup/12_24_2008/base/rcvry.rcv--- Basebackup /pg/backup/12_24_2008/changes/0001.chg --- Changed Data /changes/0002.chg --- Changed Data /changes/0003.chg --- Changed Data /changes/0010.chg --- Changed Data /changes/0001.rsf --- Recovery Stripeset File (10 MByte) addon of Basebackup delete *.chg if a Stripeset of 10 *.chg Files exist, they should be converted or merged to one bigger *.rsf Stripesetfile (to prevent for having Millions *.chg) Why? What does this actually do? This is the Structure on FTP-Site of the Backupserver! Not locally where the Postmaster runs on! This can currently be done with a simple script, either with ftp or ssh/scp - Hannu ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Hi, apoc9009 wrote: Translation for you: A Backup is a File or Set of Files thadt contains the Data of your Business critical Informations. It should not be Archived on the same place, the same House or the same Room. I disagree, a backup does not necessarily have to be a single file or a set of files. Wikipedia has this definition: backup refers to making copies of data so that these additional copies may be used to restore the original after a data loss event. While for replica, it states: replica is a copy that is relatively indistinguishable from the original Thus a backup can very well be thought of as replica, and vice versa. A Replication Database has nothing to do with a Backup, it works only for Failover if the Primary Database has a Mailfunction. That's certainly plain wrong, see multi-master replication where failover doesn't make any sense. Wikipedia again (although that's unfair, as I've contributed to that definition myself) [1]: Replication is the process of sharing information so as to ensure consistency between redundant resources ..for example a master database and a backup. Keep in Mind: Backup is NOT Replication! Write it down 100 times and maybe you understand A backup IS a replica. A backup IS a replica. A backup IS a replica. A backup IS a replica... Regards Markus [1]: http://en.wikipedia.org/wiki/Replication_%28computer_science%29 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Filip Rembiałkowski schrieb: please take following remarks: thx, but if i need some advice form a scandinavian dickhead then i will let you know this ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 apoc9009 wrote: Filip Rembiałkowski schrieb: please take following remarks: thx, but if i need some advice form a scandinavian dickhead then i will let you know this This is not acceptable on our lists. Do not post in such a way again. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFG4W9BATb/zqfZUUQRAgglAJ9Le5Yxu796/tfJfVTXUfRSecGnlACfe+iB KNK7jelJo30lh8ymw1Ppfqo= =CSbC -END PGP SIGNATURE- ---(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
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
apoc9009 wrote: Filip Rembiałkowski schrieb: please take following remarks: thx, but if i need some advice form a scandinavian dickhead then i will let you know this That kind of remark is not acceptable on the PostgreSQL mailing lists. Please do not post here again unless you can speak to people with an appropriate amount of respect. Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Fri, Sep 07, 2007 at 12:03:31PM +0200, apoc9009 wrote: Andrew Sullivan schrieb: It seems that what you want is near-real-time online backups with _no cost_, which is not a feature that I think anyone will ever work on. A 100% Correct! I think anyone commit the Statement, thadt a Databases is a very imported Part of Software for a wide range of Peoples. It is absoluty normaly thadt thoose Peoples who using Databases having the Wish to have there Data save and up to Date until to the Last Record of a Customer (for Example: A Order form a Onlineshop). Ah, but that's not what you asked (at least not as I interpreted it). You see, postgres does what you want if there's a simple power failure. Everything committed stays committed. You can protect against disk failure with RAID arrays also. What it sounds like is that you want to protect from someone blowing up your storage system. I think you need to step back and work out exactly what you are protecting against. Because it looks like your suggestion won't protect against a meteor stike on your data centre and 100km surrounding. Tell us what you are protecting against, then you can get some more useful answers. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Trevor Talbot schrieb: Backup 12/24/2008 Version 2 /pg/backup/12_24_2008/base/rcvry.rcv--- Basebackup /pg/backup/12_24_2008/changes/0001.chg --- Changed Data /changes/0002.chg --- Changed Data /changes/0003.chg --- Changed Data /changes/0010.chg --- Changed Data /changes/0001.rsf --- Recovery Stripeset File (10 MByte) addon of Basebackup delete *.chg if a Stripeset of 10 *.chg Files exist, they should be converted or merged to one bigger *.rsf Stripesetfile (to prevent for having Millions *.chg) Why? What does this actually do? This is the Structure on FTP-Site of the Backupserver! Not locally where the Postmaster runs on! Locally the Postmaster can create a basebackup (rcvry.rcv) then the FTP-Deamon logs on to the Remote FTP-Backupserver and Uploads this File to keep it save: /pg/backup/12_24_2008/ /pg/backup/12_24_2008/base/rcvry.rcv--- Basebackup At the Next: If a New Record at the Database was written, then the Postmaster locally creates A change File named: 0001.chg After thits the locally FTP-Deamon transfers this File to /pg/backup/12_24_2008/changes/0001.chg --- Changed Data Then /pg/backup/12_24_2008/changes/0002.chg --- Changed Data /pg/backup/12_24_2008/changes/0003.chg --- Changed Data /pg/backup/12_24_2008/changes/0010.chg --- Changed Data Then the FTP-Backupdeamon merged the last 10.x *.chg Files to one greater *.RSF File Online while logged into the FTP-Backupserver and Delete Online the Last 10 *.chg Files (after this the Last 10.x *.chg Files, created by the Postmaster can be deleted localy where the Postmaster runs). The Benefit: If the Machine where the Database run on having a Mailfunction, then the Last Record of the Databasecatalouge is backed up to an *.chg or *. RSF File. Thadt my Idea of this Apoc ---(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
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Andrew Sullivan schrieb: It seems that what you want is near-real-time online backups with _no cost_, which is not a feature that I think anyone will ever work on. A 100% Correct! I think anyone commit the Statement, thadt a Databases is a very imported Part of Software for a wide range of Peoples. It is absoluty normaly thadt thoose Peoples who using Databases having the Wish to have there Data save and up to Date until to the Last Record of a Customer (for Example: A Order form a Onlineshop). Very Large Databases with sizes more then over 1 Terrabyte cannot be backed up continously using Dumps or the common Backupmethods The Abstract need is: 1.An exact Copy (1:1) of the Productiondatabase should be mirrored at a diffrent Machine, connected via (LAN/WAN) (as Backup) 2.The Backup Operations should be done normaly while the Database is Online with very small Bandwich, I/O and CPU Trafficcosts. 3.Per Month or Week (or choosable) at a New Backup-Timeline/Version should be started (depend on the Basebackup) 4.A New Base and its Streaming Backup Configuration should be Hot-Addable to an running Productiondatabase (without Shutdown or Lock) 5.A Remote Operationmanager Interface (GUI) should be there for Monitoring and Maintaining the Backups (maybe in PGAdmin) 6.If the Production and Mirror replicated Database is crashed (Hardware Failure or Provider Problems) the Recovery should done verry fast. (An Idea for 5. / Clientside)... It would be a great Feature, if the PSQL-Client having a Local Deamon, thadt can Download the Backup Catalogues from the WAN-Backup FTP-Server continously down to the local Adminmachine. Then the Admin is able to burn the DB-Backups on DVD or write it on Tape on its local Machine at his Company (if the Backupreplicationserver fails two or the Internetprovider has Problems, the DB-Admin can apply its local Backups from DVD to the New DB on a New Machine anytime) Apoc ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Ühel kenal päeval, R, 2007-09-07 kell 16:41, kirjutas apoc9009: Filip Rembiałkowski schrieb: please take following remarks: thx, but if i need some advice form a scandinavian dickhead then i will let you know this Is this apoc9009 guy real ? For some time I honestly believed (based in part on the english-like language used in postings) that he just cant understand what is written in our documentation. My other suspicion was that he has never actually tried to do what he claims, but has just click-checked some checkboxes on some third-party backup software and is actually lamenting about the lack of box labeled Absolutely Reliable PostgreSQL backup. But now i suspect that someone is just pulling our collective leg and just trolling under false name to test how long we stand it ? --- Hannu P.S.: any ideas, whom he suspect think to be scandinavian in this thread ? or is advice form a scandinavian dickhead an idiom in some language ? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Sat, Sep 08, 2007 at 01:02:04AM +0300, Hannu Krosing wrote: ??hel kenal p??eval, R, 2007-09-07 kell 16:41, kirjutas apoc9009: Filip Rembia??kowski schrieb: please take following remarks: thx, but if i need some advice form a scandinavian dickhead then i will let you know this Is this apoc9009 guy real ? Pretty much as soon as I saw that comment I just nuked the whole thread and moved on. I suggest everyone else just do the same. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpwJlvmA4mop.pgp Description: PGP signature
[HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Hi Hackers, In my Project i have to handle a Database with 600 GByte Text only, distributed on 4 Tablespaces on multiple Harddisks and Remote SAN's connected via Gigaethernet to the Remote SAN-Storage. I need more flexibility by doing Backups of my big Database, but the built in Online Backupsystem dont work for my Setup good enought for me. I Can not accept 16 MByte big WAL's Files for securing it on Tape. 16 MByte Data loss on a Crash Situation is Fatal and no helpfully (1 MByte to). I wish to have a continoues Backup without any data losses. My Idea: - 1 A Complete Database Backup from Scratch (its implemented right now) - 2 Online streaming Backup thadt, updates my Basebackup continously every Time Changes was made (the half functionality is allready implemented) - 3 Able to redirect the Online Streamingbackup Files to an Remote Servermachine (FTP) (the ARCHIVE Param in postgresql.conf can do thadt allready but the Method is not 100% Continously, big Holes of Datalosses can occur) - 4 Version Management of Multiple Backuplines by Timestamp (is not yet implemented) - 5 Recovery Option inside the PSQL-Client, for doing the Desaster Recovery. (is not yet implemented) Benefitts: All Users of Hugh Databases (Missioncritical and allways Online) can bring up its Databases with the same information with differences 1-5 Sec. before the Crash occurs! ps: At EMCSoftware there is an Tool thadt has can do thadt for ORACLE and MSSQL but there is not Option for Postgres avaiable ); Sry for my bad english and i hope there is some one thadt can understand the Problem. Apoc ---(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
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
apoc9009 wrote: Hi Hackers, In my Project i have to handle a Database with 600 GByte Text only, distributed on 4 Tablespaces on multiple Harddisks and Remote SAN's connected via Gigaethernet to the Remote SAN-Storage. I need more flexibility by doing Backups of my big Database, but the built in Online Backupsystem dont work for my Setup good enought for me. I Can not accept 16 MByte big WAL's Files for securing it on Tape. 16 MByte Data loss on a Crash Situation is Fatal and no helpfully (1 MByte to). I wish to have a continoues Backup without any data losses. http://www.postgresql.org/docs/8.2/static/warm-standby.html Particularly section 23.4.4 That can get you to 1 second or less. -- Richard Huxton Archonet Ltd ---(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
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
http://www.postgresql.org/docs/8.2/static/warm-standby.html Particularly section 23.4.4 Thadt is Replication NOT Backup ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
http://www.postgresql.org/docs/8.2/static/warm-standby.html Particularly section 23.4.4 23.4.4 is thadt what iam using just im Time but this ist not eneought for me! No Versioning, no chances to prevent data losses You have to wait until a WAL File ist written (Default Value for WAL Files is 16 MBytes). I need an EXCAT Copy from the Database and only the last changes in it to for updating my Backupresitory. If the System Crash, you have Dataloss of over 16 MBytes thadts Fatal and not acceptable! 1MByte Dataloss of ASCII Data is also not acceptable! ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
apoc9009 wrote: http://www.postgresql.org/docs/8.2/static/warm-standby.html Particularly section 23.4.4 23.4.4 is thadt what iam using just im Time but this ist not eneought for me! No Versioning, no chances to prevent data losses You have to wait until a WAL File ist written (Default Value for WAL Files is 16 MBytes). You've either not read 23.4.4 or haven't understood it. If the text is unclear, documentation additions/changes are always welcome. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
You've either not read 23.4.4 or haven't understood it. If the text is unclear, documentation additions/changes are always welcome. I have read this: PostgreSQL directly supports file-based log shipping as described above. It is also possible to implement record-based log shipping, though this requires custom development. But thadt is not thadt what iam looking for! Filebased Logship backups having a big Problem for doing continous Backups. You have to wait until the Postmaster has written the WAL File, after this you can save it to the Backupserver. But 1 WAL has a size of 16 MByte ny default! (thadt is a big Datahole in your Online Backup!) It makes no sense to reduce the Filesize. If the Filesize is smaller then 16 MBytes for WAL Files you have still the same Problem, there are Data losses and thadt the Center of the Problem. I wish to have an Solution, thadt backup my Database DB wihout Datalosses, without locking Tables, without Shutdown and without any User must be forced for logging out (Backup in Production State Online without Datalosses). ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
apoc9009 wrote: I wish to have an Solution, thadt backup my Database DB wihout Datalosses, without locking Tables, without Shutdown and without any User must be forced for logging out (Backup in Production State Online without Datalosses). Without datalosses is utopy. For that, you'd need something like synchronous replication, otherwise you're always going to have a window where you have something committed in the server, but not yet in the backup. So it's just a question of how wide that window is, how much data loss can you live with. With file-based log shipping, you can get down to 1 second, by using the archive_timeout setting. It will produce a lot of log files with very little content in them, but they will compress well. The record-based log shipping will give you a very narrow window, down to 1 second or even less if you're willing to poll the server that often, but requires some custom development. I wonder, do you really need such a super real time backup solution, when you have the remote SAN? Don't you trust that the SAN hardware? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Hi, On 9/6/07, apoc9009 [EMAIL PROTECTED] wrote: Filebased Logship backups having a big Problem for doing continous Backups. You have to wait until the Postmaster has written the WAL File, after this you can save it to the Backupserver. But 1 WAL has a size of 16 MByte ny default! (thadt is a big Datahole in your Online Backup!) You should read the documentation more carefully: archive_timeout (integer) The archive_command is only invoked on completed WAL segments. Hence, if your server generates little WAL traffic (or has slack periods where it does so), there could be a long delay between the completion of a transaction and its safe recording in archive storage. To put a limit on how old unarchived data can be, you can set archive_timeout to force the server to switch to a new WAL segment file periodically. When this parameter is greater than zero, the server will switch to a new segment file whenever this many seconds have elapsed since the last segment file switch. Note that archived files that are closed early due to a forced switch are still the same length as completely full files. Therefore, it is unwise to use a very short archive_timeout — it will bloat your archive storage. archive_timeout settings of a minute or so are usually reasonable. This parameter can only be set in the postgresql.conf file or on the server command line. (Taken from http://www.postgresql.org/docs/8.2/static/runtime-config-wal.html#GUC-ARCHIVE-TIMEOUT) I wish to have an Solution, thadt backup my Database DB wihout Datalosses, without locking Tables, without Shutdown and without any User must be forced for logging out (Backup in Production State Online without Datalosses). Well, there's what you want and there's what is possible. Warm standby is definitely the best solution for your problem, even if not perfect. -- Guillaume ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
apoc9009 wrote: You've either not read 23.4.4 or haven't understood it. If the text is unclear, documentation additions/changes are always welcome. I have read this: PostgreSQL directly supports file-based log shipping as described above. It is also possible to implement record-based log shipping, though this requires custom development. But thadt is not thadt what iam looking for! Filebased Logship backups having a big Problem for doing continous Backups. You have to wait until the Postmaster has written the WAL File, after this you can save it to the Backupserver. But 1 WAL has a size of 16 MByte ny default! (thadt is a big Datahole in your Online Backup!) Which is why that entire section is about copying just the changed parts of WAL files. It makes no sense to reduce the Filesize. If the Filesize is smaller then 16 MBytes for WAL Files you have still the same Problem, there are Data losses and thadt the Center of the Problem. But in your original email you said: All Users of Hugh Databases (Missioncritical and allways Online) can bring up its Databases with the same information with differences 1-5 Sec. before the Crash occurs! That suggested to me that you didn't want per-transaction backup, just one backup every second. OK, what you actually want is a continuous backup with one copy made per transaction. I wish to have an Solution, thadt backup my Database DB wihout Datalosses, without locking Tables, without Shutdown and without any User must be forced for logging out (Backup in Production State Online without Datalosses). So, if I understand, you want on of: 1. External RAID array. If main machine dies, turn backup machine on. Both share the same disks. 2. Something like DRBD to copy individual disk blocks between machines. You could do this just for WAL. 3. Replication. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Ühel kenal päeval, N, 2007-09-06 kell 12:53, kirjutas apoc9009: You've either not read 23.4.4 or haven't understood it. If the text is unclear, documentation additions/changes are always welcome. I have read this: PostgreSQL directly supports file-based log shipping as described above. It is also possible to implement record-based log shipping, though this requires custom development. check out SkyTools, it seems to make use of 23.4.4 to have sub-second failure window - Hannu ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Thu, 2007-09-06 at 12:21 +0200, apoc9009 wrote: If the System Crash, you have Dataloss of over 16 MBytes thats Fatal and not acceptable! 1MByte Dataloss of ASCII Data is also not acceptable! Is any data loss acceptable in the case of a disaster? How much? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(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
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Ühel kenal päeval, N, 2007-09-06 kell 12:53, kirjutas apoc9009: I wish to have an Solution, thadt backup my Database DB wihout Datalosses, Then you need the backup record to be completed on the backup machine before the transaction commit returns on master. This is quaranteed to be really slow for any significant transaction rate but can be done using DRBD. without locking Tables, No backup I know of needs locking tables without Shutdown This one I just don't understand. Shtdown what ? without any User must be forced for logging out (Backup in Production State Online without Datalosses). So you want the user to still be connected to the failed machine, but at the same time be connected to the new live failover machine ? - Hannu ---(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
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
So you want the user to still be connected to the failed machine, but at the same time be connected to the new live failover machine ? - Hannu No. The User should be connected to the running db without restrictions while backup is in progress Apoc ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Ühel kenal päeval, N, 2007-09-06 kell 16:15, kirjutas apoc9009: So you want the user to still be connected to the failed machine, but at the same time be connected to the new live failover machine ? - Hannu No. The User should be connected to the running db without restrictions while backup is in progress And what's wrong with WAL-based backup then ? Especially the variant described in 23.4.4 wich keeps the WAL copied to backup machine up to last second ? --- Hannu ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Thu, 2007-09-06 at 12:53 +0200, apoc9009 wrote: You've either not read 23.4.4 or haven't understood it. If the text is unclear, documentation additions/changes are always welcome. I have read this: PostgreSQL directly supports file-based log shipping as described above. It is also possible to implement record-based log shipping, though this requires custom development. We may expect that feature in later releases, but definitely not in 8.3 I wish to have an Solution, thadt backup my Database DB wihout Datalosses, without locking Tables, without Shutdown and without any User must be forced for logging out (Backup in Production State Online without Datalosses). I'm not clear whether you are looking for Backup, or High Availability Replication. There is no data loss with the online backup technique described in the manual. If you require HA replication with zero data-loss then you are currently restricted to non-database techniques, which you already mentioned, so you have your answer already. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Heikki Linnakangas schrieb: apoc9009 wrote: Without datalosses is utopy. For that, you'd need something like synchronous replication, otherwise you're always going to have a window where you have something committed in the server, but not yet in the backup. So it's just a question of how wide that window is, how much data loss can you live with. With file-based log shipping, you can get down to 1 second, by using the archive_timeout setting. It will produce a lot of log files with very little content in them, but they will compress well. Yes, it is possible but not recommended . My Backup Servers Filesystem will explode :D The record-based log shipping will give you a very narrow window, down to 1 second or even less if you're willing to poll the server that often, but requires some custom development. Correct, but this is not good enought and i think there are a lot of Peoples having the same Problem. It was wishfull, having a Online Streaming Backupsubsystem, thadt can produce portable Backupdatabases, to prevent users for Millions of Archive Files on the Backup FTP-Server. My Ideaa is the following Folder Structure: /pg/backup/ Backup 12/24/2007 Version 1 /pg/backup/12_24_2007/base/rcvry.rcv --- Basebackup /pg/backup/12_24_2007/changes/0001.chg --- Changed Data /changes/0002.chg --- Changed Data /changes/0003.chg --- Changed Data Backup 12/24/2008 Version 2 /pg/backup/12_24_2008/base/rcvry.rcv --- Basebackup /pg/backup/12_24_2008/changes/0001.chg --- Changed Data /changes/0002.chg --- Changed Data /changes/0003.chg --- Changed Data /changes/0010.chg --- Changed Data /changes/0001.rsf --- Recovery Stripeset File (10 MByte) addon of Basebackup delete *.chg if a Stripeset of 10 *.chg Files exist, they should be converted or merged to one greater Recovery Stripe File (*.RSF) Things to do: 1.A Integradted FTP-Client to the Postmaster Process 2.Online Streamingbackup Logic inside the Postmaster Apoc ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Simon Riggs schrieb: On Thu, 2007-09-06 at 12:53 +0200, apoc9009 wrote: You've either not read 23.4.4 or haven't understood it. If the text is unclear, documentation additions/changes are always welcome. I have read this: PostgreSQL directly supports file-based log shipping as described above. It is also possible to implement record-based log shipping, though this requires custom development. We may expect that feature in later releases, but definitely not in 8.3 I wish to have an Solution, thadt backup my Database DB wihout Datalosses, without locking Tables, without Shutdown and without any User must be forced for logging out (Backup in Production State Online without Datalosses). I'm not clear whether you are looking for Backup, or High Availability Replication. There is no data loss with the online backup technique described in the manual. No, there is a lost of Data. The WAL File musst bei generated by the Postmaster and this File must be copied to the safe Remote Backupserver. If the Machine, where the Database is running crashed, then the Last WAL is lost and the Backup isnt complet,e this is the Center of the Problem. If you require HA replication with zero data-loss then you are currently restricted to non-database techniques, which you already mentioned, so you have your answer already. we talking about Backup this isnt the same as Replication. ---(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
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On 9/6/07, apoc9009 [EMAIL PROTECTED] wrote: Backup 12/24/2008 Version 2 /pg/backup/12_24_2008/base/rcvry.rcv --- Basebackup /pg/backup/12_24_2008/changes/0001.chg --- Changed Data /changes/0002.chg --- Changed Data /changes/0003.chg --- Changed Data /changes/0010.chg --- Changed Data /changes/0001.rsf --- Recovery Stripeset File (10 MByte) addon of Basebackup delete *.chg if a Stripeset of 10 *.chg Files exist, they should be converted or merged to one greater Recovery Stripe File (*.RSF) Why? What does this actually do? ---(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
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Thu, Sep 06, 2007 at 04:08:10PM +0200, apoc9009 wrote: archive_timeout setting. It will produce a lot of log files with very little content in them, but they will compress well. Yes, it is possible but not recommended . My Backup Servers Filesystem will explode :D . . . Correct, but this is not good enought and i think there are a lot of Peoples having the same Problem. It was wishfull, having a Online Streaming Backupsubsystem, thadt can produce portable Backupdatabases, to prevent users for Millions of Archive Files on the Backup FTP-Server. It seems that what you want is near-real-time online backups with _no cost_, which is not a feature that I think anyone will ever work on. A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath. --Damien Katz ---(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
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Thu, 2007-09-06 at 12:08 +0100, Heikki Linnakangas wrote: With file-based log shipping, you can get down to 1 second, by using the archive_timeout setting. It will produce a lot of log files with very little content in them, but they will compress well. I tried doing a couple pg_switch_xlog() in quick succession, and the WAL files that represent less than a second of actual data don't seem much more compressible than the rest. I think WAL compression has been talked about before, with ideas such as removing unneeded full page images. However, it seems like it would be easy to get a lot of the benefit by just having a utility that could discard useless contents that are left over from a previously-recycled file, and then could just reconstruct it later with zeros. Regards, Jeff Davis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Thu, 2007-09-06 at 17:53 +0300, Hannu Krosing wrote: Ühel kenal päeval, N, 2007-09-06 kell 16:15, kirjutas apoc9009: So you want the user to still be connected to the failed machine, but at the same time be connected to the new live failover machine ? - Hannu No. The User should be connected to the running db without restrictions while backup is in progress And what's wrong with WAL-based backup then ? Especially the variant described in 23.4.4 wich keeps the WAL copied to backup machine up to last second ? I think the OP means - synchronous replication - ability to access the standby node for queries Yes, both requests are planned for later releases. Bruce, can we edit the TODO to include a section called High Availability? We already have a few scattered references to such things, but in Admin and WAL. We need a specific section. We currently have these items, that can be moved to the new section: - Allow a warm standby system to also allow read-only statements - Allow WAL traffic to be streamed to another server for stand-by replication new section would look like this: High Availability - - Allow a warm standby system to also allow read-only statements - Allow WAL traffic to be streamed to another server for stand-by replication (synchronous/asynchronous options) - Improve scalability of recovery for large I/O bound databases -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Ühel kenal päeval, N, 2007-09-06 kell 19:33, kirjutas apoc9009: Simon Riggs schrieb: I'm not clear whether you are looking for Backup, or High Availability Replication. There is no data loss with the online backup technique described in the manual. No, there is a lost of Data. The WAL File musst bei generated by the Postmaster and this File must be copied to the safe Remote Backupserver. If the Machine, where the Database is running crashed, then the Last WAL is lost and the Backup isnt complet,e this is the Center of the Problem. read and re-read 23.4.4 you DO NOT have to wait for the whole file to be complete to copy it over you can query the position where postgres has currently completed writing and then copy over that part. doing so you can have wal-based backup that is good up to last second (if you poll the function and copy over the newly written part of the file every second) Look at WalMgr.py in SkyTools package how this can be done in practice. Skytools is available at http://pgfoundry.org/projects/skytools/ If used for setting up WAL-based backup on pg 8.2, it runs automatically in syncdaemon mode, which means that parts of WAL file are copied over as soon as they are written by postgres. -- Hannu ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Jeff Davis [EMAIL PROTECTED] writes: On Thu, 2007-09-06 at 12:08 +0100, Heikki Linnakangas wrote: With file-based log shipping, you can get down to 1 second, by using the archive_timeout setting. It will produce a lot of log files with very little content in them, but they will compress well. I tried doing a couple pg_switch_xlog() in quick succession, and the WAL files that represent less than a second of actual data don't seem much more compressible than the rest. That's because we do not try to zero out a WAL file when recycling it, so the part after what's been used is not any more compressible than the valid part. pg_switch_xlog is not, and was never intended to be, a solution for a low-latency log-shipping scheme. The right solution for that is to make a smarter log-shipping daemon that transmits only part of a WAL segment at a time. (As Hannu is getting tired of repeating, you can find a working example in SkyTools.) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Thu, Sep 6, 2007 at 7:03 PM, in message [EMAIL PROTECTED], Jeff Davis [EMAIL PROTECTED] wrote: I think ... there's still room for a simple tool that can zero out the meaningless data in a partially-used WAL segment before compression. It seems reasonable to me, so long as you keep archive_timeout at something reasonably high. If nothing else, people that already have a collection of archived WAL segments would then be able to compact them. That would be a *very* useful tool for us, particularly if it could work against our existing collection of old WAL files. -Kevin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup(Maybe OFFTOPIC)
On Thu, 2007-09-06 at 21:50 +0100, Gregory Stark wrote: - Improve scalability of recovery for large I/O bound databases That seems too vague for the TODO. Did you have specific items in mind? I think we should parallelise recovery. Heikki wanted to do this another way, so I worded it vaguely (i.e. as a requirement) to cover multiple approaches. Some of that was discussed on -hackers already (where?). -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup(Maybe OFFTOPIC)
Simon Riggs [EMAIL PROTECTED] writes: High Availability - - Allow a warm standby system to also allow read-only statements - Allow WAL traffic to be streamed to another server for stand-by replication (synchronous/asynchronous options) Asynchronous streaming of WAL would be a very good feature. Synchronous streaming where a commit doesn't return until the backup server (or a quorum of backup servers if you have more than one) acknowledges receipt of the logs past the commit record would be an *extremely* good feature. Those could be broken out into two separate points. Streaming WAL is one thing, feedback and pitr-synchronous commits would be a second point. Knowing what WAL record the backup server had received so far and what WAL record it had processed so far would be useful for plenty of other purposes as well. - Improve scalability of recovery for large I/O bound databases That seems too vague for the TODO. Did you have specific items in mind? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Thu, 2007-09-06 at 19:23 -0400, Tom Lane wrote: Jeff Davis [EMAIL PROTECTED] writes: On Thu, 2007-09-06 at 12:08 +0100, Heikki Linnakangas wrote: With file-based log shipping, you can get down to 1 second, by using the archive_timeout setting. It will produce a lot of log files with very little content in them, but they will compress well. I tried doing a couple pg_switch_xlog() in quick succession, and the WAL files that represent less than a second of actual data don't seem much more compressible than the rest. That's because we do not try to zero out a WAL file when recycling it, so the part after what's been used is not any more compressible than the valid part. That was my point. pg_switch_xlog is not, and was never intended to be, a solution for a low-latency log-shipping scheme. The right solution for that is to make a smarter log-shipping daemon that transmits only part of a WAL segment at a time. (As Hannu is getting tired of repeating, you can find a working example in SkyTools.) I think that in addition to WalMgr (which is the tool I assume you're referring to), there's still room for a simple tool that can zero out the meaningless data in a partially-used WAL segment before compression. It seems reasonable to me, so long as you keep archive_timeout at something reasonably high. If nothing else, people that already have a collection of archived WAL segments would then be able to compact them. I agree that something like WalMgr is better for low-latency, however. Regards, Jeff Davis ---(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
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Thu, Sep 6, 2007 at 3:25 PM, in message [EMAIL PROTECTED], Jeff Davis [EMAIL PROTECTED] wrote: On Thu, 2007-09-06 at 12:08 +0100, Heikki Linnakangas wrote: With file-based log shipping, you can get down to 1 second, by using the archive_timeout setting. It will produce a lot of log files with very little content in them, but they will compress well. I tried doing a couple pg_switch_xlog() in quick succession, and the WAL files that represent less than a second of actual data don't seem much more compressible than the rest. Agreed. We kick out a WAL file once per hour (if they don't fill first) and have found that a compressed WAL file during normal activity averages a little over 4 MB. During the nightly VACUUM ANALYZE we get a few over 10 MB. The interesting thing is that about the time that these WAL files would get reused, even when the system is idle, they are at the VACUUM ANALYZE size again. Note how the first three 18:00 file sizes echo at the next morning, before the users are in and working. 5293110 2007-08-22 17:14 000100180044.gz 5205720 2007-08-22 17:14 000100180045.gz 5104595 2007-08-22 17:14 000100180046.gz 3747524 2007-08-22 17:14 000100180047.gz 3118762 2007-08-22 17:14 000100180048.gz 3342918 2007-08-22 17:14 000100180049.gz 4600438 2007-08-22 17:14 00010018004A.gz 2715708 2007-08-22 17:15 00010018004B.gz 2865803 2007-08-22 17:15 00010018004C.gz 10111700 2007-08-22 18:00 00010018004D.gz 11561630 2007-08-22 18:00 00010018004E.gz 11619590 2007-08-22 18:00 00010018004F.gz 7165231 2007-08-22 18:00 000100180050.gz 4012164 2007-08-22 18:00 000100180051.gz 4590502 2007-08-22 18:00 000100180052.gz 3617203 2007-08-22 18:01 000100180053.gz 3552210 2007-08-22 18:01 000100180054.gz 4006261 2007-08-22 18:01 000100180055.gz 3538293 2007-08-22 18:01 000100180056.gz 4291776 2007-08-22 18:02 000100180057.gz 4837268 2007-08-22 18:02 000100180058.gz 3948408 2007-08-22 19:02 000100180059.gz 2714635 2007-08-22 20:02 00010018005A.gz 4989698 2007-08-22 21:02 00010018005B.gz 5290729 2007-08-22 22:02 00010018005C.gz 5203306 2007-08-22 23:02 00010018005D.gz 5101976 2007-08-23 00:02 00010018005E.gz 3745125 2007-08-23 01:02 00010018005F.gz 3119218 2007-08-23 02:02 000100180060.gz 3340691 2007-08-23 03:02 000100180061.gz 4599279 2007-08-23 04:02 000100180062.gz 10110899 2007-08-23 05:02 000100180063.gz 11555895 2007-08-23 06:02 000100180064.gz 11587646 2007-08-23 07:02 000100180065.gz ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
