Re: Sort-of replication for reporting purposes

On 6 Jan 2017 8:30 p.m., "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> wrote:

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

I forgot to add one more information, the databases are 50G+ each so doing
the base backup on demand over the network is not a great option.