Thread

1. Command to prune archive at restartpoints

   Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2010-03-17T09:37:04Z

   One awkward omission in the new built-in standby mode, mainly used for
   streaming replication, is that there is no easy way to delete old
   archived files like you do with the %r parameter to restore_command.
   This was discussed at
   http://archives.postgresql.org/pgsql-hackers/2010-02/msg01003.php, among
   other things.
   
   Per discussion, attached patch adds a new restartpoint_command option to
   recovery.conf. That's an external shell command just like
   recovery_end_command that's executed at every restartpoint. You can use
   the %r parameter to pass the filename of the oldest WAL file that needs
   to be retained.
   
   While developing this I noticed that %r in recovery_end_command is not
   working correctly:
   
   LOG:  redo done at 0/14000C10
   LOG:  last completed transaction was at log time 2000-01-01
   02:21:08.816445+02
   cp: cannot stat
   `/home/hlinnaka/pgsql.cvshead/walarchive/000000010000000000000014': No
   such file or directory
   cp: cannot stat
   `/home/hlinnaka/pgsql.cvshead/walarchive/00000002.history': No such file
   or directory
   LOG:  selected new timeline ID: 2
   cp: cannot stat
   `/home/hlinnaka/pgsql.cvshead/walarchive/00000001.history': No such file
   or directory
   LOG:  archive recovery complete
   LOG:  checkpoint starting: end-of-recovery immediate wait
   LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log
   file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s,
   total=0.003 s
   LOG:  executing recovery_end_command "echo recovery_end_command %r"
   recovery_end_command 000000000000000000000000
   LOG:  database system is ready to accept connections
   LOG:  autovacuum launcher started
   
   Note how %r is always expanded to 000000000000000000000000. That's
   because %r is expanded only when InRedo is true, which makes sense for
   restore_command where that piece of code was copy-pasted from, but it's
   never true anymore when recovery_end_command is run. The attached patch
   fixes that too.
   
   Barring objections, I will commit this later today.
   
   -- 
     Heikki Linnakangas
     EnterpriseDB   http://www.enterprisedb.com
   

2. Re: Command to prune archive at restartpoints

   Greg Stark <gsstark@mit.edu> — 2010-03-17T10:01:44Z

   On Wed, Mar 17, 2010 at 9:37 AM, Heikki Linnakangas
   <heikki.linnakangas@enterprisedb.com> wrote:
   > One awkward omission in the new built-in standby mode, mainly used for
   > streaming replication, is that there is no easy way to delete old
   > archived files like you do with the %r parameter to restore_command.
   
   I'm still finding this kind of narrow-minded. I'm picturing a system
   with multiple replicas -- obvious no one replica can take it upon
   itself to delete archived log files based only on its own
   restartpoint. And besides, if you're using the archived log files for
   backups you also need to take into account the backup policy and only
   delete files that aren't needed for a consistent backup and aren't
   needed for the replica.
   
   What we need is a program which can take all this information from all
   your slaves and backup labels into account and implement your backup
   policies. It probably won't exist in time for the release and in any
   case doesn't really have to ship with Postgres. There might even be
   more than one.
   
   But do we have all the information that such a program would need? Is
   there a way to connect to a replica and ask it what the restart point
   is? I suppose with this new command you could always just make it a
   command which wakes up this demon and sends it the restart point and
   the replica id and it can update its internal state and recalculate
   what archives are needed. It is a bit nerve-wracking that it's
   dependent on its internal state remembering the restart points it's
   been given though.
   
   
   
   
   -- 
   greg
   
   

3. Re: Command to prune archive at restartpoints

   Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2010-03-17T10:14:40Z

   Greg Stark wrote:
   > On Wed, Mar 17, 2010 at 9:37 AM, Heikki Linnakangas
   > <heikki.linnakangas@enterprisedb.com> wrote:
   >> One awkward omission in the new built-in standby mode, mainly used for
   >> streaming replication, is that there is no easy way to delete old
   >> archived files like you do with the %r parameter to restore_command.
   > 
   > I'm still finding this kind of narrow-minded. I'm picturing a system
   > with multiple replicas -- obvious no one replica can take it upon
   > itself to delete archived log files based only on its own
   > restartpoint. And besides, if you're using the archived log files for
   > backups you also need to take into account the backup policy and only
   > delete files that aren't needed for a consistent backup and aren't
   > needed for the replica.
   
   That's why we provide options that take any shell command you want,
   rather than e.g a path to an archive directory that's pruned automatically.
   
   For example, if you have multiple standbys sharing one archive, you
   could do something like this:
   
   In each standby, have a restartpoint_command along the lines of:
   "echo %r > <archivedirectory>/standby1_location; archive_cleanup.sh"
   
   Where '1' is different for every standby
   
   and in archive_cleanup.sh, scan through all the standbyX_location files,
   take the minimum, and delete all files smaller than that.
   
   You'll need some care with locking etc., but the point is that the
   current hooks allow you to implement complex setups like that.
   
   > What we need is a program which can take all this information from all
   > your slaves and backup labels into account and implement your backup
   > policies. It probably won't exist in time for the release and in any
   > case doesn't really have to ship with Postgres. There might even be
   > more than one.
   
   I guess I just described such a program :-). Yeah, I'd imagine that to
   become part of toolkits like skytools.
   
   > But do we have all the information that such a program would need? Is
   > there a way to connect to a replica and ask it what the restart point
   > is?
   
   Hmm, Greg Smith opened a thread on exposing the fields in the control
   file as user-defined functions. IIRC last restartpoint location was the
   piece of information that triggered the discussion this time. Perhaps we
   should indeed add a function to expose that in 9.0.
   
   -- 
     Heikki Linnakangas
     EnterpriseDB   http://www.enterprisedb.com
   
   

4. Re: Command to prune archive at restartpoints

   Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2010-03-18T09:19:21Z

   Committed.
   
   Heikki Linnakangas wrote:
   > One awkward omission in the new built-in standby mode, mainly used for
   > streaming replication, is that there is no easy way to delete old
   > archived files like you do with the %r parameter to restore_command.
   > This was discussed at
   > http://archives.postgresql.org/pgsql-hackers/2010-02/msg01003.php, among
   > other things.
   > 
   > Per discussion, attached patch adds a new restartpoint_command option to
   > recovery.conf. That's an external shell command just like
   > recovery_end_command that's executed at every restartpoint. You can use
   > the %r parameter to pass the filename of the oldest WAL file that needs
   > to be retained.
   > 
   > While developing this I noticed that %r in recovery_end_command is not
   > working correctly:
   > 
   > LOG:  redo done at 0/14000C10
   > LOG:  last completed transaction was at log time 2000-01-01
   > 02:21:08.816445+02
   > cp: cannot stat
   > `/home/hlinnaka/pgsql.cvshead/walarchive/000000010000000000000014': No
   > such file or directory
   > cp: cannot stat
   > `/home/hlinnaka/pgsql.cvshead/walarchive/00000002.history': No such file
   > or directory
   > LOG:  selected new timeline ID: 2
   > cp: cannot stat
   > `/home/hlinnaka/pgsql.cvshead/walarchive/00000001.history': No such file
   > or directory
   > LOG:  archive recovery complete
   > LOG:  checkpoint starting: end-of-recovery immediate wait
   > LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log
   > file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s,
   > total=0.003 s
   > LOG:  executing recovery_end_command "echo recovery_end_command %r"
   > recovery_end_command 000000000000000000000000
   > LOG:  database system is ready to accept connections
   > LOG:  autovacuum launcher started
   > 
   > Note how %r is always expanded to 000000000000000000000000. That's
   > because %r is expanded only when InRedo is true, which makes sense for
   > restore_command where that piece of code was copy-pasted from, but it's
   > never true anymore when recovery_end_command is run. The attached patch
   > fixes that too.
   > 
   > Barring objections, I will commit this later today.
   > 
   > 
   
   
   -- 
     Heikki Linnakangas
     EnterpriseDB   http://www.enterprisedb.com
   
   

5. Re: Command to prune archive at restartpoints

   Simon Riggs <simon@2ndquadrant.com> — 2010-03-18T09:43:24Z

   On Wed, 2010-03-17 at 11:37 +0200, Heikki Linnakangas wrote:
   
   > One awkward omission in the new built-in standby mode, mainly used for
   > streaming replication, is that there is no easy way to delete old
   > archived files like you do with the %r parameter to restore_command.
   > This was discussed at
   > http://archives.postgresql.org/pgsql-hackers/2010-02/msg01003.php, among
   > other things.
   
   ...
   
   > Barring objections, I will commit this later today.
   
   Would it be better to call this "archive_cleanup_command"? That might
   help people understand the need for and the use of this parameter.
   
   -- 
    Simon Riggs           www.2ndQuadrant.com
   
   
   

6. Re: Command to prune archive at restartpoints

   Greg Stark <gsstark@mit.edu> — 2010-03-22T15:58:17Z

   On Thu, Mar 18, 2010 at 9:43 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
   > On Wed, 2010-03-17 at 11:37 +0200, Heikki Linnakangas wrote:
   >
   >> One awkward omission in the new built-in standby mode, mainly used for
   >> streaming replication, is that there is no easy way to delete old
   >> archived files like you do with the %r parameter to restore_command.
   >
   > Would it be better to call this "archive_cleanup_command"? That might
   > help people understand the need for and the use of this parameter.
   
   This is bikeshedding but fwiw I like Simon's suggestion.
   
   -- 
   greg
   
   

7. Re: Command to prune archive at restartpoints

   Robert Haas <robertmhaas@gmail.com> — 2010-06-08T21:17:34Z

   On Mon, Mar 22, 2010 at 11:58 AM, Greg Stark <gsstark@mit.edu> wrote:
   > On Thu, Mar 18, 2010 at 9:43 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
   >> On Wed, 2010-03-17 at 11:37 +0200, Heikki Linnakangas wrote:
   >>
   >>> One awkward omission in the new built-in standby mode, mainly used for
   >>> streaming replication, is that there is no easy way to delete old
   >>> archived files like you do with the %r parameter to restore_command.
   >>
   >> Would it be better to call this "archive_cleanup_command"? That might
   >> help people understand the need for and the use of this parameter.
   >
   > This is bikeshedding but fwiw I like Simon's suggestion.
   
   So, this thread is hanging out on our list of open items for 9.0.  My
   personal opinion on it is that I don't really care much one way or the
   other.  archive_cleanup_command does seem easier to understand, but
   restartpoint_command has the advantage of describing exactly when it
   gets run from a technical perspective, which might be a good thing,
   too.  Since nobody's felt motivated to do anything about this for two
   and a half months and we've now been through two betas with it the way
   it is, I'm inclined to say we should just leave it alone.  On the
   other hand, both of the people who voted in favor of changing it are
   committers, and if one of them feels like putting in the effort to
   change it, it won't bother me much, except that I feel it should get
   done RSN.  But one way or the other we need to make a decision and get
   this off the list.
   
   -- 
   Robert Haas
   EnterpriseDB: http://www.enterprisedb.com
   The Enterprise Postgres Company
   
   

8. Re: Command to prune archive at restartpoints

   Simon Riggs <simon@2ndquadrant.com> — 2010-06-08T21:32:10Z

   On Tue, 2010-06-08 at 17:17 -0400, Robert Haas wrote:
   > On Mon, Mar 22, 2010 at 11:58 AM, Greg Stark <gsstark@mit.edu> wrote:
   > > On Thu, Mar 18, 2010 at 9:43 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
   > >> On Wed, 2010-03-17 at 11:37 +0200, Heikki Linnakangas wrote:
   > >>
   > >>> One awkward omission in the new built-in standby mode, mainly used for
   > >>> streaming replication, is that there is no easy way to delete old
   > >>> archived files like you do with the %r parameter to restore_command.
   > >>
   > >> Would it be better to call this "archive_cleanup_command"? That might
   > >> help people understand the need for and the use of this parameter.
   > >
   > > This is bikeshedding but fwiw I like Simon's suggestion.
   > 
   > So, this thread is hanging out on our list of open items for 9.0.  My
   > personal opinion on it is that I don't really care much one way or the
   > other.  archive_cleanup_command does seem easier to understand, but
   > restartpoint_command has the advantage of describing exactly when it
   > gets run from a technical perspective, which might be a good thing,
   > too.  Since nobody's felt motivated to do anything about this for two
   > and a half months and we've now been through two betas with it the way
   > it is, I'm inclined to say we should just leave it alone.  On the
   > other hand, both of the people who voted in favor of changing it are
   > committers, and if one of them feels like putting in the effort to
   > change it, it won't bother me much, except that I feel it should get
   > done RSN.  But one way or the other we need to make a decision and get
   > this off the list.
   
   Yes, restartpoint_command is exactly correct, and I do understand it; I
   just don't think anyone else will. If there's another use for a
   restartpoint_command other than for clearing up an archive, then it
   would be sufficient to destroy the name change idea. 
   
   -- 
    Simon Riggs           www.2ndQuadrant.com
   
   
   

9. Re: Command to prune archive at restartpoints

   Andrew Dunstan <andrew@dunslane.net> — 2010-06-08T22:30:38Z

   
   Robert Haas wrote:
   > On Mon, Mar 22, 2010 at 11:58 AM, Greg Stark <gsstark@mit.edu> wrote:
   >   
   >> On Thu, Mar 18, 2010 at 9:43 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
   >>     
   >>> On Wed, 2010-03-17 at 11:37 +0200, Heikki Linnakangas wrote:
   >>>
   >>>       
   >>>> One awkward omission in the new built-in standby mode, mainly used for
   >>>> streaming replication, is that there is no easy way to delete old
   >>>> archived files like you do with the %r parameter to restore_command.
   >>>>         
   >>> Would it be better to call this "archive_cleanup_command"? That might
   >>> help people understand the need for and the use of this parameter.
   >>>       
   >> This is bikeshedding but fwiw I like Simon's suggestion.
   >>     
   >
   > So, this thread is hanging out on our list of open items for 9.0.  My
   > personal opinion on it is that I don't really care much one way or the
   > other.  archive_cleanup_command does seem easier to understand, but
   > restartpoint_command has the advantage of describing exactly when it
   > gets run from a technical perspective, which might be a good thing,
   > too.  Since nobody's felt motivated to do anything about this for two
   > and a half months and we've now been through two betas with it the way
   > it is, I'm inclined to say we should just leave it alone.  On the
   > other hand, both of the people who voted in favor of changing it are
   > committers, and if one of them feels like putting in the effort to
   > change it, it won't bother me much, except that I feel it should get
   > done RSN.  But one way or the other we need to make a decision and get
   > this off the list.
   >
   >   
   
   I prefer archive_cleanup_command. We should name things after their 
   principal function, not an implementation detail, IMNSHO.
   
   More importantly, we should include an example in the docs. I created 
   one the other day  when this was actually bothering me a bit (see 
   <http://people.planetpostgresql.org/andrew/index.php?/archives/85-Keeping-a-hot-standby-log-archive-clean.html>). 
   That seemed to work ok, but maybe it's too long, and maybe people would 
   prefer a shell script to perl.
   
   cheers
   
   andrew
   
   

10. Re: Command to prune archive at restartpoints

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-06-08T22:45:19Z

    Andrew Dunstan <andrew@dunslane.net> writes:
    > I prefer archive_cleanup_command. We should name things after their 
    > principal function, not an implementation detail, IMNSHO.
    
    Weak preference for archive_cleanup_command here.
    
    > More importantly, we should include an example in the docs. I created 
    > one the other day  when this was actually bothering me a bit (see 
    > <http://people.planetpostgresql.org/andrew/index.php?/archives/85-Keeping-a-hot-standby-log-archive-clean.html>). 
    > That seemed to work ok, but maybe it's too long, and maybe people would 
    > prefer a shell script to perl.
    
    Short is good.  Maybe you could remove the logging stuff from the
    example.
    
    As for the language choice, my first thought is +1 for perl over shell,
    mainly because it might be directly useful to people on Windows while
    shell never would be.  On the other hand, if it's possible to do a
    useful one-liner in shell then let's do it that way.
    
    			regards, tom lane
    
    

11. Re: Command to prune archive at restartpoints

    Andrew Dunstan <andrew@dunslane.net> — 2010-06-08T23:07:10Z

    
    Tom Lane wrote:
    > As for the language choice, my first thought is +1 for perl over shell,
    > mainly because it might be directly useful to people on Windows while
    > shell never would be.  On the other hand, if it's possible to do a
    > useful one-liner in shell then let's do it that way.
    >   
    
    I don't think it is, reasonably. But here is fairly minimal version that 
    might suit the docs:
    
        use strict;
        my ($dir, $num) = @ARGV;
        foreach my $file (glob("$dir/*"))
        {
                my $name = basename($file);
                unlink $file if (-f $file && $name =~ /^[0-9A-Z]{24}$/ && $name lt $num);
        }
          
    
    
    
    cheers
    
    andrew
    
    

12. Re: Command to prune archive at restartpoints

    Robert Haas <robertmhaas@gmail.com> — 2010-06-09T00:47:20Z

    On Tue, Jun 8, 2010 at 6:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    > Andrew Dunstan <andrew@dunslane.net> writes:
    >> I prefer archive_cleanup_command. We should name things after their
    >> principal function, not an implementation detail, IMNSHO.
    >
    > Weak preference for archive_cleanup_command here.
    
    OK, sounds like we have consensus on that.  Who wants to do it?
    
    -- 
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
    
    

13. Re: Command to prune archive at restartpoints

    Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> — 2010-06-09T01:45:20Z

    Robert Haas <robertmhaas@gmail.com> wrote:
    
    > On Tue, Jun 8, 2010 at 6:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    > > Andrew Dunstan <andrew@dunslane.net> writes:
    > >> I prefer archive_cleanup_command. We should name things after their
    > >> principal function, not an implementation detail, IMNSHO.
    > >
    > > Weak preference for archive_cleanup_command here.
    > 
    > OK, sounds like we have consensus on that.  Who wants to do it?
    
    Do we just need to replace all of them? If so, patch attached.
    I replaced 3 terms: recovery_end_command, recovery-end-command,
    and recoveryEndCommand.
    
    
    Regards,
    ---
    Takahiro Itagaki
    NTT Open Source Software Center
    
    

14. Re: Command to prune archive at restartpoints

    Robert Haas <robertmhaas@gmail.com> — 2010-06-09T01:51:59Z

    On Tue, Jun 8, 2010 at 9:45 PM, Takahiro Itagaki
    <itagaki.takahiro@oss.ntt.co.jp> wrote:
    > Robert Haas <robertmhaas@gmail.com> wrote:
    >
    >> On Tue, Jun 8, 2010 at 6:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    >> > Andrew Dunstan <andrew@dunslane.net> writes:
    >> >> I prefer archive_cleanup_command. We should name things after their
    >> >> principal function, not an implementation detail, IMNSHO.
    >> >
    >> > Weak preference for archive_cleanup_command here.
    >>
    >> OK, sounds like we have consensus on that.  Who wants to do it?
    >
    > Do we just need to replace all of them? If so, patch attached.
    > I replaced 3 terms: recovery_end_command, recovery-end-command,
    > and recoveryEndCommand.
    
    I think we're replacing restartpoint_command, not recovery_end_command.
    
    -- 
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
    
    

15. Re: Command to prune archive at restartpoints

    Fujii Masao <masao.fujii@gmail.com> — 2010-06-09T02:00:41Z

    On Wed, Jun 9, 2010 at 10:45 AM, Takahiro Itagaki
    <itagaki.takahiro@oss.ntt.co.jp> wrote:
    >
    > Robert Haas <robertmhaas@gmail.com> wrote:
    >
    >> On Tue, Jun 8, 2010 at 6:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    >> > Andrew Dunstan <andrew@dunslane.net> writes:
    >> >> I prefer archive_cleanup_command. We should name things after their
    >> >> principal function, not an implementation detail, IMNSHO.
    >> >
    >> > Weak preference for archive_cleanup_command here.
    >>
    >> OK, sounds like we have consensus on that.  Who wants to do it?
    >
    > Do we just need to replace all of them? If so, patch attached.
    > I replaced 3 terms: recovery_end_command, recovery-end-command,
    > and recoveryEndCommand.
    
    s/recovery_end_command/restartpoint_command?
    
    I prefer restartpoint_command over archive_cleanup_command because
    not only restartpoint_command but also recovery_end_command is used
    for archive cleanup.
    
    Regards,
    
    -- 
    Fujii Masao
    NIPPON TELEGRAPH AND TELEPHONE CORPORATION
    NTT Open Source Software Center
    
    

16. Re: Command to prune archive at restartpoints

    Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> — 2010-06-09T02:18:00Z

    Robert Haas <robertmhaas@gmail.com> wrote:
    > I think we're replacing restartpoint_command, not recovery_end_command.
    
    Ah, sorry. I did the same replacement for restartpoint_command
    in _, -, and camel case words.
    
    BTW, should we also have a release note for the command?
    I added a simple description for it in the patch.
    
    Regards,
    ---
    Takahiro Itagaki
    NTT Open Source Software Center
    
    

17. Re: Command to prune archive at restartpoints

    Robert Haas <robertmhaas@gmail.com> — 2010-06-09T02:58:32Z

    On Tue, Jun 8, 2010 at 10:18 PM, Takahiro Itagaki
    <itagaki.takahiro@oss.ntt.co.jp> wrote:
    >
    > Robert Haas <robertmhaas@gmail.com> wrote:
    >> I think we're replacing restartpoint_command, not recovery_end_command.
    >
    > Ah, sorry. I did the same replacement for restartpoint_command
    > in _, -, and camel case words.
    
    Gah.  Perhaps one of these days we will stop spelling every identifier
    in multiple different ways.
    
    > BTW, should we also have a release note for the command?
    > I added a simple description for it in the patch.
    
    Yeah, it should be definitely mentioned in the release notes somewhere, I think.
    
    -- 
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
    
    

18. Re: Command to prune archive at restartpoints

    Simon Riggs <simon@2ndquadrant.com> — 2010-06-09T07:19:29Z

    On Wed, 2010-06-09 at 11:18 +0900, Takahiro Itagaki wrote:
    > Robert Haas <robertmhaas@gmail.com> wrote:
    > > I think we're replacing restartpoint_command, not recovery_end_command.
    > 
    > Ah, sorry. I did the same replacement for restartpoint_command
    > in _, -, and camel case words.
    > 
    > BTW, should we also have a release note for the command?
    > I added a simple description for it in the patch.
    
    I don't think so, its not a separate feature. It's a change as part of
    SR.
    
    -- 
     Simon Riggs           www.2ndQuadrant.com
    
    
    

19. Re: Command to prune archive at restartpoints

    Simon Riggs <simon@2ndquadrant.com> — 2010-06-09T07:21:08Z

    On Tue, 2010-06-08 at 18:30 -0400, Andrew Dunstan wrote:
    
    > I prefer archive_cleanup_command. We should name things after their 
    > principal function, not an implementation detail, IMNSHO.
    > 
    > More importantly, we should include an example in the docs. I created 
    > one the other day  when this was actually bothering me a bit (see 
    > <http://people.planetpostgresql.org/andrew/index.php?/archives/85-Keeping-a-hot-standby-log-archive-clean.html>). 
    > That seemed to work ok, but maybe it's too long, and maybe people would 
    > prefer a shell script to perl.
    
    I submitted a patch to make the command "pg_standby -a %r"
    
    That's a more portable solution, ISTM.
    
    I'll commit that and fix the docs.
    
    -- 
     Simon Riggs           www.2ndQuadrant.com
    
    
    

20. Re: Command to prune archive at restartpoints

    Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2010-06-10T07:18:08Z

    On 09/06/10 10:21, Simon Riggs wrote:
    > On Tue, 2010-06-08 at 18:30 -0400, Andrew Dunstan wrote:
    >
    >> I prefer archive_cleanup_command. We should name things after their
    >> principal function, not an implementation detail, IMNSHO.
    >>
    >> More importantly, we should include an example in the docs. I created
    >> one the other day  when this was actually bothering me a bit (see
    >> <http://people.planetpostgresql.org/andrew/index.php?/archives/85-Keeping-a-hot-standby-log-archive-clean.html>).
    >> That seemed to work ok, but maybe it's too long, and maybe people would
    >> prefer a shell script to perl.
    >
    > I submitted a patch to make the command "pg_standby -a %r"
    >
    > That's a more portable solution, ISTM.
    >
    > I'll commit that and fix the docs.
    
    Huh, wait. There's no -a option in pg_standby, so I presume you're 
    planning to add that too. I don't like confusing pg_standby into this, 
    the docs are currently quite clear that if you want to use the built-in 
    standby mode, you can't use pg_standby, and this would muddy the waters.
    
    Maybe we could add a new pg_cleanuparchive binary, but we'll need some 
    discussion...
    
    -- 
       Heikki Linnakangas
       EnterpriseDB   http://www.enterprisedb.com
    
    

21. Re: Command to prune archive at restartpoints

    Simon Riggs <simon@2ndquadrant.com> — 2010-06-10T07:28:25Z

    On Thu, 2010-06-10 at 10:18 +0300, Heikki Linnakangas wrote:
    > On 09/06/10 10:21, Simon Riggs wrote:
    > > On Tue, 2010-06-08 at 18:30 -0400, Andrew Dunstan wrote:
    > >
    > >> I prefer archive_cleanup_command. We should name things after their
    > >> principal function, not an implementation detail, IMNSHO.
    > >>
    > >> More importantly, we should include an example in the docs. I created
    > >> one the other day  when this was actually bothering me a bit (see
    > >> <http://people.planetpostgresql.org/andrew/index.php?/archives/85-Keeping-a-hot-standby-log-archive-clean.html>).
    > >> That seemed to work ok, but maybe it's too long, and maybe people would
    > >> prefer a shell script to perl.
    > >
    > > I submitted a patch to make the command "pg_standby -a %r"
    > >
    > > That's a more portable solution, ISTM.
    > >
    > > I'll commit that and fix the docs.
    > 
    > Huh, wait. There's no -a option in pg_standby, so I presume you're 
    > planning to add that too. I don't like confusing pg_standby into this, 
    > the docs are currently quite clear that if you want to use the built-in 
    > standby mode, you can't use pg_standby, and this would muddy the waters.
    
    It won't kill us to change that sentence. "pg_standby is only used now
    within the cleanup command" etc
    
    pg_standby already contains the exact logic we need here. Having two
    sets of code for the same thing isn't how we do things.
    
    > Maybe we could add a new pg_cleanuparchive binary, but we'll need some 
    > discussion...
    
    Which will go nowhere, as we both already know.
    
    -- 
     Simon Riggs           www.2ndQuadrant.com
    
    
    

22. Re: Command to prune archive at restartpoints

    Robert Haas <robertmhaas@gmail.com> — 2010-06-10T14:17:20Z

    On Thu, Jun 10, 2010 at 3:28 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
    > On Thu, 2010-06-10 at 10:18 +0300, Heikki Linnakangas wrote:
    >> On 09/06/10 10:21, Simon Riggs wrote:
    >> > On Tue, 2010-06-08 at 18:30 -0400, Andrew Dunstan wrote:
    >> >
    >> >> I prefer archive_cleanup_command. We should name things after their
    >> >> principal function, not an implementation detail, IMNSHO.
    >> >>
    >> >> More importantly, we should include an example in the docs. I created
    >> >> one the other day  when this was actually bothering me a bit (see
    >> >> <http://people.planetpostgresql.org/andrew/index.php?/archives/85-Keeping-a-hot-standby-log-archive-clean.html>).
    >> >> That seemed to work ok, but maybe it's too long, and maybe people would
    >> >> prefer a shell script to perl.
    >> >
    >> > I submitted a patch to make the command "pg_standby -a %r"
    >> >
    >> > That's a more portable solution, ISTM.
    >> >
    >> > I'll commit that and fix the docs.
    >>
    >> Huh, wait. There's no -a option in pg_standby, so I presume you're
    >> planning to add that too. I don't like confusing pg_standby into this,
    >> the docs are currently quite clear that if you want to use the built-in
    >> standby mode, you can't use pg_standby, and this would muddy the waters.
    >
    > It won't kill us to change that sentence. "pg_standby is only used now
    > within the cleanup command" etc
    >
    > pg_standby already contains the exact logic we need here. Having two
    > sets of code for the same thing isn't how we do things.
    >
    >> Maybe we could add a new pg_cleanuparchive binary, but we'll need some
    >> discussion...
    >
    > Which will go nowhere, as we both already know.
    
    I have a feeling that I may be poking my nose into an incipient
    shouting match, but FWIW I agree with Heikki that it would be
    preferable to keep this separate from pg_standby.  Considering that
    Andrew wrote this in 24 lines of Perl code (one-third of which are
    basically just there for logging purposes), I'm not that worried about
    code duplication, unless what we actually need is significantly more
    complicated.
    
    -- 
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
    
    

23. Re: Command to prune archive at restartpoints

    Andrew Dunstan <andrew@dunslane.net> — 2010-06-10T14:38:20Z

    
    Robert Haas wrote:
    >> It won't kill us to change that sentence. "pg_standby is only used now
    >> within the cleanup command" etc
    >>
    >> pg_standby already contains the exact logic we need here. Having two
    >> sets of code for the same thing isn't how we do things.
    >>     
    
    Well, we could factor out that part of the code so it could be used in 
    two binaries. But ...
    
    >>> Maybe we could add a new pg_cleanuparchive binary, but we'll need some
    >>> discussion...
    >>>       
    >> Which will go nowhere, as we both already know.
    >>     
    >
    > I have a feeling that I may be poking my nose into an incipient
    > shouting match, but FWIW I agree with Heikki that it would be
    > preferable to keep this separate from pg_standby.  Considering that
    > Andrew wrote this in 24 lines of Perl code (one-third of which are
    > basically just there for logging purposes), I'm not that worried about
    > code duplication, unless what we actually need is significantly more
    > complicated.
    >
    >   
    
    I think my logic needs a tiny piece of adjustment, to ignore the 
    timeline segment of the file name. But that will hardly involve a great 
    deal of extra code - just chop off the first 8 chars. It's not like the 
    code for this in pg_standby.c is terribly complex.
    
    The virtue of a perl script is that it's very easily customizable, e.g. 
    you might only delete files if they are older than a certain age.
    
    cheers
    
    andrew
    
    

24. Re: Command to prune archive at restartpoints

    Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2010-06-10T15:19:18Z

    On 10/06/10 17:38, Andrew Dunstan wrote:
    > I think my logic needs a tiny piece of adjustment, to ignore the
    > timeline segment of the file name.
    
    I'm not sure you should ignore it. Presumably anything in an older 
    timeline is indeed not required anymore and can be removed, and anything 
    in a newer timeline... how did it get there? Seems safer not remove it.
    
    -- 
       Heikki Linnakangas
       EnterpriseDB   http://www.enterprisedb.com
    
    

25. Re: Command to prune archive at restartpoints

    Andrew Dunstan <andrew@dunslane.net> — 2010-06-10T15:33:25Z

    
    Heikki Linnakangas wrote:
    > On 10/06/10 17:38, Andrew Dunstan wrote:
    >> I think my logic needs a tiny piece of adjustment, to ignore the
    >> timeline segment of the file name.
    >
    > I'm not sure you should ignore it. Presumably anything in an older 
    > timeline is indeed not required anymore and can be removed, and 
    > anything in a newer timeline... how did it get there? Seems safer not 
    > remove it.
    >
    
    Well, I was just following the logic in pg-standby.c:
    
                    /*
                     * We ignore the timeline part of the XLOG segment 
    identifiers
                     * in deciding whether a segment is still needed.  This
                     * ensures that we won't prematurely remove a segment from a
                     * parent timeline. We could probably be a little more
                     * proactive about removing segments of non-parent 
    timelines,
                     * but that would be a whole lot more complicated.
                     *
                     * We use the alphanumeric sorting property of the filenames
                     * to decide which ones are earlier than the
                     * exclusiveCleanupFileName file. Note that this means files
                     * are not removed in the order they were originally 
    written,
                     * in case this worries you.
                     */
                    if (strlen(xlde->d_name) == XLOG_DATA_FNAME_LEN &&
                        strspn(xlde->d_name, "0123456789ABCDEF")
                            == XLOG_DATA_FNAME_LEN &&
                      strcmp(xlde->d_name + 8, exclusiveCleanupFileName + 8) 
    < 0)
    
    
    cheers
    
    andrew
    
    
    

26. Re: Command to prune archive at restartpoints

    Dimitri Fontaine <dfontaine@hi-media.com> — 2010-06-10T19:24:41Z

    Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
    > Maybe we could add a new pg_cleanuparchive binary, but we'll need some
    > discussion...
    
    Would this binary ever be used manually, not invoked by PostgreSQL? As
    it depends on the %r option to be given and to be right, I don't think
    so.
    
    Therefore my take on this problem is to provide internal commands here,
    that maybe wouldn't need to be explicitly passed any argument. If
    they're internal they certainly can access to the information they need?
    
    As a user, I'd find it so much better to trust PostgreSQL for proposing
    sane defaults. As a developer, you will certainly find it easier to
    maintain, document and distribute.
    
    While at it, the other internal command we need is pg_archive_bypass for
    the archive_command so that windows users have the /usr/bin/true option
    too.
    
    Regards,
    -- 
    dim
    
    

27. Re: Command to prune archive at restartpoints

    Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2010-06-10T19:49:00Z

    On 10/06/10 22:24, Dimitri Fontaine wrote:
    > Heikki Linnakangas<heikki.linnakangas@enterprisedb.com>  writes:
    >> Maybe we could add a new pg_cleanuparchive binary, but we'll need some
    >> discussion...
    >
    > Would this binary ever be used manually, not invoked by PostgreSQL? As
    > it depends on the %r option to be given and to be right, I don't think
    > so.
    
    Hmm, actually it would be pretty handy. To make use of a base backup, 
    you need all the WAL files following the one where pg_start_backup() was 
    called. We create a .backup file in the archive to indicate that 
    location, like:
    
    00000001000000000000002F.00000020.backup
    
    So to clean up all WAL files older than those needed by that base 
    backup, you would simply copy-paste that location and call 
    pg_cleanuparchive:
    
    pg_cleanuparchive /walarchive/ 00000001000000000000002F
    
    Of course, if there's a perl one-liner to do that, we can just put that 
    in the docs and don't really need pg_cleanuparchive at all.
    
    > Therefore my take on this problem is to provide internal commands here,
    > that maybe wouldn't need to be explicitly passed any argument. If
    > they're internal they certainly can access to the information they need?
    
    You want more flexibility in more advanced cases. Like if you have 
    multiple standbys sharing the archive, you only want to remove old WAL 
    files after they're not needed by *any* of the standbys anymore. Doing 
    the cleanup directly in the archive_cleanup_command would cause the old 
    WAL files to be removed prematurely, but you could put a shell script 
    there to store the location to a file, and call pg_cleanuparchive with 
    the max() of the locations reported by all standby servers.
    
    -- 
       Heikki Linnakangas
       EnterpriseDB   http://www.enterprisedb.com
    
    

28. Re: Command to prune archive at restartpoints

    Simon Riggs <simon@2ndquadrant.com> — 2010-06-10T20:09:05Z

    On Thu, 2010-06-10 at 22:49 +0300, Heikki Linnakangas wrote:
    > On 10/06/10 22:24, Dimitri Fontaine wrote:
    > > Heikki Linnakangas<heikki.linnakangas@enterprisedb.com>  writes:
    > >> Maybe we could add a new pg_cleanuparchive binary, but we'll need some
    > >> discussion...
    > >
    > > Would this binary ever be used manually, not invoked by PostgreSQL? As
    > > it depends on the %r option to be given and to be right, I don't think
    > > so.
    > 
    > Hmm, actually it would be pretty handy. To make use of a base backup, 
    > you need all the WAL files following the one where pg_start_backup() was 
    > called. We create a .backup file in the archive to indicate that 
    > location, like:
    > 
    > 00000001000000000000002F.00000020.backup
    > 
    > So to clean up all WAL files older than those needed by that base 
    > backup, you would simply copy-paste that location and call 
    > pg_cleanuparchive:
    > 
    > pg_cleanuparchive /walarchive/ 00000001000000000000002F
    
    OK, sounds like we're on the same thought train.
    
    Here's the code.
    
    -- 
     Simon Riggs           www.2ndQuadrant.com
     PostgreSQL Development, 24x7 Support, Training and Services
    

29. Re: Command to prune archive at restartpoints

    Dimitri Fontaine <dfontaine@hi-media.com> — 2010-06-11T18:18:57Z

    Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
    > So to clean up all WAL files older than those needed by that base backup,
    > you would simply copy-paste that location and call pg_cleanuparchive:
    >
    > pg_cleanuparchive /walarchive/ 00000001000000000000002F
    
    Ok, idle though: what about having a superuser-only SRF doing the same?
    So that we have internal command for simple case, and SRF for use in
    scripts in more complex case.
    
    > Of course, if there's a perl one-liner to do that, we can just put that in
    > the docs and don't really need pg_cleanuparchive at all.
    
    psql -c "SELECT * FROM pg_cleanup_archive('00000001000000000000002F');"
    
    >> Therefore my take on this problem is to provide internal commands here,
    >> that maybe wouldn't need to be explicitly passed any argument. If
    >> they're internal they certainly can access to the information they need?
    >
    > You want more flexibility in more advanced cases. Like if you have multiple
    > standbys sharing the archive, you only want to remove old WAL files after
    > they're not needed by *any* of the standbys anymore. Doing the cleanup
    > directly in the archive_cleanup_command would cause the old WAL files to be
    > removed prematurely, but you could put a shell script there to store the
    > location to a file, and call pg_cleanuparchive with the max() of the
    > locations reported by all standby servers.
    
    Yes you still need to support external commands. That was not at all
    what I'm proposing: I'm just after having the simple case dead simple to
    setup. Like you don't write any script.
    
    Regards,
    -- 
    dim
    
    

30. Re: Command to prune archive at restartpoints

    Dimitri Fontaine <dfontaine@hi-media.com> — 2010-06-12T18:00:03Z

    Dimitri Fontaine <dfontaine@hi-media.com> writes:
    > Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
    >> So to clean up all WAL files older than those needed by that base backup,
    >> you would simply copy-paste that location and call pg_cleanuparchive:
    >>
    >> pg_cleanuparchive /walarchive/ 00000001000000000000002F
    >
    > Ok, idle though: what about having a superuser-only SRF doing the
    > same?
    
    So I'm looking at what it'd take to have that code, and it seems it
    would be quite easy. I wonder if we want to return only a boolean
    (command success status) or the list of files we're pruning (that's the
    SRF part), but other than that, it's all about having the code provided
    by Simon in another place and some internal command support.
    
    Something strange though: I notice that the error and signal handling in
    pgarch.c::pgarch_archiveXlog (lines 551 and following) and in
    xlog.c::ExecuteRecoveryCommand (lines 3143 and following) are very
    different for no reason that I can see.
    
    Why is that?
    
    Also, should I try to send a patch implementing my proposal (internal
    command exposed as a function at the SQL level, and while at it, maybe
    the internal command "pg_archive_bypass" to mimic /usr/bin/true as an
    archive_command)?
    
    Regards,
    -- 
    dim
    
    

31. Re: Command to prune archive at restartpoints

    Dimitri Fontaine <dfontaine@hi-media.com> — 2010-06-12T20:51:48Z

    Dimitri Fontaine <dfontaine@hi-media.com> writes:
    > Also, should I try to send a patch implementing my proposal (internal
    > command exposed as a function at the SQL level, and while at it, maybe
    > the internal command "pg_archive_bypass" to mimic /usr/bin/true as an
    > archive_command)?
    
    I had to have a try at it, even if quick and dirty. I've not tried to
    code the pg_archive_bypass internal command for lack of discussion, but
    I still think it would be great to have it.
    
    So here's a "see my idea in code" patch, that put the previous code by
    Simon into a backend function. As the goal was not to adapt the existing
    code intended as external to use the internal APIs, you'll find it quite
    ugly I'm sure.
    
    For example, this #define XLOG_DATA_FNAME_LEN has to go away, but that
    won't help having the idea accepted or not, and as I'm only warming up,
    I didn't tackle the problem. If you want me to do it, I'd appreciate
    some guidance as how to, though.
    
    It goes like this:
    
    dim=# select pg_switch_xlog();
     pg_switch_xlog 
    ----------------
     0/1000098
    (1 row)
    
    dim=# select pg_archive_cleanup('0/1000098');
    DEBUG:  removing "pg_xlog/000000010000000000000000"
    DEBUG:  removing "pg_xlog/000000010000000000000001"
     pg_archive_cleanup 
    --------------------
     t
    (1 row)
    
    I hope you too will find this way of interfacing is easier to deal with
    for everybody (from code maintenance to user settings).
    
    Regards,
    -- 
    dim
    
    

32. Re: Command to prune archive at restartpoints

    Robert Haas <robertmhaas@gmail.com> — 2010-06-13T16:22:31Z

    On Thu, Jun 10, 2010 at 4:09 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
    > Here's the code.
    
    I haven't more than glanced at this, but +1 for committing it if
    you're confident it DTRT.
    
    -- 
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
    
    

33. Re: Command to prune archive at restartpoints

    Robert Haas <robertmhaas@gmail.com> — 2010-06-13T16:31:31Z

    On Sat, Jun 12, 2010 at 4:51 PM, Dimitri Fontaine
    <dfontaine@hi-media.com> wrote:
    > Dimitri Fontaine <dfontaine@hi-media.com> writes:
    >> Also, should I try to send a patch implementing my proposal (internal
    >> command exposed as a function at the SQL level, and while at it, maybe
    >> the internal command "pg_archive_bypass" to mimic /usr/bin/true as an
    >> archive_command)?
    >
    > I had to have a try at it, even if quick and dirty. I've not tried to
    > code the pg_archive_bypass internal command for lack of discussion, but
    > I still think it would be great to have it.
    >
    > So here's a "see my idea in code" patch, that put the previous code by
    > Simon into a backend function. As the goal was not to adapt the existing
    > code intended as external to use the internal APIs, you'll find it quite
    > ugly I'm sure.
    >
    > For example, this #define XLOG_DATA_FNAME_LEN has to go away, but that
    > won't help having the idea accepted or not, and as I'm only warming up,
    > I didn't tackle the problem. If you want me to do it, I'd appreciate
    > some guidance as how to, though.
    >
    > It goes like this:
    >
    > dim=# select pg_switch_xlog();
    >  pg_switch_xlog
    > ----------------
    >  0/1000098
    > (1 row)
    >
    > dim=# select pg_archive_cleanup('0/1000098');
    > DEBUG:  removing "pg_xlog/000000010000000000000000"
    > DEBUG:  removing "pg_xlog/000000010000000000000001"
    >  pg_archive_cleanup
    > --------------------
    >  t
    > (1 row)
    >
    > I hope you too will find this way of interfacing is easier to deal with
    > for everybody (from code maintenance to user settings).
    
    I'm a bit perplexed here.  The archive cleanup has to run on the
    standby, not the master, right?  Whereas pg_switch_xlog() can only run
    on the master.  The purpose of making this a standalone executable is
    so that people who have, for example, multiple standbys, can customize
    the logic without having to hack the backend.  Pushing this into the
    backend would defeat that goal; plus, it wouldn't be usable at all for
    people who aren't running Hot Standby.
    
    -- 
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
    
    

34. Re: Command to prune archive at restartpoints

    Dimitri Fontaine <dfontaine@hi-media.com> — 2010-06-13T17:04:28Z

    Robert Haas <robertmhaas@gmail.com> writes:
    > I'm a bit perplexed here.  The archive cleanup has to run on the
    > standby, not the master, right?  Whereas pg_switch_xlog() can only run
    > on the master.
    
    I used it just to show a possible use case, easy to grasp. Sorry if
    that's confusing instead.
    
    >  The purpose of making this a standalone executable is
    > so that people who have, for example, multiple standbys, can customize
    > the logic without having to hack the backend.  Pushing this into the
    > backend would defeat that goal; plus, it wouldn't be usable at all for
    > people who aren't running Hot Standby.
    
    In the simple cases, what you want to be able to easily choose is just
    the first XLOG file you're NOT cleaning. And this is the only argument
    you give the function. 
    
    So you can either use the backend function as your internal command for
    archive cleanup, or use a script that choose where to stop cleaning then
    call it with that as an argument (it's SQL callable).
    
    What it does is unlink the file. If that behavior doesn't suit you, it's
    still possible to use an external command and tune some already proposed
    scripts. I just don't see how an external binary has more to offer than
    a backend function here. It's more code to maintain, it's harder to
    setup for people, and if it does not suit you, you still have to make
    you own script but you can not use what we ship easily (you have to get
    the sources and code in C for that).
    
    What I'm after is being able to tell people to just setup a GUC to a
    given value, not to copy/paste a (perl or bash) script from the docs,
    make it executable under their system, then test it and run it in
    production. We can do better than that, and it's not even hard.
    
    Regards,
    -- 
    dim
    
    

35. Re: Command to prune archive at restartpoints

    Robert Haas <robertmhaas@gmail.com> — 2010-06-13T18:25:53Z

    On Sun, Jun 13, 2010 at 1:04 PM, Dimitri Fontaine
    <dfontaine@hi-media.com> wrote:
    > Robert Haas <robertmhaas@gmail.com> writes:
    >> I'm a bit perplexed here.  The archive cleanup has to run on the
    >> standby, not the master, right?  Whereas pg_switch_xlog() can only run
    >> on the master.
    >
    > I used it just to show a possible use case, easy to grasp. Sorry if
    > that's confusing instead.
    >
    >>  The purpose of making this a standalone executable is
    >> so that people who have, for example, multiple standbys, can customize
    >> the logic without having to hack the backend.  Pushing this into the
    >> backend would defeat that goal; plus, it wouldn't be usable at all for
    >> people who aren't running Hot Standby.
    >
    > In the simple cases, what you want to be able to easily choose is just
    > the first XLOG file you're NOT cleaning. And this is the only argument
    > you give the function.
    >
    > So you can either use the backend function as your internal command for
    > archive cleanup, or use a script that choose where to stop cleaning then
    > call it with that as an argument (it's SQL callable).
    >
    > What it does is unlink the file. If that behavior doesn't suit you, it's
    > still possible to use an external command and tune some already proposed
    > scripts. I just don't see how an external binary has more to offer than
    > a backend function here. It's more code to maintain, it's harder to
    > setup for people, and if it does not suit you, you still have to make
    > you own script but you can not use what we ship easily (you have to get
    > the sources and code in C for that).
    >
    > What I'm after is being able to tell people to just setup a GUC to a
    > given value, not to copy/paste a (perl or bash) script from the docs,
    > make it executable under their system, then test it and run it in
    > production. We can do better than that, and it's not even hard.
    
    We're not going to make them cut/paste anything from the docs.  We're
    going to provide a production-ready executable they can just use,
    which should be installed (presumably, already with the correct
    permissions) by their packaging system if they install
    postgresql-contrib or the equivalent.
    
    -- 
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
    
    

36. Re: Command to prune archive at restartpoints

    Dimitri Fontaine <dfontaine@hi-media.com> — 2010-06-13T18:51:14Z

    Robert Haas <robertmhaas@gmail.com> writes:
    >> Robert Haas <robertmhaas@gmail.com> writes:
    >>>  The purpose of making this a standalone executable is
    >>> so that people who have, for example, multiple standbys, can customize
    >>> the logic without having to hack the backend.  Pushing this into the
    >>> backend would defeat that goal; plus, it wouldn't be usable at all for
    >>> people who aren't running Hot Standby.
    >
    > We're not going to make them cut/paste anything from the docs.  We're
    > going to provide a production-ready executable they can just use,
    > which should be installed (presumably, already with the correct
    > permissions) by their packaging system if they install
    > postgresql-contrib or the equivalent.
    
    I still run against people not wanting to trust contrib. I still read
    here from time to time that contrib's chapter is maintaining working
    examples of extensibility, not maintaining production ready add-ons.
    
    Other than that, you proposed something flexible and easy to customize,
    and you end up with an executable binary that will only offer one
    behavior (unlink), the only option is where to stop (%r).
    
    The backend function I'm proposing uses the same option, but is easier
    to call from a script, should you need to customize. You don't even have
    to run the script locally or remember where is the XLOG directory of
    that instance. You could operate over a JDBC connection, e.g.
    
    I now realize that my proposal ain't helping if Streaming Replication is
    filling the standby's pg_xlog and hot_standby = off. I don't remember
    that SR rebuilds pg_xlog on the standby though, does it?
    
    The proposed script will only cleanup XLOGDIR in fact, so if you use a
    common archive elsewhere then you still need some external command not
    provided by the project. So we still need the script example in the
    docs.
    
    I think that the pg_archivecleanup binary is a good solution, all the
    more if not shipped in contrib, but that the SQL callable function is
    better.
    
    Regards,
    -- 
    dim
    
    

37. Re: Command to prune archive at restartpoints

    Andrew Dunstan <andrew@dunslane.net> — 2010-06-13T19:03:58Z

    
    Dimitri Fontaine wrote:
    > I still read
    > here from time to time that contrib's chapter is maintaining working
    > examples of extensibility, not maintaining production ready add-ons.
    >
    >   
    
    Even if this were true, and I don't believe it is, ISTM the solution 
    would be to have a utility command alongside the other utility commands 
    like pg_controldata.
    
    cheers
    
    andrew
    
    

38. Re: Command to prune archive at restartpoints

    Fujii Masao <masao.fujii@gmail.com> — 2010-06-14T04:02:39Z

    On Mon, Jun 14, 2010 at 3:51 AM, Dimitri Fontaine
    <dfontaine@hi-media.com> wrote:
    > I now realize that my proposal ain't helping if Streaming Replication is
    > filling the standby's pg_xlog and hot_standby = off. I don't remember
    > that SR rebuilds pg_xlog on the standby though, does it?
    
    In SR, WAL files in the pg_xlog directory on the standby are recycled
    by every restartpoints. So your proposed function seems not to be helpful
    even if hot_standby = on.
    
    Regards,
    
    -- 
    Fujii Masao
    NIPPON TELEGRAPH AND TELEPHONE CORPORATION
    NTT Open Source Software Center
    
    

39. Re: Command to prune archive at restartpoints

    Dimitri Fontaine <dfontaine@hi-media.com> — 2010-06-14T10:21:35Z

    Fujii Masao <masao.fujii@gmail.com> writes:
    > In SR, WAL files in the pg_xlog directory on the standby are recycled
    > by every restartpoints. So your proposed function seems not to be helpful
    > even if hot_standby = on.
    
    Then I guess I'm at a loss here: what is the pg_archivecleanup utility
    good for in a standby?
    
    -- 
    dim
    
    

40. Re: Command to prune archive at restartpoints

    Simon Riggs <simon@2ndquadrant.com> — 2010-06-14T11:29:38Z

    On Mon, 2010-06-14 at 12:21 +0200, Dimitri Fontaine wrote:
    > Fujii Masao <masao.fujii@gmail.com> writes:
    > > In SR, WAL files in the pg_xlog directory on the standby are recycled
    > > by every restartpoints. So your proposed function seems not to be helpful
    > > even if hot_standby = on.
    > 
    > Then I guess I'm at a loss here: what is the pg_archivecleanup utility
    > good for in a standby?
    
    Cleaning the archive directory, not the pg_xlog directory.
    
    -- 
     Simon Riggs           www.2ndQuadrant.com
    
    
    

41. Re: Command to prune archive at restartpoints

    Dimitri Fontaine <dfontaine@hi-media.com> — 2010-06-14T12:37:09Z

    Simon Riggs <simon@2ndQuadrant.com> writes:
    > Cleaning the archive directory, not the pg_xlog directory.
    
    Hence the choice of the directory where to act. I was slow on that,
    sorry guys.
    
    I guess my main problem here is that I still picture PostgreSQL has
    being able to maintain an archive with no external script in the simple
    case.
    
    Regards,
    -- 
    dim