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.
Table of Contents
- What is PostgreSQL?
- How to Install PostgreSQL on Ubuntu?
- What are the Basic PostgreSQL Commands?
What is PostgreSQL (Postgres)?
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 ETL Using Hevo’s No-code Data Pipeline
Hevo Data helps you directly transfer data from PostgreSQL and 100+ other data sources (including 30+ free sources) to Business Intelligence tools, Data Warehouses, or a destination of your choice in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.
Hevo takes care of all your data preprocessing needs required to set up the integration and lets you focus on key business activities and draw a much more powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination.Get Started with Hevo for Free
Check out what makes Hevo amazing:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, E-Mail, and support calls.Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
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.
This is the interactive terminal for PostgreSQL.
This quits psql and takes the user back to the regular command line.
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;
createuser Janet; dropuser Janet;
Only superusers and users with CREATEROLE privilege can create a new user.
the command will list out all the existing roles.
2. CREATING A NEW DATABASE
This command lets us create a new database.
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;
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.
In this tutorial, you learned how to install Postgres Ubuntu and other commands to manipulate the database and table. 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.Visit our Website to Explore Hevo
We at Hevo Data are building the most robust and comprehensive ETL solution in the industry. We integrate with your in-house databases, cloud apps, flat files, and the clickstream. It is a No-code Data Pipeline solution that can help you load and transform data from any data source to a desired destination such as Data Warehouse. Want to know more. Reach out to us.
Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.
Share your experience of installing PostgreSQL on Ubuntu in the comments section below!