Search Postgresql Archives

Re: Merge a sharded master into a single read-only slave

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Jun 4, 2014 at 1:50 PM, Keith Fiske <keith@xxxxxxxxxx> wrote:
Not sure if this will work for you, but sharing a similar scenario in case it may work for you.

An extension I wrote provides similar logical replication as you've probably seen in other tools.
https://github.com/omniti-labs/mimeo
One difference is it has several methods for this replication, one being incremental based on either time or serial ID. Since incremental replication requires just read-only access on the source databases, it causes no extra write overhead as most logical replication solutions do (triggers writing to queue tables).

A client of ours had a table sharded by UUID to 512 clusters but needed that data pulled to a single cluster for reporting purposes. The tables also had a timestamp column that was set on each insert/update, so the incremental replication method was able to be used here to pull data from all clusters to a single cluster. The single reporting cluster then just had an inheritance table set up with an empty parent table pointing to all the child tables that pulled data into them.

Yes, it was a lot of setup since each of the 512 tables has to be set up individually. But once it was set up it worked surprisingly well. And it's honestly a use case I had never foreseen for the extension.

Not sure if this would work in your case, but maybe it can at least give you an idea of what can be done.

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com


​Thank you Keith for taking the time to let me know about your solution. It looks great indeed, especially the part about not putting load on the shards themselves. Correct me if I am wrong, but will it not also suffer the same limitation as any statement based replication, namely that the "merged" slave will have to sustain the same write load as all shards combined ?

Sébastien


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux