pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSort-of replication for reporting purposes
10+ messages / 7 participants
[nested] [flat]
* Sort-of replication for reporting purposes
@ 2017-01-06 19:24 Ivan Voras <ivoras@gmail.com>
0 siblings, 4 replies; 10+ messages in thread
From: Ivan Voras @ 2017-01-06 19:24 UTC (permalink / raw)
To: pgsql-performance
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?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sort-of replication for reporting purposes
@ 2017-01-06 19:30 Scott Marlowe <scott.marlowe@gmail.com>
parent: Ivan Voras <ivoras@gmail.com>
3 siblings, 1 reply; 10+ messages in thread
From: Scott Marlowe @ 2017-01-06 19:30 UTC (permalink / raw)
To: Ivan Voras <ivoras@gmail.com>; +Cc: pgsql-performance
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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sort-of replication for reporting purposes
@ 2017-01-06 19:33 Ivan Voras <ivoras@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Ivan Voras @ 2017-01-06 19:33 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
On 6 Jan 2017 8:30 p.m., "Scott Marlowe" <scott.marlowe@gmail.com> wrote:
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.
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sort-of replication for reporting purposes
@ 2017-01-06 19:33 Rick Otten <rottenwindfish@gmail.com>
parent: Ivan Voras <ivoras@gmail.com>
3 siblings, 0 replies; 10+ messages in thread
From: Rick Otten @ 2017-01-06 19:33 UTC (permalink / raw)
To: Ivan Voras <ivoras@gmail.com>; +Cc: pgsql-performance
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 <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?
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sort-of replication for reporting purposes
@ 2017-01-06 19:43 Stephen Frost <sfrost@snowman.net>
parent: Ivan Voras <ivoras@gmail.com>
3 siblings, 0 replies; 10+ messages in thread
From: Stephen Frost @ 2017-01-06 19:43 UTC (permalink / raw)
To: Ivan Voras <ivoras@gmail.com>; +Cc: pgsql-performance
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sort-of replication for reporting purposes
@ 2017-01-12 17:48 ProPAAS DBA <dba@propaas.com>
parent: Ivan Voras <ivoras@gmail.com>
3 siblings, 0 replies; 10+ messages in thread
From: ProPAAS DBA @ 2017-01-12 17:48 UTC (permalink / raw)
To: pgsql-performance
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sort-of replication for reporting purposes
@ 2017-01-13 11:00 Stuart Bishop <stuart@stuartbishop.net>
parent: Ivan Voras <ivoras@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Stuart Bishop @ 2017-01-13 11:00 UTC (permalink / raw)
To: Ivan Voras <ivoras@gmail.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance
On 7 January 2017 at 02:33, Ivan Voras <ivoras@gmail.com> wrote:
>
>
> On 6 Jan 2017 8:30 p.m., "Scott Marlowe" <scott.marlowe@gmail.com> wrote:
>
> 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.
>
>
> 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.
>
If you don't want to rebuild your report databases, you can use PostgreSQL
built in replication to keep them in sync. Just promote the replica to a
primary, run your reports, then wind it back to a standby and let it catch
up. pg_rewind might be able to wind it back, or you could use a filesystem
snapshot from before you promoted the replica to a primary. You do need to
ensure that the real primary keep enough WAL logs to cover the period your
report database is broken out.
Personally though, I'd take the opportunity to set up wal shipping and
point in time recovery on your primary, and rebuild your reporting database
regularly from these backups. You get your fresh reporting database on
demand without overloading the primary, and regularly test your backups.
--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sort-of replication for reporting purposes
@ 2017-01-13 11:17 Ivan Voras <ivoras@gmail.com>
parent: Stuart Bishop <stuart@stuartbishop.net>
0 siblings, 2 replies; 10+ messages in thread
From: Ivan Voras @ 2017-01-13 11:17 UTC (permalink / raw)
To: Stuart Bishop <stuart@stuartbishop.net>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance
On 13 January 2017 at 12:00, Stuart Bishop <stuart@stuartbishop.net> wrote:
>
>
> On 7 January 2017 at 02:33, Ivan Voras <ivoras@gmail.com> wrote:
>
>>
>>
>>
>> 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.
>>
>
> If you don't want to rebuild your report databases, you can use PostgreSQL
> built in replication to keep them in sync. Just promote the replica to a
> primary, run your reports, then wind it back to a standby and let it catch
> up.
>
Ah, that's a nice option, didn't know about pg_rewind! I need to read about
it some more...
So far, it seems like the best one.
> Personally though, I'd take the opportunity to set up wal shipping and
> point in time recovery on your primary, and rebuild your reporting database
> regularly from these backups. You get your fresh reporting database on
> demand without overloading the primary, and regularly test your backups.
>
I don't think that would solve the main problem. If I set up WAL shipping,
then the secondary server will periodically need to ingest the logs, right?
And then I'm either back to running it for a while and rewinding it, as
you've said, or basically restoring it from scratch every time which will
be slower than just doing a base backup, right?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sort-of replication for reporting purposes
@ 2017-01-13 11:47 Phillip Couto <phillip@couto.in>
parent: Ivan Voras <ivoras@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Phillip Couto @ 2017-01-13 11:47 UTC (permalink / raw)
To: Ivan Voras <ivoras@gmail.com>; Stuart Bishop <stuart@stuartbishop.net>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance
<html><head><meta http-equiv="Content-Security-Policy" content="script-src 'self'; img-src * cid: data:;"></head><body style="background-color: rgb(255, 255, 255); background-image: initial; line-height: initial;"><div id="response_container_BBPPID" style="outline:none;font-size:initial;font-family:"Calibri","Slate Pro",sans-serif,"sans-serif";color:#1f497d;" contenteditable="false"> <div name="BB10" dir="auto" style="width: 100%; background: rgb(255, 255, 255); padding: initial; font-size: initial; text-align: initial;"> Why not utilize the pglogical plugin from 2ndQuadrant? They demonstrate your use case on the webpage for it and it is free. </div> <div name="BB10" dir="auto" style="width: 100%; background: rgb(255, 255, 255); padding: initial; font-size: initial; text-align: initial;"> <br style="display:initial"></div> <div id="blackberry_signature_BBPPID" name="BB10" dir="auto"> <div name="BB10" dir="auto" style="padding: initial; font-size: initial; text-align: initial; background-color: rgb(255, 255, 255);">Phillip Couto</div> </div></div><div id="_original_msg_header_BBPPID"> <table width="100%" style="background-color: white; border-spacing: 0px; display: table; outline: none;" contenteditable="false"> <tbody><tr><td colspan="2" style="padding: initial; font-size: initial; text-align: initial; background-color: rgb(255, 255, 255);"> <div style="border-right: none; border-bottom: none; border-left: none; border-image: initial; border-top: 1pt solid rgb(181, 196, 223); padding: 3pt 0in 0in; font-family: Tahoma, "BB Alpha Sans", "Slate Pro"; font-size: 10pt;"> <div id="from"><b>From:</b> ivoras@gmail.com</div><div id="sent"><b>Sent:</b> January 13, 2017 06:20</div><div id="to"><b>To:</b> stuart@stuartbishop.net</div><div id="cc"><b>Cc:</b> scott.marlowe@gmail.com; pgsql-performance@postgresql.org</div><div id="subject"><b>Subject:</b> Re: [PERFORM] Sort-of replication for reporting purposes</div></div></td></tr></tbody></table><div style="border-right: none; border-bottom: none; border-left: none; border-image: initial; border-top: 1pt solid rgb(186, 188, 209); display: block; padding: initial; font-size: initial; text-align: initial; background-color: rgb(255, 255, 255);"></div> <br> </div><!--start of _originalContent --><div name="BB10" dir="auto" style="background-image: initial; line-height: initial; outline: none;" contenteditable="false"><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On 13 January 2017 at 12:00, Stuart Bishop <span dir="ltr"><<a href="mailto:stuart@stuartbishop.net">stuart@stuartbishop.net</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote"><div><div class="m_3901336426231379794m_-3749035723351087179h5">On 7 January 2017 at 02:33, Ivan Voras <span dir="ltr"><<a href="mailto:ivoras@gmail.com">ivoras@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="auto"><div><br><div class="gmail_extra"><div class="gmail_quote"><blockquote class="m_3901336426231379794m_-3749035723351087179m_6989716290429316170m_5684550514169094327quote" style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex"><br>
</blockquote></div><br></div></div><div class="gmail_extra" dir="auto">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.</div></div></blockquote><div><br></div></div></div><div>If you don't want to rebuild your report databases, you can use PostgreSQL built in replication to keep them in sync. Just promote the replica to a primary, run your reports, then wind it back to a standby and let it catch up. </div></div></div></div></blockquote><div><br></div><div><br></div><div>Ah, that's a nice option, didn't know about pg_rewind! I need to read about it some more...</div><div>So far, it seems like the best one.</div><div><br></div><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><div>Personally though, I'd take the opportunity to set up wal shipping and point in time recovery on your primary, and rebuild your reporting database regularly from these backups. You get your fresh reporting database on demand without overloading the primary, and regularly test your backups.<br></div></div></div></div></blockquote><div><br></div><div>I don't think that would solve the main problem. If I set up WAL shipping, then the secondary server will periodically need to ingest the logs, right? And then I'm either back to running it for a while and rewinding it, as you've said, or basically restoring it from scratch every time which will be slower than just doing a base backup, right?</div><div><br></div><div><br></div><div><br></div></div></div></div>
<!--end of _originalContent --></div></body></html>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Sort-of replication for reporting purposes
@ 2017-01-13 11:48 Stuart Bishop <stuart@stuartbishop.net>
parent: Ivan Voras <ivoras@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Stuart Bishop @ 2017-01-13 11:48 UTC (permalink / raw)
To: Ivan Voras <ivoras@gmail.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance
On 13 January 2017 at 18:17, Ivan Voras <ivoras@gmail.com> wrote:
> On 13 January 2017 at 12:00, Stuart Bishop <stuart@stuartbishop.net>
> wrote:
>
>>
>>
>> On 7 January 2017 at 02:33, Ivan Voras <ivoras@gmail.com> wrote:
>>
>>>
>>>
>>>
>>> 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.
>>>
>>
>> If you don't want to rebuild your report databases, you can use
>> PostgreSQL built in replication to keep them in sync. Just promote the
>> replica to a primary, run your reports, then wind it back to a standby and
>> let it catch up.
>>
>
>
> Ah, that's a nice option, didn't know about pg_rewind! I need to read
> about it some more...
> So far, it seems like the best one.
>
>
>
>> Personally though, I'd take the opportunity to set up wal shipping and
>> point in time recovery on your primary, and rebuild your reporting database
>> regularly from these backups. You get your fresh reporting database on
>> demand without overloading the primary, and regularly test your backups.
>>
>
> I don't think that would solve the main problem. If I set up WAL shipping,
> then the secondary server will periodically need to ingest the logs, right?
> And then I'm either back to running it for a while and rewinding it, as
> you've said, or basically restoring it from scratch every time which will
> be slower than just doing a base backup, right?
>
It is solving a different problem (reliable, tested backups). As a side
effect, you end up with a copy of your main database that you can run
reports on. I'm suggesting that maybe the slow restoration of the database
is not actually a problem, but instead that you can use it to your
advantage. Maybe this fits into your bigger picture. Or maybe having a
dozen hot standbys of your existing dozen servers is a better option for
you.
--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2017-01-13 11:48 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-01-06 19:24 Sort-of replication for reporting purposes Ivan Voras <ivoras@gmail.com>
2017-01-06 19:30 ` Scott Marlowe <scott.marlowe@gmail.com>
2017-01-06 19:33 ` Ivan Voras <ivoras@gmail.com>
2017-01-13 11:00 ` Stuart Bishop <stuart@stuartbishop.net>
2017-01-13 11:17 ` Ivan Voras <ivoras@gmail.com>
2017-01-13 11:47 ` Phillip Couto <phillip@couto.in>
2017-01-13 11:48 ` Stuart Bishop <stuart@stuartbishop.net>
2017-01-06 19:33 ` Rick Otten <rottenwindfish@gmail.com>
2017-01-06 19:43 ` Stephen Frost <sfrost@snowman.net>
2017-01-12 17:48 ` ProPAAS DBA <dba@propaas.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox