Horton

Thread

  1. Opening a recovering DB in for read-only access?

    Philip Warner <pjw@rhyme.com.au> — 2008-11-21T04:45:46Z

    Sounds somewhat evil, I know, but I was wondering if it was even
remotely possible with the current design?

The reason: we are contemplating using pg_standy to create a
warm-standby. It would be a bonus if we would run read-only queries
against this DB to take some of the load off or production servers.

We currently use slony to provide warm-standby *and* read-only access,
but pg_standby is a great deal more appealing...especially if there was
some way to do read-only access at the same time.

FWIW, the data would not even need to be completely consistent ... the
kinds of things we are looking at offloading are large summary-type
sequential scans of big tables.


-- 
----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 03 5330 3171          |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
http://www.rhyme.com.au <http://www.rhyme.com.au/>         
|                /           \|
                                 |    --________--
GPG key available upon request.  |  /
                                 |/

  2. Re: Opening a recovering DB in for read-only access?

    Alex Hunsaker <badalex@gmail.com> — 2008-11-21T05:12:29Z

    On Thu, Nov 20, 2008 at 21:45, Philip Warner <pjw@rhyme.com.au> wrote:
>
> Sounds somewhat evil, I know, but I was wondering if it was even
> remotely possible with the current design?
>
> The reason: we are contemplating using pg_standy to create a
> warm-standby. It would be a bonus if we would run read-only queries
> against this DB to take some of the load off or production servers.
>
> We currently use slony to provide warm-standby *and* read-only access,
> but pg_standby is a great deal more appealing...especially if there was
> some way to do read-only access at the same time.
>
> FWIW, the data would not even need to be completely consistent ... the
> kinds of things we are looking at offloading are large summary-type
> sequential scans of big tables.

Uhh sounds like you are describing hot standby (currently in the works
for 8.4) see:
http://wiki.postgresql.org/wiki/Hot_Standby
http://archives.postgresql.org/pgsql-hackers/2008-11/msg00005.php

Synchronous replication might also be of interest
http://archives.postgresql.org/pgsql-hackers/2008-11/msg00987.php

  3. Re: Opening a recovering DB in for read-only access?

    Philip Warner <pjw@rhyme.com.au> — 2008-11-21T05:29:41Z

    Alex Hunsaker wrote
>
> Uhh sounds like you are describing hot standby (currently in the works
> for 8.4) see:
>   

Yep. That's exactly what I'm talking about. Thanks for the links!

  4. Re: Opening a recovering DB in for read-only access?

    Simon Riggs <simon@2ndquadrant.com> — 2008-11-21T16:53:22Z

    On Fri, 2008-11-21 at 15:45 +1100, Philip Warner wrote:
> Sounds somewhat evil, I know, but I was wondering if it was even
> remotely possible with the current design?
> 
> The reason: we are contemplating using pg_standy to create a
> warm-standby. It would be a bonus if we would run read-only queries
> against this DB to take some of the load off or production servers.
> 
> We currently use slony to provide warm-standby *and* read-only access,
> but pg_standby is a great deal more appealing...especially if there was
> some way to do read-only access at the same time.

Yes, exactly what I'm working on now, currently patch in review.

> FWIW, the data would not even need to be completely consistent ... the
> kinds of things we are looking at offloading are large summary-type
> sequential scans of big tables.

Access to inconsistent data has not been agreed. We will only allow
access to consistent data with this approach.

-- 
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support