How to set up Postgres on an Ubuntu 20.04 server

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