Skip site navigation (1)
Skip section navigation (2)
Peripheral Links
Re: PostgreSQL Replication with read-only access to standby DB
Our organization is looking for a hot-standby option for PostgreSQL that uses the WAL (transaction) data to keep the standby current and also allows the standby to be read-only accessible for reporting. We have implemented WAL shipping through a set of scripts we developed and that works well to have a standby DB on the ready in case we need to fail over, but we are looking to increase the value of the standby server by making it available for queries. Because of the complexities of our environment using a table/trigger based replication method such as Slony won¹t work well. It would be great if there was a solution (Open Source or Commercial) that worked in a similar manner as Oracle Active Data Guard: ³Oracle Active Data Guard enables a physical standby database to be open for read-only access for reporting, simple or complex queries while changes from the production database are being applied to it. This means any operation that requires up-to-date read-only access can be offloaded to the replica, enhancing and protecting the performance of the production database.² ³All queries reading from the physical replica execute in real-time, and return current results. A Data Guard configuration consists of one production (or primary) database and up to nine standby databases. A standby database is initially created from a backup copy of the primary database. Once created, Data Guard automatically maintains the standby database as a synchronized copy of the primary database by transmitting primary database redo data to the standby system and then applying the redo data to the standby database.² Does anyone know of such a solution for PostgreSQL? Thanks, Keaton
Our organization is looking for a hot-standby option for PostgreSQL that uses the WAL (transaction) data to keep the standby current and also allows the standby to be read-only accessible for reporting. We have implemented WAL shipping through a set of scripts we developed and that works well to have a standby DB on the ready in case we need to fail over, but we are looking to increase the value of the standby server by making it available for queries. Because of the complexities of our environment using a table/trigger based replication method such as Slony won¹t work well. It would be great if there was a solution (Open Source or Commercial) that worked in a similar manner as Oracle Active Data Guard: ³Oracle Active Data Guard enables a physical standby database to be open for read-only access for reporting, simple or complex queries while changes from the production database are being applied to it. This means any operation that requires up-to-date read-only access can be offloaded to the replica, enhancing and protecting the performance of the production database.² ³All queries reading from the physical replica execute in real-time, and return current results. A Data Guard configuration consists of one production (or primary) database and up to nine standby databases. A standby database is initially created from a backup copy of the primary database. Once created, Data Guard automatically maintains the standby database as a synchronized copy of the primary database by transmitting primary database redo data to the standby system and then applying the redo data to the standby database.² Does anyone know of such a solution for PostgreSQL? Thanks, Keaton
Keaton Adams wrote: > Our organization is looking for a hot-standby option for PostgreSQL that > uses the WAL (transaction) data to keep the standby current and also allows > the standby to be read-only accessible for reporting. We have implemented > WAL shipping through a set of scripts we developed and that works well to > have a standby DB on the ready in case we need to fail over, but we are > looking to increase the value of the standby server by making it available > for queries. Because of the complexities of our environment using a > table/trigger based replication method such as Slony won¹t work well. > > It would be great if there was a solution (Open Source or Commercial) that > worked in a similar manner as Oracle Active Data Guard: > > ³Oracle Active Data Guard enables a physical standby database to be open for > read-only access for reporting, simple or complex queries while changes > from the production database are being applied to it. This means any > operation that requires up-to-date read-only access can be offloaded to the > replica, enhancing and protecting the performance of the production > database.² > > ³All queries reading from the physical replica execute in real-time, and > return current results. A Data Guard configuration consists of one > production (or primary) database and up to nine standby databases. A > standby database is initially created from a backup copy of the primary > database. Once created, Data Guard automatically maintains the standby > database as a synchronized copy of the primary database by transmitting > primary database redo data to the standby system and then applying the redo > data to the standby database.² > > Does anyone know of such a solution for PostgreSQL? > > Thanks, > > Keaton > IIRC, it was mentioned previously in one posting that this a TODO for a future version of postgres but not something that's expected soon. Someone please correct me if I'm wrong. -salman
On Tue, Mar 25, 2008 at 1:11 PM, Keaton Adams <kadams(at)mxlogic(dot)com> wrote: > Our organization is looking for a hot-standby option for PostgreSQL that > uses the WAL (transaction) data to keep the standby current and also allows > the standby to be read-only accessible for reporting. We have implemented > WAL shipping through a set of scripts we developed and that works well to > have a standby DB on the ready in case we need to fail over, but we are > looking to increase the value of the standby server by making it available > for queries. Because of the complexities of our environment using a > table/trigger based replication method such as Slony won't work well. > > It would be great if there was a solution (Open Source or Commercial) that > worked in a similar manner as Oracle Active Data Guard: > > Does anyone know of such a solution for PostgreSQL? I think this does what you want. http://commandprompt.com/products/mammothreplicator/ -- Regards, Richard Broersma Jr.
On Tue, Mar 25, 2008 at 1:17 PM, salman <salmanb(at)quietcaresystems(dot)com> wrote: > IIRC, it was mentioned previously in one posting that this a TODO for a > future version of postgres but not something that's expected soon. > > Someone please correct me if I'm wrong. This is what I saw on the TODO list: Write-Ahead Log * Allow WAL traffic to be streamed to another server for stand-by replication -- Regards, Richard Broersma Jr.
It is close, but has limitations that will be problematic for our environment, such as: Replicator will not replicate the schema. You must restore your schema to th e slaves from the master before you begin replication. Replicator can only replicate one database. If you have multiple databases y ou can either initialize clusters for each database or move all databases into a si ngle database using schemas/namespaces. It is possible to add and drop columns to replicated tables within Replicato r. This type of change to your table structure will require a full sync and therefore is best done in batch or after hours. Thanks for the reply, Keaton On 3/25/08 2:18 PM, "Richard Broersma" <richard(dot)broersma(at)gmail(dot)com> wrote: > On Tue, Mar 25, 2008 at 1:11 PM, Keaton Adams <kadams(at)mxlogic(dot)com> wrote: >> Our organization is looking for a hot-standby option for PostgreSQL that >> uses the WAL (transaction) data to keep the standby current and also allows >> the standby to be read-only accessible for reporting. We have implemented >> WAL shipping through a set of scripts we developed and that works well to >> have a standby DB on the ready in case we need to fail over, but we are >> looking to increase the value of the standby server by making it available >> for queries. Because of the complexities of our environment using a >> table/trigger based replication method such as Slony won't work well. >> >> It would be great if there was a solution (Open Source or Commercial) that >> worked in a similar manner as Oracle Active Data Guard: >> >> Does anyone know of such a solution for PostgreSQL? > > I think this does what you want. > > http://commandprompt.com/products/mammothreplicator/
But will that stand-by replication provide for a read-only slave? On 3/25/08 2:26 PM, "Richard Broersma" <richard(dot)broersma(at)gmail(dot)com> wrote: > On Tue, Mar 25, 2008 at 1:17 PM, salman <salmanb(at)quietcaresystems(dot)com> wrote: >> IIRC, it was mentioned previously in one posting that this a TODO for a >> future version of postgres but not something that's expected soon. >> >> Someone please correct me if I'm wrong. > > This is what I saw on the TODO list: > Write-Ahead Log > * Allow WAL traffic to be streamed to another server for stand-by > replication >
On Tue, 2008-03-25 at 14:11 -0600, Keaton Adams wrote: > “All queries reading from the physical replica execute in real-time, > and return current results. A Data Guard configuration consists of > one production (or primary) database and up to nine standby > databases. A standby database is initially created from a backup copy > of the primary database. Once created, Data Guard automatically > maintains the standby database as a synchronized copy of the primary > database by transmitting primary database redo data to the standby > system and then applying the redo data to the standby database.” > > Does anyone know of such a solution for PostgreSQL? > There has been work in this area, but unfortunately this feature has not been completed yet. Many people would like this feature. I have heard of a strategy used by some PostgreSQL users in which they use something like ZFS (which allows fast filesystem snapshots/clones) to quickly clone the data in a separate area, and then bring up the database on the clone for reporting purposes. This obviously does not work in real time, but it may be useful. It does not require a lot of additional space to do this because of the ZFS copy-on-write implementation. Regards, Jeff Davis
On Tue, 2008-03-25 at 14:11 -0600, Keaton Adams wrote: > “Oracle Active Data Guard enables a physical standby database to be > open for read-only access – for reporting, simple or complex queries – > while changes from the production database are being applied to it. > This means any operation that requires up-to-date read-only access can > be offloaded to the replica, enhancing and protecting the performance > of the production database.” > > “All queries reading from the physical replica execute in real-time, > and return current results. A Data Guard configuration consists of > one production (or primary) database and up to nine standby > databases. A standby database is initially created from a backup copy > of the primary database. Once created, Data Guard automatically > maintains the standby database as a synchronized copy of the primary > database by transmitting primary database redo data to the standby > system and then applying the redo data to the standby database.” > > Does anyone know of such a solution for PostgreSQL? Some funding would help that move forwards. If you or others would consider that, it would help, even if just to provide the seed for additional contributors. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk
On Tue, Mar 25, 2008 at 3:08 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote: > On Tue, 2008-03-25 at 14:11 -0600, Keaton Adams wrote: > Some funding would help that move forwards. If you or others would > consider that, it would help, even if just to provide the seed for > additional contributors. That is an interesting thought, is it possible to earmark donations for a specific (set of) todo(s)? -- Regards, Richard Broersma Jr.
That is an interesting question. If our organization were to help fund the development of such a feature, would that be something taken into consideration by the development team? -Keaton On 3/25/08 4:32 PM, "Richard Broersma" <richard(dot)broersma(at)gmail(dot)com> wrote: > On Tue, Mar 25, 2008 at 3:08 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote: >> On Tue, 2008-03-25 at 14:11 -0600, Keaton Adams wrote: >> Some funding would help that move forwards. If you or others would >> consider that, it would help, even if just to provide the seed for >> additional contributors. > > That is an interesting thought, is it possible to earmark donations > for a specific (set of) todo(s)? >
Similar case has been already happened. For example, I have propsed to implement WITH RECURSIVE clause and the work is supported by Sumitomo Electric Information Systems Co., Ltd. (http://www.sei-info.co.jp/) and SRA OSS, Inc. Japan (http://www.sraoss.co.jp). -- Tatsuo Ishii SRA OSS, Inc. Japan > That is an interesting question. If our organization were to help fund the > development of such a feature, would that be something taken into > consideration by the development team? > > -Keaton > > > > On 3/25/08 4:32 PM, "Richard Broersma" <richard(dot)broersma(at)gmail(dot)com> wrote: > > > On Tue, Mar 25, 2008 at 3:08 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote: > >> On Tue, 2008-03-25 at 14:11 -0600, Keaton Adams wrote: > >> Some funding would help that move forwards. If you or others would > >> consider that, it would help, even if just to provide the seed for > >> additional contributors. > > > > That is an interesting thought, is it possible to earmark donations > > for a specific (set of) todo(s)? > > > > > -- > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
I'm in the same boat, looking for master-slave replication for 1 master & 2 'standby' read-only servers (one would get promoted to master in case of failure). I recently read about WAL here: http://developer.postgresql.org/pgdocs/postgres/warm-standby.html The standby server is not available for access, since it is continually performing recovery processing. PostgreSQL does not provide the system software required to identify a failure on the primary and notify the standby system and then the standby database server. Many such tools exist and are well integrated with other aspects required for successful failover, such as IP address migration. In short there's not much automation magic at the moment and doesn't seem like what you're looking for. Pgpool-II might be the best alternative. I know very little about postgreSQL internals but it would be great if: - WAL files could be applied while the standby server is operational / allow read-only queries - Allow master server to send WAL files to standby servers / * WAL traffic to be streamed to another server - Allow master server to send list of all known standby servers - Allow standby server to check if master server is alive and promote itself as master (would need to ask / make sure other standby servers do not try promote themselves at the same time) Then in my ways, you can use a pool to query the read-only standby servers. As I was writing this out, I thought this would make a great SOC project, but then found it already exists! http://code.google.com/soc/2007/postgres/appinfo.html?csaid=6545828A8197EBC6 Great news, I'd be happy to pitch in any time to help design a solution like this :) -----Original Message----- From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Keaton Adams Sent: March 25, 2008 4:29 PM To: Richard Broersma; salman Cc: pgsql-general(at)postgresql(dot)org Subject: Re: [GENERAL] PostgreSQL Replication with read-only access to standby DB But will that stand-by replication provide for a read-only slave? On 3/25/08 2:26 PM, "Richard Broersma" <richard(dot)broersma(at)gmail(dot)com> wrote: > On Tue, Mar 25, 2008 at 1:17 PM, salman <salmanb(at)quietcaresystems(dot)com> wrote: >> IIRC, it was mentioned previously in one posting that this a TODO for a >> future version of postgres but not something that's expected soon. >> >> Someone please correct me if I'm wrong. > > This is what I saw on the TODO list: > Write-Ahead Log > * Allow WAL traffic to be streamed to another server for stand-by > replication >
On Tue, 25 Mar 2008, Jonathan Bond-Caron wrote: > I know very little about postgreSQL internals but it would be great if: > - WAL files could be applied while the standby server is operational / allow > read-only queries This is the part that requires modifying PostgreSQL, and that progress was made toward by Florian's GSoC project last summer. > - Allow master server to send WAL files to standby servers / * WAL traffic > to be streamed to another server > - Allow master server to send list of all known standby servers > - Allow standby server to check if master server is alive and promote itself > as master (would need to ask / make sure other standby servers do not try > promote themselves at the same time) These parts you could build right now, except that there's not too much value to more than one standby if you're not using them to execute queries against. People who are using the current warm-standby code are already grappling with issues like how to coordinate master/slave failover (including my second favorite acronym, STONITH for "shoot the other node in the head"). I don't expect handling that sort of thing will ever be integrated into the PostgreSQL database core. What is happening instead is that the appropriate interfaces to allow building higher-level tools are being designed and made available. (My favorite acronym is TLA) -- * Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
On Tue, 2008-03-25 at 17:53 -0600, Keaton Adams wrote: > That is an interesting question. If our organization were to help fund the > development of such a feature, would that be something taken into > consideration by the development team? Yes. Many of my major projects have been funded that way. I have funding now for some projects, but not this one. Most of my smaller contributions and advocacy work are not made for direct reward. I'm trying to get in touch with Florian to discuss working together on this. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk
Le mercredi 26 mars 2008, Greg Smith a écrit : > (My favorite acronym is TLA) Hehe :) I'd vote for AAAAA... -- dim
On Wed, Mar 26, 2008 at 01:03:34AM -0400, Greg Smith wrote: > against. People who are using the current warm-standby code are already > grappling with issues like how to coordinate master/slave failover > (including my second favorite acronym, STONITH for "shoot the other node > in the head"). I don't expect handling that sort of thing will ever be > integrated into the PostgreSQL database core. Note that most other database products don't integrate it in their core either. They package separate tools for it, and sell it as a single system, but you can often buy the separate tools independently. Oracle's RAC is an exception, but it also works completely differently than any of this. A
kadams(at)mxlogic(dot)com (Keaton Adams) writes: > That is an interesting question. If our organization were to help fund the > development of such a feature, would that be something taken into > consideration by the development team? I seem to recall there being a relevant Google Summer of Code project about this, last year. http://code.google.com/soc/2007/postgres/appinfo.html?csaid=6545828A8197EBC6 http://archives.postgresql.org/pgsql-hackers/2007-03/msg00050.php I do not recall how far it got. It obviously didn't make it into 8.3 ;-)! -- (format nil "~S(at)~S" "cbbrowne" "acm.org") http://cbbrowne.com/info/rdbms.html Why are they called apartments, when they're all stuck together?
Chris Browne wrote: > I seem to recall there being a relevant Google Summer of Code project > about this, last year. > I do not recall how far it got. It obviously didn't make it into 8.3 > ;-)! Some parts of it did -- for example we got "read-only transactions" which were a step towards that goal. (The point here is that a hot standby needs to be able to execute readonly transactions.) -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
This is probably better answered by the PostgreSQL developer team, so I am posting/moving my discussion to this thread. My apologies if there was a better avenue to pursue this requested feature. This is exactly what we are after. Log based replication built into the core database that would also allow for read-only queries on the slave server. Trigger based / application layer based replication is not a good option for our environment. Neither is a solution that cannot easily handle DDL replication or multiple DBs per PostgreSQL instance. We are using WAL log shipping in production through a series of scripts I wrote in order to have a hot-standby server, which has been working quite well since last September. The request is to now allow that standby server to be more useful than just a hot spare by way of read-only queries for reporting purposes. From a Google Code posting: Title Implementing support for read-only queries on PITR slaves Student Florian G. Pflug Mentor Simon Riggs "The support for PITR (Point-In-Time-Recovery) in postgres can be used to build a simple form a master-slave replication. Currently, no queries can be executed on the slave, though - it only replays WAL (Write-Ahead-Log) segments it receives from the master. I want to implement support for running read-only queries on such a PITR slave, making PITR useful not only for disaster recovery, but also for load-balancing." So, what would it take to get this read-only server feature implemented in PostgreSQL? I have been working with PG / Open Source projects for only a year and need some direction on how to propose having this development effort undertaken by the PG development group. My prior experience has been all closed source from Oracle, Informix, IBM, etc. If funding for this specific development effort would help this is an option that we could explore as well. Thanks, Keaton Adams MX Logic, Inc. On 3/26/08 11:48 AM, "Alvaro Herrera" <alvherre(at)commandprompt(dot)com> wrote: > Chris Browne wrote: > >> I seem to recall there being a relevant Google Summer of Code project >> about this, last year. > >> I do not recall how far it got. It obviously didn't make it into 8.3 >> ;-)! > > Some parts of it did -- for example we got "read-only transactions" > which were a step towards that goal. (The point here is that a hot > standby needs to be able to execute readonly transactions.) > against. People who are using the current warm-standby code are already > grappling with issues like how to coordinate master/slave failover > (including my second favorite acronym, STONITH for "shoot the other node > in the head"). I don't expect handling that sort of thing will ever be > integrated into the PostgreSQL database core. Note that most other database products don't integrate it in their core either. They package separate tools for it, and sell it as a single system, but you can often buy the separate tools independently. Oracle's RAC is an exception, but it also works completely differently than any of this. A > I know very little about postgreSQL internals but it would be great if: > - WAL files could be applied while the standby server is operational / allow > read-only queries This is the part that requires modifying PostgreSQL, and that progress was made toward by Florian's GSoC project last summer. > - Allow master server to send WAL files to standby servers / * WAL traffic > to be streamed to another server > - Allow master server to send list of all known standby servers > - Allow standby server to check if master server is alive and promote itself > as master (would need to ask / make sure other standby servers do not try > promote themselves at the same time) These parts you could build right now, except that there's not too much value to more than one standby if you're not using them to execute queries against. People who are using the current warm-standby code are already grappling with issues like how to coordinate master/slave failover (including my second favorite acronym, STONITH for "shoot the other node in the head"). I don't expect handling that sort of thing will ever be integrated into the PostgreSQL database core. What is happening instead is that the appropriate interfaces to allow building higher-level tools are being designed and made available. I'm in the same boat, looking for master-slave replication for 1 master & 2 'standby' read-only servers (one would get promoted to master in case of failure). I recently read about WAL here: http://developer.postgresql.org/pgdocs/postgres/warm-standby.html The standby server is not available for access, since it is continually performing recovery processing. PostgreSQL does not provide the system software required to identify a failure on the primary and notify the standby system and then the standby database server. Many such tools exist and are well integrated with other aspects required for successful failover, such as IP address migration. In short there's not much automation magic at the moment and doesn't seem like what you're looking for. Pgpool-II might be the best alternative. I know very little about postgreSQL internals but it would be great if: - WAL files could be applied while the standby server is operational / allow read-only queries - Allow master server to send WAL files to standby servers / * WAL traffic to be streamed to another server - Allow master server to send list of all known standby servers - Allow standby server to check if master server is alive and promote itself as master (would need to ask / make sure other standby servers do not try promote themselves at the same time) Then in my ways, you can use a pool to query the read-only standby servers. As I was writing this out, I thought this would make a great SOC project, but then found it already exists! http://code.google.com/soc/2007/postgres/appinfo.html?csaid=6545828A8197EBC6 Great news, I'd be happy to pitch in any time to help design a solution like this :) Our organization is looking for a hot-standby option for PostgreSQL that uses the WAL (transaction) data to keep the standby current and also allows the standby to be read-only accessible for reporting. We have implemented WAL shipping through a set of scripts we developed and that works well to have a standby DB on the ready in case we need to fail over, but we are looking to increase the value of the standby server by making it available for queries. Because of the complexities of our environment using a table/trigger based replication method such as Slony won¹t work well. It would be great if there was a solution (Open Source or Commercial) that worked in a similar manner as Oracle Active Data Guard: ³Oracle Active Data Guard enables a physical standby database to be open for read-only access for reporting, simple or complex queries while changes from the production database are being applied to it. This means any operation that requires up-to-date read-only access can be offloaded to the replica, enhancing and protecting the performance of the production database.² ³All queries reading from the physical replica execute in real-time, and return current results. A Data Guard configuration consists of one production (or primary) database and up to nine standby databases. A standby database is initially created from a backup copy of the primary database. Once created, Data Guard automatically maintains the standby database as a synchronized copy of the primary database by transmitting primary database redo data to the standby system and then applying the redo data to the standby database.² Does anyone know of such a solution for PostgreSQL?
On Thu, 27 Mar 2008, Keaton Adams wrote: > I have been working with PG / Open Source projects for only a year and > need some direction on how to propose having this development effort > undertaken by the PG development group. Don't everybody answer at once. The "PostgreSQL Global Development Group" (or PGDG for short) is a legal entity with group of organizers. They tell you outright at http://www.postgresql.org/developer/ : "We don't hire programmers, we reach across the Internet, drawing the best database developers in the world to PostgreSQL". While it's great to donate money to them via http://www.postgresql.org/about/donate that fund is earmarked for "advocacy materials, conference expenses, legal expenses, and travel costs"; note the lack of the work "development" on that list. > This is probably better answered by the PostgreSQL developer team The people actually developing new features in PostgreSQL aren't all on a single "team" as you're used to in traditional software companies, they're a community: lots of people with similar goals who happen to be working on the same project, each with their own agenda and source(s) of funding and motivation. There is a "core team" of 7 people: http://www.postgresql.org/community/contributors/ but since they're all too busy to write your feature their existence doesn't really help you. Bruce addresses part of what you're asking about at http://momjian.us/main/writings/pgsql/company_contributions/ which has some more good comments on the whole community aspect to things, I'd recommend that since you say you're still new to how open source projects work. But that article is more aimed at companies offering bodies to work on the code rather than ones with dollars to spend. The exact mechanics of how to effectively sponsor work on a feature you'd like to have is somewhat off-topic for this list. Discussion here is aimed at hashing out technical issues, not business ones. And it's kind of a touchy subject to bring up as well, since it's hard to make recommendations without looking unprofessional--which partly explains the dead silence you've gotten as a response here so far. -- * Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
On Mar 25, 2008, at 4:28 PM, Jeff Davis wrote: > This obviously does not work in real time, but it may be useful. It > does > not require a lot of additional space to do this because of the ZFS > copy-on-write implementation. But what benefit does it give you if you're pounding on the same set of physical disks? You might as well run it on the original since you're limit is the disk I/O.