Subject: Re: Sort-of replication for reporting purposes
To: pgsql-performance@postgresql.org
References: 
 <CAF-QHFVM1RK=cO9jzO2JGkF3+x0YH3OFRvsoECCG2nnCSSEhUw@mail.gmail.com>
 <CAF-QHFUyJsUzfe+1jVr+0aOY1zaCe1bRGAoGMP6yoC2PLmwaOg@mail.gmail.com>
 <CAF-QHFVvGit2UK6ez5jKqexsmyrHpZw5hwFeRoiT2oC=OvaN8A@mail.gmail.com>
 <CAF-QHFXj1adiYaXzmwwSkNc2HE5ng27Foze7mDTNS9x=w-okoA@mail.gmail.com>
 <CAF-QHFVSZYxNHf+VDqrw_SJ_5mEhDfxF=N6zKmZfQttNAdGF2w@mail.gmail.com>
 <CAF-QHFU54UqB0NEpahnsc5TmOCA1WzW-mY4FYchH8+=s5sYAUQ@mail.gmail.com>
 <CAF-QHFXLRfCuTHfiq_38fEdWxD3qYrm_TDzUQDAm1cUQ9QyA9A@mail.gmail.com>
 <CAF-QHFUxvMYXPj-ufT-ivobXmj8-0AGSmPh8hBwrzLvSoZfzsw@mail.gmail.com>
 <CAF-QHFVP5O+JZH7LfjoGrgT=_OqPujpHbM5kpwjdffLoNJA10g@mail.gmail.com>
 <CAF-QHFUk7QsV21LzexC45n3ODNYBU7Kj+fh_EB59mXYw4aCq7Q@mail.gmail.com>
 <CAF-QHFXBhJxZyJ9ifBf2GJY1VXjjbV2nbZGHv34Wd50Ura=Qjg@mail.gmail.com>
 <CAF-QHFXC=DLeQqLMfmsbPq5-Tegi0TNi_zPkrd_SL8zUXijH6w@mail.gmail.com>
From: ProPAAS DBA <dba@propaas.com>
Message-ID: <4ddea826-e99e-ff6f-d791-cc7bff89b03c@propaas.com>
Date: Thu, 12 Jan 2017 10:48:24 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CAF-QHFXC=DLeQqLMfmsbPq5-Tegi0TNi_zPkrd_SL8zUXijH6w@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance