How to Install PostgreSQL on Ubuntu

PostgreSQL is a well-known relational database management system that provides high-availability features. These are renowned for their functionalities, such as support for complex statements, views, triggers, and foreign keys. PostgreSQL is installed on Ubuntu to provide a secure as well as flexible DB infrastructure. By installing it on the Linux distribution, you can enable and deal with the vast data in an efficient and secure manner. This infrastructure is designed to deal with different workloads, from single-machine apps to large internet-facing apps with multiple concurrent users.

In this tutorial, we will walk through installing and setting up PostgreSQL on Ubuntu.

Installing PostgreSQL on Ubuntu

Copy link

Ubuntu comes with the PostgreSQL package in its default repositories. You can install it on a Linux system following the given steps. It downloads the stable package. This is important for DB analysts, administrators, and industries that require a dependable DB solution for managing a large volume of data. 

And if you’re looking for a reliable, high-performance, and budget-friendly solution for your workflows, Hostman has you covered with Linux VPS Hosting options, including Debian VPS, Ubuntu VPS, and VPS CentOS.

Let’s move forward into each step of installing PostgreSQL with detailed explanations on Ubuntu.

Step 1: Refresh Ubuntu Repository

Copy link

First, refresh the Ubuntu package indexes. It is useful for maintaining system stability and security:

sudo apt update

Step 2: PostgreSQL Installation 

Copy link

PostgreSQL is included in Ubuntu's default repositories, making installation straightforward. To install it with some additional tools, execute the below command:

sudo apt install -y postgresql postgresql-contrib

Here:

• postgresql: The core PostgreSQL DB server.
• postgresql-contrib: Additional utilities and extensions useful for DB.

Step 3: Start and Enable Services

Copy link

It is essential to enable the PostgreSQL service after installation and ensure it starts automatically upon bootup. The installed package utilizes the systemd daemon, which deals with the DB server runtime operations. 

Run the commands below to make sure the DB server automatically initializes when the server boots up.

Let’s initialize the service:

sudo systemctl start postgresql

And enable it so that it launches automatically when the machine boots up:

sudo systemctl enable postgresql

Step 4: Verify Installation

Copy link

To verify if the installed package is operating properly, check out its status through the below:

sudo systemctl status postgresql

In the figure, you can see an active status. It represents the services that are correctly running.

Step 5: Access Shell

Copy link

Finally, switch to the PostgreSQL user account and launch the CLI to interact with database operations. Here, the -i flag provides an interactive login shell:

sudo -i -u postgres

The above display gives access to a user account.

Once you're in the user environment, launch the CLI through the given command:

psql

As you can see in the above screenshot, this command launchs the CLI, where users can perform multiple operations by executing commands.

Step 6: Basic Database Commands

Copy link

The creation of a database and a new user for any application is a good practice rather than using the root. In this way, a particular user can access the shell interface and run basic statements on the particular database. Here are general commands with thorough explanations and examples:

1. Creation of the New Database

To prevent conflicts, ensure data integrity and control access privileges for different applications or users, you need to create a new database. For creation, use the given command within the shell:

CREATE DATABASE hostmandb;

The screenshot shows that a database has been created.

2. Creation of the New User

Create a customized user and set a strong password:

CREATE USER hostman_user WITH ENCRYPTED PASSWORD 'Qwer1234';

We have created a hostman_user with the password Qwer1234.

3. Give Privileges to the Created User

You can grant hostman_user complete privileges to the hostmandb through the given command. This allows controlled accessibility to database objects, enhancing data integrity as well as security:

GRANT ALL PRIVILEGES ON DATABASE hostmandb TO hostman_user;

4. Exit Shell

For closing the shell, you can utilize the \q option as below:

\q

The figure shows that the shell has been exited.

Finally, you can exit the user environment by executing the given command:

exit

The output confirms that you have returned to the main system user account.

5. Access a Database as the Particular User

You can connect to the particular database from the specific user account through the -U flag:

psql -U hostman_user -d hostmandb -h 127.0.0.1 -p 5432

Here:

• -U hostman_user: Indicates the user.
• -d hostmandb: Indicates the database name.
• -h 127.0.0.1: Indicates the host (local machine).
• -p 5432: Indicates the PostgreSQL default port.

In the given output, you can see that the user hostman_user has accessed the hostmandb console.

6. Listing of All Databases

View all databases and check whether the hostmandb database is available or not:

\l

In the figure, you can see that the database hostmandb is listed.

7. Switch/Connect to the Particular Database

To switch to a specific database, such as hostmandb, within PostgreSQL, use the \c command:

\c hostmandb

The above output confirms that the postgres user has successfully connected to the hostmandb database.

8. Creation of the Particular Table

To create a customized table, use the below statement. In our example, we create a hostmantb table:

CREATE TABLE hostmantb(
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    department_id INTEGER
);

Here:

• id SERIAL PRIMARY KEY: It creates an auto-incrementing primary key column with the name id.

• name VARCHAR(100): It creates a name column that can store up to 100 characters.

• age INTEGER: It creates an age column that stores integer values.

• department_id INTEGER: This line creates a department_id column that stores integer values.

The outcome of the above screenshot confirms that the table hostmantb is successfully created with specific attributes.

9. List All Tables in the Particular Database

For listing all tables in the hostmandb database, use the \dt command:

\dt

It displays all tables as well as confirms that the hostmantb is listed above.

10. Overview a Table's Structure

You can view the structure of the created table through the \d command. Let’s display the structure of hostmantb table:

\d hostmantb

In the above figure, you can see the complete structure of hostmantb table with id, name, age, and department_id having specific types.

11. Input Entries into a Particular Table

To input entries into a hostmantb table, use the below statement. This way, we input the values Peter, 35, and 1 to the columns name, age, and department_id, respectively:

INSERT INTO hostmantb(name, age, department_id) VALUES ('Peter', 35, 1);

The output confirms that we have successfully input data into the hostmantb table.

12. Select All Data from a Particular Table

You can select all data from the hostmantb table through the below statement:

SELECT * FROM hostmantb;

13. Modify Data in Particular Table

For modifying data in hostmantb, you can utilize the below statement. For example, set the age column to 40 for the row where the name column equals Peter:

UPDATE hostmantb SET age = 40 WHERE name = 'Peter';

The output confirms that the age column is updated.

14. Remove Data from the Particular Table

In this section, you can remove data from the specific table through the DELETE statement. It deletes all entries from the hostmantb table where the value in the name column is Peter:

DELETE FROM hostmantb WHERE name = 'Peter';

In the screenshot, one entry from the hostmantb table is removed.

15. Exit CLI

You can easily exit the CLI via the \q utility:

\q

This tutorial has given you thorough guidelines for every step involved in installing as well as setting PostgreSQL on Ubuntu. 

Conclusion

Copy link

By installing PostgreSQL on Ubuntu, you can optimize the DB infrastructure. PostgreSQL is often chosen for its freely available nature, which allows for customization, the system's stability and security features. In addition, PostgreSQL's supporters' help and comprehensive guide make it simpler to enhance operations and troubleshoot problems. 

This combination makes sure that the DB system is both powerful as well as adaptable to several needs. In this tutorial, there are a lot of fundamental DB commands to get you started with database administration. Therefore, you will be able to create databases, and users and perform basic tasks.

This can be also be useful when creating your own virtual private network.