First, in general, if you have dedicated servers, I think you are usually better off using them. Through a business that does hosting for a PostgreSQL-backed ERP, I am relatively aware of what the problems are. For us, replication is more or less set up out of the box but tuning the db is problematic. I would expect that 2ndQuadrant's hosting (which I think is dedicated server if I understand correctly) to be able to set up and things for you, but I am not sure what the package would cost.
At Efficito, we set up replication by default since we use it for taking base backups (pg_basebackup requires that replication is enabled and continuous archiving configured). This being said, that's the easy part.
Auto-configuration for performance tuning and resources is a big difficulty. This is dependent, honestly, not only on resources available but on query profiles. Things like effective cache size can be changed, but what about shared_buffers? Increasing this can have a negative impact on database performance in some cases.
One huge issue with cloud hosting though is that it is far harder to guarantee the performance required than it is with dedicated hardware. If dedicated hardware is an option, go with it (and if you need hosting/management, contact 2ndQuadrant). If you really need cloud hosting, consider the options by others.
postgresql
tag with 14 upvotes. From the answers, it seems that Heroku is the only popular choice with AWS+Postgre Plus Cloud being a distant second. – user Sep 14 '13 at 10:16