Re: [PERFORM] Sort-of replication for reporting purposes

On 01/06/2017 12:24 PM, Ivan Voras wrote:
> Hello,
>
> I'm investigating options for an environment which has about a dozen
> servers and several dozen databases on each, and they occasionally
> need to run huge reports which slow down other services. This is of
> course "legacy code". After some discussion, the idea is to offload
> these reports to separate servers - and that would be fairly
> straightforward if not for the fact that the report code creates temp
> tables which are not allowed on read-only hot standby replicas.
>
> So, the next best thing would be to fiddle with the storage system and
> make lightweight snapshots of live database clusters (their storage
> volumes) and mount them on the reporting servers when needed for the
> reports. This is a bit messy :-)
>
> I'm basically fishing for ideas. Are there any other options available
> which would offer fast replication-like behaviour ?
>
> If not, what practices would minimise problems with the storage
> snapshots idea? Any filesystem options?
>

You could have a look at SLONY - it locks the replicated tables into
read only but the standby cluster remains read/write. As an added bonus
you could replicate everything into a single reporting database cluster,
in separate schema's there are lots and lots of features with SLONY that
give you flexibility.

http://slony.info/

I can't speak from direct experience but I think pg_logical may offer
similar features