When building a web app, you will often find yourself in need of a database. There are several to choose from and many places to host them, but one of the best and cheapest is running PostgreSQL on a virtual private server (VPS). It's a low effort way to add persistence to your application and works effectively with apps running on the server.
The steps to install PostgreSQL and get it running are pretty straightforward. You will need root access to the server to install the database. The first step is to add the PostgreSQL repo to the machine so you can install different versions. The default Ubuntu package is often out of date, and it is best to install the latest version.
# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Update the package lists:
sudo apt-get update
# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get -y install postgresql-14
Congratulations! You now have PostgreSQL running on Ubuntu. When you change a configuration, you will need to restart the service.
service postgresql restart
service postgresql stop
service postgresql start
You can find the configuration files in this directory when you need to make
Setting up a Database
After setting up a database, a common next step is to create credentials for your application. You don't want to use the default Postgres user for this. Instead, you should create a new user, a database, and grant permission to access it. You can do that with the following commands:
sudo -u postgres psql
CREATE DATABASE name_of_database;
CREATE USER name_of_user WITH ENCRYPTED PASSWORD 'secret_password_here';
ALTER DATABASE name_of_database OWNER TO name_of_user;
GRANT ALL PRIVILEGES ON DATABASE name_of_database TO name_of_user;
The above commands will create a database and a user. Then, it will grant the user the permissions it needs to work with that database. The last line gives all permissions, but you can grant a subset.
A note about AWS RDS
If you are setting up a new database on RDS, you'll want to first create the user and then login with them.
CREATE USER name_of_user WITH CREATEDB ENCRYPTED PASSWORD 'secret_password_here';
Then you can follow the commands above (without recreating the user again).
The new user will not be able to log in because PostgreSQL limits the connections to a Unix socket by default. Your application most likely will need to connect with a regular password, so you should update the connection method:
/etc/postgresql/14/main/pg_hba.conf and change the last line marked here:
# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
# THIS IS THE LINE YOU WANT TO CHANGE TO BE THE FOLLOWING:
local all all md5
Then restart the service:
service postgresql restart.
Now you can test the connection:
root@hostname:~# psql -U name_of_user -d name_of_database
Password for user name_of_user:
You can view the important directories from the configuration file:
The database data directory by default is:
Further, you should update the password mode from
password_encryption = 'scram-sha-256'
postgresql.conf. If you have set any passwords, you will need to reset them.
If you want Postgres to listen on the open Internet, you can change the
listen_address = * in
postgresql.conf. While generally not a good idea if you can avoid it, a strong passwords and TLS connections will be secure.
Also keep the database and server up to date.
Connecting via SSL
Postgres has SSL enabled by default. To connect with it, add the following parameter to your connection string:
Congratulations! You have set up PostgreSQL on your Ubuntu system, created a user and database for your application, and can connect to it. So, wire up your app and keep coding!