Install and Use PostgreSQL on Ubuntu 18.04

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:

psql

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:

\q

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:

\q

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:

createuser --interactive

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:

createdb sammy

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

sudo -u postgres createdb sammy

Conclusion

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:

Total
0
Shares
Leave a Reply

Your email address will not be published. Required fields are marked *

Previous Post

Install Node.js on Ubuntu 20.04

Next Post

Create a New User and Grant Permissions in MySQL

Related Posts
Total
0
Share