2 Answers
2

To keep PostgreSQL databases in sync, you'll want a single master database that you write to, and one or more read-only slave databases that follow it using PostgreSQL's built-in replication. See the user manual. PostgreSQL does not support multi-master replication (where all replicas are writeable) natively.

In this case, though, I cannot imagine you needing a second database for load reasons. If you're on the EC2 free tier then you're using a severely throttled micro instance with woeful disk I/O performance on EBS. You'll get a thousand (or more) times more performance with an EBS-optimized large instance with a striped raid10 array of four EBS provisioned IOPS volumes. Even an ordinary medium instance is likely to perform massively better. I wrote a bit about this recently.

Trying to scale out at this point is totally unnecessary for performance. However, it's a good idea for data protection and redundancy. Consider using barman with hook scripts to store your base backups and WAL archives in S3, or adding a second PostgreSQL server in a different availability zone that runs a streaming replica of the master server. Remember to keep good backups off AWS too, like nightly database dumps.

Good info. At this point of time my concern is not load or performance . Actual concern is if I want to start a new application instance then how will I connect it to the database. One option is keeping a separate db server. Other is I treat the original instance running the db as db server for newly generated instance. Does this make sense?
–
codecoolNov 23 '12 at 11:08

If you're going to load balance, you need to assume that at some point, you'll have more than 2 instances running. This means, that if you have a Postgresql server on each, you'll have to perform many-to-many replication, which is probably a big hassle to setup (if it even supports such a thing).

The best solution is to move the database off the application server so that each instance accesses a common database server.