How to install PostgreSQL on ubuntu 20.04 LTS

Step 1 – Enable PostgreSQL Apt Repository

PostgreSQL packages are also available in default Ubuntu repository. So you need to add PostgreSQL apt repository to your system suggested on official PostgreSQL website using following command.

Start with the import of the GPG key for PostgreSQL packages.

sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Now add the repository to your system.

sudo sh -c "echo "deb http://apt.postgresql.org/pub/repos/apt/ "lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list"

Step 2 – Install PostgreSQL on Ubuntu

Now as we have added PostgreSQL official repository in our system, First we need to update the repository list. After that install Latest PostgreSQL Server in our Ubuntu system using the following commands.

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Multiple other dependencies will also be installed. PostgreSQL 12 is the latest available version during the last update of this tutorial.

Creating PostgreSQL users

A default PostgresSQL installation always includes the postgres superuser. Initially, you must connect to PostgreSQL as the user until you create other users (which are also referred to as roles).

To create a PostgreSQL user, follow these steps:

1 - At the command line, type the following command as the server’s root user:

su - postgres

2 - You can now run commands as the PostgreSQL superuser. To create a user, type the following command:

createuser --interactive --pwprompt

3 - At the Enter name of role to add: prompt, type the user’s name.

4 - At the Enter password for new role: prompt, type a password for the user.

5 - At the Enter it again: prompt, retype the password.

6 - At the Shall the new role be a superuser? prompt, type y if you want to grant superuser access. Otherwise, type n.

7 - At the Shall the new role be allowed to create databases? prompt, type y if you want to allow the user to create new databases. Otherwise, type n.

8 - At the Shall the new role be allowed to create more new roles? prompt, type y if you want to allow the user to create new users. Otherwise, type n.

9 - PostgreSQL creates the user with the settings you specified.

Creating PostgreSQL databases

To create a PostgreSQL database, follow these steps:

1 - At the command line, type the following command as the server’s root user:

su - postgres

2 - You can now run commands as the PostgreSQL superuser. To create a database, type the following command. Replace user with the name of the user that you want to own the database, and replace dbname with the name of the database that you want to create:

createdb -O user dbname

PostgreSQL users that have permission to create databases can do so from their own accounts by typing the following command, where dbname is the name of the database to create:

createdb dbname

Adding an existing user to a database

To grant an existing user privileges to a database, follow these steps:

1 - Run the psql program as the database’s owner, or as the postgres superuser.

2 - Type the following command. Replace permissions with the permissions you want to grant, dbname with the name of the database, and username with the user:

GRANT permissions ON DATABASE dbname TO username;

For detailed information about the access privileges that are available in PostgreSQL, please visit PostgreSQL website.

3 - The user can now access the database with the specified permissions.

Deleting PostgreSQL databases

Similar to the createdb command for creating databases, there is the dropdb command for deleting databases. To delete a database, you must be the owner or have superuser privileges.

Type the following command, replacing dbname with the name of the database that you want to delete:

dropdb dbname

Join The Conversation

Please to post.