We're at the juncture (I can even say, right time) that we need to separate our database server apart from our application web server. This we arrived at after careful & thorough monitoring of the server (load average, CPU usage, etc.) itself.

When the database server is separated:

What would be the ideal setup/best practice for network communication path between 2 servers? If it were running on the same server, connectivity between database and application would make use of TCP/IP communication, but how it should be running/setup when it is separated, so that performance is not majorly affected? For example, currently our JDBC URL looks like jdbc:postgresql://localhost:9999/ourproductiondb, probably this localhost would be changed to private IP address of the database server.

In our application, we also allow application users to submit/attach attachments (.txt, .doc, .pdf, .xls, etc.), which are actually stored physically in server disk and only its file path is stored in the database. Which is the ideal place/approach for maintaining/keeping documents physically, either in application web server or in database server?

We're very new into this area of separating database as a separate server and our knowledge on this is very limited. Therefore, any resources, links, documentation, articles, guidelines, suggestion etc. in this regard are highly welcome and really appreciated.

1 Answer
1

Maybe I don't get the question but the best (and the only) option you have IS very same TCP/IP. The only thing you may be concerned is latency (ping time) between the servers. As long as it is on the same LAN, it's ok.

The ideal place is maybe the least loaded server of the two. Since files are not stored in database, they have nothing to do with the database. It seems to be most straightforward just to keep them on application server.

All you have to do is move PostgreSQL and change that "localhost" in your URL to the new server IP.