pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feed9.2 documentation/configuration question
5+ messages / 4 participants
[nested] [flat]
* 9.2 documentation/configuration question
@ 2014-12-02 19:59 John Scalia <jayknowsunix@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: John Scalia @ 2014-12-02 19:59 UTC (permalink / raw)
To: pgsql-admin
Hi All,
I've been asked to assist another group in our company and their db configuration is a little confusing to me. The database is not replicated, but it is running WAL archiving. I've
been looking at http://www.postgresql.org/docs/9.2/static/wal-configuration.html and my confusion regards the 4th paragraph:
"The server's checkpointer process automatically performs a checkpoint every so often. A checkpoint is created every checkpoint_segments log segments, or every checkpoint_timeout
seconds, whichever comes first. The default settings are 3 segments and 300 seconds (5 minutes), respectively. In cases where no WAL has been written since the previous checkpoint,
new checkpoints will be skipped even if checkpoint_timeout has passed. If WAL archiving is being used and you want to put a lower limit on how often files are archived in order to
bound potential data loss, you should adjust archive_timeout parameter rather than the checkpoint parameters..."
So, are docs saying that checkpoint_timeout is ignored if you have a value for archive_timeout or is it still used? This server currently has:
checkpoint_segments=32
checkpoint_timeout = 5min #default
archive_timeout=3600
and I'm not sure these make sense together in light of this paragraph.
--
Jay
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 9.2 documentation/configuration question
@ 2014-12-02 21:03 Igor Neyman <ineyman@perceptron.com>
parent: John Scalia <jayknowsunix@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Igor Neyman @ 2014-12-02 21:03 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; pgsql-admin
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of John Scalia
Sent: Tuesday, December 02, 2014 3:00 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] 9.2 documentation/configuration question
Hi All,
I've been asked to assist another group in our company and their db configuration is a little confusing to me. The database is not replicated, but it is running WAL archiving. I've been looking at http://www.postgresql.org/docs/9.2/static/wal-configuration.html and my confusion regards the 4th paragraph:
"The server's checkpointer process automatically performs a checkpoint every so often. A checkpoint is created every checkpoint_segments log segments, or every checkpoint_timeout seconds, whichever comes first. The default settings are 3 segments and 300 seconds (5 minutes), respectively. In cases where no WAL has been written since the previous checkpoint, new checkpoints will be skipped even if checkpoint_timeout has passed. If WAL archiving is being used and you want to put a lower limit on how often files are archived in order to bound potential data loss, you should adjust archive_timeout parameter rather than the checkpoint parameters..."
So, are docs saying that checkpoint_timeout is ignored if you have a value for archive_timeout or is it still used? This server currently has:
checkpoint_segments=32
checkpoint_timeout = 5min #default
archive_timeout=3600
and I'm not sure these make sense together in light of this paragraph.
--
Jay
No, documentation says that even if WAL was not written, WAL archive will be created on archive_timeout, in your case at least once per hour.
Regards,
Igor Neyman
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 9.2 documentation/configuration question
@ 2014-12-02 21:42 David G Johnston <david.g.johnston@gmail.com>
parent: John Scalia <jayknowsunix@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: David G Johnston @ 2014-12-02 21:42 UTC (permalink / raw)
To: pgsql-admin
Jay at Verizon wrote
> checkpoint_timeout = 5min #default
This impacts the primary machine only and ensures that regardless of the
lack of activity a crash will only need to REDO that last five minutes of
history since everything prior to that will already have been persisted to
the data files.
> archive_timeout=3600
This impacts how far, in time, the archive can be behind the master in a
low-volume situation. This is because only complete (16MB) WAL files are
shipped. In a low volume situation it may take several active
checkpoint_timeout periods (not to mention zero activity periods) to
generate enough data to fill up a single 16MB file.
In a low volume situation a single WAL will have multiple checkpoint
records. In a high volume situation there will be many WAL files that have
no checkpoint records. This later situation being the reason for the
"checkpoint_segments" parameter.
So the archive will never be more than 16MB behind the master but the
corresponding time period is unbounded in the absence of archive_timeout.
The master will always be up-to-date but the amount of time needed for
recovery is bounded either by time or size - which ever one comes first.
David J.
--
View this message in context: http://postgresql.nabble.com/9-2-documentation-configuration-question-tp5828964p5828990.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 9.2 documentation/configuration question
@ 2014-12-02 22:06 David G Johnston <david.g.johnston@gmail.com>
parent: John Scalia <jayknowsunix@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: David G Johnston @ 2014-12-02 22:06 UTC (permalink / raw)
To: pgsql-admin
Jay at Verizon wrote
> and I'm not sure these make sense together in light of this paragraph.
While not a severe need this section would be well served if it were divided
into:
x.1: Primary - Checkpoint Configuration
x.2: Archive & Replication
Addressing the apparent confusion that people may have that the checkpoint
parameters impact the recency of the archive is necessary but probably
deserves more than a byline in a paragraph explaining exactly how the two
bounding checkpoint parameters function - a concept complex enough by
itself.
x.2 could also be separated into log shipping and warm/hot standby
subsections. They probably do not warrant separation into their own
top-level sections but physically separating and framing them within this
section would introduce the needed mental boundaries between the pieces that
affect primary operations and those that are specific to whatever form of
replication/archive the user may be using.
I'll add this my own doc patches listing but I'm not expecting any immediate
attention right now.
David J.
--
View this message in context: http://postgresql.nabble.com/9-2-documentation-configuration-question-tp5828964p5828994.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 9.2 documentation/configuration question
@ 2014-12-02 22:39 Scott Ribe <scott_ribe@elevated-dev.com>
parent: David G Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Scott Ribe @ 2014-12-02 22:39 UTC (permalink / raw)
To: David G Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin
On Dec 2, 2014, at 3:06 PM, David G Johnston <david.g.johnston@gmail.com> wrote:
>
> x.2 could also be separated into log shipping and warm/hot standby
> subsections. They probably do not warrant separation into their own
> top-level sections but physically separating and framing them within this
> section would introduce the needed mental boundaries between the pieces that
> affect primary operations and those that are specific to whatever form of
> replication/archive the user may be using.
I've had similar thoughts, that it seemed confusing based on log shipping and streaming, and the setup for each, being not sufficiently distinguished.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2014-12-02 22:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-12-02 19:59 9.2 documentation/configuration question John Scalia <jayknowsunix@gmail.com>
2014-12-02 21:03 ` Igor Neyman <ineyman@perceptron.com>
2014-12-02 21:42 ` David G Johnston <david.g.johnston@gmail.com>
2014-12-02 22:06 ` David G Johnston <david.g.johnston@gmail.com>
2014-12-02 22:39 ` Scott Ribe <scott_ribe@elevated-dev.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox