Streaming Replication docs

On Fri, Feb 12, 2010 at 6:14 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:

So the new layout would be:

III. Server Administration

   ...
   21. Managing Databases
   22. Localization
   23. Routine Database Maintenance Tasks
   24. Backup and Restore
   25. High Availability, Load Balancing, and Replication
*    26. Recovery Configuration *
   27. Monitoring Database Activity
   28. Monitoring Disk Usage
   29. Reliability and the Write-Ahead Log
   30. Regression Tests

Thoughts, better ideas?

+1

Another change I'd like to make is to document the new built-in way (ie.
standby_mode='on', restore_command='cp ...', primary_conninfo not set)
as the primary way of implementing file-based log shipping. Using
pg_standby or similar scripts that do the waiting would still be
documented, but I'd like to de-emphasize it, moving it into an
"Alternative way to implement File-based log shipping" section. The
description would go along the lines of "An alternative way to implement
File-based log shipping is to leave standby_mode='false', and use a
restore_command that waits until the next WAL file arrives. This offers
more flexibility and control over the process. ..."

+1

We might need to add the following code of pg_standby into the core,
to prefer it for many cases.

#ifdef WIN32

/*
* Windows 'cp' sets the final file size before the copy is
* complete, and not yet ready to be opened by pg_standby. So we
* wait for sleeptime secs before attempting to restore. If that
* is not enough, we will rely on the retry/holdoff mechanism.
* GNUWin32's cp does not have this problem.
*/
pg_usleep(sleeptime * 1000000L);
#endif

Regards,

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

Fujii Masao wrote:

We might need to add the following code of pg_standby into the core,
to prefer it for many cases.

#ifdef WIN32

/*
* Windows 'cp' sets the final file size before the copy is
* complete, and not yet ready to be opened by pg_standby. So we
* wait for sleeptime secs before attempting to restore. If that
* is not enough, we will rely on the retry/holdoff mechanism.
* GNUWin32's cp does not have this problem.
*/
pg_usleep(sleeptime * 1000000L);
#endif

That's actually a bit questionable, always has been even in pg_standby.
It adds a constant 1 s delay to the recovery each WAL file, which
effectively rate-limits the WAL recovery to 16MB per second. I think we
should rather add a warning to the docs, suggesting the copy-then-rename
method on Windows.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Fri, Feb 12, 2010 at 7:15 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:

That's actually a bit questionable, always has been even in pg_standby.
It adds a constant 1 s delay to the recovery each WAL file, which
effectively rate-limits the WAL recovery to 16MB per second. I think we
should rather add a warning to the docs, suggesting the copy-then-rename
method on Windows.

Right. Adding a warning to the docs looks enough.

Regards,

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

On 2/12/10 1:14 AM, Heikki Linnakangas wrote:

It's pretty clear that we should have a single place that documents all
the recovery.conf options. I suggest that we move the "24.3.3.1 Recovery
Settings" section after "High Availability, Load Balancing, and
Replication", and add the new streaming replication related options
there. The previous sections would still briefly describe the most
important settings and give examples, but the new section would be the
authoritative reference page for recovery.conf.

I'd also suggest that we should have a cross-reference from Server
Runtime Settings in Administration.

--Josh Berkus

Heikki,

Crossing this thread over to pgsql-docs, where I think it actually belongs.

In addition to the changes you've proposed, one thing our docs could
really use is a single reference page which we could go to for all of
the .conf files. Right now, you need to rely on postgresql.org doc
search in order to find, for example, pg_hba.conf.

I think it would be good to put into server administration somewhere a
single page called "Configuration Files" which references:
postgresql.conf
pg_hba.conf
recovery.conf
pg_ident.conf
... hmmm, am I missing one?

--Josh Berkus

On Fri, 2010-02-12 at 10:22 -0800, Josh Berkus wrote:

Heikki,

Crossing this thread over to pgsql-docs, where I think it actually belongs.

In addition to the changes you've proposed, one thing our docs could
really use is a single reference page which we could go to for all of
the .conf files. Right now, you need to rely on postgresql.org doc
search in order to find, for example, pg_hba.conf.

I think it would be good to put into server administration somewhere a
single page called "Configuration Files" which references:
postgresql.conf
pg_hba.conf
recovery.conf
pg_ident.conf
... hmmm, am I missing one?

Seems that should go... under "Reference"

Joshua D. Drake

--Josh Berkus

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.

On Fri, Feb 12, 2010 at 9:14 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:

One glaring issue with the current documentation layout is that the
documentation for the various options in recovery.conf is split in two
places.

I've been trying to explain to someone how to set this all up and they
keep asking me why there's a separate recovery.conf file. I thought it
made sense back when it was to control PITR and was a transient file
which only applied as long as it was recovering but it seems now to
have become part of the normal setup of the server.

It seems like it would be much easier to keep things straight if you
could set up the master and all the slaves identically and they would
know when they had to store or retrieve files from the WAL archive and
when they had to make or listen for streaming connections based on
their master/slave status -- which is the only bit which needs to be
stored outside the configuration so it can be changed dynamically.

The master/slave status would be indicated by a trigger file or some
external master election daemon connecting and issuing a query calling
a magic sql function.

But having the separate recovery.conf seems kind of pointless legacy
structure at this point.
--
greg

On 2/13/10 10:17 AM, Greg Stark wrote:

But having the separate recovery.conf seems kind of pointless legacy
structure at this point.

Agreed, but I've been trying to minimize my tinkering with HS/SR.
Several things would need to be changed to get rid of Recovery.conf for
HS and PITR.

I expect that we'll get rid of Recovery.conf, and fold it into
PostgreSQL.conf, in 9.1.

--Josh Berkus

On fre, 2010-02-12 at 11:14 +0200, Heikki Linnakangas wrote:

So the new layout would be:

III. Server Administration

...
21. Managing Databases
22. Localization
23. Routine Database Maintenance Tasks
24. Backup and Restore
25. High Availability, Load Balancing, and Replication
* 26. Recovery Configuration *
27. Monitoring Database Activity
28. Monitoring Disk Usage
29. Reliability and the Write-Ahead Log
30. Regression Tests

Thoughts, better ideas?

Seems like 25 and 26 should be the same chapter. And chapter 29 should
perhaps come before them (and probably before 24 as well).

Joshua D. Drake wrote:

On Fri, 2010-02-12 at 10:22 -0800, Josh Berkus wrote:

In addition to the changes you've proposed, one thing our docs could
really use is a single reference page which we could go to for all of
the .conf files. Right now, you need to rely on postgresql.org doc
search in order to find, for example, pg_hba.conf.

I think it would be good to put into server administration somewhere a
single page called "Configuration Files" which references:
postgresql.conf
pg_hba.conf
recovery.conf
pg_ident.conf
... hmmm, am I missing one?

Seems that should go... under "Reference"

Seems like a good idea.

Unfortunately my SGML-skills are too weak to do that, so here's a patch
to for my original proposal. There's little text changes, mostly just
moves sections around. I'm thinking of committing this now; someone else
will have to do the above reorganization if we want it.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com