From: Phillip Couto <phillip@couto.in>
To: Ivan Voras <ivoras@gmail.com>
To: Stuart Bishop <stuart@stuartbishop.net>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Sort-of replication for reporting purposes
Date: Fri, 13 Jan 2017 06:47:36 -0500
Message-ID: <00aca7a0-cd9d-4947-8ad7-352e128196f0@mtasv.net> (raw)
In-Reply-To: <CAF-QHFUg8NiiTSBKucK9zf0uOzoKyAjrS0ruGGzB+dabpHVSng@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: phillip@couto.in, ivoras@gmail.com, stuart@stuartbishop.net, scott.marlowe@gmail.com
Subject: Re: Sort-of replication for reporting purposes
In-Reply-To: <00aca7a0-cd9d-4947-8ad7-352e128196f0@mtasv.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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