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 disablesThis corresponds to what we have in the documentation and mirrors the
example in recovery.conf.sample.
Objections?
On 31 August 2011 04:39, Peter Eisentraut <peter_e@gmx.net> wrote:
I think it would be useful to add the following explanation and sample
to the postgresql.conf sample file:
Good idea Peter, +1.
Cheers,
BJ
2011/8/30 Peter Eisentraut <peter_e@gmx.net>:
I think it would be useful to add the following explanation and sample
to the postgresql.conf sample file:diff --git i/src/backend/utils/misc/postgresql.conf.sample w/src/backend/utils/misc/postgresql.conf.sample --- i/src/backend/utils/misc/postgresql.conf.sample +++ w/src/backend/utils/misc/postgresql.conf.sample @@ -186,6 +186,9 @@ #archive_mode = off # allows archiving to be done # (change requires restart) #archive_command = '' # command to use to archive a logfile segment + # placeholders: %p = path of file to archive + # %f = file name only + # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f' #archive_timeout = 0 # force a logfile segment switch after this # number of seconds; 0 disablesThis corresponds to what we have in the documentation and mirrors the
example in recovery.conf.sample.Objections?
No objections, it is welcome.
Just a question: can we build a different postgresql.conf for windows
or do we add a windows command example here as well ?
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation
On ons, 2011-08-31 at 11:18 +0200, Cédric Villemain wrote:
Just a question: can we build a different postgresql.conf for windows
or do we add a windows command example here as well ?
Well, we could make initdb patch it up, but that might seem excessive.
2011/8/31 Peter Eisentraut <peter_e@gmx.net>:
On ons, 2011-08-31 at 11:18 +0200, Cédric Villemain wrote:
Just a question: can we build a different postgresql.conf for windows
or do we add a windows command example here as well ?Well, we could make initdb patch it up, but that might seem excessive.
sure. I was wondering if it was already possible, not proposing to do it.
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation
Peter Eisentraut <peter_e@gmx.net> writes:
Well, we could make initdb patch it up, but that might seem excessive.
I sometime wonder if archive_mode shouldn't default to "on" with the
archive_command set to either '/bin/true' or 'rem' for windows.
That allows to install proper archiving without restart, but the
tradeoff is of course that you need to restart to enable some
optimisation cases by turning archive_mode off.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
2011/9/1 Dimitri Fontaine <dimitri@2ndquadrant.fr>:
Peter Eisentraut <peter_e@gmx.net> writes:
Well, we could make initdb patch it up, but that might seem excessive.
I sometime wonder if archive_mode shouldn't default to "on" with the
archive_command set to either '/bin/true' or 'rem' for windows.That allows to install proper archiving without restart, but the
tradeoff is of course that you need to restart to enable some
optimisation cases by turning archive_mode off.
Seems like it would be better to fix archive_mode so that it can be
changed without a restart.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Seems like it would be better to fix archive_mode so that it can be
changed without a restart.
+1
I'm also wondering if providing some shell script examples of a
fault-tolerant script to handle archiving would be useful.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
On Thu, Sep 1, 2011 at 3:05 PM, Josh Berkus <josh@agliodbs.com> wrote:
+1
I'm also wondering if providing some shell script examples of a
fault-tolerant script to handle archiving would be useful.
I think it would.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
I'm also wondering if providing some shell script examples of a
fault-tolerant script to handle archiving would be useful.I think it would.
My usual advice is to avoid having to write one if possible, because
it's more complex than it looks. What about recommending existing
solutions, such as walmgr from Skytools?
Even better, what about including a default archiving tool, that could
be either another script in bin/ or rather an internal command. The
default would accept a location as argument, for simple needs you mount
a remote filesystem and there you go. If you need something more
complex, you still can provide it yourself.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Dimitri Fontaine <dimitri@2ndQuadrant.fr> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
I'm also wondering if providing some shell script examples of a
fault-tolerant script to handle archiving would be useful.I think it would.
My usual advice is to avoid having to write one if possible,
because it's more complex than it looks. What about recommending
existing solutions, such as walmgr from Skytools?Even better, what about including a default archiving tool, that
could be either another script in bin/ or rather an internal
command. The default would accept a location as argument, for
simple needs you mount a remote filesystem and there you go. If
you need something more complex, you still can provide it
yourself.
In a green field I might argue for having an archvie_directory GUC
instead of archive_command. As it stands, it might be a really good
idea to provide a pg_archiveto executable which takes as arguments a
directory path and the arguments passed to the archive script. With
a little extra effort, the executable could check for some file
which would specify what host and path should be writing archives
there, to avoid problems with copied database directories
accidentally writing to the same location as the source.
Such an executable seems like minimal effort compared to the
problems it would solve.
If there's an existing tool with appropriate licensing which is
sufficiently portable and reliable, all the better -- let's ship it
and use that for our example archive_command.
-Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
In a green field I might argue for having an archvie_directory GUC
instead of archive_command. As it stands, it might be a really good
I would think we then would need both. archive_command with parameters
offers both.
idea to provide a pg_archiveto executable which takes as arguments a
directory path and the arguments passed to the archive script. With
a little extra effort, the executable could check for some file
which would specify what host and path should be writing archives
there, to avoid problems with copied database directories
accidentally writing to the same location as the source.Such an executable seems like minimal effort compared to the
problems it would solve.If there's an existing tool with appropriate licensing which is
sufficiently portable and reliable, all the better -- let's ship it
and use that for our example archive_command.
I would like for it not to be an example, but a default value.
Something ready for production but with a very narrow use case.
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On Fri, Sep 2, 2011 at 10:34 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
Dimitri Fontaine <dimitri@2ndQuadrant.fr> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
I'm also wondering if providing some shell script examples of a
fault-tolerant script to handle archiving would be useful.I think it would.
My usual advice is to avoid having to write one if possible,
because it's more complex than it looks. What about recommending
existing solutions, such as walmgr from Skytools?Even better, what about including a default archiving tool, that
could be either another script in bin/ or rather an internal
command. The default would accept a location as argument, for
simple needs you mount a remote filesystem and there you go. If
you need something more complex, you still can provide it
yourself.In a green field I might argue for having an archvie_directory GUC
instead of archive_command. As it stands, it might be a really good
idea to provide a pg_archiveto executable which takes as arguments a
directory path and the arguments passed to the archive script. With
a little extra effort, the executable could check for some file
which would specify what host and path should be writing archives
there, to avoid problems with copied database directories
accidentally writing to the same location as the source.Such an executable seems like minimal effort compared to the
problems it would solve.If there's an existing tool with appropriate licensing which is
sufficiently portable and reliable, all the better -- let's ship it
and use that for our example archive_command.
Another thought I have here is to wonder whether we should change
something on the server side so that we don't NEED such a complicated
archive_command. I mean, copying a file to a directory somewhere is
not fundamentally a complex operation. Nor is using ssh to copy it to
another machine. The fact that archive_commands need to be so complex
seems like a usability defect. The consensus seems to be that just
using something like 'cp' for your archive command won't work out
well, but maybe instead of shipping a more complicated script we
should be trying to eliminate (or at least reduce) the need for a more
complicated script.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 09/02/2011 01:00 PM, Robert Haas wrote:
On Fri, Sep 2, 2011 at 10:34 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:Dimitri Fontaine<dimitri@2ndQuadrant.fr> wrote:
Robert Haas<robertmhaas@gmail.com> writes:
I'm also wondering if providing some shell script examples of a
fault-tolerant script to handle archiving would be useful.I think it would.
My usual advice is to avoid having to write one if possible,
because it's more complex than it looks. What about recommending
existing solutions, such as walmgr from Skytools?Even better, what about including a default archiving tool, that
could be either another script in bin/ or rather an internal
command. The default would accept a location as argument, for
simple needs you mount a remote filesystem and there you go. If
you need something more complex, you still can provide it
yourself.In a green field I might argue for having an archvie_directory GUC
instead of archive_command. As it stands, it might be a really good
idea to provide a pg_archiveto executable which takes as arguments a
directory path and the arguments passed to the archive script. With
a little extra effort, the executable could check for some file
which would specify what host and path should be writing archives
there, to avoid problems with copied database directories
accidentally writing to the same location as the source.Such an executable seems like minimal effort compared to the
problems it would solve.If there's an existing tool with appropriate licensing which is
sufficiently portable and reliable, all the better -- let's ship it
and use that for our example archive_command.Another thought I have here is to wonder whether we should change
something on the server side so that we don't NEED such a complicated
archive_command. I mean, copying a file to a directory somewhere is
not fundamentally a complex operation. Nor is using ssh to copy it to
another machine. The fact that archive_commands need to be so complex
seems like a usability defect. The consensus seems to be that just
using something like 'cp' for your archive command won't work out
well, but maybe instead of shipping a more complicated script we
should be trying to eliminate (or at least reduce) the need for a more
complicated script.
The problem is that the number of ways you might want to do things is
quite large. For example, you might want to copy the archives to more
than one place for safety reasons. I pretty much always set
archive_command to a script which I can then customize to my heart's
content, and it seems to work pretty well. Providing a simple example of
such a script seems like it could be useful.
cheers
andrew
Robert Haas <robertmhaas@gmail.com> wrote:
maybe instead of shipping a more complicated script we should be
trying to eliminate (or at least reduce) the need for a more
complicated script.
That was the intent of my pg_archiveto suggestion. I'll amend it
(based on other comments) to allow for a URL as an alternative way
to specify the directory. So your archive_command might be:
'pg_archiveto /var/pgsql/backup/wal/ %p %f'
or:
'pg_archiveto http://backup-server/mydbserver/wal/ %p %f'
or maybe:
'pg_archiveto /mnt/someremotedirectory/ %p %f'
-Kevin
Andrew Dunstan <andrew@dunslane.net> wrote:
For example, you might want to copy the archives to more than one
place for safety reasons.
We've never felt that the way to do that was to put the logic for it
in the archive script -- we archive to a local directory and set up
rsync tasks on cron to distribute it. Otherwise you might not
archive to one target if the other is down, or might have trouble
catching up with a target when it comes back from being down.
-Kevin
On Fri, Sep 2, 2011 at 19:13, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
Andrew Dunstan <andrew@dunslane.net> wrote:
For example, you might want to copy the archives to more than one
place for safety reasons.We've never felt that the way to do that was to put the logic for it
in the archive script -- we archive to a local directory and set up
rsync tasks on cron to distribute it. Otherwise you might not
archive to one target if the other is down, or might have trouble
catching up with a target when it comes back from being down.
Archiving it locally will give you a window of lost data if you crash.
The point being - different people have different requirements, which
is one thing our currently solution is very good at catering to - the
downside being lots of work. I don't think dumbing down the system is
a good idea - but shipping an example script probably is.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Robert Haas <robertmhaas@gmail.com> writes:
Another thought I have here is to wonder whether we should change
something on the server side so that we don't NEED such a complicated
archive_command. I mean, copying a file to a directory somewhere is
not fundamentally a complex operation. Nor is using ssh to copy it to
another machine.
It is once you consider error handling and catering to N combinations of
user requirements.
I think the notion that we should get rid of archive_command in favor of
something more hard-wired is sheer lunacy. We have a nicely decoupled
arrangement for dealing with these issues now; why would we want to pull
them into the server?
Now, providing a more useful sample script is certainly reasonable.
regards, tom lane
On Fri, Sep 2, 2011 at 3:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
Another thought I have here is to wonder whether we should change
something on the server side so that we don't NEED such a complicated
archive_command. I mean, copying a file to a directory somewhere is
not fundamentally a complex operation. Nor is using ssh to copy it to
another machine.It is once you consider error handling and catering to N combinations of
user requirements.I think the notion that we should get rid of archive_command in favor of
something more hard-wired is sheer lunacy. We have a nicely decoupled
arrangement for dealing with these issues now; why would we want to pull
them into the server?
I wasn't really proposing to get rid of it, but I do wonder if there
are some configuration parameters we could add somewhere that would
make common cases easier without making really complex things
impossible.
Now, providing a more useful sample script is certainly reasonable.
Yep, so let's start with that.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
