Re: PostgreSQL Replication with read-only access to standby DB

Keaton Adams wrote:
> 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?
>
> Thanks,
>
> Keaton
>

IIRC, it was mentioned previously in one posting that this a TODO for a
future version of postgres but not something that's expected soon.

Someone please correct me if I'm wrong.

-salman