Re: Postgres and data warehouses

On Sun, 8 Mar 2015 11:20:16 -0400
Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> * Nigel Gardiner (nigelgardiner(at)gmail(dot)com) wrote:
> > I've had a quick search and haven't seen this approach used yet, but I was
> > thinking, the asynchronous replication of Postgres databases could be used
> > as a streaming journal of changes to be processed by a data warehouse. The
> > other approach that suggests itself is WAL file shipping. I've not dug into
> > the async rep protocol yet, before I do so I just wanted to get some brief
> > feedback on whether I'm on the wrong track or not, and if there's some
> > better approach I should be looking at first instead.
>
> Using a streaming replica for data warehouse queries is quite common..
> The issue there is if you want to change the data structure or store
> data on the replica because a streaming replica based on WAL shipping is
> read-only. You can create FDW tables which you can write to (even on
> the replica) or you can create an independent database which has FDW
> tables to the replica. They have their own pros and cons, of course.

Another option is to replicate using Slony instead of streaming, which allows
you to create additional tables on the replica that are read/write in
addition to triggers that only fire on the replica. It's complicated, but
pretty damn powerful.

--
Bill Moran