Re: [PERFORM] Sort-of replication for reporting purposes
Ivan, * Ivan Voras (ivo...@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 signature.asc Description: Digital signature
Re: [PERFORM] Sort-of replication for reporting purposes
I suggest SymmetricDS. ( http://symmetricds.org ) I've had good luck using them to aggregate data from a heterogeneous suite of database systems and versions back to a single back-end data mart for exactly this purpose. On Fri, Jan 6, 2017 at 2: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? > >
Re: [PERFORM] Sort-of replication for reporting purposes
On 6 Jan 2017 8:30 p.m., "Scott Marlowe" wrote: On Fri, Jan 6, 2017 at 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? 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.
Re: [PERFORM] Sort-of replication for reporting purposes
On Fri, Jan 6, 2017 at 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? 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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Sort-of replication for reporting purposes
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?
Re: [PERFORM] Slow query after 9.3 to 9.6 migration
Thank you for the reply. I had been trying to find that option for awhile now. On Fri, Jan 6, 2017 at 12:51 PM, Michael Paquier wrote: > On Fri, Jan 6, 2017 at 6:14 AM, Filipe Oliveira > wrote: > > Can you remove me from your mailing list? > > There is an unsubscribe action here: > https://www.postgresql.org/community/lists/subscribe/ > -- > Michael >
Re: [PERFORM] Slow query after 9.3 to 9.6 migration
On Fri, Jan 6, 2017 at 6:14 AM, Filipe Oliveira wrote: > Can you remove me from your mailing list? There is an unsubscribe action here: https://www.postgresql.org/community/lists/subscribe/ -- Michael -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
