How to install PostgreSQL on Ubuntu 20.04?

[Yang M.]

PostgreSQL (Postgres) is an open-source general-purpose object-relational database management system with numerous advanced capabilities that allows you to build fault-tolerant systems or complex applications.

In this tutorial, you will see how you can install PostgreSQL on Ubuntu 20.04.

1. Connect to your server and run system updates:

sudo apt update

2. After that, install the Postgres package together with a -contrib package that adds some additional utilities and functionality:

sudo apt install postgresql postgresql-contrib

3. Then make sure that the service is running:

sudo systemctl start postgresql.service

4. PostgreSQL uses a concept called “roles” to handle authorization and authentication, by default.

sudo -i -u postgres

5. So you can access the Postgres prompt with the command:

psql

It will log you into the PostgreSQL prompt, and you will be able to interact with the database management system.

To exit out of the PostgreSQL prompt, you can use this command:

\q

6. If you are logged in as the postgres account, you can create a new role by running:

createuser --interactive

Then you will need to enter the name of the new role and make the role as superuser.

7. Another default assumption of the Postgres authentication system is that each role used to log in will have a database with the same name that it can access.

This implies that if the user you established in the previous section is named, for example, test, the role will attempt to connect to a database named test by default. The createdb command can be used to construct the necessary database.

If you're logged in as the postgres account, you can use this command:

createdb test

If you want to use sudo for each command without switching from your normal account, you can use the following command:

sudo -u postgres createdb test

8. You'll need a Linux user with the same name as your Postgres role and database to log in with ident based authentication.

If you don't have a matching Linux user, use the adduser command to create one. You must do the following using a non-root account with sudo access (not as the postgres user):

sudo adduser test

9. Then the new account is created, you can either switch over and connect to the database by running these commands:

sudo -i -u sammy
psql

10. However, if you want your user to connect to a different database, you can do that by specifying the database:

psql -d postgres

11. After running that command, you can check your current connection information by running this command:

\conninfo