Streaming Replication docs

One glaring issue with the current documentation layout is that the
documentation for the various options in recovery.conf is split in two
places. The old options, restore_command, recovery_target_* and so forth
are in section "24.3.3.1 Recovery Settings", while the new streaming
replication related options are in "25.3.1 Setup [of streaming
replication]".

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.

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?

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. ..."

The reason for that is that it would make the documentation flow better
with Streaming Replication. In a nutshell, there would be three steps to
set up a full streaming replication system:

1. Set up WAL archiving in master (section "24.3 Continuous Archiving
and Point-In-Time Recovery")
2. Use the WAL archive to implement file-based log shipping (Section
"25.2 File-based Log Shipping")
3. Add streaming replication to the file-based log shipping (Section
25.3 Streaming Replication") setup.

Each section could then simply refer to the previous step: "first set up
... as described in section ...". The new way of setting up file-based
log shipping is a little bit easier than pg_standby to set up anyway
(not that pg_standby is hard either), so it would be good to describe
the simpler method first anyway.

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

On Fri, Feb 12, 2010 at 6:14 PM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)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(dot)linnakangas(at)enterprisedb(dot)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(dot)linnakangas(at)enterprisedb(dot)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