PostgreSQL is one of the most popular open-source relational database management systems within the web development space, being the go-to choice for many a developer.
It brings many advantages with it when deploying a ready-to-scale application, and with it being a server-side database it operates faster, handles concurrent requests, queues transactions and locks rows on update if set-up in your code to avoid race conditions in multi-threaded applications and has better firewall customisation and authentication methods in comparison to, let's say a sqlite3 file.
Let's start off by updating our package lists and installing what we need in our Ubuntu Linux environment:
sudo apt update
sudo apt install postgresql postgresql-contrib
Let's get our PostgreSQL server enabled and started in our Linux environment:
sudo systemctl enable postgresql
sudo systemctl start postgresql
Upon installing and booting up PostgreSQL, you will have a new username within your LInux shell which has permissions to access the PostgreSQL command line where you can input SQL queries. So, let's switch to the "postgres" user and access it.
sudo su - postgres
psql
We're in! Brilliant. Now, let's write some SQL queries to get set up. Let's start by creating our first database and we will call it "mysitedb". Note that each SQL query must end with a semi-colon:
CREATE DATABASE mysitedb;
First database created... that was easy, but we still can't access it yet. So now we need to create a username and password combination in PostgreSQL and grant it access to the database we've just created. We will make a user called "user_db" with the password as "abcd1234". In a true production setting, please make your password stronger than what I have here and store it securely. With sensative information like this, I usually encrypt my passwords and private keys and store them as an environment variable:
CREATE USER user_db WITH PASSWORD 'abcd1234';
GRANT ALL PRIVILEGES ON DATABASE mysitedb TO user_db;
Now let's just alter some roles on our user and set them correctly. For the timezone, select the timezone that is applicable to your application. I often however always set my application's timezone and my database's user role to UTC because this is the universal time, and if I need to display any local times on an app to the end user I just simply convert it. After we've done this we can come out of PostgreSQL's command line with "\q" and log out of our postgres user by just typing "exit":
ALTER ROLE user_db SET client_encoding TO 'utf8';
ALTER ROLE user_db SET timezone TO 'UTC';
\q
exit
Right! Now it's time to edit some of PostgreSQL's configuration files. If you have an application on a different server from your PostgreSQL server, you may need to configure port-forwarding rules on your router or VPS and then input the remote/external information in these files. I will give a commented-out example on each code snippet for setting up for remote connections, but my main code will assume that your app and your PostgreSQL database are on the same server, therefore will only need local interaction between app and database.
Let's get into out first file, which will be /etc/postgresql/{version}/main/postgresql.conf. {version} should be the version number of your PostgreSQL server, you can find this out simply with the "ls" command. My version is 14, so I will use this in my code snippet. In this file, we need to look for the listen_addresses and port variables and change this to our desired listening address and port. This is the IP address(es) and port of which your PostgreSQL server will be listening on. The standardised port is 5432 for PostgreSQL, but you can change it to anything you like.
/# etc/postgresql/14/main/postgresql.conf
listen_addresses = '127.0.0.1'
port = '5432'
# listen_addresses = '*' # For all accesible IP addresses on the system
# listen_addresses = '73.23.54.34,98.32.44.201' # Comma seperated list
Great! Now let's go over to the pg_hba.conf file. This file is for specifying the IP addresses that are trusted, require authentication or disallowed from connecting to our database alongside the type of connection, the database in question, and database username. We specify each parameter in this order: (TYPE, DATABASE, USER, ADDRESS, AUTH_METHOD). For security, it's best to keep the scope as limited as it possibly needs to be, but you can customise these to how you need. Again in the code snippet, I will set this as if the app is running on the same environment as the database, but will show other ways to set up. So, all you have to do is open your pg_hba.conf file and append this line to the file:
# /etc/postgresql/14/main/pg_hba.conf
# Allows TCP/IP connections (host) from 127.0.0.1 to the database "mysitedb" when you log in with "user_db" with a password (md5).
host mysitedb user_db 127.0.0.1/32 md5
# Allows TCP/IP connections through SSL (hostssl) from anyone who requests it to all databases when you log in with any username with its password (md5).
# hostssl all all all md5
# Allows TCP/IP connections (host) from 127.0.0.1 to all databases when you log in with "user_db" and you don't need a password to authenticate (trust).
# host all user_db
127.0.0.1/32 trust
All configuration done! Now we just need to restart PostgreSQL to put these changes into place. Put this command into your Linux command line:
sudo systemctl restart postgresql
Et voilà! That's our PostgreSQL configured and ready to be implemented into our app. From here on, I'm going to be showing you how to implement this into a Django back-end. If you use another back-end framework, PostgreSQL should be set up and ready for it, but you just need to find out how to implement it into your app.
Now we need to head into our Python virtual environment, assuming that you're using one. We need to install a PostgreSQL adapter package for Python called psycopg:
pip install psycopg
Now let's head over to our application's settings.py file. Inside it, we need to look for the DATABASES variable and make some changes to it. You can comment out or just simply delete the default sqlite3 configuration, whatever you're preference:
# /home/username/projects/mysite/mysite/settings.py
# I am showing you the raw data so you have a good idea of how it works. PLEASE store sensitive data like this in environment variables, preferably encrypted, then import and decrypt them into your app
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql',
'NAME': 'mysitedb',
'USER': 'user_db',
'PASSWORD': 'abcd1234',
'HOST': '127.0.0.1',
'PORT': '5432',
}
}
That's it! Now when you've set up your database models just:
python manage.py makemigrations
python manage.py migrate
And you're all set! Enjoy your production-level database ready to scale!