PostgreSQL is a Relational Database Management System used as a data warehouse for various Software Applications. PostgreSQL is faster at processing read-write queries, especially in Larger Datasets where complex queries are required. Postgres is a cross-platform database that can be installed on Ubuntu to perform different database operations.
This article discusses how you can install PostgreSQL in Ubuntu 22.04.
How to Install PostgreSQL in Ubuntu 22.04?
Ubuntu is a Linux-based operating system that allows us to install and use different software applications securely and efficiently. PostgreSQL can be installed on Ubuntu by executing the “apt” command. Once installed, you can access the Postgres Account. You will also be able to access the Databases. Follow the steps below to install PostgreSQL in Ubuntu 22.04.
Step 1: Update Packages
Before installing a new package in Ubuntu, update the existing Ubuntu packages repository:
Once the packages are updated, upgrade the packages that require upgradation using the below-provided command:
The “-y” option confirms the upgradation of packages:
Step 2: Install PostgreSQL
After updating the existing Ubuntu Packages, Install PostgreSQL from the PostgreSQL repository. To Install PostgreSQL, use the “apt install” command with sudo privileges:
The Terminal will ask for additional disk space. Enter “Y” to continue with the installation process:
Step 3: Using PostgreSQL
Once the PostgreSQL packages are installed, you must switch to the PostgreSQL Account to use it.
Switch to Postgres Account
To switch to the PostgreSQL account in the Terminal, use the “postgres” command with the “-iu” option:
The “-i” option will ensure the start of the shell with root privileges. The Terminal command line will change to the “postgres” command line ensuring that you are now using PostgreSQL:
Access Postgres Prompt
Now to access the PostgreSQL prompt, use the “psql” command which will allow you to run SQL queries in the Terminal:
Once you run the “psql” command, the command line will change to the “postgres=#” meaning you can now use the SQL queries inside the Terminal from the Postgres Account:
Logout of Postgres Account:
You can simply log out of the Postgres Account with the symbol “\” followed by the alphabet “q”. Run the command below to log out from the Postgres Account:
This will switch back to PostgreSQL meaning you can now run queries directly from the terminal without having to access the Postgres Prompt:
You can also log out or exit from PostgreSQL with the “exit” command:
The command line changes to your username meaning you are no longer using the PostgreSQL Account:
Directly access the Postgres Prompt
Instead of logging in to PostgreSQL and then accessing the Postgres Prompt to run queries, you can directly access the Postgres Prompt as well. To access the Postgres Prompt directly, use the “postgres” and “psql” command:
The command line directly changes to the Postgres Prompt:
Creating a New Role
A New Role in PostgreSQL is important for performing different operations in the database. Also, with the New Role, privileges for each role can be changed. A new role can be added to the Postgres Account. To create a New Role, log in to your PostgreSQL with the below-stated command:
The PostgreSQL command line will appear in the Terminal:
To create a new role, the “createuser” command is used. The “–interactive” option will ask for the new role permissions:
The “–interactive” command will ensure that the command line asks for the new user or new role name and superuser permission. Enter the name, and permission and then hit “Enter”:
You can also use the command below without having to log in to PostgreSQL to create a new role:
This will directly ask for the new role details in the Terminal:
Creating a New Database
The PostgreSQL Database is used as a data warehouse for numerous applications. To create a new database in PostgreSQL, log in to your Postgres Account and execute the following “createdb” Command:
The command line in the Terminal moves to the next line indicating that the database was created successfully:
Connecting to a Database
From the PostgreSQL Prompt, the Postgres account database can also be accessed. Once the database is accessed, the Postgres User can perform different queries in the Database. To connect to the Database, the “psql” command is used along with the Database Name:
In our case, the Database Name is “darren”. Thus to access the “darren” database, ensure that you have accessed the PostgreSQL Prompt. To access the “darren” database, the “psql” command will be:
The option “-d” ensures access to the Database of the Postgres User “darren”. Once accessed, the command line changes from “=#” to “-#” for Database:
After you’ve accessed the Postgres Account Database, you can check the information of the Database as well. To list the Database Details, the “conninfo” command is used:
In our case, we listed the “darren” database details:
List all Databases in PostgreSQL
All the Databases created inside the PostgreSQL can also be listed in the Ubuntu Terminal. To list the databases in PostgreSQL ensure that you have accessed the Postgres Prompt and then use “\list” to list all databases:
In our case, we have Six PostgreSQL Databases in total:
List all Postgres Users in PostgreSQL
Similar to listing all the Databases in PostgreSQL, all the Postgres Users can also be listed. To list all the Postgres User Accounts, the “\du” command is used. Before listing the User Accounts, ensure that you are logged in to the Postgres Prompt. Once logged in, use the command:
In our case, we have Three Postgres User Roles:
Uninstalling PostgreSQL
After you are done with using PostgreSQL and you do not require Postgres anymore, you can uninstall it to free up some space on your Ubuntu System. To uninstall PostgreSQL, use the “remove” command:
Enter “Y” during uninstalling to continue with the removal process:
That’s all about installing, using, and uninstalling PostgreSQL on Ubuntu 22.04.
Conclusion
PostgreSQL can be installed on Ubuntu 22.04 using the “apt install postgresql” command. Once installed, you can create new roles, create new databases, and access databases from your Ubuntu System to perform queries. This article discusses the installation and usage of PostgreSQL in Ubuntu 22.04.
