One of the many architectural decisions that will start to impact you when you get to a level where you need to scale is splitting you db and app. Typically we start on a budget and have to share resources but ideally you want to start out separate. The reasons is that the db server will know exactly how much RAM is available to it at all times and will hence improve the consistency and reliability.
Provision 2 Servers
To start off provision 2 (ubuntu) servers, to label things give each a fully qualified domain name like web.myserver.com and db.myserver.com
Then do a basic security and authentication setup on both servers.
The App Server
To setup the app server you can use this guide which uses python 3.6, Nginx, gunicorn and mysql. Just skip the database setup part.
The Database Server
We need a role (user) for the database and because this role will be adding extensions it needs to be a superuser.
CREATE ROLE dbuser LOGIN PASSWORD 'mydbpass' SUPERUSER;Importantly we need to look at django’s optimal postgres config
ALTER ROLE dbuser SET client_encoding TO 'utf8';
ALTER ROLE dbuser SET default_transaction_isolation TO 'read committed';
ALTER ROLE dbuser SET timezone TO 'UTC';Then create the database:
CREATE DATABASE myproject;Ok…so now fill out the DATABASES setting in your application and make sure the HOST is the internal ip as the servers are within the same data-center hopefully.
But we will need to configure postgres to allow and listen for connections from the internal network. We don’t want public ip’s to have access to it only our other app server within the same datacentre. I’ve done this with MySQL but forgot how to it, so I’m searching how to do it now.
First thing is setup the uncomplicated firewall with:
sudo ufw enable
sudo ufw allow OpenSSH
sudo ufw status
Now we want to enable connections from our app server:
sudo ufw allow from app_server_internal_ip_address to any port 5432Log into psql and set it to listen on all ip’s:
ALTER SYSTEM SET listen_addresses = '*';then reload the server:
SELECT pg_reload_conf();
Check where your pg_hba.conf is with:
SELECT name, setting FROM pg_settings WHERE category = 'File Locations';then add the following line:
# IPv4 local connections:
host all all 10.0.0.4/32 md5
Restart
sudo systemctl restart postgresql
Test with the postgres client on the app server:
sudo apt install postgresql-clientThere are a few performance tweaks you can do, but I’m always inclined to leave it standard before doing that.
https://www.digitalocean.com/community/tutorials/how-to-secure-postgresql-against-automated-attacks
https://stackoverflow.com/questions/22080307/access-postgresql-server-from-lan