PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. It is a popular choice for many small and large projects and has the advantage of being standards-compliant and having many advanced features like reliable transactions and concurrency without reading locks.
Step 1 — Installing PostgreSQL
Ubuntu’s default repositories contain Postgres packages, so you can install these using the
apt packaging system.
Since this is your first time using
apt in this session, refresh your local package index. Then, install the Postgres package along with a
-contrib package that adds some additional utilities and functionality:
sudo apt update sudo apt install postgresql postgresql-contrib
Now that the software is installed, we can go over how it works and how it may be different from similar database management systems you may have used.
Step 2 — Using PostgreSQL Roles and Databases
By default, Postgres uses a concept known as roles to handle authentication and authorization. These are, in some ways, similar to regular Unix-style accounts, but Postgres does not distinguish between users and groups and instead prefers the more flexible term “role”.
The installation procedure created a user account called Postgres that is associated with the default Postgres role. In order to use Postgres, you can log into that account.
There are a few ways to use this account to access Postgres.
Switching Over to the postgres Account
Switch over to the Postgres account on your server by typing:
sudo -i -u postgres
You can now access a Postgres prompt immediately by typing:
This will log you into the PostgreSQL prompt, and from here you are free to interact with the database management system right away.
Exit out of the PostgreSQL prompt by typing:
This will bring you back to the
postgres Linux command prompt.
Accessing a Postgres Prompt Without Switching Accounts
In the last example, you were instructed to get to the Postgres prompt by first switching to the Postgres user and then running psql to open the Postgres prompt. You could alternatively do this in one step by running the single command psql as the Postgres user with
sudo, like this:
sudo -u postgres psql
This will log you directly into Postgres without the intermediary
bash shell in between.
Again, you can exit the interactive Postgres session by typing:
Many use cases require more than one Postgres role. Read on to learn how to configure these.
Step 3 — Creating a New Role
Currently, you just have the Postgres role configured within the database. You can create new roles from the command line with the createrole command. The –interactive the flag will prompt you for the name of the new role and also ask whether it should have superuser permissions.
If you are logged in as the Postgres account, you can create a new user by typing:
If, instead, you prefer to use
sudo for each command without switching from your normal account, type:
sudo -u postgres createuser --interactive
First, the prompt will ask you to specify a name for the new role. The following example names the role Rahul but you can name yours whatever you like:
OutputEnter name of role to add: rahul
Next, you’ll be asked if the new role should be a superuser. In PostgreSQL, a superuser role has extremely broad privileges and can bypass nearly all permission checks.
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 which it can access.
If you are logged in as the Postgres account, you would type something like:
If instead, you prefer to use
sudo for each command without switching from your normal account, you would type:
sudo -u postgres createdb sammy
You are now set up with PostgreSQL on your Ubuntu 20.04 server. However, there is still much more to learn with Postgres. Here are some more guides that cover how to use Postgres: