Re: Sort-of replication for reporting purposes

From: ProPAAS DBA <dba(at)propaas(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Sort-of replication for reporting purposes
Date: 2017-01-12 17:48:24
Message-ID: 4ddea826-e99e-ff6f-d791-cc7bff89b03c@propaas.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Stuart Bishop 2017-01-13 11:00:31 Re: Sort-of replication for reporting purposes
Previous Message Joshua D. Drake 2017-01-12 17:18:08 Re: How can I find the source of postgresql per-connection memory leaks?