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