Обсуждение: postgresql.conf archive_command example
I think it would be useful to add the following explanation and sample to the postgresql.conf sample file: diff --git i/src/backend/utils/misc/postgresql.conf.sample w/src/backend/utils/misc/postgresql.conf.sample --- i/src/backend/utils/misc/postgresql.conf.sample +++ w/src/backend/utils/misc/postgresql.conf.sample @@ -186,6 +186,9 @@#archive_mode = off # allows archiving to be done # (change requiresrestart)#archive_command = '' # command to use to archive a logfile segment + # placeholders: %p = path of file to archive + # %f = file name only + # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'#archive_timeout= 0 # force a logfile segment switch after this # number of seconds; 0 disables This corresponds to what we have in the documentation and mirrors the example in recovery.conf.sample. Objections?
On 31 August 2011 04:39, Peter Eisentraut <peter_e@gmx.net> wrote: > I think it would be useful to add the following explanation and sample > to the postgresql.conf sample file: > Good idea Peter, +1. Cheers, BJ
2011/8/30 Peter Eisentraut <peter_e@gmx.net>: > I think it would be useful to add the following explanation and sample > to the postgresql.conf sample file: > > diff --git i/src/backend/utils/misc/postgresql.conf.sample w/src/backend/utils/misc/postgresql.conf.sample > --- i/src/backend/utils/misc/postgresql.conf.sample > +++ w/src/backend/utils/misc/postgresql.conf.sample > @@ -186,6 +186,9 @@ > #archive_mode = off # allows archiving to be done > # (change requires restart) > #archive_command = '' # command to use to archive a logfile segment > + # placeholders: %p = path of file to archive > + # %f = file name only > + # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f' > #archive_timeout = 0 # force a logfile segment switch after this > # number of seconds; 0 disables > > This corresponds to what we have in the documentation and mirrors the > example in recovery.conf.sample. > > Objections? No objections, it is welcome. Just a question: can we build a different postgresql.conf for windows or do we add a windows command example here as well ? > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation
On ons, 2011-08-31 at 11:18 +0200, Cédric Villemain wrote: > Just a question: can we build a different postgresql.conf for windows > or do we add a windows command example here as well ? Well, we could make initdb patch it up, but that might seem excessive.
2011/8/31 Peter Eisentraut <peter_e@gmx.net>: > On ons, 2011-08-31 at 11:18 +0200, Cédric Villemain wrote: >> Just a question: can we build a different postgresql.conf for windows >> or do we add a windows command example here as well ? > > Well, we could make initdb patch it up, but that might seem excessive. sure. I was wondering if it was already possible, not proposing to do it. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation
Peter Eisentraut <peter_e@gmx.net> writes: > Well, we could make initdb patch it up, but that might seem excessive. I sometime wonder if archive_mode shouldn't default to "on" with the archive_command set to either '/bin/true' or 'rem' for windows. That allows to install proper archiving without restart, but the tradeoff is of course that you need to restart to enable some optimisation cases by turning archive_mode off. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
2011/9/1 Dimitri Fontaine <dimitri@2ndquadrant.fr>: > Peter Eisentraut <peter_e@gmx.net> writes: >> Well, we could make initdb patch it up, but that might seem excessive. > > I sometime wonder if archive_mode shouldn't default to "on" with the > archive_command set to either '/bin/true' or 'rem' for windows. > > That allows to install proper archiving without restart, but the > tradeoff is of course that you need to restart to enable some > optimisation cases by turning archive_mode off. Seems like it would be better to fix archive_mode so that it can be changed without a restart. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
> Seems like it would be better to fix archive_mode so that it can be > changed without a restart. +1 I'm also wondering if providing some shell script examples of a fault-tolerant script to handle archiving would be useful. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On Thu, Sep 1, 2011 at 3:05 PM, Josh Berkus <josh@agliodbs.com> wrote: > +1 > > I'm also wondering if providing some shell script examples of a > fault-tolerant script to handle archiving would be useful. I think it would. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes: >> I'm also wondering if providing some shell script examples of a >> fault-tolerant script to handle archiving would be useful. > > I think it would. My usual advice is to avoid having to write one if possible, because it's more complex than it looks. What about recommending existing solutions, such as walmgr from Skytools? Even better, what about including a default archiving tool, that could be either another script in bin/ or rather an internal command. The default would accept a location as argument, for simple needs you mount a remote filesystem and there you go. If you need something more complex, you still can provide it yourself. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Dimitri Fontaine <dimitri@2ndQuadrant.fr> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >>> I'm also wondering if providing some shell script examples of a >>> fault-tolerant script to handle archiving would be useful. >> >> I think it would. > > My usual advice is to avoid having to write one if possible, > because it's more complex than it looks. What about recommending > existing solutions, such as walmgr from Skytools? > > Even better, what about including a default archiving tool, that > could be either another script in bin/ or rather an internal > command. The default would accept a location as argument, for > simple needs you mount a remote filesystem and there you go. If > you need something more complex, you still can provide it > yourself. In a green field I might argue for having an archvie_directory GUC instead of archive_command. As it stands, it might be a really good idea to provide a pg_archiveto executable which takes as arguments a directory path and the arguments passed to the archive script. With a little extra effort, the executable could check for some file which would specify what host and path should be writing archives there, to avoid problems with copied database directories accidentally writing to the same location as the source. Such an executable seems like minimal effort compared to the problems it would solve. If there's an existing tool with appropriate licensing which is sufficiently portable and reliable, all the better -- let's ship it and use that for our example archive_command. -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > In a green field I might argue for having an archvie_directory GUC > instead of archive_command. As it stands, it might be a really good I would think we then would need both. archive_command with parameters offers both. > idea to provide a pg_archiveto executable which takes as arguments a > directory path and the arguments passed to the archive script. With > a little extra effort, the executable could check for some file > which would specify what host and path should be writing archives > there, to avoid problems with copied database directories > accidentally writing to the same location as the source. > > Such an executable seems like minimal effort compared to the > problems it would solve. > > If there's an existing tool with appropriate licensing which is > sufficiently portable and reliable, all the better -- let's ship it > and use that for our example archive_command. I would like for it not to be an example, but a default value. Something ready for production but with a very narrow use case. -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On Fri, Sep 2, 2011 at 10:34 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Dimitri Fontaine <dimitri@2ndQuadrant.fr> wrote: >> Robert Haas <robertmhaas@gmail.com> writes: >>>> I'm also wondering if providing some shell script examples of a >>>> fault-tolerant script to handle archiving would be useful. >>> >>> I think it would. >> >> My usual advice is to avoid having to write one if possible, >> because it's more complex than it looks. What about recommending >> existing solutions, such as walmgr from Skytools? >> >> Even better, what about including a default archiving tool, that >> could be either another script in bin/ or rather an internal >> command. The default would accept a location as argument, for >> simple needs you mount a remote filesystem and there you go. If >> you need something more complex, you still can provide it >> yourself. > > In a green field I might argue for having an archvie_directory GUC > instead of archive_command. As it stands, it might be a really good > idea to provide a pg_archiveto executable which takes as arguments a > directory path and the arguments passed to the archive script. With > a little extra effort, the executable could check for some file > which would specify what host and path should be writing archives > there, to avoid problems with copied database directories > accidentally writing to the same location as the source. > > Such an executable seems like minimal effort compared to the > problems it would solve. > > If there's an existing tool with appropriate licensing which is > sufficiently portable and reliable, all the better -- let's ship it > and use that for our example archive_command. Another thought I have here is to wonder whether we should change something on the server side so that we don't NEED such a complicated archive_command. I mean, copying a file to a directory somewhere is not fundamentally a complex operation. Nor is using ssh to copy it to another machine. The fact that archive_commands need to be so complex seems like a usability defect. The consensus seems to be that just using something like 'cp' for your archive command won't work out well, but maybe instead of shipping a more complicated script we should be trying to eliminate (or at least reduce) the need for a more complicated script. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 09/02/2011 01:00 PM, Robert Haas wrote: > On Fri, Sep 2, 2011 at 10:34 AM, Kevin Grittner > <Kevin.Grittner@wicourts.gov> wrote: >> Dimitri Fontaine<dimitri@2ndQuadrant.fr> wrote: >>> Robert Haas<robertmhaas@gmail.com> writes: >>>>> I'm also wondering if providing some shell script examples of a >>>>> fault-tolerant script to handle archiving would be useful. >>>> I think it would. >>> My usual advice is to avoid having to write one if possible, >>> because it's more complex than it looks. What about recommending >>> existing solutions, such as walmgr from Skytools? >>> >>> Even better, what about including a default archiving tool, that >>> could be either another script in bin/ or rather an internal >>> command. The default would accept a location as argument, for >>> simple needs you mount a remote filesystem and there you go. If >>> you need something more complex, you still can provide it >>> yourself. >> In a green field I might argue for having an archvie_directory GUC >> instead of archive_command. As it stands, it might be a really good >> idea to provide a pg_archiveto executable which takes as arguments a >> directory path and the arguments passed to the archive script. With >> a little extra effort, the executable could check for some file >> which would specify what host and path should be writing archives >> there, to avoid problems with copied database directories >> accidentally writing to the same location as the source. >> >> Such an executable seems like minimal effort compared to the >> problems it would solve. >> >> If there's an existing tool with appropriate licensing which is >> sufficiently portable and reliable, all the better -- let's ship it >> and use that for our example archive_command. > Another thought I have here is to wonder whether we should change > something on the server side so that we don't NEED such a complicated > archive_command. I mean, copying a file to a directory somewhere is > not fundamentally a complex operation. Nor is using ssh to copy it to > another machine. The fact that archive_commands need to be so complex > seems like a usability defect. The consensus seems to be that just > using something like 'cp' for your archive command won't work out > well, but maybe instead of shipping a more complicated script we > should be trying to eliminate (or at least reduce) the need for a more > complicated script. > The problem is that the number of ways you might want to do things is quite large. For example, you might want to copy the archives to more than one place for safety reasons. I pretty much always set archive_command to a script which I can then customize to my heart's content, and it seems to work pretty well. Providing a simple example of such a script seems like it could be useful. cheers andrew
Robert Haas <robertmhaas@gmail.com> wrote: > maybe instead of shipping a more complicated script we should be > trying to eliminate (or at least reduce) the need for a more > complicated script. That was the intent of my pg_archiveto suggestion. I'll amend it (based on other comments) to allow for a URL as an alternative way to specify the directory. So your archive_command might be: 'pg_archiveto /var/pgsql/backup/wal/ %p %f' or: 'pg_archiveto http://backup-server/mydbserver/wal/ %p %f' or maybe: 'pg_archiveto /mnt/someremotedirectory/ %p %f' -Kevin
Andrew Dunstan <andrew@dunslane.net> wrote: > For example, you might want to copy the archives to more than one > place for safety reasons. We've never felt that the way to do that was to put the logic for it in the archive script -- we archive to a local directory and set up rsync tasks on cron to distribute it. Otherwise you might not archive to one target if the other is down, or might have trouble catching up with a target when it comes back from being down. -Kevin
On Fri, Sep 2, 2011 at 19:13, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Andrew Dunstan <andrew@dunslane.net> wrote: > >> For example, you might want to copy the archives to more than one >> place for safety reasons. > > We've never felt that the way to do that was to put the logic for it > in the archive script -- we archive to a local directory and set up > rsync tasks on cron to distribute it. Otherwise you might not > archive to one target if the other is down, or might have trouble > catching up with a target when it comes back from being down. Archiving it locally will give you a window of lost data if you crash. The point being - different people have different requirements, which is one thing our currently solution is very good at catering to - the downside being lots of work. I don't think dumbing down the system is a good idea - but shipping an example script probably is. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Robert Haas <robertmhaas@gmail.com> writes: > Another thought I have here is to wonder whether we should change > something on the server side so that we don't NEED such a complicated > archive_command. I mean, copying a file to a directory somewhere is > not fundamentally a complex operation. Nor is using ssh to copy it to > another machine. It is once you consider error handling and catering to N combinations of user requirements. I think the notion that we should get rid of archive_command in favor of something more hard-wired is sheer lunacy. We have a nicely decoupled arrangement for dealing with these issues now; why would we want to pull them into the server? Now, providing a more useful sample script is certainly reasonable. regards, tom lane
On Fri, Sep 2, 2011 at 3:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> Another thought I have here is to wonder whether we should change >> something on the server side so that we don't NEED such a complicated >> archive_command. I mean, copying a file to a directory somewhere is >> not fundamentally a complex operation. Nor is using ssh to copy it to >> another machine. > > It is once you consider error handling and catering to N combinations of > user requirements. > > I think the notion that we should get rid of archive_command in favor of > something more hard-wired is sheer lunacy. We have a nicely decoupled > arrangement for dealing with these issues now; why would we want to pull > them into the server? I wasn't really proposing to get rid of it, but I do wonder if there are some configuration parameters we could add somewhere that would make common cases easier without making really complex things impossible. > Now, providing a more useful sample script is certainly reasonable. Yep, so let's start with that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Tom Lane <tgl@sss.pgh.pa.us> wrote: > I think the notion that we should get rid of archive_command in > favor of something more hard-wired is sheer lunacy. It's a good thing nobody proposed that. -Kevin
Robert Haas <robertmhaas@gmail.com> writes: > I wasn't really proposing to get rid of it, but I do wonder if there > are some configuration parameters we could add somewhere that would > make common cases easier without making really complex things > impossible. I think the solution to that problem is to provide a default archive command that just does the very simple thing, namely moving the WAL file to some place given as parameter. Some “local” mount point. >> Now, providing a more useful sample script is certainly reasonable. Allow me to insist here, I don't think a sample is what we need to be providing our users. I think they deserve a default production grade implementation of a very simple archive command. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > (1) We're talking about a new /bin executable to do this which > could be referenced in an archive_command string or run from a > script called by archive_command, right? That, or an internal implementation. That would be a function in the backend that would be called when archive_command is set to some specific value, like for example test and cd are command lines referring not to some executable on the PATH but to some internal code in bash. But I know some people here will frown upon that idea. > (2) It should copy, not move, with protection against overwriting > an existing file. See, we need to provide a good production grade facility. I've never tried to do it myself, I'm just using walmgr to manage my archives. > (3) Maybe not in the initial version, but eventually it might be > nice to support URLs of some known protocols in addition to local > directories. I guess that if patches are provided in that direction it would be kind of hard to refuse integrating them :) > (4) Maybe not in the initial version, but eventually it might be > nice to support checking for an "owner" file of some sort in the > target directory, to help sort out problems with copied databases > writing to the same location as the source. Then we need to provide the associated restore command which must not be one "owner" here I guess… Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Dimitri Fontaine <dimitri@2ndQuadrant.fr> wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> (1) We're talking about a new /bin executable to do this which >> could be referenced in an archive_command string or run from a >> script called by archive_command, right? > > That, or an internal implementation. That would be a function in > the backend that would be called when archive_command is set to > some specific value, like for example test and cd are command > lines referring not to some executable on the PATH but to some > internal code in bash. > > But I know some people here will frown upon that idea. I think that would limit its usefulness. We're doing some things in our archive script (like sending the DBA team an email when an archive command fails) which might not be included in such a utility. (Or would it???) >> (2) It should copy, not move, with protection against >> overwriting an existing file. > > See, we need to provide a good production grade facility. I've > never tried to do it myself, I'm just using walmgr to manage my > archives. I'm not familiar with that product, so that doesn't mean much to me. Could you talk in terms of what features you would want? >> (4) Maybe not in the initial version, but eventually it might be >> nice to support checking for an "owner" file of some sort in the >> target directory, to help sort out problems with copied databases >> writing to the same location as the source. > > Then we need to provide the associated restore command which must > not be one "owner" here I guess* I'm not following you here. My reasoning is that the main reason I've seen given to avoid overwriting an existing file (and something we've run into in this shop a few times when people weren't paying proper attention), is that a database can be copied to another location on the same machine (say for a test or development database based on the production database) and the archive command in that copy might directly or indirectly point to the same archive directory as the original. You don't want the databases clobbering each other's WAL files -- you want an error here. The feature I'm proposing is one that would allow the original to continue without any errors, and generate an error on an attempt to archive there from the copy -- even if it tried to write the file first. A couple other things which would need a little thought regarding such a utility would be what to do about "cleaning" each WAL file (we use pg_clearxlogtail and others use pglesslog) and what to do about compression (we filter through gzip). There is a net performance win with running the WAL files through this filtering even before you look at disk space or network bandwidth issues. Maybe we could extract a subset of pg_standby or something to get all these features; not sure. -Kevin
Dimitri Fontaine <dimitri@2ndQuadrant.fr> wrote: > I think the solution to that problem is to provide a default > archive command that just does the very simple thing, namely > moving the WAL file to some place given as parameter. Some > *local* mount point. I think we've been mostly in agreement, but I have a few quibbles or clarifications to that: (1) We're talking about a new /bin executable to do this which could be referenced in an archive_command string or run from a script called by archive_command, right? (2) It should copy, not move, with protection against overwriting an existing file. (3) Maybe not in the initial version, but eventually it might be nice to support URLs of some known protocols in addition to local directories. (4) Maybe not in the initial version, but eventually it might be nice to support checking for an "owner" file of some sort in the target directory, to help sort out problems with copied databases writing to the same location as the source. -Kevin
On Sat, Sep 3, 2011 at 5:10 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > (2) It should copy, not move, with protection against overwriting > an existing file. I agree that basically archive_command should not overwrite an existing file. But if the size of existing file is less than 16MB, it should do that. Otherwise, that WAL file would be lost forever. I have another feature request; (5) Maybe not in the initial version, but eventually it might be nice to support calling posix_fadvise(POSIX_FADV_DONTNEED) after copying a WAL file. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
On Tue, Sep 6, 2011 at 10:11 PM, Fujii Masao <masao.fujii@gmail.com> wrote: > On Sat, Sep 3, 2011 at 5:10 AM, Kevin Grittner > <Kevin.Grittner@wicourts.gov> wrote: >> (2) It should copy, not move, with protection against overwriting >> an existing file. > > I agree that basically archive_command should not overwrite an existing file. > But if the size of existing file is less than 16MB, it should do that. > Otherwise, > that WAL file would be lost forever. > I think best practice in this case is that if you ever find an existing file with the same name already in place, you should error and investigate. We don't ship around partially completed WAL files, and finding an existing one probably means something went wrong. (Of course, we use rsync instead of copy/move, so we have some better guarantees about this). > I have another feature request; > (5) Maybe not in the initial version, but eventually it might be > nice to support calling posix_fadvise(POSIX_FADV_DONTNEED) > after copying a WAL file. > Can you go into more details on how you envision this working. I'm mostly curious because I think rsync might already support this, which would make it easy to incorporate. On a side note, seeing this thread hasn't died, I'd encourage everyone to take another look at OmniPITR, https://github.com/omniti-labs/omnipitr. It's postgresql licensed, solves a lot of the problems listed here, and I think makes for a good example for people who want to accomplish more advanced awl management goals. So far the biggest criticism we've gotten is that it wasn't written in python, for some of you that might be a plus though ;-) Robert Treat play: xzilla.net work: omniti.com
On Wed, Sep 7, 2011 at 11:53 PM, Robert Treat <rob@xzilla.net> wrote: > On Tue, Sep 6, 2011 at 10:11 PM, Fujii Masao <masao.fujii@gmail.com> wrote: >> I agree that basically archive_command should not overwrite an existing file. >> But if the size of existing file is less than 16MB, it should do that. >> Otherwise, >> that WAL file would be lost forever. > > I think best practice in this case is that if you ever find an > existing file with the same name already in place, you should error > and investigate. We don't ship around partially completed WAL files, > and finding an existing one probably means something went wrong. (Of > course, we use rsync instead of copy/move, so we have some better > guarantees about this). That's an option. But I don't think that finding an existing file is so serious problem. The most common cases which cause a partially-filled archived file are; 1. The server crashes while WAL file is being archived, and then the server restarts. In this case, the restarted serverwould find partially-filled archived file. 2. In replication environment, the master crashes while WAL file is being archived, and then a failover happens. In thiscase, new master would find partially-filled archived file. In these cases, I don't think it's so unsafe to overwrite an existing file. OTOH, the practice you explained might fill up an archive area and pg_xlog directory and then cause a PANIC error. Such a PANIC error is more serious thing at least for me. So I'd like to overwrite an exiting file when its size is not 16MB. >> I have another feature request; >> (5) Maybe not in the initial version, but eventually it might be >> nice to support calling posix_fadvise(POSIX_FADV_DONTNEED) >> after copying a WAL file. >> > > Can you go into more details on how you envision this working. I'm > mostly curious because I think rsync might already support this, which > would make it easy to incorporate. I'm expecting that the executable is written in C, it calls posix_fadvice against the file descriptor created when opening the WAL file in pg_xlog directory, just before closing that descriptor. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
On Thu, Sep 8, 2011 at 7:05 AM, Fujii Masao <masao.fujii@gmail.com> wrote: > On Wed, Sep 7, 2011 at 11:53 PM, Robert Treat <rob@xzilla.net> wrote: >> On Tue, Sep 6, 2011 at 10:11 PM, Fujii Masao <masao.fujii@gmail.com> wrote: >>> I agree that basically archive_command should not overwrite an existing file. >>> But if the size of existing file is less than 16MB, it should do that. >>> Otherwise, >>> that WAL file would be lost forever. >> >> I think best practice in this case is that if you ever find an >> existing file with the same name already in place, you should error >> and investigate. We don't ship around partially completed WAL files, >> and finding an existing one probably means something went wrong. (Of >> course, we use rsync instead of copy/move, so we have some better >> guarantees about this). > > That's an option. But I don't think that finding an existing file is so serious > problem. The recommendation should be that the archived files are never overwritten because that prevents a huge range of data loss bugs and kills them stone dead. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Thu, Sep 8, 2011 at 3:26 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > On Thu, Sep 8, 2011 at 7:05 AM, Fujii Masao <masao.fujii@gmail.com> wrote: >> That's an option. But I don't think that finding an existing file is so serious >> problem. > > The recommendation should be that the archived files are never > overwritten because that prevents a huge range of data loss bugs and > kills them stone dead. I'm OK with that default behavior of the executable. It's helpful if the executable supports overwrite-if-filesize-is-not-16MB option. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
On Thu, Sep 8, 2011 at 2:05 AM, Fujii Masao <masao.fujii@gmail.com> wrote: > That's an option. But I don't think that finding an existing file is so serious > problem. The most common cases which cause a partially-filled archived > file are; > > 1. The server crashes while WAL file is being archived, and then the server > restarts. In this case, the restarted server would find partially-filled > archived file. > > 2. In replication environment, the master crashes while WAL file is being > archived, and then a failover happens. In this case, new master would > find partially-filled archived file. > > In these cases, I don't think it's so unsafe to overwrite an existing file. Personally, I think both of these show examples of why PG should be looking hard at either providing a simple robust local directory based archive_command, or very seriously pointing users at properly written tools like omniptr, or ptrtools, walmgr, etc... Neither of those cases should ever happen. If you're copying a file into the archive, and making it appear non-atomically in your archive, your doing something wrong. Period. No excuses. a. -- Aidan Van Dyk Create like a god, aidan@highrise.ca command like a king, http://www.highrise.ca/ work like a slave.
Aidan Van Dyk <aidan@highrise.ca> wrote: > If you're copying a file into the archive, and making it appear > non-atomically in your archive, your doing something wrong. > > Period. > > No excuses. +1 -Kevin
On Sep8, 2011, at 15:09 , Aidan Van Dyk wrote: > Personally, I think both of these show examples of why PG should be > looking hard at either providing a simple robust local directory based > archive_command, or very seriously pointing users at properly written > tools like omniptr, or ptrtools, walmgr, etc... > > Neither of those cases should ever happen. If you're copying a file > into the archive, and making it appear non-atomically in your archive, > your doing something wrong. +1000. Archiving WAL should be done by copying to a temp file and moving it into place. Before returning success, one should probably also do the fsync incantations the linux kernel guys argued are necessary to prevent the file from appearing empty if the machine crashes shortly after the move. (Yeah, they fixed that after enough people complained, but the fact that they even went as far as arguing their behaviour is correct according to POSIX makes me uneasy...) It'd be very cool if we shipped a tool that did that correctly (pg_walcopy maybe?) on all supported platforms. best regards, Florian Pflug
On Fri, Sep 09, 2011 at 08:59:43PM +0200, Florian Pflug wrote: > Archiving WAL should be done by copying to a temp file and moving it > into place. Before returning success, one should probably also do the > fsync incantations the linux kernel guys argued are necessary to prevent > the file from appearing empty if the machine crashes shortly after the > move. (Yeah, they fixed that after enough people complained, but the fact > that they even went as far as arguing their behaviour is correct according > to POSIX makes me uneasy...) Well, they fixed it for ext2/3/4 but that doesn't change the fact that most other filesystems don't provide the same guarentees. If you want to be sure the file contents hit the disk, you need to do an fsync. (If you suggested to people we could add a new WAL sync method that wrote the data to disk without fsync and renamed it over an existing file and assured them that the data would survive a crash, they'd say you're nuts). > It'd be very cool if we shipped a tool that did that correctly (pg_walcopy > maybe?) on all supported platforms. It's hard enough to get right that shipping a tool that works properly is eminently sensible. If only to demonstrate how it should be done. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer
On Friday, September 09, 2011 08:59:43 PM Florian Pflug wrote: > On Sep8, 2011, at 15:09 , Aidan Van Dyk wrote: > > Personally, I think both of these show examples of why PG should be > > looking hard at either providing a simple robust local directory based > > archive_command, or very seriously pointing users at properly written > > tools like omniptr, or ptrtools, walmgr, etc... > > > > Neither of those cases should ever happen. If you're copying a file > > into the archive, and making it appear non-atomically in your archive, > > your doing something wrong. > > +1000. > > Archiving WAL should be done by copying to a temp file and moving it > into place. Before returning success, one should probably also do the > fsync incantations the linux kernel guys argued are necessary to prevent > the file from appearing empty if the machine crashes shortly after the > move. (Yeah, they fixed that after enough people complained, but the fact > that they even went as far as arguing their behaviour is correct according > to POSIX makes me uneasy...) The only problem being that its only fixed with certain mount options on a certain filesystem (ext3, ext4, data=ordered). Every other filesystem (like e.g. XFS) still does it that way. And did it for at least a decade. It makes me just as uneasy that so few people knew about that - preexisting! - problem... > It'd be very cool if we shipped a tool that did that correctly (pg_walcopy > maybe?) on all supported platforms. +1