-1

I have the situation where I am running my application and Postgresql database on same Ec2 instance. Data of Postgresql gets saved on another EBS Volume different from the Root volume of the instance.

If I want to load balance this setup by creating another instance then :

  1. What are the techniques that will help me keep database on both instances in sync?

  2. What are the pros and cons of those techniques?

  3. Is going for separate database server better than going through the above hassle? (Right now the main reason I am avoiding the separate database instance is to remain within the free tier)

codecool
  • 123
  • 7

2 Answers2

3

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.

Craig Ringer
  • 10,553
  • 9
  • 38
  • 59
  • 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? – codecool Nov 23 '12 at 11:08
  • @codecool I'd run a separate DB server, personally. – Craig Ringer Nov 23 '12 at 11:12
0

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.

Matt Houser
  • 9,709
  • 1
  • 26
  • 25
  • PostgreSQL does not support "many-to-many replication" (multi-master replication). Postgres-XC does, but I haven't tested it. – Craig Ringer Nov 22 '12 at 23:31