Re: postgresql.conf archive_command example

I think it would be useful to add the following explanation and sample
to the postgresql.conf sample file:

diff --git i/src/backend/utils/misc/postgresql.conf.sample w/src/backend/utils/misc/postgresql.conf.sample
--- i/src/backend/utils/misc/postgresql.conf.sample
+++ w/src/backend/utils/misc/postgresql.conf.sample
@@ -186,6 +186,9 @@
#archive_mode = off # allows archiving to be done
# (change 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?

On 31 August 2011 04:39, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> I think it would be useful to add the following explanation and sample
> to the postgresql.conf sample file:
>

Good idea Peter, +1.

Cheers,
BJ

2011/8/30 Peter Eisentraut <peter_e(at)gmx(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

On ons, 2011-08-31 at 11:18 +0200, Cédric Villemain wrote:
> Just a question: can we build a different postgresql.conf for windows
> or do we add a windows command example here as well ?

Well, we could make initdb patch it up, but that might seem excessive.

2011/8/31 Peter Eisentraut <peter_e(at)gmx(dot)net>:
> On ons, 2011-08-31 at 11:18 +0200, Cédric Villemain wrote:
>> Just a question: can we build a different postgresql.conf for windows
>> or do we add a windows command example here as well ?
>
> Well, we could make initdb patch it up, but that might seem excessive.

sure. I was wondering if it was already possible, not proposing to do it.

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Well, we could make initdb patch it up, but that might seem excessive.

I sometime wonder if archive_mode shouldn't default to "on" with the
archive_command set to either '/bin/true' or 'rem' for windows.

That allows to install proper archiving without restart, but the
tradeoff is of course that you need to restart to enable some
optimisation cases by turning archive_mode off.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

2011/9/1 Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> Well, we could make initdb patch it up, but that might seem excessive.
>
> I sometime wonder if archive_mode shouldn't default to "on" with the
> archive_command set to either '/bin/true' or 'rem' for windows.
>
> That allows to install proper archiving without restart, but the
> tradeoff is of course that you need to restart to enable some
> optimisation cases by turning archive_mode off.

Seems like it would be better to fix archive_mode so that it can be
changed without a restart.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> Seems like it would be better to fix archive_mode so that it can be
> changed without a restart.

+1

I'm also wondering if providing some shell script examples of a
fault-tolerant script to handle archiving would be useful.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Thu, Sep 1, 2011 at 3:05 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> +1
>
> I'm also wondering if providing some shell script examples of a
> fault-tolerant script to handle archiving would be useful.

I think it would.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I'm also wondering if providing some shell script examples of a
>> fault-tolerant script to handle archiving would be useful.
>
> I think it would.

My usual advice is to avoid having to write one if possible, because
it's more complex than it looks. What about recommending existing
solutions, such as walmgr from Skytools?

Even better, what about including a default archiving tool, that could
be either another script in bin/ or rather an internal command. The
default would accept a location as argument, for simple needs you mount
a remote filesystem and there you go. If you need something more
complex, you still can provide it yourself.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> I'm also wondering if providing some shell script examples of a
>>> fault-tolerant script to handle archiving would be useful.
>>
>> I think it would.
>
> My usual advice is to avoid having to write one if possible,
> because it's more complex than it looks. What about recommending
> existing solutions, such as walmgr from Skytools?
>
> Even better, what about including a default archiving tool, that
> could be either another script in bin/ or rather an internal
> command. The default would accept a location as argument, for
> simple needs you mount a remote filesystem and there you go. If
> you need something more complex, you still can provide it
> yourself.

In a green field I might argue for having an archvie_directory GUC
instead of archive_command. As it stands, it might be a really good
idea to provide a pg_archiveto executable which takes as arguments a
directory path and the arguments passed to the archive script. With
a little extra effort, the executable could check for some file
which would specify what host and path should be writing archives
there, to avoid problems with copied database directories
accidentally writing to the same location as the source.

Such an executable seems like minimal effort compared to the
problems it would solve.

If there's an existing tool with appropriate licensing which is
sufficiently portable and reliable, all the better -- let's ship it
and use that for our example archive_command.

-Kevin

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> In a green field I might argue for having an archvie_directory GUC
> instead of archive_command. As it stands, it might be a really good

I would think we then would need both. archive_command with parameters
offers both.

> idea to provide a pg_archiveto executable which takes as arguments a
> directory path and the arguments passed to the archive script. With
> a little extra effort, the executable could check for some file
> which would specify what host and path should be writing archives
> there, to avoid problems with copied database directories
> accidentally writing to the same location as the source.
>
> Such an executable seems like minimal effort compared to the
> problems it would solve.
>
> If there's an existing tool with appropriate licensing which is
> sufficiently portable and reliable, all the better -- let's ship it
> and use that for our example archive_command.

I would like for it not to be an example, but a default value.
Something ready for production but with a very narrow use case.

--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Fri, Sep 2, 2011 at 10:34 AM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>>> I'm also wondering if providing some shell script examples of a
>>>> fault-tolerant script to handle archiving would be useful.
>>>
>>> I think it would.
>>
>> My usual advice is to avoid having to write one if possible,
>> because it's more complex than it looks.  What about recommending
>> existing solutions, such as walmgr from Skytools?
>>
>> Even better, what about including a default archiving tool, that
>> could be either another script in bin/ or rather an internal
>> command. The default would accept a location as argument, for
>> simple needs you mount a remote filesystem and there you go.  If
>> you need something more complex, you still can provide it
>> yourself.
>
> In a green field I might argue for having an archvie_directory GUC
> instead of archive_command.  As it stands, it might be a really good
> idea to provide a pg_archiveto executable which takes as arguments a
> directory path and the arguments passed to the archive script.  With
> a little extra effort, the executable could check for some file
> which would specify what host and path should be writing archives
> there, to avoid problems with copied database directories
> accidentally writing to the same location as the source.
>
> Such an executable seems like minimal effort compared to the
> problems it would solve.
>
> If there's an existing tool with appropriate licensing which is
> sufficiently portable and reliable, all the better -- let's ship it
> and use that for our example archive_command.

Another thought I have here is to wonder whether we should change
something on the server side so that we don't NEED such a complicated
archive_command. I mean, copying a file to a directory somewhere is
not fundamentally a complex operation. Nor is using ssh to copy it to
another machine. The fact that archive_commands need to be so complex
seems like a usability defect. The consensus seems to be that just
using something like 'cp' for your archive command won't work out
well, but maybe instead of shipping a more complicated script we
should be trying to eliminate (or at least reduce) the need for a more
complicated script.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 09/02/2011 01:00 PM, Robert Haas wrote:
> On Fri, Sep 2, 2011 at 10:34 AM, Kevin Grittner
> <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> Dimitri Fontaine<dimitri(at)2ndQuadrant(dot)fr> wrote:
>>> Robert Haas<robertmhaas(at)gmail(dot)com> writes:
>>>>> I'm also wondering if providing some shell script examples of a
>>>>> fault-tolerant script to handle archiving would be useful.
>>>> I think it would.
>>> My usual advice is to avoid having to write one if possible,
>>> because it's more complex than it looks. What about recommending
>>> existing solutions, such as walmgr from Skytools?
>>>
>>> Even better, what about including a default archiving tool, that
>>> could be either another script in bin/ or rather an internal
>>> command. The default would accept a location as argument, for
>>> simple needs you mount a remote filesystem and there you go. If
>>> you need something more complex, you still can provide it
>>> yourself.
>> In a green field I might argue for having an archvie_directory GUC
>> instead of archive_command. As it stands, it might be a really good
>> idea to provide a pg_archiveto executable which takes as arguments a
>> directory path and the arguments passed to the archive script. With
>> a little extra effort, the executable could check for some file
>> which would specify what host and path should be writing archives
>> there, to avoid problems with copied database directories
>> accidentally writing to the same location as the source.
>>
>> Such an executable seems like minimal effort compared to the
>> problems it would solve.
>>
>> If there's an existing tool with appropriate licensing which is
>> sufficiently portable and reliable, all the better -- let's ship it
>> and use that for our example archive_command.
> Another thought I have here is to wonder whether we should change
> something on the server side so that we don't NEED such a complicated
> archive_command. I mean, copying a file to a directory somewhere is
> not fundamentally a complex operation. Nor is using ssh to copy it to
> another machine. The fact that archive_commands need to be so complex
> seems like a usability defect. The consensus seems to be that just
> using something like 'cp' for your archive command won't work out
> well, but maybe instead of shipping a more complicated script we
> should be trying to eliminate (or at least reduce) the need for a more
> complicated script.
>

The problem is that the number of ways you might want to do things is
quite large. For example, you might want to copy the archives to more
than one place for safety reasons. I pretty much always set
archive_command to a script which I can then customize to my heart's
content, and it seems to work pretty well. Providing a simple example of
such a script seems like it could be useful.

cheers

andrew

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> maybe instead of shipping a more complicated script we should be
> trying to eliminate (or at least reduce) the need for a more
> complicated script.

That was the intent of my pg_archiveto suggestion. I'll amend it
(based on other comments) to allow for a URL as an alternative way
to specify the directory. So your archive_command might be:

'pg_archiveto /var/pgsql/backup/wal/ %p %f'

or:

'pg_archiveto http://backup-server/mydbserver/wal/ %p %f'

or maybe:

'pg_archiveto /mnt/someremotedirectory/ %p %f'

-Kevin

Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

> For example, you might want to copy the archives to more than one
> place for safety reasons.

We've never felt that the way to do that was to put the logic for it
in the archive script -- we archive to a local directory and set up
rsync tasks on cron to distribute it. Otherwise you might not
archive to one target if the other is down, or might have trouble
catching up with a target when it comes back from being down.

-Kevin

On Fri, Sep 2, 2011 at 19:13, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>> For example, you might want to copy the archives to more than one
>> place for safety reasons.
>
> We've never felt that the way to do that was to put the logic for it
> in the archive script -- we archive to a local directory and set up
> rsync tasks on cron to distribute it.  Otherwise you might not
> archive to one target if the other is down, or might have trouble
> catching up with a target when it comes back from being down.

Archiving it locally will give you a window of lost data if you crash.

The point being - different people have different requirements, which
is one thing our currently solution is very good at catering to - the
downside being lots of work. I don't think dumbing down the system is
a good idea - but shipping an example script probably is.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Another thought I have here is to wonder whether we should change
> something on the server side so that we don't NEED such a complicated
> archive_command. I mean, copying a file to a directory somewhere is
> not fundamentally a complex operation. Nor is using ssh to copy it to
> another machine.

It is once you consider error handling and catering to N combinations of
user requirements.

I think the notion that we should get rid of archive_command in favor of
something more hard-wired is sheer lunacy. We have a nicely decoupled
arrangement for dealing with these issues now; why would we want to pull
them into the server?

Now, providing a more useful sample script is certainly reasonable.

regards, tom lane

On Fri, Sep 2, 2011 at 3:29 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Another thought I have here is to wonder whether we should change
>> something on the server side so that we don't NEED such a complicated
>> archive_command.  I mean, copying a file to a directory somewhere is
>> not fundamentally a complex operation.  Nor is using ssh to copy it to
>> another machine.
>
> It is once you consider error handling and catering to N combinations of
> user requirements.
>
> I think the notion that we should get rid of archive_command in favor of
> something more hard-wired is sheer lunacy.  We have a nicely decoupled
> arrangement for dealing with these issues now; why would we want to pull
> them into the server?

I wasn't really proposing to get rid of it, but I do wonder if there
are some configuration parameters we could add somewhere that would
make common cases easier without making really complex things
impossible.

> Now, providing a more useful sample script is certainly reasonable.

Yep, so let's start with that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I think the notion that we should get rid of archive_command in
> favor of something more hard-wired is sheer lunacy.

It's a good thing nobody proposed that.

-Kevin

Robert Haas <robertmhaas(at)gmail(dot)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

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)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

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> (1) We're talking about a new /bin executable to do this which
> could be referenced in an archive_command string or run from a
> script called by archive_command, right?

That, or an internal implementation. That would be a function in the
backend that would be called when archive_command is set to some
specific value, like for example test and cd are command lines referring
not to some executable on the PATH but to some internal code in bash.

But I know some people here will frown upon that idea.

> (2) It should copy, not move, with protection against overwriting
> an existing file.

See, we need to provide a good production grade facility. I've never
tried to do it myself, I'm just using walmgr to manage my archives.

> (3) Maybe not in the initial version, but eventually it might be
> nice to support URLs of some known protocols in addition to local
> directories.

I guess that if patches are provided in that direction it would be kind
of hard to refuse integrating them :)

> (4) Maybe not in the initial version, but eventually it might be
> nice to support checking for an "owner" file of some sort in the
> target directory, to help sort out problems with copied databases
> writing to the same location as the source.

Then we need to provide the associated restore command which must not be
one "owner" here I guess…

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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

On Sat, Sep 3, 2011 at 5:10 AM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> (2)  It should copy, not move, with protection against overwriting
> an existing file.

I agree that basically archive_command should not overwrite an existing file.
But if the size of existing file is less than 16MB, it should do that.
Otherwise,
that WAL file would be lost forever.

I have another feature request;
(5) Maybe not in the initial version, but eventually it might be
nice to support calling posix_fadvise(POSIX_FADV_DONTNEED)
after copying a WAL file.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Tue, Sep 6, 2011 at 10:11 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Sat, Sep 3, 2011 at 5:10 AM, Kevin Grittner
> <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> (2)  It should copy, not move, with protection against overwriting
>> an existing file.
>
> I agree that basically archive_command should not overwrite an existing file.
> But if the size of existing file is less than 16MB, it should do that.
> Otherwise,
> that WAL file would be lost forever.
>

I think best practice in this case is that if you ever find an
existing file with the same name already in place, you should error
and investigate. We don't ship around partially completed WAL files,
and finding an existing one probably means something went wrong. (Of
course, we use rsync instead of copy/move, so we have some better
guarantees about this).

> I have another feature request;
> (5) Maybe not in the initial version, but eventually it might be
> nice to support calling posix_fadvise(POSIX_FADV_DONTNEED)
> after copying a WAL file.
>

Can you go into more details on how you envision this working. I'm
mostly curious because I think rsync might already support this, which
would make it easy to incorporate.

On a side note, seeing this thread hasn't died, I'd encourage everyone
to take another look at OmniPITR,
https://github.com/omniti-labs/omnipitr. It's postgresql licensed,
solves a lot of the problems listed here, and I think makes for a good
example for people who want to accomplish more advanced awl management
goals. So far the biggest criticism we've gotten is that it wasn't
written in python, for some of you that might be a plus though ;-)

Robert Treat
play: xzilla.net
work: omniti.com

On Wed, Sep 7, 2011 at 11:53 PM, Robert Treat <rob(at)xzilla(dot)net> wrote:
> On Tue, Sep 6, 2011 at 10:11 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)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

On Thu, Sep 8, 2011 at 7:05 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Wed, Sep 7, 2011 at 11:53 PM, Robert Treat <rob(at)xzilla(dot)net> wrote:
>> On Tue, Sep 6, 2011 at 10:11 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>>> I agree that basically archive_command should not overwrite an existing file.
>>> But if the size of existing file is less than 16MB, it should do that.
>>> Otherwise,
>>> that WAL file would be lost forever.
>>
>> I think best practice in this case is that if you ever find an
>> existing file with the same name already in place, you should error
>> and investigate. We don't ship around partially completed WAL files,
>> and finding an existing one probably means something went wrong. (Of
>> course, we use rsync instead of copy/move, so we have some better
>> guarantees about this).
>
> That's an option. But I don't think that finding an existing file is so serious
> problem.

The recommendation should be that the archived files are never
overwritten because that prevents a huge range of data loss bugs and
kills them stone dead.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Thu, Sep 8, 2011 at 3:26 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Thu, Sep 8, 2011 at 7:05 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> That's an option. But I don't think that finding an existing file is so serious
>> problem.
>
> The recommendation should be that the archived files are never
> overwritten because that prevents a huge range of data loss bugs and
> kills them stone dead.

I'm OK with that default behavior of the executable. It's helpful if
the executable
supports overwrite-if-filesize-is-not-16MB option.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Thu, Sep 8, 2011 at 2:05 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:

> That's an option. But I don't think that finding an existing file is so serious
> problem. The most common cases which cause a partially-filled archived
> file are;
>
> 1. The server crashes while WAL file is being archived, and then the server
>    restarts. In this case, the restarted server would find partially-filled
>    archived file.
>
> 2. In replication environment, the master crashes while WAL file is being
>    archived, and then a failover happens. In this case, new master would
>    find partially-filled archived file.
>
> In these cases, I don't think it's so unsafe to overwrite an existing file.

Personally, I think both of these show examples of why PG should be
looking hard at either providing a simple robust local directory based
archive_command, or very seriously pointing users at properly written
tools like omniptr, or ptrtools, walmgr, etc...

Neither of those cases should ever happen. If you're copying a file
into the archive, and making it appear non-atomically in your archive,
your doing something wrong.

Period.

No excuses.

a.
--
Aidan Van Dyk                                             Create like a god,
aidan(at)highrise(dot)ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

Aidan Van Dyk <aidan(at)highrise(dot)ca> wrote:

> If you're copying a file into the archive, and making it appear
> non-atomically in your archive, your doing something wrong.
>
> Period.
>
> No excuses.

+1

-Kevin

On Sep8, 2011, at 15:09 , Aidan Van Dyk wrote:
> Personally, I think both of these show examples of why PG should be
> looking hard at either providing a simple robust local directory based
> archive_command, or very seriously pointing users at properly written
> tools like omniptr, or ptrtools, walmgr, etc...
>
> Neither of those cases should ever happen. If you're copying a file
> into the archive, and making it appear non-atomically in your archive,
> your doing something wrong.

+1000.

Archiving WAL should be done by copying to a temp file and moving it
into place. Before returning success, one should probably also do the
fsync incantations the linux kernel guys argued are necessary to prevent
the file from appearing empty if the machine crashes shortly after the
move. (Yeah, they fixed that after enough people complained, but the fact
that they even went as far as arguing their behaviour is correct according
to POSIX makes me uneasy...)

It'd be very cool if we shipped a tool that did that correctly (pg_walcopy
maybe?) on all supported platforms.

best regards,
Florian Pflug

On Fri, Sep 09, 2011 at 08:59:43PM +0200, Florian Pflug wrote:
> Archiving WAL should be done by copying to a temp file and moving it
> into place. Before returning success, one should probably also do the
> fsync incantations the linux kernel guys argued are necessary to prevent
> the file from appearing empty if the machine crashes shortly after the
> move. (Yeah, they fixed that after enough people complained, but the fact
> that they even went as far as arguing their behaviour is correct according
> to POSIX makes me uneasy...)

Well, they fixed it for ext2/3/4 but that doesn't change the fact that
most other filesystems don't provide the same guarentees. If you want
to be sure the file contents hit the disk, you need to do an fsync.

(If you suggested to people we could add a new WAL sync method that
wrote the data to disk without fsync and renamed it over an existing
file and assured them that the data would survive a crash, they'd say
you're nuts).

> It'd be very cool if we shipped a tool that did that correctly (pg_walcopy
> maybe?) on all supported platforms.

It's hard enough to get right that shipping a tool that works properly
is eminently sensible. If only to demonstrate how it should be done.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer

On Friday, September 09, 2011 08:59:43 PM Florian Pflug wrote:
> On Sep8, 2011, at 15:09 , Aidan Van Dyk wrote:
> > Personally, I think both of these show examples of why PG should be
> > looking hard at either providing a simple robust local directory based
> > archive_command, or very seriously pointing users at properly written
> > tools like omniptr, or ptrtools, walmgr, etc...
> >
> > Neither of those cases should ever happen. If you're copying a file
> > into the archive, and making it appear non-atomically in your archive,
> > your doing something wrong.
>
> +1000.
>
> Archiving WAL should be done by copying to a temp file and moving it
> into place. Before returning success, one should probably also do the
> fsync incantations the linux kernel guys argued are necessary to prevent
> the file from appearing empty if the machine crashes shortly after the
> move. (Yeah, they fixed that after enough people complained, but the fact
> that they even went as far as arguing their behaviour is correct according
> to POSIX makes me uneasy...)
The only problem being that its only fixed with certain mount options on a
certain filesystem (ext3, ext4, data=ordered).
Every other filesystem (like e.g. XFS) still does it that way. And did it for
at least a decade.
It makes me just as uneasy that so few people knew about that - preexisting! -
problem...

> It'd be very cool if we shipped a tool that did that correctly (pg_walcopy
> maybe?) on all supported platforms.
+1