Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I could not find a solution for this anywhere, not even in the pgpool manuals.

How do I configure multiple - master postgresql databases with pgpool-II? Additional requirement is that each master should have its own set of slaves using streaming replication. The reason for multiple master is that each master holds a partition of a large data set.

Any help and guidance is much appreciated. Thank you.

share|improve this question

migrated from stackoverflow.com Jan 25 at 13:50

This question came from our site for professional and enthusiast programmers.

    
Are these masters related to each other or are they more like siloed data sets that don't interact or need to be updated across each other? A project worth looking at for massive systems is plproxy, which moves all this logic into a set of postgres servers that simply dispatch all the work to be done onto slaves. But without knowing what you're really trying to do it's hard to recommend a solution. –  Scott Marlowe Jan 26 at 3:54
    
Scott - thank you very much for your comment. Great question. It is a large data set (say about 100 million users, their account information, plus additional information about their accounts) which is partitioned; so yes it is siloed. The data model is being designed so that the siloes do not need to talk to each other. –  Rajeev Nair Jan 26 at 5:25
    
I will take a look at the plproxy solution. I was hopeful about pgpool-II when I saw parallel query and data partitioning as an option. But the document is super light. –  Rajeev Nair Jan 26 at 5:26
    
Well if they are fully siloed then you don't need plproxy per se. But it is a great method to silo things with. I.e. one (set of) pl proxy machine(s) handle all requests and route them to the proper silo without the application having to be silo aware. There are plenty of ways to skin this cat. Basically if you have 2 or more silos and the application is silo-aware then you can run a different plpool / pgbouncer server on a different port for each silo. So silo1 answers on port 65432, silo2 on 65433, and so on. Each pgpool / pgbouncer server you run is itself isolated from the other.s –  Scott Marlowe Jan 26 at 5:46

1 Answer 1

OK your first decision is WHERE to split the silos out, in the application layer, or in the db layer. If your application will keep track of the silos then you can run individual pgbouncer or pgpool servers on different ports for each silo.

If you want the application to not have to know or deal with how siloing is done, then something like plproxy can work wonders. Basically you have 1 or more plproxy servers that distribute all the queries / data based on some pre-determined modulo math or some other method for breaking it up, and your app just calls the plproxy server with it's queries and the plproxy server is where the logic for siloing exists.

Each method has it's pluses and minuses. The nice thing about it being in the app layer is that your database layer is fairly simple. just a bunch of master/slave pairs. If you add a new silo you just edit the config file for it in the app and it goes online. The disadvantage is that all apps hitting your data HAVE to be silo aware etc.

With plproxy the advantage is that the app never knows its siloed. If you write a fairly simple SOAP type interface to talk to the plproxy servers then every app your write can use that single interface and never have to be told which silo does what. Now though the complexity is moved into the db layer. Any changes to the farm structure require work on the db level. Also plproxy works by basically wrapping all your database work into functions ahead of time. If you want to select data from a table joined to another table you need to create a function to do that and call that function on the plproxy machine. It seems overly complex at first but actually it's not that hard, just quite different from slinging random SQL at your db servers.

share|improve this answer
    
Scott - Thank you very much for the valuable comments. I was initially leaning towards housing the partitioning logic in the app layer. But I read about the capabilities of plproxy and your comments, and I have decided to give plproxy a try. Thinking ahead, I feel that plproxy (for data partitioning, coon pooling) can chained with pgpool-II (for load balancing, master-slave streaming replication failover management). I do not know if this chaining is kosher, but no harm giving it a try. Thank you very much for your guidance. –  Rajeev Nair Jan 29 at 6:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.