Relational database management systems (RDBMS) have proven to be a key component of many websites and applications, as they provide a structured way to store, organize, and access information.
In this article, we will discuss PostgreSQL in detail, along with a step-by-step guide on installing and configuring PostgreSQL on Ubuntu.
What Is PostgreSQL?
PostgreSQL is an open-source database management system that supports SQL. Using PostgreSQL, developers can build fraud-tolerant applications as it provides excellent data management resources to the database administrator.
This platform gives you the mobility to define your own data sets, develop custom fonts, and merge code written in different programming languages. PostgreSQL is highly scalable in terms of data quantities and the number of concurrent users on a project.
Let’s look at the PostgreSQL installation process for Ubuntu 21.04.
Step 1: Install PostgreSQL on Ubuntu
Some PostgreSQL packages are present in the default Ubuntu repository. To install PostgreSQL via the command line, type:
sudo apt install postgresql postgresql-contrib
Verify the Installation
You can find the location of the configuration file using the ls command. This is a verification step that confirms whether PostgreSQL was successfully installed on your system or not.
The number 12 denotes the version of PostgreSQL. It might be different for you depending on the package you've downloaded on your system.
Check the PostgreSQL Status
After installation, check the status of PostgreSQL using the following command:
service postgresql status
The output would look like this:
If the output displays the active status, then the PostgreSQL service is running on your system. On the other hand, if the status is inactive, then you need to start the service by typing:
service postgresql start
Apart from status and start, there are several other PostgreSQL commands that you can use:
Step 2: Log In As a Super-User
Before proceeding further, you need to log in as a database superuser on the PostgreSQL server. One of the simplest ways to connect as a PostgreSQL user is to change your hostname to the postgres Unix user.
Set Root User Credentials
Login to PostgreSQL interactive shell using the command:
sudo -u postgres psql
Set the root user credentials using the following query:
ALTER USER postgres PASSWORD 'newpassword';
Make sure to replace newpassword with a strong password of your choice. Type exit to quit the interactive shell.
Login to psql with the following command:
psql -U postgres -h localhost
Enter the new root password for the user when the prompt appears.
Step 3: Connect to the PostgreSQL Server
When you install PostgreSQL, the platform creates a default user postgres and a system account with the same name. You need to log in as the user postgres to connect to the PostgreSQL server.
Use the following command to log in to the PostgreSQL server:
sudo su postgres
As soon as you run this command, you will notice a change in the way the system displays your hostname. The bash prompt will look like this:
This shows that you have successfully logged in as a PostgresSQL user.
How to Manage PostgreSQL Users
Now that you have connected to the server, it is time to create new users. Type psql to start running commands on the PostgreSQL server.
Create a New User
If there are multiple team members working on different levels within a project, you will need to create different roles for different employees and assign them their accesses. Use the CREATE USER command to create a new user profile:
CREATE USER user1 WITH PASSWORD 'test123';
In the command above, user1 is the username you want for the new user followed by test123, which is the password for this user.
To check the list of new users added to a database, use the \du command.
As you can see in the output above, there are no privileges available for the new user yet.
Grant Superuser Privileges to New Users
To add a set of privileges to a new user, run the following command:
ALTER USER user1 WITH SUPERUSER;
The ALTER command will grant administrative privileges to the new member. Run the /du command again to verify if the new user has the required set of superuser privileges.
Drop a User From the List of Users
To remove a user from the list of authorized users, use the following command:
DROP USER user1;
Verify the change by listing out the users with the /du command.
How to Manage PostgreSQL Databases
PostgreSQL provides its users with several commands to create and remove databases.
Add or Remove a Database
To create a new database using PostgreSQL:
CREATE DATABASE db1;
…where db1 is the name of the database you want to create. Use the \l command to get a list of all the available databases.
If you want to remove a database, use the DROP command:
DROP DATABASE db1;
Grant Database Access to Users
You can grant database access to a user using the GRANT command:
GRANT ALL PRIVILEGES ON DATABASE db1 TO user1;
Get Command-Line Help for PostgreSQL
To know more about PostgreSQL and how to use its various commands, you can open the help page by typing the following command in the terminal:
Recommended Step: Install pgAdmin
Another recommended step is to install pgAdmin. PgAdmin is one of the most popular and feature-rich open-source administration tools available for PostgreSQL. While installing pgAdmin is an optional step, you should install it to manage users and databases in a better way.
To start, add the official pgAdmin repository and its key to your system:
curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key addsudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
Now, to install the desktop version:
sudo apt install pgadmin4-desktop
To install the web version, type:
sudo apt install pgadmin4-web
To configure web mode, run the setup-web.sh script provided by pgAdmin:
Follow the on-screen instructions to complete the process. Rest assured, this is just a one-time step, so you don't have to worry about installing and configuring this again and again.
Managing Databases on Ubuntu Using PostgreSQL
PostgreSQL is a powerful platform for creating database management applications. The ability to process any quantity of data on the platform is one of its biggest highlights. The installation process boils down to the initial downloading, installing, and finally logging in to the database.
With a few simple commands, you can master the process of adding new users, creating databases, and further on adding users to existing databases. Not sure if you like PostgreSQL? Try installing Microsoft SQL Server on your machine.