How To Install PostgreSQL on Ubuntu 20.04 [Quickstart] | DigitalOcean

Introduction

PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL query language. It complies with standards and has many advanced features such as reliable transactions and concurrency without read locks.

This guide shows how to quickly get Postgres up and running on an Ubuntu 20.04 server, from installing PostgreSQL to setting up a new user and database. If you prefer a more detailed tutorial on installing and managing a PostgreSQL database, see How to install and use PostgreSQL on Ubuntu 20.04.

Prerequisites

To continue with this tutorial, you will need an Ubuntu 20.04 server that has been configured following our guide Initial Server Setup for Ubuntu 20.04. After completing this prerequisite tutorial, the server must have a non-root user with sudo permissions and a basic firewall.

Step 1 — Installing PostgreSQL To install PostgreSQL,

first update your server’s local package index

: sudo apt update

Then, install the Postgres package along with a -contrib package that adds some additional utilities and functionality

: sudo apt install postgresql postgresql-contrib

Make sure the service is started:

1. sudo systemctl start postgresql.service

Step 2 — Using

1. PostgreSQL

Roles and Databases

By default, Postgres uses a concept called “roles” to handle authentication and authorization. These are, in some ways, similar to normal Unix-style users and groups.

After installation, Postgres

is configured to use ident authentication, which means that it associates Postgres roles with a matching Unix/Linux system account. If a role exists within Postgres, a Unix/Linux username with the same name can log in as that role.

The installation procedure created a user account named postgres associated with the default Postgres role. There are a few ways to use this account to access Postgres. One way is to switch to the postgres account on your server by running the following command

:

1. sudo -i -u postgres

You can then access the Postgres command prompt by running:

1. psql

This will log you into the PostgreSQL prompt,

and from here you will be able to interact with the database management system immediately. To exit the PostgreSQL prompt,

run the following:

1. q

This will take you back to the Linux postgres command prompt. To return to the usual system user,

run the exit:

1. exit

command Another way to connect to the Postgres prompt is to run the psql command as the postgres account directly with sudo:

1. sudo -u postgres psql

This will register you directly in Postgres without the intermediate bash shell in between.

Again, you can exit the interactive

Postgres

session by running the following:

1. q

Step 3 — Creating

a new role If you

are logged in as the postgres account, you can create a new role by running the following command: createuser -interactive If, instead, you prefer to use sudo for each command without changing from your normal account,

Run:

1. sudo -u postgres createuser -interactive

Either way, the script will ask you for some options and, based on your answers, it will run the correct Postgres commands to create a user based on your specifications.

OutputEnter the name of the role you want to add: sammy Will the new role be a superuser? (y/n) and

Step 4 — Creating

a new database Another assumption that the

Postgres authentication system makes by default is that for any role used to log in, that role will have a database with the same name that it can access.

This means that if the user you created in the last section is named sammy , That role will attempt to connect to a database that is also named “Sammy” by default. You can create the appropriate database with the createdb command.

If

you are logged in as

the postgres account, you would type something like the following: createdb sammy

If, instead, you prefer to use sudo for each command without changing from your normal account, you would run:

1. sudo -u postgres createdb sammy

Step 5 — Open a Postgres prompt with the new role

To log in with ident-based authentication, you’ll need a Linux user with the same name as your Postgres role and database.

If you don’t have a matching Linux user available, you can create one with the adduser command. You will need to do this from your non-root account with sudo privileges (i.e. you are not logged in as a postgres user):

sudo

1. adduser

sammy

Once this new account is available, you can switch and connect to the database by running the following

:

1. sudo -i -u sammy
2. psql

Or, you can do this online:

1. sudo -u sammy

psql

This command will automatically log in, assuming all components are configured correctly.

If you want the user to connect to a different database, you can do so by specifying the database as follows

:

1. psql -d postgres

Once logged in, you can verify your current connection information by running:

1. conninfo

OutputYou are connected to the database “sammy” as user “sammy” via socket in “/var/run/postgresql” on port “5432”.

Conclusion

You are now configured with PostgreSQL on your Ubuntu 20.04 server. If you want to learn more about Postgres and how to use it, we recommend that you check out the following guides:

• A comparison of relational database management systems
• Practice query execution with SQL