PostgreSQL, also known as Postgres, is a relational database management system that emphasizes extensibility and SQL compliance. Ubuntu is a Debian-based Linux distribution that uses mostly free and open-source software.

This article talks about how to install Postgres on Ubuntu extensively. In addition to that, it gives a brief introduction to PostgreSQL.

What is PostgreSQL (Postgres)?

Postgresql logo

PostgreSQL relational database system is a powerful, scalable, and standards-compliant open-source database platform. It offers the standard SQL features as well as some modern ones like triggers, and transaction integrity. Companies and individual developers install Postgres Ubuntu because it gives them flexibility and is easy to manage. This tutorial will help you install PostgreSQL on Ubuntu and get a hang of the basic commands.

Key Features of Postgres

  • Customizable: PostgreSQL can be customized by developing plugins to make the Database Management System fit your requirements. PostgreSQL also allows you to incorporate custom functions that are made with other programming languages like Java, C, C++, etc.
  • Long History: PostgreSQL has been around for more than 30 years, having been initially released in 1988.
  • Frequent Updates: The most recent PostgreSQL update was Version 13.1 on 12 November 2020.  
  • MVCC Features: PostgreSQL happens to be the first Database Management System to implement Multi-Version Concurrency Control (MVCC) features.
  • A Supportive Community: A dedicated community is always at your disposal. Private, third-party support services are available as well. The community updates the PostgreSQL platform via the PostgreSQL Global Development Group.
  • Open-Source: This is an Object-Relational Database Management System(ORDBMS). This allows PostgreSQL to provide Object-Oriented and Relational Database functionality. PostgreSQL is a free and Open-Source ORDBMS.
  • Users: PostgreSQL users include Apple, Cisco, Etsy, Facebook, Instagram, Red Hat, Skype, Spotify, Yahoo to name a few

How to Install Postgres on Ubuntu?

Ubuntu contains PostgreSQL by default. We can get it by using the apt command. By following the given commands below one can easily install Postgres ubuntu.

sudo apt-get update

sudo apt-get install postgresql postgresql-contrib

This installs the latest version of PostgreSQL on Ubuntu and the commonly used add-ons for it.

Simplify PostgreSQL ETL Using Hevo’s No-code Data Pipeline

Say goodbye to manual data pipelines—Hevo simplifies PostgreSQL data migration, offering automation, scalability, and unparalleled reliability for all your data needs. With Hevo, you can:

  1. Effortlessly extract data from PostgreSQL and other 150+ connectors
  2. Tailor your data to your destination’s needs with features like drag-and-drop and custom Python scripts.
  3. Achieve lightning-fast data loading, making your data analysis-ready.

Try to see why customers like ThoughtSpot and Harmoney have upgraded to a powerful data and analytics stack by incorporating Hevo! 

Get Started with Hevo for Free

What are the Basic PostgreSQL Commands?

Now that you are done with installing Postgres Ubuntu. In this section, you will learn some of the basic commands to get started with PostgreSQL.

1. CREATING A NEW ROLE

PostgreSQL manages database access permissions using the concept of roles. Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects.

The default role for PostgreSQL is called Postgres which was created during installation. We need to log in to this account to use PostgreSQL.

sudo -i -u postgres

This will ask for the user account password which is the same as the login password.

psql

This is the interactive terminal for PostgreSQL.

q

This quits psql and takes the user back to the regular command line.

psql -l 

can be used to check all the databases.

We can connect to a different database by using –d.

psql -d postgres 

Since the Postgres Linux account has all the privileges it can create and delete additional roles.

We can create and delete roles by these commands:

createuser user_name;

delete user_name;

Example:

createuser Janet;

dropuser Janet;

Only superusers and users with CREATEROLE privilege can create a new user.

du 

the command will list out all the existing roles.

2. CREATING A NEW DATABASE

This command lets us create a new database.

createdb company;

Normally, the database user who executes this command becomes the owner of the new database. However, a different owner can be specified via the –O option, if the executing user has appropriate privileges.

We can change the Linux account to the company by typing:

sudo -i -u Joe;

(Assuming we have a user called Joe)

Normally the superuser postgre is the default user.

3. CREATING A NEW TABLE

Now that we have a database, let’s create a new table.

CREATE TABLE Employee(ID int PRIMARY KEY,Name varchar(20),Division varchar(20), City varchar(20), Salary float);

d command can be used for verification of the table created.

It lists all the tables in the current database.

d table_name is used for the description of each table.

4. GRANTING PRIVILEGES

Whenever an object is created in a database, an owner is assigned to it. The owner is usually the one who executed the creation statement. For most kinds of objects, the initial state is that only the owner (or a superuser) can modify or delete the object. To allow other roles or users to use it, privileges or permission must be granted.

GRANT ALL ON employee TO company;

REVOKE DELETE FROM company;

The privileges are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE.

5. ADDING, QUERYING, AND DELETING DATA IN A TABLE

Let’s add some employees’ information to the database.

INSERT INTO Employee values (101,'Ray Alexson','Engineering','California',100000);

INSERT INTO Employee values(102,'Riley Mason','Marketing','New York',90000);

INSERT INTO Employee values(103,'Shawn Price','Marketing','New York',11000) ;

We can use SELECT statements to ask

SELECT * FROM Employee;

DELETE query is used to delete the records from the table.

DELETE FROM Employee WHERE Salary >= 100000;
Integrate PostgreSQL to Amazon S3
Integrate PostgreSQL to BigQuery
Integrate Amazon Ads to PostgreSQL

6. UPDATING DATA IN A TABLE

UPDATE Query is used to altering the existing records in the table.

Let’s update the salary of all employees in the Marketing division to $100000.

UPDATE Employee SET Salary = 100000 WHERE Division = ‘Marketing’;

7. DROPPING A TABLE

DROP TABLE Employee;

We can drop multiple tables at the same time.

DROP TABLE Employee, Employee_salary;

8. ALTERING THE TABLE COLUMNS

We can add new columns in a table using ADD command with ALTER TABLE.

Let’s add a new column denoting the work experience of a particular employee.

ALTER TABLE Employee ADD Work_Experience int;

We can delete columns in a table using the DROP command with ALTER TABLE.

Let’s drop the city information of each employee.

ALTER TABLE Employee DROP COLUMN City;

9. DROPPING A DATABASE

We can drop the entire database with dropdb command.

dropdb dbname;

Conclusion

Installing PostgreSQL on Ubuntu is a straightforward process that provides a robust foundation for your database needs. By following the steps outlined in this guide, you can easily set up and configure PostgreSQL, enabling you to harness its powerful features for managing and querying data effectively. Looking for similar tutorials on Ubuntu and Postgres? We have written these blogs which would interest you: How to install MongoDB on Ubuntu, How to set up Kafka on Ubuntu and Postgres to Snowflake.

Whether you’re a developer, data engineer, or system administrator, PostgreSQL’s scalability, reliability, and extensive feature set make it a top choice for diverse use cases, from web applications to data analytics. Ensure you follow best practices for security, regularly update your PostgreSQL version, and take advantage of its vast ecosystem of extensions to unlock its full potential.

Sign up for a 14-day free trial to start your journey with PostgreSQL today and leverage its capabilities to build high-performance, data-driven pipelines for smooth migration.

Frequently Asked Questions

1. How to install PostgreSQL in Ubuntu terminal?

Run the following code:
sudo apt update
sudo apt install postgresql postgresql-contrib

2. How to install PostgreSQL 13 on Ubuntu?

Add the PostgreSQL Repository and add install version 13:
sudo apt update
sudo apt install wget
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install postgresql-13

3. Is Postgres installed on Ubuntu?

You can check this by running:
psql --version

Sarad Mohanan
Software Engineer, Hevo Data

With over a decade of experience, Sarad has been instrumental in designing and developing Hevo's fundamental components. His expertise lies in building lean solutions for various software challenges. Sarad is passionate about mentoring fellow engineers and continually exploring new technologies to stay at the forefront of the industry. His dedication and innovative approach have made significant contributions to Hevo's success.