PostgreSQL Replication with read-only access to standby DB

This is probably better answered by the PostgreSQL developer team, so I am
posting/moving my discussion to this thread. My apologies if there was a
better avenue to pursue this requested feature.

This is exactly what we are after. Log based replication built into the
core database that would also allow for read-only queries on the slave
server. Trigger based / application layer based replication is not a good
option for our environment. Neither is a solution that cannot easily handle
DDL replication or multiple DBs per PostgreSQL instance. We are using WAL
log shipping in production through a series of scripts I wrote in order to
have a hot-standby server, which has been working quite well since last
September. The request is to now allow that standby server to be more
useful than just a hot spare by way of read-only queries for reporting
purposes.

From a Google Code posting:

Title Implementing support for read-only queries on PITR slaves
Student Florian G. Pflug
Mentor Simon Riggs

"The support for PITR (Point-In-Time-Recovery) in postgres can be used to
build a simple form a master-slave replication. Currently, no queries can be
executed on the slave, though - it only replays WAL (Write-Ahead-Log)
segments it receives from the master. I want to implement support for
running read-only queries on such a PITR slave, making PITR useful not only
for disaster recovery, but also for load-balancing."

So, what would it take to get this read-only server feature implemented in
PostgreSQL? I have been working with PG / Open Source projects for only a
year and need some direction on how to propose having this development
effort undertaken by the PG development group. My prior experience has been
all closed source from Oracle, Informix, IBM, etc. If funding for this
specific development effort would help this is an option that we could
explore as well.

Thanks,

Keaton Adams
MX Logic, Inc.

On 3/26/08 11:48 AM, "Alvaro Herrera" <alvherre(at)commandprompt(dot)com> wrote:

> Chris Browne wrote:
>
>> I seem to recall there being a relevant Google Summer of Code project
>> about this, last year.
>
>> I do not recall how far it got. It obviously didn't make it into 8.3
>> ;-)!
>
> Some parts of it did -- for example we got "read-only transactions"
> which were a step towards that goal. (The point here is that a hot
> standby needs to be able to execute readonly transactions.)

> against. People who are using the current warm-standby code are already
> grappling with issues like how to coordinate master/slave failover
> (including my second favorite acronym, STONITH for "shoot the other node
> in the head"). I don't expect handling that sort of thing will ever be
> integrated into the PostgreSQL database core.

Note that most other database products don't integrate it in their core
either. They package separate tools for it, and sell it as a single system,
but you can often buy the separate tools independently. Oracle's RAC is an
exception, but it also works completely differently than any of this.

A

> I know very little about postgreSQL internals but it would be great if:
> - WAL files could be applied while the standby server is operational / allow
> read-only queries

This is the part that requires modifying PostgreSQL, and that progress was
made toward by Florian's GSoC project last summer.

> - Allow master server to send WAL files to standby servers / * WAL traffic
> to be streamed to another server
> - Allow master server to send list of all known standby servers
> - Allow standby server to check if master server is alive and promote itself
> as master (would need to ask / make sure other standby servers do not try
> promote themselves at the same time)

These parts you could build right now, except that there's not too much
value to more than one standby if you're not using them to execute queries
against. People who are using the current warm-standby code are already
grappling with issues like how to coordinate master/slave failover
(including my second favorite acronym, STONITH for "shoot the other node
in the head"). I don't expect handling that sort of thing will ever be
integrated into the PostgreSQL database core. What is happening instead
is that the appropriate interfaces to allow building higher-level tools
are being designed and made available.

I'm in the same boat, looking for master-slave replication for 1 master & 2
'standby' read-only servers (one would get promoted to master in case of
failure).

I recently read about WAL here:
http://developer.postgresql.org/pgdocs/postgres/warm-standby.html

The standby server is not available for access, since it is continually
performing recovery processing.

PostgreSQL does not provide the system software required to identify a
failure on the primary and notify the standby system and then the standby
database server. Many such tools exist and are well integrated with other
aspects required for successful failover, such as IP address migration.

In short there's not much automation magic at the moment and doesn't seem
like what you're looking for.
Pgpool-II might be the best alternative.

I know very little about postgreSQL internals but it would be great if:
- WAL files could be applied while the standby server is operational / allow
read-only queries
- Allow master server to send WAL files to standby servers / * WAL traffic
to be streamed to another server
- Allow master server to send list of all known standby servers
- Allow standby server to check if master server is alive and promote itself
as master (would need to ask / make sure other standby servers do not try
promote themselves at the same time)

Then in my ways, you can use a pool to query the read-only standby servers.

As I was writing this out, I thought this would make a great SOC project,
but then found it already exists!
http://code.google.com/soc/2007/postgres/appinfo.html?csaid=6545828A8197EBC6

Great news, I'd be happy to pitch in any time to help design a solution like
this :)

Our organization is looking for a hot-standby option for PostgreSQL that
uses the WAL (transaction) data to keep the standby current and also allows
the standby to be read-only accessible for reporting. We have implemented
WAL shipping through a set of scripts we developed and that works well to
have a standby DB on the ready in case we need to fail over, but we are
looking to increase the value of the standby server by making it available
for queries. Because of the complexities of our environment using a
table/trigger based replication method such as Slony won¹t work well.

It would be great if there was a solution (Open Source or Commercial) that
worked in a similar manner as Oracle Active Data Guard:

³Oracle Active Data Guard enables a physical standby database to be open for
read-only access ­ for reporting, simple or complex queries ­ while changes
from the production database are being applied to it. This means any
operation that requires up-to-date read-only access can be offloaded to the
replica, enhancing and protecting the performance of the production
database.²

³All queries reading from the physical replica execute in real-time, and
return current results. A Data Guard configuration consists of one
production (or primary) database and up to nine standby databases. A
standby database is initially created from a backup copy of the primary
database. Once created, Data Guard automatically maintains the standby
database as a synchronized copy of the primary database by transmitting
primary database redo data to the standby system and then applying the redo
data to the standby database.²

Does anyone know of such a solution for PostgreSQL?