On 6 Jan 2017 8:30 p.m., "Scott Marlowe" <scott.marl...@gmail.com> wrote:
On Fri, Jan 6, 2017 at 12:24 PM, Ivan Voras <ivo...@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. 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.
