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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
