Re: Hot-standby/Reporting database.

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