hi. On Mar 18, 2011, at 5:25 PM, John P Weatherman wrote:
> All, > > I have recently upgraded to Postgres9 and am building a hot standby for > reporting. Unfortunately, my end users are providing requirements for > 1) real time data replication (which the hot standby does) and 2) the > ability to create temporary tables as part of their reporting jobs > (which is expressly prohibited in a hot standby. As you rightly note, if you are seeking replication for reporting and need to change the reporting database, then you do *not* want to use PostgreSQL 9's replication/hot-standby features, as WAL shipping disallows any writes to the database, or differences. Instead, you will want to look at the following for realtime replication/failover, if you have not already: http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling Pay particular attention to the references, particularly from the Mailing List, and links. You might find Slony-I or Londiste or Bucardo to be the right answer for your needs. Here's more on Replication with pgpool-II and slony-I: http://pgsqlpgpool.blogspot.com/2010/06/pgpool-ii-and-hot-standby.html http://pgsqlpgpool.blogspot.com/2010/06/talk-with-author-of-streaming.html http://scanningpages.wordpress.com/2010/10/09/9-0-streaming-replication-vs-slony/ http://stackoverflow.com/questions/3692493/pgpool-ii-for-postgres-is-it-what-i-need > Has anyone run into this already and have an idea for a work around? I > am primarily an Oracle guy and in that environment I would set up a > second DB with database links to the hot standby, then they could > connect somewhere they could create tables and use the links to pull the > real time data...keeping them away from production with ad hoc code. > I'm not sure if there is any way to do that with postgres. I too am creating a similar set-up. I too would value the wisdom of the ML. According to the Slony-I page, it appears that slaves might be able to be writable reporting databases. Is this true? I second the what $REP_TECH be used to replicate to a reporting database, where reporters want to write to the reporting database? +1 on "how have people done this?" In my researches so far, I've found the following informative: http://archives.postgresql.org/pgsql-admin/2010-08/msg00173.php http://www.sraoss.co.jp/event_seminar/2010/20100702-03char10.pdf http://momjian.us/main/writings/pgsql/replication.pdf http://www.fastware.com.au/docs/PostgreSQL_HighAvailability.pdf Thanks. have a day.yad jdpf -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
