June 11th, 2013, 07:45 PM
Hot Steaming DB setting for reporting?
Hi There. Please forgive and or correct me for my limited knowledge or misunderstandings in my post below...
I have a couple of db servers set up. One is the master and another is the slave using hot streaming to keep them in sync.
Previously I have used SlonyI (prior to postgres 9.1) to manage replicating data to another DB server. This was great because the replication was transactional leaving the DB fully operational for running reporting queries against.
With the hot streaming because it is replaying the WAL log in recovery mode (at least thats my limited knowledge of how it works) it means running queries against the slave for reporting purposes becomes problematic. The slave server appears to either reject connections or time the queries out while the WAL log replays. I have read that you can adjust the time between commits of the WAL log but this doesn't really seem like the solution that I require.
I have a couple of needs/wants
1) To separate all database activity into read and writes with writes going to the Master (of course) and with *all* reads going to the slave server.
2) The ability to read data from the slave DB where it is up to date as possible (within reason). An acceptable delay would be perhaps 1 minute.
3) I want the ability to run large reporting queries against the slave DB where execution time could potentially be 10 min.
Can someone let me know if this is possible and if so either point me in the right direction for info or let me know what I need to do?