Re: Postgres and data warehouses

* 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.

Thanks!

Stephen