Re: [PERFORM] Sort-of replication for reporting purposes

От: Phillip Couto
Тема: Re: [PERFORM] Sort-of replication for reporting purposes
Дата: ,
Msg-id: 00aca7a0-cd9d-4947-8ad7-352e128196f0@mtasv.net
(см: обсуждение, исходный текст)
Ответ на: Re: [PERFORM] Sort-of replication for reporting purposes  (Ivan Voras)
Список: pgsql-performance

Скрыть дерево обсуждения

[PERFORM] Sort-of replication for reporting purposes  (Ivan Voras, )
 Re: [PERFORM] Sort-of replication for reporting purposes  (Scott Marlowe, )
  Re: [PERFORM] Sort-of replication for reporting purposes  (Ivan Voras, )
   Re: [PERFORM] Sort-of replication for reporting purposes  (Stuart Bishop, )
    Re: [PERFORM] Sort-of replication for reporting purposes  (Ivan Voras, )
     Re: [PERFORM] Sort-of replication for reporting purposes  (Stuart Bishop, )
     Re: [PERFORM] Sort-of replication for reporting purposes  ("Phillip Couto" , )
 Re: [PERFORM] Sort-of replication for reporting purposes  (Rick Otten, )
 Re: [PERFORM] Sort-of replication for reporting purposes  (ProPAAS DBA, )
 Re: [PERFORM] Sort-of replication for reporting purposes  (Stephen Frost, )

Why not utilize the pglogical plugin from 2ndQuadrant? They demonstrate your use case on the webpage for it and it is free. 

Phillip Couto
From:
Sent: January 13, 2017 06:20
To:
Cc: ;
Subject: Re: [PERFORM] Sort-of replication for reporting purposes

On 13 January 2017 at 12:00, Stuart Bishop <> wrote:


On 7 January 2017 at 02:33, Ivan Voras <> 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?




В списке pgsql-performance по дате сообщения:

От: Stuart Bishop
Дата:
Сообщение: Re: [PERFORM] Sort-of replication for reporting purposes
От: Samir Magar
Дата:
Сообщение: [PERFORM] out of range error while restore using pgdump