PostgreSQL is a powerful and open-source relational database management system. It is widely used for data storage and retrieval in modern applications.
In this guide, we will learn how to install PostgreSQL on Ubuntu, create a new role, and a new database.
Step 1 – Update Ubuntu’s package index
Before installing any package in Ubuntu, it’s always a good practice to update the system’s package index. This ensures that you have the latest package information and dependencies required for installation.
Open a terminal and run the following command:
$ sudo apt update
Step 2 – Install PostgreSQL
To install PostgreSQL on Ubuntu, run the following command:
$ sudo apt install postgresql postgresql-contrib
This command will install the PostgreSQL server and client packages along with additional contrib packages.
During the installation process, you will be prompted to create a password for the PostgreSQL database superuser (postgres).
Step 3 – Verify PostgreSQL installation
To verify that PostgreSQL is installed and running, you can run the following command:
$ sudo systemctl status postgresql
This command will show you the status of the PostgreSQL service. If the service is running, you should see a message indicating that it’s active.
Step 4 – Access PostgreSQL
To access PostgreSQL, you can use the psql command-line interface. To start the psql shell, run the following command:
$ sudo -u postgres psql
This will log you in as the PostgreSQL superuser (postgres) and give you access to the PostgreSQL command-line interface.
Step 5 – Creating a New Role
A role is a user account that can log in to a PostgreSQL database cluster. To create a new role, follow these steps:
In the PostgreSQL command-line interface, type the following command to create a new role:
CREATE ROLE newuser WITH LOGIN PASSWORD 'password';
Replace newuser with the name of the new user you want to create, and password with a secure password for the user.
To grant the new role permission to create databases, type the following command:
ALTER ROLE newuser CREATEDB;
This will allow the new user to create databases.
To exit the PostgreSQL command-line interface, type \q and press Enter.
Step 6 – Creating a New Database
To create a new database in PostgreSQL, follow these steps:
Log in to the PostgreSQL command-line interface as the superuser (postgres) by running the following command:
$ sudo -u postgres psql
Type the following command to create a new database:
CREATE DATABASE newdatabase;
Replace newdatabase with the name of the new database you want to create.
To grant permission to the new user (newuser) to access the new database (newdatabase), type the following command:
GRANTALL PRIVILEGES ON DATABASE newdatabase TO newuser;
This will give the new user full access to the new database.
To exit the PostgreSQL command-line interface, type \q and press Enter.
Conclusion
Congratulations! You have successfully installed PostgreSQL on Ubuntu, created a new role, and a new database. You can now start using PostgreSQL for your data storage and retrieval needs.