pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: ProPAAS DBA <dba@propaas.com>
To: pgsql-performance@postgresql.org
Subject: Re: Sort-of replication for reporting purposes
Date: Thu, 12 Jan 2017 10:48:24 -0700
Message-ID: <4ddea826-e99e-ff6f-d791-cc7bff89b03c@propaas.com> (raw)
In-Reply-To: <CAF-QHFXC=DLeQqLMfmsbPq5-Tegi0TNi_zPkrd_SL8zUXijH6w@mail.gmail.com>
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>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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





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



view thread (10+ messages)  latest in thread

Message-ID: <4ddea826-e99e-ff6f-d791-cc7bff89b03c@propaas.com>
Permalink:  ../4ddea826-e99e-ff6f-d791-cc7bff89b03c@propaas.com/
Also on:    postgresql.org/message-id/4ddea826-e99e-ff6f-d791-cc7bff89b03c@propaas.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: dba@propaas.com
  Subject: Re: Sort-of replication for reporting purposes
  In-Reply-To: <4ddea826-e99e-ff6f-d791-cc7bff89b03c@propaas.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox