- Sudarshan Achaler
How to set up Postgres on an Ubuntu 20.04 server
Updated: Feb 28, 2022
PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language.
This guide demonstrates how to set up Postgres on an Ubuntu 20.04 server.
Prerequisites:
A server with Ubuntu installed.
Step 1: Installing Postgres
You can use the apt packaging system to install Postgres as Ubuntu’s default repositories contain Postgres packages.
You can update your server’s local package index by using the command:
$ sudo apt update
You can now install the Postgres using the command:
$ sudo apt install postgresql postgresql-contrib

The postgresql-contrib package adds some additional utilities and functionality.
Step 2: Logging in using the default user
The installation procedure created a user account called postgres that is associated with the default Postgres role. You can log into this account to use Postgres.
You can switch to a postgres user account by using the command:
$ sudo -i -u postgres
Now you can access the PostgreSQL prompt by typing the command:
$ psql
You can exit the PostgreSQL prompt by using the command: exit or \q.
You can also exit the postgres account by using the command: exit
You can also directly access the PostgreSQL prompt without switching accounts by using the command:
$ sudo -u postgres psql

Step 3: Creating a new Role (User)
First, you have to log in into your postgres account by using the command:
$ sudo -i -u postgres
You can create a new role using the command:
$ createuser --interactive
The script will prompt you with some choices and based on your responses, Postgres will create a role.

Step 4: Accessing Postgres prompt with the new role:
Upon installation, Postgres is set up to use peer authentication which means that it associates Postgres roles with a matching ubuntu system account. If a role exists within Postgres, an ubuntu username with the same name can sign in as that role.
So to access the Postgres prompt with the new role we have to add a new user to Ubuntu.
You can add a user in ubuntu using the command:
$ sudo adduser test_user
Note: username should be equal to the role name in Postgres.
Now you can switch over to a new account using the command:
$ sudo -i -u test_user
Postgres by default will assume that the user will connect to the database having the same name as the user.
So now if you try typing psql, Postgres will try to connect you to the database having the same name as the user (here test_user).
You can specify the database name by using the following syntax:
$ psql -d database_name
You can connect to the postgres database by typing:
$ psql -d postgres

Once logged in, you can check your current connection information by typing:
postgres=# \conninfo

Step 5: Creating a new database
To create a database you will have to switch account to a postgres account or a new account you created.
You can switch accounts by typing:
$ sudo -i -u username
For this guide, we have created a new user named test_user. So will first switch account to test_user.
$ sudo -i -u test_user
To create a new database you would type something like:
test_user@server:~$ createdb newDb
A new database named newDb will be created.
You can connect to this database using the command:
test_user@server:~$ psql -d newDb
You can directly connect to any database without switching accounts by using the command:
$ sudo -u test_user psql -d newDb

Conclusion:
You now have a basic PostgreSQL setup installed on your ubuntu server.
If you’d like to learn how to set up MySQL in ubuntu 20.04, we encourage you to check out the following guide:
How to set up MySQL on Ubuntu 20.04