[DOCS] Streaming replication and document about avoiding WAL-logging
Hi, In 9.0, some SQL commands can avoid WAL-logging only when not only archive_mode is off but also max_wal_senders is zero (new condition since 9.0). But I forgot modifying the documents about this behavior enough :( The attached patch adds the description about that new condition for WAL-logging. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center doc_skip_logging_v1.patch Description: Binary data -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [DOCS] Streaming replication document improvements
Fujii Masao wrote: > *** > *** 829,834 if (!triggered) > --- 826,834 > > Set the maximum number of concurrent connections from the standby > servers > (see for details). > +Since those connections are for superusers, > + should be > +set accordingly. > > > That's an interesting point, do streaming replication connections consume superuser_reserved_connections slots? How should superuser_reserved_connections be set? > *** a/src/backend/access/transam/recovery.conf.sample > --- b/src/backend/access/transam/recovery.conf.sample > *** > *** 88,94 > #recovery_target_timeline = '33'# number or 'latest' > # > #--- > ! # LOG-STREAMING REPLICATION PARAMETERS > #--- > # > # When standby_mode is enabled, the PostgreSQL server will work as > --- 88,94 > #recovery_target_timeline = '33'# number or 'latest' > # > #--- > ! # STANDBY SERVER PARAMETERS > #--- > # > # When standby_mode is enabled, the PostgreSQL server will work as Hmm, that makes the HOT STANDBY notice after that section look weird: > #--- > # HOT STANDBY PARAMETERS > #--- > # > # Hot Standby related parameters are listed in postgresql.conf > # > #--- Do we need that notice? Maybe just move that sentence to the "STANDBY SERVER PARAMETERS" section. I just committed a patch to move around the high-availability sections a bit. That caused conflicts with this patch, so I picked the changes from the patch and applied them over the new layout, and I also did a lot of other editing. So, I committed most parts of this patch (except the above), with a lot of changes to fix the bit-rot, and also other editing to my liking. I hope I made it better not worse. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [DOCS] confusing archive_command example
Josh Kupershmidt wrote: > Hi, > On the page: > http://www.postgresql.org/docs/current/interactive/continuous-archiving.html#BACKUP-ARCHIVING-WAL > > an example archive_command of: > archive_command = 'cp -i %p /mnt/server/archivedir/%f > is given. Then, a few lines later, an example archive command of: > archive_command = 'test ! -f .../%f && cp %p .../%f' > > is given. I think this second command would be more clear if it used > "/mnt/server/archivedir/" instead of "...", to tie in with the > previous archive_command. It took me a minute to figure out that the > three dots were supposed to be an ellipsis instead of a typo for the > parent directory "..". I agree our use of cp -i http://momjian.us EnterpriseDB http://enterprisedb.com Index: doc/src/sgml/backup.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/backup.sgml,v retrieving revision 2.144 diff -c -c -r2.144 backup.sgml *** doc/src/sgml/backup.sgml 22 Feb 2010 17:15:10 - 2.144 --- doc/src/sgml/backup.sgml 31 Mar 2010 23:34:20 - *** *** 604,614 directory). It is advisable to test your proposed archive command to ensure that it indeed does not overwrite an existing file, and that it returns ! nonzero status in this case. We have found that cp -i does ! this correctly on some platforms but not others. If the chosen command ! does not itself handle this case correctly, you should add a command ! to test for existence of the archive file. For example, something ! like: archive_command = 'test ! -f .../%f && cp %p .../%f' --- 604,615 directory). It is advisable to test your proposed archive command to ensure that it indeed does not overwrite an existing file, and that it returns ! nonzero status in this case. On many Unix platforms, cp ! -i causes copy to prompt before overwriting a file, and ! < /dev/null causes the prompt (and overwriting) to ! fail. If your platform does not support this behavior, you should ! add a command to test for the existence of the archive file. For ! example, something like: archive_command = 'test ! -f .../%f && cp %p .../%f' -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [DOCS] confusing archive_command example
Bruce Momjian writes: > As far as "...", those are used to show only the important changes in > the string, which I think is the right approach, but I did change the > line so the dots are not right up against slashes: > archive_command = 'test ! -f ... %f && cp %p ... %f' This is *not* an improvement, because it makes it look like the %f is a separate command argument. Please revert if you can't think of something better. regards, tom lane -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [DOCS] confusing archive_command example
Tom Lane wrote: > Bruce Momjian writes: > > As far as "...", those are used to show only the important changes in > > the string, which I think is the right approach, but I did change the > > line so the dots are not right up against slashes: > > archive_command = 'test ! -f ... %f && cp %p ... %f' > > This is *not* an improvement, because it makes it look like the %f is a > separate command argument. Please revert if you can't think of > something better. What about: archive_command = 'test ! -f ...%f && cp %p ...%f' -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [DOCS] confusing archive_command example
Bruce Momjian writes: > What about: > archive_command = 'test ! -f ...%f && cp %p ...%f' Perhaps, though I still think this isn't an improvement over the original. regards, tom lane -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [DOCS] confusing archive_command example
Tom Lane wrote: > Bruce Momjian writes: > > What about: > > archive_command = 'test ! -f ...%f && cp %p ...%f' > > Perhaps, though I still think this isn't an improvement over the > original. His complaint was that .../%f looks like ../%f; is that a valid concern? I have reverted the change. Also, should we be using test ! -e instead of -f? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [DOCS] confusing archive_command example
Bruce Momjian writes: > His complaint was that .../%f looks like ../%f; is that a valid > concern? Well, it does look like it, I'm just not seeing an easy fix that makes that better. I think the original suggestion was to turn it into a concrete example by writing something like /mnt/archive/%f. > I have reverted the change. Also, should we be using test ! > -e instead of -f? No opinion. regards, tom lane -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [DOCS] confusing archive_command example
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > As far as "...", those are used to show only the important changes in > the string, which I think is the right approach, but I did change the > line so the dots are not right up against slashes: > > archive_command = 'test ! -f ... %f && cp %p ... %f' The triple dots need to be removed entirely, too confusing. Write everything out. - -- Greg Sabino Mullane [email protected] End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201003312007 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkuz5AwACgkQvJuQZxSWSshGMACdEBRm5VScsv4w2fGTZTTr3b19 Zj8AoIFV9exrwQfO8t8MSwL0BXx41ZCU =VP+w -END PGP SIGNATURE- -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [DOCS] confusing archive_command example
Tom Lane wrote: > Bruce Momjian writes: > > His complaint was that .../%f looks like ../%f; is that a valid > > concern? > > Well, it does look like it, I'm just not seeing an easy fix that makes > that better. I think the original suggestion was to turn it into a > concrete example by writing something like /mnt/archive/%f. > > > I have reverted the change. Also, should we be using test ! > > -e instead of -f? > > No opinion. Well -e tests for any type of file, while -f is only for regular files. In practice, there should only be regular files in the archive directory. But because we are always super-cautious, I changed it to -e. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [DOCS] Streaming replication document improvements
On Thu, Apr 1, 2010 at 5:39 AM, Heikki Linnakangas wrote: > Fujii Masao wrote: >> *** >> *** 829,834 if (!triggered) >> --- 826,834 >> >> Set the maximum number of concurrent connections from the standby >> servers >> (see for details). >> + Since those connections are for superusers, >> + should be >> + set accordingly. >> >> >> > > That's an interesting point, do streaming replication connections > consume superuser_reserved_connections slots? Yes. Since SR connection is superuser connection, setting superuser_reserved_connections appropriately would be useful to prevent non-superuser connections from blocking the connection from the standby. > How should > superuser_reserved_connections be set? Well.. setting the number of superuser connection required for maintenance plus max_wal_senders seems to be reasonable. >> *** a/src/backend/access/transam/recovery.conf.sample >> --- b/src/backend/access/transam/recovery.conf.sample >> *** >> *** 88,94 >> #recovery_target_timeline = '33' # number or 'latest' >> # >> >> #--- >> ! # LOG-STREAMING REPLICATION PARAMETERS >> >> #--- >> # >> # When standby_mode is enabled, the PostgreSQL server will work as >> --- 88,94 >> #recovery_target_timeline = '33' # number or 'latest' >> # >> >> #--- >> ! # STANDBY SERVER PARAMETERS >> >> #--- >> # >> # When standby_mode is enabled, the PostgreSQL server will work as > > Hmm, that makes the HOT STANDBY notice after that section look weird: > >> #--- >> # HOT STANDBY PARAMETERS >> #--- >> # >> # Hot Standby related parameters are listed in postgresql.conf >> # >> #--- > > Do we need that notice? Maybe just move that sentence to the "STANDBY > SERVER PARAMETERS" section. I don't think that the notice is necessary. But I agree to the move. > I just committed a patch to move around the high-availability sections a > bit. That caused conflicts with this patch, so I picked the changes from > the patch and applied them over the new layout, and I also did a lot of > other editing. So, I committed most parts of this patch (except the > above), with a lot of changes to fix the bit-rot, and also other editing > to my liking. I hope I made it better not worse. Thanks a lot! doc/src/sgml/monitoring.sgml > +In streaming replication (see for > details), > +WAL sender process is listed in the ps display on the > primary server. > +The form of its command line display is: > + > + postgres: wal sender process user host > streaming location > + > +The user and host items remain the same for the life of the replication > connection. > +The location indicates how far WAL sender process has sent the WAL. > +On the other hand, WAL receiver process is listed in the ps > display > +on the standby server. The form of its command line display is: > + > + postgres: wal receiver process streaming location > + > +The location indicates how far WAL receiver has received the WAL. > + > + > + The original patch includes the above change for monitoring.sgml, but it's been excluded from the commit. You think that it's not worth applying the change? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [HACKERS] [DOCS] Streaming replication document improvements
On Thu, Apr 1, 2010 at 10:52 AM, Robert Haas wrote: > On Wed, Mar 31, 2010 at 9:50 PM, Fujii Masao wrote: >>> That's an interesting point, do streaming replication connections >>> consume superuser_reserved_connections slots? >> >> Yes. Since SR connection is superuser connection, setting >> superuser_reserved_connections appropriately would be useful >> to prevent non-superuser connections from blocking the connection >> from the standby. > > I think this is wacky, especially since we'd someday like to have > replication be a separate privilege from superuser. I think we should > change this. You mean that we should change replication connection not to consume superuser_reserved_connections slots in 9.0? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [HACKERS] [DOCS] Streaming replication document improvements
On Thu, Apr 1, 2010 at 11:00 AM, Robert Haas wrote: > On Wed, Mar 31, 2010 at 9:58 PM, Fujii Masao wrote: >> On Thu, Apr 1, 2010 at 10:52 AM, Robert Haas wrote: >>> On Wed, Mar 31, 2010 at 9:50 PM, Fujii Masao wrote: > That's an interesting point, do streaming replication connections > consume superuser_reserved_connections slots? Yes. Since SR connection is superuser connection, setting superuser_reserved_connections appropriately would be useful to prevent non-superuser connections from blocking the connection from the standby. >>> >>> I think this is wacky, especially since we'd someday like to have >>> replication be a separate privilege from superuser. I think we should >>> change this. >> >> You mean that we should change replication connection not to consume >> superuser_reserved_connections slots in 9.0? > > Yes. Preventing superuser connections from consuming superuser_reserved_connections slots seems strange for me. So I'm leaning toward just removing superuser privilege from replication connection again. Thought? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
Re: [DOCS] confusing archive_command example
On ons, 2010-03-31 at 19:51 -0400, Bruce Momjian wrote: > Also, should we be using test ! -e instead of -f? test -e is not portable. -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
