Re: [HACKERS] postgresql.conf archive_command example

2011-09-10 Thread Andres Freund
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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-09 Thread Florian Pflug
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




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-09 Thread Martijn van Oosterhout
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   klep...@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


signature.asc
Description: Digital signature


Re: [HACKERS] postgresql.conf archive_command example

2011-09-08 Thread Fujii Masao
On Wed, Sep 7, 2011 at 11:53 PM, Robert Treat r...@xzilla.net wrote:
 On Tue, Sep 6, 2011 at 10:11 PM, Fujii Masao masao.fu...@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 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.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-08 Thread Simon Riggs
On Thu, Sep 8, 2011 at 7:05 AM, Fujii Masao masao.fu...@gmail.com wrote:
 On Wed, Sep 7, 2011 at 11:53 PM, Robert Treat r...@xzilla.net wrote:
 On Tue, Sep 6, 2011 at 10:11 PM, Fujii Masao masao.fu...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-08 Thread Fujii Masao
On Thu, Sep 8, 2011 at 3:26 PM, Simon Riggs si...@2ndquadrant.com wrote:
 On Thu, Sep 8, 2011 at 7:05 AM, Fujii Masao masao.fu...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-08 Thread Aidan Van Dyk
On Thu, Sep 8, 2011 at 2:05 AM, Fujii Masao masao.fu...@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,
ai...@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-08 Thread Kevin Grittner
Aidan Van Dyk ai...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-07 Thread Robert Treat
On Tue, Sep 6, 2011 at 10:11 PM, Fujii Masao masao.fu...@gmail.com wrote:
 On Sat, Sep 3, 2011 at 5:10 AM, Kevin Grittner
 kevin.gritt...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-06 Thread Kevin Grittner
Dimitri Fontaine dimi...@2ndquadrant.fr wrote:
 Kevin Grittner kevin.gritt...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-06 Thread Kevin Grittner
Dimitri Fontaine dimi...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-06 Thread Fujii Masao
On Sat, Sep 3, 2011 at 5:10 AM, Kevin Grittner
kevin.gritt...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-02 Thread Dimitri Fontaine
Robert Haas robertmh...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-02 Thread Kevin Grittner
Dimitri Fontaine dimi...@2ndquadrant.fr wrote:
 Robert Haas robertmh...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-02 Thread Dimitri Fontaine
Kevin Grittner kevin.gritt...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-02 Thread Robert Haas
On Fri, Sep 2, 2011 at 10:34 AM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
 Dimitri Fontaine dimi...@2ndquadrant.fr wrote:
 Robert Haas robertmh...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-02 Thread Andrew Dunstan



On 09/02/2011 01:00 PM, Robert Haas wrote:

On Fri, Sep 2, 2011 at 10:34 AM, Kevin Grittner
kevin.gritt...@wicourts.gov  wrote:

Dimitri Fontainedimi...@2ndquadrant.fr  wrote:

Robert Haasrobertmh...@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



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-02 Thread Kevin Grittner
Robert Haas robertmh...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-02 Thread Kevin Grittner
Andrew Dunstan and...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-02 Thread Magnus Hagander
On Fri, Sep 2, 2011 at 19:13, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
 Andrew Dunstan and...@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/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-02 Thread Tom Lane
Robert Haas robertmh...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-02 Thread Robert Haas
On Fri, Sep 2, 2011 at 3:29 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-02 Thread Kevin Grittner
Tom Lane t...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-02 Thread Dimitri Fontaine
Robert Haas robertmh...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-02 Thread Dimitri Fontaine
Kevin Grittner kevin.gritt...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-01 Thread Dimitri Fontaine
Peter Eisentraut pete...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-01 Thread Robert Haas
2011/9/1 Dimitri Fontaine dimi...@2ndquadrant.fr:
 Peter Eisentraut pete...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-01 Thread Josh Berkus

 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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-09-01 Thread Robert Haas
On Thu, Sep 1, 2011 at 3:05 PM, Josh Berkus j...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-08-31 Thread Cédric Villemain
2011/8/30 Peter Eisentraut pete...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-08-31 Thread Peter Eisentraut
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.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-08-31 Thread Cédric Villemain
2011/8/31 Peter Eisentraut pete...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] postgresql.conf archive_command example

2011-08-30 Thread Peter Eisentraut
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?



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] postgresql.conf archive_command example

2011-08-30 Thread Brendan Jurd
On 31 August 2011 04:39, Peter Eisentraut pete...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers