pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHot-standby/Reporting database.
7+ messages / 4 participants
[nested] [flat]
* Hot-standby/Reporting database.
@ 2011-03-18 21:25 John P Weatherman <jweatherman91@alumni.wfu.edu>
2011-03-20 20:54 ` Re: Hot-standby/Reporting database. jonathan ferguson <jdpf@hoozinga.com>
2011-03-21 01:04 ` Re: Hot-standby/Reporting database. Jaime Casanova <jaime@2ndquadrant.com>
2011-03-23 10:58 ` Re: Hot-standby/Reporting database. Vibhor Kumar <vibhor.aim@gmail.com>
0 siblings, 3 replies; 7+ messages in thread
From: John P Weatherman @ 2011-03-18 21:25 UTC (permalink / raw)
To: pgsql-admin
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.
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.
Thanks,
John
John P Weatherman
Sr DBA
Centerstone
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Hot-standby/Reporting database.
2011-03-18 21:25 Hot-standby/Reporting database. John P Weatherman <jweatherman91@alumni.wfu.edu>
@ 2011-03-20 20:54 ` jonathan ferguson <jdpf@hoozinga.com>
2 siblings, 0 replies; 7+ messages in thread
From: jonathan ferguson @ 2011-03-20 20:54 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Hot-standby/Reporting database.
2011-03-18 21:25 Hot-standby/Reporting database. John P Weatherman <jweatherman91@alumni.wfu.edu>
@ 2011-03-21 01:04 ` Jaime Casanova <jaime@2ndquadrant.com>
2011-03-21 01:05 ` Re: Hot-standby/Reporting database. Jaime Casanova <jaime@2ndquadrant.com>
2 siblings, 1 reply; 7+ messages in thread
From: Jaime Casanova @ 2011-03-21 01:04 UTC (permalink / raw)
To: jweatherman91@alumni.wfu.edu; +Cc: pgsql-admin
On Fri, Mar 18, 2011 at 4:25 PM, John P Weatherman
<jweatherman91@alumni.wfu.edu> wrote:
>
> 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,
you can use the contrib module dblink for this:
http://www.postgresql.org/docs/9.0/static/dblink.html
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Hot-standby/Reporting database.
2011-03-18 21:25 Hot-standby/Reporting database. John P Weatherman <jweatherman91@alumni.wfu.edu>
2011-03-21 01:04 ` Re: Hot-standby/Reporting database. Jaime Casanova <jaime@2ndquadrant.com>
@ 2011-03-21 01:05 ` Jaime Casanova <jaime@2ndquadrant.com>
2011-03-21 03:38 ` Re: Hot-standby/Reporting database. jonathan ferguson <jdpf@hoozinga.com>
0 siblings, 1 reply; 7+ messages in thread
From: Jaime Casanova @ 2011-03-21 01:05 UTC (permalink / raw)
To: jweatherman91@alumni.wfu.edu; +Cc: pgsql-admin
On Sun, Mar 20, 2011 at 8:04 PM, Jaime Casanova <jaime@2ndquadrant.com> wrote:
> On Fri, Mar 18, 2011 at 4:25 PM, John P Weatherman
> <jweatherman91@alumni.wfu.edu> wrote:
>>
>> 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,
>
> you can use the contrib module dblink for this:
> http://www.postgresql.org/docs/9.0/static/dblink.html
>
obviously you need to install the module libraries in both the master
and the slave and the sql functions that create objects in master only
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Hot-standby/Reporting database.
2011-03-18 21:25 Hot-standby/Reporting database. John P Weatherman <jweatherman91@alumni.wfu.edu>
2011-03-21 01:04 ` Re: Hot-standby/Reporting database. Jaime Casanova <jaime@2ndquadrant.com>
2011-03-21 01:05 ` Re: Hot-standby/Reporting database. Jaime Casanova <jaime@2ndquadrant.com>
@ 2011-03-21 03:38 ` jonathan ferguson <jdpf@hoozinga.com>
2011-03-22 19:11 ` Re: Hot-standby/Reporting database. Jaime Casanova <jaime@2ndquadrant.com>
0 siblings, 1 reply; 7+ messages in thread
From: jonathan ferguson @ 2011-03-21 03:38 UTC (permalink / raw)
To: pgsql-admin
hi.
On Mar 20, 2011, at 9:05 PM, Jaime Casanova wrote:
> On Sun, Mar 20, 2011 at 8:04 PM, Jaime Casanova <jaime@2ndquadrant.com> wrote:
>> On Fri, Mar 18, 2011 at 4:25 PM, John P Weatherman
>> <jweatherman91@alumni.wfu.edu> wrote:
>>> 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,
>>
>> you can use the contrib module dblink for this:
>> http://www.postgresql.org/docs/9.0/static/dblink.html
>
> obviously you need to install the module libraries in both the master
> and the slave and the sql functions that create objects in master only
Perhaps I'm in the clueless n00b category, here, but how does:
"dblink [] a module which supports connections to other PostgreSQL databases from within a database session."
Even approach solving the problem of Database Replication for Data Warehousing aka Reports DB?
dblink looks more like SSH or Telnet for DB access than a db replication solution to me.
Please explain.
Thanks.
have a day.yad
jdpf
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Hot-standby/Reporting database.
2011-03-18 21:25 Hot-standby/Reporting database. John P Weatherman <jweatherman91@alumni.wfu.edu>
2011-03-21 01:04 ` Re: Hot-standby/Reporting database. Jaime Casanova <jaime@2ndquadrant.com>
2011-03-21 01:05 ` Re: Hot-standby/Reporting database. Jaime Casanova <jaime@2ndquadrant.com>
2011-03-21 03:38 ` Re: Hot-standby/Reporting database. jonathan ferguson <jdpf@hoozinga.com>
@ 2011-03-22 19:11 ` Jaime Casanova <jaime@2ndquadrant.com>
0 siblings, 0 replies; 7+ messages in thread
From: Jaime Casanova @ 2011-03-22 19:11 UTC (permalink / raw)
To: jonathan ferguson <jdpf@hoozinga.com>; +Cc: pgsql-admin
On Sun, Mar 20, 2011 at 10:38 PM, jonathan ferguson <jdpf@hoozinga.com> wrote:
> hi.
>
> On Mar 20, 2011, at 9:05 PM, Jaime Casanova wrote:
>
>> On Sun, Mar 20, 2011 at 8:04 PM, Jaime Casanova <jaime@2ndquadrant.com> wrote:
>>> On Fri, Mar 18, 2011 at 4:25 PM, John P Weatherman
>>> <jweatherman91@alumni.wfu.edu> wrote:
>>>> 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,
>>>
>>> you can use the contrib module dblink for this:
>>> http://www.postgresql.org/docs/9.0/static/dblink.html
>>
>> obviously you need to install the module libraries in both the master
>> and the slave and the sql functions that create objects in master only
>
> Perhaps I'm in the clueless n00b category, here, but how does:
>
> "dblink [] a module which supports connections to other PostgreSQL databases from within a database session."
>
your question had 2 parts...
> 1) real time data replication (which the hot standby does) and
for this one hot standby is almost that. ok, you have a little delay
but is that delay that bad that you can't use it?
if it is, 9.1 will ship with synchronous replication
> 2) the ability to create temporary tables as part of their reporting jobs
> (which is expressly prohibited in a hot standby.
AFAIU, the links you say you create in Oracle are for this... no?
that's why i suggest using dblink...
if i misunderstood how you use the database links, please explain...
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Hot-standby/Reporting database.
2011-03-18 21:25 Hot-standby/Reporting database. John P Weatherman <jweatherman91@alumni.wfu.edu>
@ 2011-03-23 10:58 ` Vibhor Kumar <vibhor.aim@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Vibhor Kumar @ 2011-03-23 10:58 UTC (permalink / raw)
To: jweatherman91@alumni.wfu.edu; +Cc: pgsql-admin
On Mar 19, 2011, at 2:55 AM, John P Weatherman wrote:
> 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.
>
Yes. On Hotstandby you cannot create Tables.
> Has anyone run into this already and have an idea for a work around?
Looking your requirement, I would recommend to use Slony Replication, which replicate data from mater to slave and also, gives ability to create temporary table, without allowing user to tamper/modify the data of Replicated Tables of (Master).
Thanks & Regards,
Vibhor Kumar
vibhor.aim@gmail.com
Blog:http://vibhork.blogspot.com
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-03-23 10:58 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-18 21:25 Hot-standby/Reporting database. John P Weatherman <jweatherman91@alumni.wfu.edu>
2011-03-20 20:54 ` jonathan ferguson <jdpf@hoozinga.com>
2011-03-21 01:04 ` Jaime Casanova <jaime@2ndquadrant.com>
2011-03-21 01:05 ` Jaime Casanova <jaime@2ndquadrant.com>
2011-03-21 03:38 ` jonathan ferguson <jdpf@hoozinga.com>
2011-03-22 19:11 ` Jaime Casanova <jaime@2ndquadrant.com>
2011-03-23 10:58 ` Vibhor Kumar <vibhor.aim@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox