Обсуждение: [PERFORM] Sort-of replication for reporting purposes

On Fri, Jan 6, 2017 at 12:24 PM, Ivan Voras <ivoras@gmail.com> 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?

I've always solved this with slony replication, but pg_basebackup
should be pretty good for making sort of up to date slave copies. Just
toss a recovery.conf file and touch whatever failover file the slave
expects etc.

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

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