Re: [GENERAL] Understanding PG9.0 streaming replication feature
On Wed, Jan 26, 2011 at 09:02:24PM -0500, Ray Stell wrote: On Wed, Jan 26, 2011 at 02:22:41PM -0800, Dan Birken wrote: Can you give some concrete suggestions on what needs to be added? The current documentation is here: It seems like there is a departure in postgresql/pg_hba.conf with 9.x at standby activation. I'm running 8.x pitr standbys with the same conf files on both systems. This is not possible in 9.x, right? After the standby is activated, don't you have to restart with the a different config file in order to get a new standby into recovery? I suggest a statement about actions needed after a standby is activated. This could be added in the 25.2.x somewhere. wal_level and max_wal_senders are not dynamic and are not set as needed on the new primary. Could this should be hacked into the activation code? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding PG9.0 streaming replication feature
Ben Carbery wrote: Thanks for the responses all, I have this working now. I had to create a base backup before copying to the standby for replication to start, but the main sticking point was actually understanding the terms and concepts involved.. I think the Binary Replication Tutorial page on the wiki basically explains everything. Unfortunately the actual pg manual is still about as clear as mud even though I now have a vague idea of how this all works. I think this is worth mentioning given the majority of the pg manual is actually of an unusually high standard - probably among the best technical manuals I have read in terms of being both comprehensive and concise, so it's a shame that this section doesn't meet that standard (IMO). Hopefully this will get a rewrite at some point! Can you give some concrete suggestions on what needs to be added? The current documentation is here: http://developer.postgresql.org/pgdocs/postgres/index.html -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding PG9.0 streaming replication feature
(I am not the OP, but recently went through the same thing so I'll chime in) Reading through the documentation now (albeit with a now pretty good understanding of how everything works), I think the main confusing thing is how different bits which apply to file-base log shipping, streaming replication and both of them are thrown together on this pagehttp://developer.postgresql.org/pgdocs/postgres/warm-standby.html, making it difficult to figure out what you need to know if you are just looking to implement streaming replication. For example, in the introduction section: Directly moving WAL records from one database server to another is typically described as log shipping. PostgreSQL implements file-based log shipping, which means that WAL records are transferred one file (WAL segment) at a time. WAL files (16MB) can be shipped easily and cheaply over any distance, whether it be to an adjacent system, another system at the same site, or another system on the far side of the globe. The bandwidth required for this technique varies according to the transaction rate of the primary server. Record-based log shipping is also possible with streaming replication (see Section 25.2.5http://developer.postgresql.org/pgdocs/postgres/warm-standby.html#STREAMING-REPLICATION ). It should be noted that the log shipping is asynchronous, i.e., the WAL records are shipped after transaction commit. As a result, there is a window for data loss should the primary server suffer a catastrophic failure; transactions not yet shipped will be lost. The size of the data loss window in file-based log shipping can be limited by use of the archive_timeout parameter, which can be set as low as a few seconds. However such a low setting will substantially increase the bandwidth required for file shipping. If you need a window of less than a minute or so, consider using streaming replication (see Section 25.2.5http://developer.postgresql.org/pgdocs/postgres/warm-standby.html#STREAMING-REPLICATION ). I colored things that apply to both in purple, that apply just to file-based log shipping in red, and that just apply to streaming replication in green. So if you are reading through this for the first time looking for information on streaming replication, it is very difficult to figure out some key points (it works by log-shipping, it is asynchronous), while avoiding stuff that you don't need to worry about (archive_timeout, WAL files are transferred one at a time, etc). I doubt I am the first person that is using postgres replication for the first time because of hot standbys and streaming replication, and I think the document is very poor for dealing with those people. Just looking at the coloring above, it looks very clearly like the document was written for file-based log shipping and then details about streaming replication are just appended at the end. The great thing about the wiki pagehttp://wiki.postgresql.org/wiki/Streaming_Replication (which I am assuming is the doc OP is referring to positively) is that it only includes details about streaming replication, thus you don't have to constantly be dodging information that doesn't apply to you. -Dan On Wed, Jan 26, 2011 at 7:04 AM, Bruce Momjian br...@momjian.us wrote: Ben Carbery wrote: Thanks for the responses all, I have this working now. I had to create a base backup before copying to the standby for replication to start, but the main sticking point was actually understanding the terms and concepts involved.. I think the Binary Replication Tutorial page on the wiki basically explains everything. Unfortunately the actual pg manual is still about as clear as mud even though I now have a vague idea of how this all works. I think this is worth mentioning given the majority of the pg manual is actually of an unusually high standard - probably among the best technical manuals I have read in terms of being both comprehensive and concise, so it's a shame that this section doesn't meet that standard (IMO). Hopefully this will get a rewrite at some point! Can you give some concrete suggestions on what needs to be added? The current documentation is here: http://developer.postgresql.org/pgdocs/postgres/index.html -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding PG9.0 streaming replication feature
Dan Birken wrote: (I am not the OP, but recently went through the same thing so I'll chime in) Reading through the documentation now (albeit with a now pretty good understanding of how everything works), I think the main confusing thing is how different bits which apply to file-base log shipping, streaming replication and both of them are thrown together on this pagehttp://developer.postgresql.org/pgdocs/postgres/warm-standby.html, making it difficult to figure out what you need to know if you are just looking to implement streaming replication. Nice idea to use color to highlight stuff. You are right that the streaming docs were added later, and seem out of place. I have applied the attached patch so the text is more fluid. Let me know if you have additional suggestions. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml index 717347c..ce3f264 100644 *** a/doc/src/sgml/high-availability.sgml --- b/doc/src/sgml/high-availability.sgml *** protocol to make nodes agree on a serial *** 486,503 para Directly moving WAL records from one database server to another is typically described as log shipping. productnamePostgreSQL/ !implements file-based log shipping, which means that WAL records are !transferred one file (WAL segment) at a time. WAL files (16MB) can be shipped easily and cheaply over any distance, whether it be to an adjacent system, another system at the same site, or another system on the far side of the globe. The bandwidth required for this technique varies according to the transaction rate of the primary server. !Record-based log shipping is also possible with streaming replication !(see xref linkend=streaming-replication). /para para !It should be noted that the log shipping is asynchronous, i.e., the WAL records are shipped after transaction commit. As a result, there is a window for data loss should the primary server suffer a catastrophic failure; transactions not yet shipped will be lost. The size of the --- 486,504 para Directly moving WAL records from one database server to another is typically described as log shipping. productnamePostgreSQL/ !implements file-based log shipping by transfering WAL records !one file (WAL segment) at a time. WAL files (16MB) can be shipped easily and cheaply over any distance, whether it be to an adjacent system, another system at the same site, or another system on the far side of the globe. The bandwidth required for this technique varies according to the transaction rate of the primary server. !Record-based log shipping is more granular and streams WAL changes !incrementally over a network connection (see xref !linkend=streaming-replication). /para para !It should be noted that log shipping is asynchronous, i.e., the WAL records are shipped after transaction commit. As a result, there is a window for data loss should the primary server suffer a catastrophic failure; transactions not yet shipped will be lost. The size of the *** protocol to make nodes agree on a serial *** 505,512 varnamearchive_timeout/varname parameter, which can be set as low as a few seconds. However such a low setting will substantially increase the bandwidth required for file shipping. !If you need a window of less than a minute or so, consider using !streaming replication (see xref linkend=streaming-replication). /para para --- 506,513 varnamearchive_timeout/varname parameter, which can be set as low as a few seconds. However such a low setting will substantially increase the bandwidth required for file shipping. !Streaming replication (see xref linkend=streaming-replication) !allows a much smaller window of data loss. /para para -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding PG9.0 streaming replication feature
On Wed, Jan 26, 2011 at 02:22:41PM -0800, Dan Birken wrote: Can you give some concrete suggestions on what needs to be added? The current documentation is here: http://developer.postgresql.org/pgdocs/postgres/index.html I had some difficulty getting the keepalives syntax in recovery.conf. http://developer.postgresql.org/pgdocs/postgres/warm-standby.html#STREAMING-REPLICATION might stand an example. When you click on the link there it takes you here: http://developer.postgresql.org/pgdocs/postgres/runtime-config-connection.html#GUC-TCP-KEEPALIVES-IDLE which doesn't help to much. Somewhere I found a comment that did it this way: primary_conninfo = 'host=test user=fred keepalives_idle= 60 ' The example I found for this in PostgreSQL 9 Admin Cookbook didn't fly: primary_conninfo = 'options=-c tcp_keepalives_idle= 60 ...' I didn't check the erratta. Just sayin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding PG9.0 streaming replication feature
Ray Stell wrote: On Wed, Jan 26, 2011 at 02:22:41PM -0800, Dan Birken wrote: Can you give some concrete suggestions on what needs to be added? The current documentation is here: http://developer.postgresql.org/pgdocs/postgres/index.html I had some difficulty getting the keepalives syntax in recovery.conf. http://developer.postgresql.org/pgdocs/postgres/warm-standby.html#STREAMING-REPLICATION might stand an example. When you click on the link there it takes you here: http://developer.postgresql.org/pgdocs/postgres/runtime-config-connection.html#GUC-TCP-KEEPALIVES-IDLE which doesn't help to much. Somewhere I found a comment that did it this way: primary_conninfo = 'host=test user=fred keepalives_idle= 60 ' The example I found for this in PostgreSQL 9 Admin Cookbook didn't fly: primary_conninfo = 'options=-c tcp_keepalives_idle= 60 ...' I didn't check the erratta. Just sayin Uh, the sentence is: On systems that support the keepalive socket option, setting tcp_keepalives_idle, tcp_keepalives_interval and tcp_keepalives_count helps the primary promptly notice a broken connection. I believe there they are talking about setting those values in the master/primary so you see the slave has disconnected --- this is not related to setting in the connection string, I think. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding PG9.0 streaming replication feature
On Wed, Jan 26, 2011 at 09:18:01PM -0500, Bruce Momjian wrote: Uh, the sentence is: On systems that support the keepalive socket option, setting tcp_keepalives_idle, tcp_keepalives_interval and tcp_keepalives_count helps the primary promptly notice a broken connection. I believe there they are talking about setting those values in the master/primary so you see the slave has disconnected --- this is not related to setting in the connection string, I think. Yep. Simon shows it the other way in his book: If you want a Standby to notice that the connection to the Master has dropped, you need to set the keepalives in the primary_conninfo in the recovery.conf That works, also. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding PG9.0 streaming replication feature
Thanks for the responses all, I have this working now. I had to create a base backup before copying to the standby for replication to start, but the main sticking point was actually understanding the terms and concepts involved.. I think the Binary Replication Tutorial page on the wiki basically explains everything. Unfortunately the actual pg manual is still about as clear as mud even though I now have a vague idea of how this all works. I think this is worth mentioning given the majority of the pg manual is actually of an unusually high standard - probably among the best technical manuals I have read in terms of being both comprehensive and concise, so it's a shame that this section doesn't meet that standard (IMO). Hopefully this will get a rewrite at some point! cheers, Ben
Re: [GENERAL] Understanding PG9.0 streaming replication feature
FYI, not looking for a detailed how to here.. I have read the manual twice and just can't figure which sections are relevant. The manual seems to be trying to cover all uses simultaneously which is always going to get confusing :) For example do I need I need WAL archiving or not? On Tue, Dec 21, 2010 at 2:40 PM, Ben Carbery ben.carb...@gmail.com wrote: Hi, I am having some trouble trying to figure out how to configure this particular scenario.. I have a pair of pg servers that I want to put in a Master/Standby configuration. Currently a script dumps the master db every hour, copies it to the standby, restores, and restarts the server. The aim is to replace the dumps/restores with streaming replication and ensure the standby is always up to date. In this case writes are infrequent, but reads are constant, and I only need high availability for reads. I would ideally like both master and standby to be available simultaneously to allow load-balancing. My confusion seems to be around the fact I don't need failover - my applications will detect a master down and immediately start using the standby, so there is no need to allow writes on the standby, they will just wait for the master to be available again - I am not sure what the minimum config needed for this scenario is.. cheers, Ben
Re: [GENERAL] Understanding PG9.0 streaming replication feature
Hi Ben, On 2010/12/22 7:46, Ben Carbery wrote: FYI, not looking for a detailed how to here.. I have read the manual twice and just can't figure which sections are relevant. The manual seems to be trying to cover all uses simultaneously which is always going to get confusing :) For example do I need I need WAL archiving or not? My blog entry would be a good entry point for you. :) 5 steps to implement a PostgreSQL replication system http://pgsnaga.blogspot.com/2010/05/5-steps-to-implement-postgresql.html It was written to be a guide for building a simple master-slave config. Please take a look, including the comments. Thanks, On 2010/12/22 7:46, Ben Carbery wrote: FYI, not looking for a detailed how to here.. I have read the manual twice and just can't figure which sections are relevant. The manual seems to be trying to cover all uses simultaneously which is always going to get confusing :) For example do I need I need WAL archiving or not? On Tue, Dec 21, 2010 at 2:40 PM, Ben Carbery ben.carb...@gmail.com mailto:ben.carb...@gmail.com wrote: Hi, I am having some trouble trying to figure out how to configure this particular scenario.. I have a pair of pg servers that I want to put in a Master/Standby configuration. Currently a script dumps the master db every hour, copies it to the standby, restores, and restarts the server. The aim is to replace the dumps/restores with streaming replication and ensure the standby is always up to date. In this case writes are infrequent, but reads are constant, and I only need high availability for reads. I would ideally like both master and standby to be available simultaneously to allow load-balancing. My confusion seems to be around the fact I don't need failover - my applications will detect a master down and immediately start using the standby, so there is no need to allow writes on the standby, they will just wait for the master to be available again - I am not sure what the minimum config needed for this scenario is.. cheers, Ben -- NAGAYASU Satoshi satoshi.nagay...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding PG9.0 streaming replication feature
Hi Ben, load balancing is not possible with the tools that are in the postgres installation. There is no automatic switch-over to a slave if the master fails. The trigger file needs to be created to make a slave to the master. This is not done automaitcally by postgres, but should be done by a cluster software (i.e. pacemaker). If you can live without load balancing, read the page of Satoshi. But the slaves can handle read requests (SELECT). Maybe this helps a little bit. Regards Matthias Am Mittwoch, 22. Dezember 2010, um 00:31:14 schrieb Satoshi Nagayasu: Hi Ben, On 2010/12/22 7:46, Ben Carbery wrote: FYI, not looking for a detailed how to here.. I have read the manual twice and just can't figure which sections are relevant. The manual seems to be trying to cover all uses simultaneously which is always going to get confusing :) For example do I need I need WAL archiving or not? My blog entry would be a good entry point for you. :) 5 steps to implement a PostgreSQL replication system http://pgsnaga.blogspot.com/2010/05/5-steps-to-implement-postgresql.html It was written to be a guide for building a simple master-slave config. Please take a look, including the comments. Thanks, On 2010/12/22 7:46, Ben Carbery wrote: FYI, not looking for a detailed how to here.. I have read the manual twice and just can't figure which sections are relevant. The manual seems to be trying to cover all uses simultaneously which is always going to get confusing :) For example do I need I need WAL archiving or not? On Tue, Dec 21, 2010 at 2:40 PM, Ben Carbery ben.carb...@gmail.com mailto:ben.carb...@gmail.com wrote: Hi, I am having some trouble trying to figure out how to configure this particular scenario.. I have a pair of pg servers that I want to put in a Master/Standby configuration. Currently a script dumps the master db every hour, copies it to the standby, restores, and restarts the server. The aim is to replace the dumps/restores with streaming replication and ensure the standby is always up to date. In this case writes are infrequent, but reads are constant, and I only need high availability for reads. I would ideally like both master and standby to be available simultaneously to allow load-balancing. My confusion seems to be around the fact I don't need failover - my applications will detect a master down and immediately start using the standby, so there is no need to allow writes on the standby, they will just wait for the master to be available again - I am not sure what the minimum config needed for this scenario is.. cheers, Ben -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding PG9.0 streaming replication feature
On Wed, Dec 22, 2010 at 8:31 AM, Satoshi Nagayasu satoshi.nagay...@gmail.com wrote: My blog entry would be a good entry point for you. :) 5 steps to implement a PostgreSQL replication system http://pgsnaga.blogspot.com/2010/05/5-steps-to-implement-postgresql.html Or http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial http://wiki.postgresql.org/wiki/Streaming_Replication#How_to_Use Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Understanding PG9.0 streaming replication feature
Hi, I am having some trouble trying to figure out how to configure this particular scenario.. I have a pair of pg servers that I want to put in a Master/Standby configuration. Currently a script dumps the master db every hour, copies it to the standby, restores, and restarts the server. The aim is to replace the dumps/restores with streaming replication and ensure the standby is always up to date. In this case writes are infrequent, but reads are constant, and I only need high availability for reads. I would ideally like both master and standby to be available simultaneously to allow load-balancing. My confusion seems to be around the fact I don't need failover - my applications will detect a master down and immediately start using the standby, so there is no need to allow writes on the standby, they will just wait for the master to be available again - I am not sure what the minimum config needed for this scenario is.. cheers, Ben
