Re: Sort-of replication for reporting purposes

On 13 January 2017 at 18:17, Ivan Voras <ivoras(at)gmail(dot)com> wrote:

> On 13 January 2017 at 12:00, Stuart Bishop <stuart(at)stuartbishop(dot)net>
> wrote:
>
>>
>>
>> On 7 January 2017 at 02:33, Ivan Voras <ivoras(at)gmail(dot)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(at)stuartbishop(dot)net>
http://www.stuartbishop.net/