I am thinking about development of simple CRM for florists. Florists (company with 1-5 employees) will have local installed app on their PCs (call it "Floppa"). Floppa will be communicating to Windows Communication Foundation (WCF) service hosted on my remote server. For each of customer there will be running one instance of WCF service. Business layer under WCF service will be communicating to Postgresql database dedicated to specific customer.
I would like to use continuous archiving. If I understand well WALs will contain logs for every change in every database in cluster.
If one of customers will accidentally deletes/change some data and will ask me for recovery "today's morning state of data" how could I quickly comply his request? According my current knowledge I would need to do:
- Stop WCF service (to prevent work on db which will be replace later)
- Restore whole cluster to "today's morning state" on another computer
- Dump customer's morning state database
- Replace customer's database by dumped one
- Start WCF service
Is that correct? Or is there any better solution to this backup/restore issue? What happen if I will create one database per cluster? What will be the drawbacks? I guess that one of advantages will be much more faster recovery.
What will differ if count of customers will be 10, 50, 100, 250? How Postgresql handles hundreds of clusters?