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

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.

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.