Date: Fri, 6 Jan 2017 14:43:03 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Ivan Voras <ivoras@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Sort-of replication for reporting purposes
Message-ID: <20170106194303.GU18360@tamriel.snowman.net>
References: 
 <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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="IbuFj1t1LJkAAh+4"
Content-Disposition: inline
In-Reply-To: 
 <CAF-QHFXC=DLeQqLMfmsbPq5-Tegi0TNi_zPkrd_SL8zUXijH6w@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


--IbuFj1t1LJkAAh+4
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Ivan,

* Ivan Voras (ivoras@gmail.com) wrote:
> 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.

You could create a new server which has postgres_fdw connections to your
read-only replicas and run the reporting code there.  That could suck,
of course, since the data would have to be pulled across to be
aggregated (assuming that's what your reporting script is doing).

If you can't change the reporting script at all, that might be what you
have to do though.  Be sure to look at the postgres_fdw options about
batch size and how planning is done.

If you can change the reporting script, another option is to create FDWs
on your primary servers with FDW tables that point to some other server
and then have the reporting script use the FDW tables as the temp or
destination tables on the replica.  The magic here is that FDW tables on
a read-only replica *can* be written to, but you have to create the FDW
and the FDW tables on the primary and let them be replicated.

As also mentioned, you could use trigger-based replication (eg: bucardo,
slony, etc) instead of block-based, or you could look at the logical
replication capabilities (pg_logical) to see about using that for your
replica-for-reporting instead.

Thanks!

Stephen

--IbuFj1t1LJkAAh+4
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJYb/NHAAoJEO1sijiDR2RVlYoP/1iGXbhOJ420klanZ/mr/ZrM
t6ENTXY7erwedmGicLKRfXC5EZ80NzkV7mOtRLv0FYH9TNI/CQLx5DHIKtf/x5+1
6d8nSYDmsQVMNWqREqRNkO96Pi+NxQ84hi1r2xFNj99c4t/N9bSgipa0E1OsjkYR
Lu74sTMvuwEt8HeqnbTwELiG9owa3L9WSQM9Jw2o5thZ81d6n3LqTshA/Cmwd39C
u6ANm9Vw0VFHTR2ZPD91MfmPlvkrkYOy2kLxG37sjm3FSPoXoK1RLCokiTT7rVJA
Bw1H1FejsrQPluz6gBoN3rRC44E4saNYujeoNhyq/oejiQvQmlHh/nI4Fuy+HmtU
Tdjsm4jw1RCiMMrtqbbgYw4DKu6gnfUKGzQpoecnG8dMZTmJA5NLShOlhlXSREL4
peomT+4ifNEWYIJxn/UliYu3FCgseErPIsSYjdLKxhqXoJrD50rBujsaqFrgwiUs
rSns2hfLj5oxvfNHQR3udViB1EQNgsBjYas/6afvLPtAkDoZiRz1/FoADC6xZ/Mr
VZwySRD083ik0OtFsPfhWHOb/ShY7BYrJkDMaKPYSc8RGI8Qu7ZDpscm80pJZfBH
+OV0rQudTrnMp0Wfoh17uhR//6lzHRXuPl5rfxk8D6naVIBZxMx52ZHMd6LVIdtB
SopBeVu/xWf7kYH5NgJd
=QQ1t
-----END PGP SIGNATURE-----

--IbuFj1t1LJkAAh+4--